Pandas Save DataFrame to SQLite
insights Stats
Code snippets and tips for various programming languages/frameworks. All code examples are under MIT or Apache 2.0 license unless specified otherwise.
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.