How to get from Pandas DataFrame to SQL in just 3 steps

Steps to get from Pandas Data Frame to SQL.

Step 1: Create a DataFrame

Here is the code to create the DataFrame .

import pandas as pd

data = {'product_name': ['Computer','Tablet','Monitor','Printer'],
        'price': [900,300,450,150]
        }

df = pd.DataFrame(data, columns= ['product_name','price'])
print (df)

Step 2: Create a Database

For demonstration purposes, let’s create a simple database.

import sqlite3

conn = sqlite3.connect('test_database')
c = conn.cursor()

c.execute('CREATE TABLE IF NOT EXISTS products (product_name text, price number)')
conn.commit()

Step 3: Get from Pandas DataFrame to SQL

You can use the following syntax to get SQL from Pandas Data Frame to SQL :

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

Where ‘products’ is the table name created in step 2.

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 Data Frame.

conn = sqlite3.connect('test_database_2') 
c = conn.cursor()

Then, create the same products table .

c.execute('CREATE TABLE IF NOT EXISTS products (product_name text, price number)')
conn.commit()

Now, build DataFrame:

data = {'product_name': ['Computer','Tablet','Monitor','Printer'],
        'price': [900,300,450,150]
        }

df = pd.DataFrame(data, columns= ['product_name','price'])

Apply the code to go from the DataFrame .

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

You can then find the maximum price among all the products .

c.execute('''  
        SELECT * FROM products
        WHERE price = (SELECT max(price) FROM products)
          ''')

Finally, get back from SQL.

df = pd.DataFrame(c.fetchall(), columns=['product_name','price'])    
print (df)