Pandas Save DataFrame to SQLite

Raymond Raymond visibility 10,476 event 2021-02-10 access_time 3 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'])
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 tru, index_label can be used to customize the column name. 
More from Kontext
copyright This page is subject to Site terms.
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts