Pandas Save DataFrame to SQLite

Create a pandas DataFrame

The following code snippet constructs a pandas DataFrame in memory:

import pandas as pd
users = {'ID': [1, 2, 3], 'Value': ['A', 'B', 'C']}
df = pd.DataFrame(users, columns=['ID', 'Value'])
print(df)

Establish a SQLite connection

Use sqlite3 package to create a database connection object:

import sqlite3
database = "../example.sqlite"
conn = sqlite3.connect(database)

Save data into SQLite database

We can use function to_sql of DataFrame to write data into a table in SQLite or any other SQL databases such as Oracle, SQL Server, MySQL, Teradata, etc.

df.to_sql(name='Users', con=conn)
conn.close()

The above code snippets creates a table Users in the example.sqlite database and then close the database connection.

About to_sql function

There are several optional parameters can be used.

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

• if_exists - {‘fail’, ‘replace’, ‘append’}. This parameter determines the behavior if the table exists. 

• index - whether to write DataFrame index as a column. The default is True. When it is true, index_label can be used to customize the column name.