问题描述
我想将数据帧写入现有的 sqlite(或 mysql)表,有时数据帧将包含数据库中尚不存在的新列.我需要做什么才能避免抛出错误?有没有办法告诉 pandas 或 sqlalchemy 使用潜在的新列自动扩展数据库表?
I want to write a dataframe to an existing sqlite (or mysql) table and sometimes the dataframe will contain a new column that is not yet present in the database. What do I need to do to avoid this throwing an error? Is there a way to tell pandas or sqlalchemy to automatically expand the database table with potential new columns?
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table match_exact_both has no column named ....
推荐答案
这是我使用 mySQL 和 sqlalchemy 的解决方案.基本思想是,如果可能的话,我想附加到 SQL 数据库中,而不是重写整个内容,但是如果有一个新列,那么我可以在 Pandas 中合并数据,然后覆盖现有数据库.
Here is my solution using mySQL and sqlalchemy. The basic idea is that if possible I would like to append to the SQL database instead of re-writing the whole thing, but if there is a new column then I can combine the data in Pandas and then overwrite the existing database.
import pymysql
from sqlalchemy import create_engine
import pandas as pd
cnx = create_engine('mysql+pymysql://username:password@hostname/database_name')
try:
#this will fail if there is a new column
df.to_sql(name='sql_table', con=cnx, if_exists = 'append', index=False)
except:
data = pd.read_sql('SELECT * FROM sql_table', cnx)
df2 = pd.concat([data,df])
df2.to_sql(name='sql_table', con=cnx, if_exists = 'replace', index=False)
这篇关于pandas.to_sql 将新列添加到现有表中,自动添加新列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!