Pandas Save DataFrame to SQLite

event 2021-02-10 visibility 12,857 comment 0 insights
more_vert
insights Stats
Raymond Raymond Code Snippets & Tips

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. 

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts