Pandas DataFrame to_sql() Method

Preparation

Before any data manipulation can occur, two (2) new libraries will require installation.

• The Pandas library enables access to/from a DataFrame.
• The Openpyxl library enables conversion to/from Excel.

To install these libraries, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

$ pip install openpyxl

Hit the <Enter> key on the keyboard to start the installation process.

If the installations were successful, a message displays in the terminal indicating the same.

Feel free to view the PyCharm installation guide for the required libraries.

• How to install Pandas on PyCharm

Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd
import openpyxl

DataFrame.to_sql()

The to_sql() method writes rows (records) from a DataFrame to a SQL database.

The syntax for this method is as follows.

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

This example reads in the countries.csv file. These records are then inserted into a Database using sqlite3. To follow along, click here to save this CSV file and move it to the current working directory.

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

c.execute('CREATE TABLE IF NOT EXISTS countries(Country text, Capital text, Population integer, Area integer)')
conn.commit()

df = pd.read_csv('countries.csv')
df.to_sql('countries', conn, if_exists='replace', index=False)
 
c.execute('SELECT * FROM countries')

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

• Line [1] creates a connection to the countries_database.
• Line [2] executes the code on Line [1] and creates the Database.
• Line [3] contains SQL code to create a database table containing the specified fields.
• Line [4] executes the code on Line [3] and creates the table.
• Line [5] reads in the countries.csv file to the DataFrame df.
• Line [6] converts the DataFrame to SQL and inserts all records into the Database.
• Line [7] executes the SQL SELECT command to retrieve all records from the Database.
• Line [8-9] instantiates a for loop and displays one (1) record/line from the Database.

Output

💡 Note: For additional details on SQL commands, click here.

More Pandas DataFrame Methods

Feel free to learn more about the previous and next pandas DataFrame methods (alphabetically) here:

Also, check out the full cheat sheet overview of all Pandas DataFrame methods.