Pandas Save DataFrame to SQLite

visibility 7,557 event 2021-02-10 access_time 2 years ago language English

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'])

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)
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 tru, index_label can be used to customize the column name. 
copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts