Insert a pandas dataframe into a SQLite table / update a table with a dataframe

Question

So I have a dataframe imported from excel and an SQL Table with matching columns. So far I have been updating the table using the columns as lists:

Schedule_Frame = DataFrame(Sheet_Schedule)
Column_ID = Schedule_Frame['ID']
Column_list = list(Column_ID)

for i in range(len(Column_list)):
    miCursor.execute("UPDATE SCHEDULE SET ID=? WHERE rowid=?",(Column_list[i],i))

However, since what I have in SQLite is a table that matches my dataframe columns, I am sure that there is a way to update the whole SQLite table using my frame. Any ideas how to do it?

mremes · Accepted Answer · 2019-09-14 17:29:13Z

18

I think you're using sqlite3 package to access your SQLite database. How about using SQLAlchemy – which operates well with Pandas' data structures – to access the database?

from sqlalchemy import create_engine
engine = create_engine('sqlite:///<replace_this_with_path_to_db_file>', echo=False)

Then doing:

Schedule_Frame.to_sql('SCHEDULE', con=engine, if_exists='append')

Edit: Example code

from sqlalchemy import create_engine
import pandas as pd

engine = sqlalchemy.create_engine('sqlite:///my.db', echo=False)
df = pd.DataFrame([[1,2],[1,2]], columns=['a', 'b'])

df.to_sql('mytable', con=engine, if_exists='append')

In sqlite3 CLI:

sqlite> select * from 'mytable';
0|1|2
1|1|2

Resources:

edited Sep 14, 2019 at 17:29

answered Nov 7, 2018 at 12:05

mremes

2361 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Val10 Over a year ago

Thanks for your reply! When I go to it and insert my code as: from sqlalchemy import create_engine engine = create_engine('sqlite://C:/Users/Javier/Desktop/Holidays Tool/Scheduler RES', echo=False) Schedule_Frame.to_sql('SCHEDULE', con=engine, if_exists='append') I get the following error: ValueError: invalid literal for int() with base 10: '' Any ideas?

mremes Over a year ago

Are you sure your path points to the database file (usually database file has a .db suffix)? I ran the code I suggested with a dummy DataFrame and it all worked out perfectly. Your error suggest though that your schema might be different to the DataFrame's schema.

Val10 Over a year ago

Thanks a lot! I used it as your and it worked. The only error I am getting now it is regarding index: sqlite3.OperationalError: table SCHEDULE has no column named index Do you know if I can change something in the code so it doesnt try to add an index column to my table in the db??

cottontail · Accepted Answer · 2023-03-02 23:43:25Z

You can append a frame to an existing SQLite database using sqlite3 too.

import sqlite3
conn = sqlite3.connect('<path to DB>')
df.to_sql('schedule', conn, if_exists='append')
conn.close()

OP's actual problem was updating an existing table. In that case, sqlite3 is useful too. For example, instead of the iterative UPDATE to update an existing table with one column, you can use UPDATE FROM statement.

df = pd.DataFrame(...)                           # dataframe in memory
with sqlite3.connect('<path to DB>') as conn:
    df.to_sql('temporary_table', conn)           # write df into the database
    conn.execute("""
    UPDATE schedule 
    SET ID = temp.column_list
    FROM (SELECT column_list, row_id FROM temporary_table) AS temp 
    WHERE schedule.row_id = temp.row_id;
    """)                                         # update the schedule table in the database
    conn.execute('DROP TABLE temporary_table')   # drop df from the database

Collectives™ on Stack Overflow

Insert a pandas dataframe into a SQLite table / update a table with a dataframe

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related