How do you export a Pandas DataFrame to a SQL script that creates the table and loads the table with the data using INSERT INTO statements. I created a project in Jupyter Notebook using Anaconda Navigator. It’s called Pandas DataFrame to SQL Script.
Here is a solution over at Stackoverflow Generate SQL statements from a Pandas Dataframe. I have used Stackoverflow to base this post on.
import pandas as pd data = {'firstname': ['Bob', 'Sally', 'Suzie', 'Rowan'], 'amount': [12, 67, 33, 44]} df = pd.DataFrame(data) df
print (pd.io.sql.get_schema(df.reset_index(), 'people'))
CREATE TABLE "people" ( "index" INTEGER, "firstname" TEXT, "amount" INTEGER )
# if you don't want the index, do it this way without the reset_index() print (pd.io.sql.get_schema(df, 'people'))
CREATE TABLE "people" ( "firstname" TEXT, "amount" INTEGER )
Create a Python function.
# SQL_CREATE_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET) # SOURCE: source dataframe # TARGET: target table to be created in database SOURCE= df TARGET = 'people_2' def SQL_INSERT_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET): sql_texts = [] for index, row in SOURCE.iterrows(): sql_texts.append('INSERT INTO '+TARGET+' ('+ str(', '.join(SOURCE.columns))+ ') VALUES '+ str(tuple(row.values))) return sql_texts tx = SQL_INSERT_STATEMENT_FROM_DATAFRAME(SOURCE, TARGET) tx
["INSERT INTO people_2 (firstname, amount) VALUES ('Bob', 12)", "INSERT INTO people_2 (firstname, amount) VALUES ('Sally', 67)", "INSERT INTO people_2 (firstname, amount) VALUES ('Suzie', 33)", "INSERT INTO people_2 (firstname, amount) VALUES ('Rowan', 44)"]