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

Run the code below.

print (pd.io.sql.get_schema(df.reset_index(), 'people'))

This is what you get (shown below). It does the work for you.

CREATE TABLE "people" (
"index" INTEGER,
  "firstname" TEXT,
  "amount" INTEGER
)

Below is another way to generate the CREATE TABLE SQL script if you don’t want the Index column.

# 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. With this we get the INSERT INTO statements without the index. It does the work for you. For large tables this will save you a lot of work.

# 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

This is what you get.

["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)"]