
        
        
            
                
                
                    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

Published by Isshin Inada

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!