将 pandas 数据框插入 SQLite 表中 / 用数据框更新表

Question

我有一个从Excel导入的数据框和一个具有匹配列的SQL表格。 到目前为止，我一直在使用这些列作为列表来更新表格：

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))

然而，由于我在SQLite中拥有与我的DataFrame列匹配的表，我相信有一种方法可以使用我的DataFrame更新整个SQLite表。你有什么想法如何做到呢？

- Val10

Answer 1

我想你正在使用sqlite3包访问SQLite数据库。为什么不考虑使用SQLAlchemy呢？它能很好地与Pandas数据结构配合使用，来访问数据库。

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

接下来执行：

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')

在sqlite3的命令行界面：

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

资源：

• SQLAlchemy中的SQLite引擎配置

• DataFrame.to_sql函数

- mremes

---

谢谢您的回复！当我尝试插入以下代码时： 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')我遇到了以下错误：ValueError: invalid literal for int() with base 10: ''有什么想法吗？ - Val10

你确定你的路径指向数据库文件吗（通常数据库文件具有.db后缀）？我用一个虚拟的DataFrame运行了我建议的代码，一切都完美地运行了。然而，你的错误提示表明你的模式可能与DataFrame的模式不同。 - mremes

非常感谢！我按照您的方法操作了一下，成功了。现在唯一出现的错误是关于索引的： sqlite3.OperationalError: table SCHEDULE has no column named index 您知道我能否更改代码中的某些内容，以便它不会尝试向我的数据库表添加索引列吗？ - Val10

Answer 2

你也可以使用sqlite3将一个框架附加到现有的SQLite数据库中。

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

OP 的实际问题是更新现有的表格。在这种情况下，sqlite3 也很有用。例如，您可以使用 UPDATE FROM 语句来更新具有一个列的现有表格，而不是迭代式地进行 UPDATE。

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

- cottontail