access_time 6 months ago languageEnglish
more_vert

Pandas Save DataFrame to SQLite

visibility 395 comment 0

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

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext