search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook
thumb_up
0
thumb_down
0
chat_bubble_outline
0
auto_stories new
settings

Writing Pandas DataFrame to SQLite

Programming
chevron_right
Python
chevron_right
Pandas
chevron_right
Cookbooks
chevron_right
DataFrame Cookbooks
chevron_right
Miscellaneous Cookbook
schedule Mar 10, 2022
Last updated
local_offer PythonPandas
Tags
tocTable of Contents
expand_more

To create and interact with the SQLite database, we make use of the sqlite3 library.

Solution

Consider the following DataFrame:

import pandas as pd

df = pd.DataFrame({
"A":[3,4],
"B":[5.0,6.0],
"C":["c","c"],
"D":[True,False],
"E":pd.date_range("2021-12-25","2021-12-26")})

df
A B C D E
0 3 5.0 c True 2021-12-25
1 4 6.0 c False 2021-12-26

To store this as a table in SQLite:

import sqlite3

# Create a connection to the SQLite database
# Doesn't matter if the database does not yet exist
conn = sqlite3.connect('test_db.sqlite')
df.to_sql('employees', conn, if_exists='replace', index=False)
conn.close()

Here, note the following:

  • Running this script will create a new file called test_db.sqlite in the same directory as this script.

  • Columns A to D will have the correct type derived in the SQLite database, but column E, which is of datetime type, will have type unknown in SQLite since SQLite does not support datetime.

  • index=False is usually what you want because to_sql(~) will automatically add the DataFrame index as a new column.

Reading SQLite table as a Pandas DataFrame

To read back this SQLite table as a DataFrame:

conn = sqlite3.connect('test_db.sqlite')
df = pd.read_sql_query('SELECT * FROM employees', conn)
conn.close()
df
A B C D E
0 3 5.0 c 1 2021-12-25 00:00:00
1 4 6.0 c 0 2021-12-26 00:00:00

Note the following:

  • There exists a method called read_sql_table(~), but this is only relevant when you're using SQLAlchemy.

  • Column E is derived to be of type object instead of datetime - this is because SQLite does not support datetime. To parse column E as datetime, set the parse_dates arguments:

    conn = sqlite3.connect('test_db.sqlite')
    df = pd.read_sql_query('SELECT * FROM employees', conn, parse_dates=["E"])
    conn.close()
    df.dtypes
    index int64
    A int64
    B float64
    C object
    D int64
    E datetime64[ns]
    dtype: object
robocat
Published by Isshin Inada
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...