Pandas DataFrame to SQL (with examples)

In this guide, I’ll show you how to get from Pandas DataFrame to SQL.

Here are the steps that you may follow.

Steps to get from Pandas DataFrame to SQL

Step 1: Create a DataFrame

To start, let’s create a DataFrame based on the following data about cars:

Brand	Price
Honda Civic	22000
Toyota Corolla	25000
Ford Focus	27000
Audi A4	35000

This is the code to create the DataFrame in Python:

from pandas import DataFrame

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

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

Once you run the code, you’ll get the following DataFrame:

Step 2: Create a Database

For demonstration purposes, I’ll create a simple database using sqlite3.

To start, you’ll need to import the sqlite3 package:

import sqlite3

Next, create the database. Here, I chose to create a database that is called: ‘TestDB1.db‘

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

Finally, create the ‘CARS’ table:

c.execute('CREATE TABLE CARS (Brand text, Price number)')
conn.commit()

The cars table will be used to store the cars information from the DataFrame.

Step 3: Get from Pandas DataFrame to SQL

You can use the following syntax to get from pandas DataFrame to SQL:

df.to_sql('CARS', conn, if_exists='replace', index = False)

Where CARS is the table name created in step 2.

Here is the full Python code to get from pandas DataFrame to SQL:

import sqlite3
from pandas import DataFrame

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

c.execute('CREATE TABLE CARS (Brand text, Price number)')
conn.commit()

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

df = DataFrame(Cars, columns= ['Brand', 'Price'])
df.to_sql('CARS', conn, if_exists='replace', index = False)
 
c.execute('''  
SELECT * FROM CARS
          ''')

for row in c.fetchall():
    print (row)

Run the code and you’ll get the following results:

Going from the DataFrame to SQL and then back to the DataFrame

Now let’s see how to go from the DataFrame to SQL, and then back to the DataFrame.

For this example, you can create a new database called: ‘TestDB2.db‘

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

Then, create the same CARS table using this syntax:

c.execute('CREATE TABLE CARS (Brand text, Price number)')
conn.commit()

Now, build the Cars DataFrame:

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

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

Apply the code to go from the DataFrame to SQL:

df.to_sql('CARS', conn, if_exists='replace', index = False)

You can then find the maximum price among all the brands using this query:

c.execute('''  
SELECT Brand, max(price) FROM CARS
          ''')

Finally, get back from SQL to the DataFrame:

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

Putting all the code components together:

import sqlite3
from pandas import DataFrame

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

c.execute('CREATE TABLE CARS (Brand text, Price number)')
conn.commit()

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

df = DataFrame(Cars, columns= ['Brand', 'Price'])
df.to_sql('CARS', conn, if_exists='replace', index = False)
 
c.execute('''  
SELECT Brand, max(price) FROM CARS
          ''')

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

#c.execute('DROP TABLE CARS')

Once you run the code in Python, you’ll get the brand with the maximum price: