Python - Save Dataframe to Database with SQL

/

Create DB file from DF

#https://datatofish.com/pandas-dataframe-to-sql/
#https://www.dataquest.io/blog/sql-insert-tutorial/
#https://stackoverflow.com/questions/62340498/open-database-files-db-using-python
#https://sqlitebrowser.org/dl/
#https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

import sqlite3
from pandas import DataFrame

#Connect to DB (or creates new one)-------------------------------------------------------------------------------------
conn = sqlite3.connect('TestDB1.db') #just opens connection, doesnt query it
c = conn.cursor() #Creating Cursor - which is needed to fetch results (get rows back) https://stackoverflow.com/questions/6318126/why-do-you-need-to-create-a-cursor-when-querying-a-sqlite-database



# #Create new table (blank this out if already created, else error)-------------------------------------------------------
# c.execute('CREATE TABLE CARS (Brand text, Price number)') #This can just be all columns in DF - name,type
# conn.commit()




#Shows all tables
table_list = [a for a in c.execute("SELECT name FROM sqlite_master WHERE type = 'table'")]
# here is you table list
print(table_list)

#Create Test DF - if already exists will append to bottom---------------------------------------------------------------

Cars = {'Brand': ['Ford Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [22000,25000,27000,35000]
        }

df = DataFrame(Cars, columns= ['Brand', 'Price'])

#Copy dataframe to SQL - need to change table names---------------------------------------------------------------------
df.to_sql('CARS', conn, if_exists = 'append', index = False,chunksize = 1000)

Make sure you uncomment Table for first run, after that comment it out else you will get an error.
If you run again, it will keep appending the DF to the DB file. If you ever add new columns, need to create table again.

Get DF from DB File

import sqlite3
import pandas as pd
from pandas import DataFrame

#If you want to write entire DB to DF-----------------------------------------------------------------------------------
conn = sqlite3.connect('TestDB1.db')
df = pd.read_sql_query("SELECT * FROM CARS", conn)

print(df)



#If you want to write DB to DF, but only select data--------------------------------------------------------------------
conn = sqlite3.connect('TestDB1.db') #just opens connection, doesnt query it
TableName = 'CARS' #Enter in table name

c = conn.cursor() #Creating Cursor - which is needed to fetch results (get rows back)

#Gets list of columns in Table
query = conn.execute("SELECT * From "+TableName)
cols = [column[0] for column in query.description]
print(cols)

#Gets max price from the price column
c.execute('''
SELECT Brand, max(price) FROM CARS
          ''')

#Puts in DF
df = DataFrame(c.fetchall(), columns=['Brand','Price'])
print (df)

First it just chucks all the data into a DF, second one selects filtered data and puts into the DF. Great if you have a huge DB!

Finally, Pandas does have an awesome library which includes saving DF to SQL! 

savingdf.to_sql('AU_STOCK', conn, if_exists='append', index=True, chunksize=1000) #If index is False, wont copy across index which in this case is date

To view your data in DB files, I recommend the opensource:
https://sqlitebrowser.org/