how make new database sqlite3 python

import sqlite3 # Create database conn = sqlite3.connect('tablename.db') c = conn.cursor() c.execute('''CREATE TABLE tablename(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, number REAL)''') conn.commit() conn.close() # Insert Values conn = sqlite3.connect('tablename.db') c = conn.cursor() c.execute("INSERT INTO tablename VALUES (?, ?)", (name, number)) conn.commit() conn.close() # Read Values conn = sqlite3.connect('tablename.db') c = conn.cursor() for row in c.execute('SELECT * FROM tablename'): print(row) number = [row[2] for row in c.execute('SELECT * FROM tablename')] conn.close()

create sqlite database python

import sqlite3
import pandas as pd
from pandas import DataFrame

conn = sqlite3.connect('TestDB.db')  
c = conn.cursor()

read_clients = pd.read_csv (r'C:UsersRonDesktopClientClient_14-JAN-2019.csv')
read_clients.to_sql('CLIENTS', conn, if_exists='append', index = False) # Insert the values from the csv file into the table 'CLIENTS' 

read_country = pd.read_csv (r'C:UsersRonDesktopClientCountry_14-JAN-2019.csv')
read_country.to_sql('COUNTRY', conn, if_exists='replace', index = False) # Replace the values from the csv file into the table 'COUNTRY'

# When reading the csv:
# - Place 'r' before the path string to read any special characters, such as ''
# - Don't forget to put the file name at the end of the path + '.csv'
# - Before running the code, make sure that the column names in the CSV files match with the column names in the tables created and in the query below
# - If needed make sure that all the columns are in a TEXT format

c.execute('''
INSERT INTO DAILY_STATUS (Client_Name,Country_Name,Date)
SELECT DISTINCT clt.Client_Name, ctr.Country_Name, clt.Date
FROM CLIENTS clt
LEFT JOIN COUNTRY ctr ON clt.Country_ID = ctr.Country_ID
          ''')

c.execute('''
SELECT DISTINCT *
FROM DAILY_STATUS
WHERE Date = (SELECT max(Date) FROM DAILY_STATUS)
          ''')
   
#print(c.fetchall())

df = DataFrame(c.fetchall(), columns=['Client_Name','Country_Name','Date'])
print (df) # To display the results after an insert query, you'll need to add this type of syntax above: 'c.execute(''' SELECT * from latest table ''')

df.to_sql('DAILY_STATUS', conn, if_exists='append', index = False) # Insert the values from the INSERT QUERY into the table 'DAILY_STATUS'

# export_csv = df.to_csv (r'C:UsersRonDesktopClientexport_list.csv', index = None, header=True) # Uncomment this syntax if you wish to export the results to CSV. Make sure to adjust the path name
# Don't forget to add '.csv' at the end of the path (as well as r at the beg to address special characters)

Posted by: Guest on April-21-2021

Source

Code answers related to "how make new database sqlite3 python"

Browse Popular Code Answers by Language

Answers for "how make new database sqlite3 python"

Code answers related to "how make new database sqlite3 python"

Code answers related to "SQL"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us