熊猫更新 sql

有没有办法做一个 SQL 更新-在哪里从一个数据框架没有迭代通过每一行？我有一个 postgreql 数据库，要从一个数据框架更新 db 中的一个表，我会使用 Psycopg2并执行以下操作:

con = psycopg2.connect(database='mydb', user='abc', password='xyz')
cur = con.cursor()


for index, row in df.iterrows():
sql = 'update table set column = %s where column = %s'
cur.execute(sql, (row['whatver'], row['something']))
con.commit()

但另一方面，如果我从 sql 读取一个表，或者将整个数据框架写入 sql (不更新-where) ，那么我只需要使用 Pandas 和 sql 炼金术。比如:

engine = create_engine('postgresql+psycopg2://user:pswd@mydb')
df.to_sql('table', engine, if_exists='append')

使用 to _ sql 的“一行程序”非常棒。是不是有一些类似的做一个更新-在哪里从熊猫到 postgreql？或者是唯一的方法，通过迭代每一行，就像我上面做的那样。对每一行进行迭代不是一种效率低下的方法吗？

109952 次浏览
小开

到目前为止，我还没有看到过熊猫 sql 连接器可以以任何可伸缩的方式用于 更新数据库数据的情况。建立这样一个系统看起来是个好主意，但实际上，对于操作性工作来说，它就是无法扩展。

我建议您使用 CSV 转储整个数据框架

df.to_csv('filename.csv', encoding='utf-8')

然后使用用于 PostgreSQL 的 收到或用于 MySQL 的 加载数据文件将 CSV 加载到数据库中。

如果在熊猫操纵数据时，您没有对有问题的表进行其他更改，那么您可以直接加载到该表中。

如果存在并发性问题，则必须将数据加载到临时表中，然后使用临时表更新主表。

在后一种情况下，您的主表需要有一个 datetime，它可以告诉您最新的修改是什么时候，这样您就可以确定您的“熊猫”更改是最新的，还是应该保留数据库更改。

小开

我想知道为什么不首先根据等式更新 df，然后将 df 存储到数据库中，您可以使用 if _ vis = ‘ place’来存储在同一个表中。

小开

看起来您正在使用存储在 df中的一些外部数据来更新数据库表。如果可能的话，为什么不只做一行 sql 更新呢？

如果您使用的是一个小型数据库(将整个数据加载到 python 数据帧对象并不会要了您的命) ，那么在使用 read_sql加载数据帧之后，可以肯定会有条件地更新数据帧。然后可以使用关键字 arg if_exists="replace"用新更新的表替换 DB 表。

df = pandas.read_sql("select * from your_table;", engine)


#update information (update your_table set column = "new value" where column = "old value")
#still may need to iterate for many old value/new value pairs
df[df['column'] == "old value", "column"] = "new value"


#send data back to sql
df.to_sql("your_table", engine, if_exists="replace")

熊猫是一个强大的工具，有限的 SQL 支持最初只是一个小特性。随着时间的推移，人们正试图使用大熊猫作为他们唯一的数据库接口软件。我不认为熊猫是数据库交互的终结者，但是有很多人一直在研究新的功能。见: https://github.com/pandas-dev/pandas/issues

小开

考虑一个临时表，它是你最终表的精确复制品，每次运行都会被清除:

engine = create_engine('postgresql+psycopg2://user:pswd@mydb')
df.to_sql('temp_table', engine, if_exists='replace')


sql = """
UPDATE final_table AS f
SET col1 = t.col1
FROM temp_table AS t
WHERE f.id = t.id
"""


with engine.begin() as conn:     # TRANSACTION
conn.execute(sql)
小开

如果列名没有更改，我倾向于删除所有行，然后将数据追加到现在为空的表中。否则，相关视图也必须重新生成:

from sqlalchemy import create_engine
from sqlalchemy import MetaData


engine = create_engine(f'postgresql://postgres:{pw}@localhost:5432/table')


# Get main table and delete all rows
# without deleting the table
meta = MetaData(engine)
meta.reflect(engine)
table = meta.tables['table']
del_st = table.delete()


conn = engine.connect()
res = conn.execute(del_st)


# Insert new data
df.to_sql('table', engine, if_exists='append', index=False)
小开

我尝试了第一个答案，发现它工作得不是很好，然后我改变了一些部分，通过使用熊猫 + sql 炼金术更新所有的情况。

def update_to_sql(self, table_name, key_name)
a = []
self.table = table_name
self.primary_key = key_name
for col in df.columns:
if col == self.primary_key:
continue
a.append("f.{col}=t.{col}".format(col=col))
df.to_sql('temporary_table', self.sql_engine, if_exists='replace', index=False)
update_stmt_1 = "UPDATE {final_table} AS f".format(final_table=self.table)
update_stmt_2 = " INNER JOIN (SELECT * FROM temporary_table) AS t ON t.{primary_key}=f.{primary_key} ".format(primary_key=self.primary_key)
update_stmt_3 = "SET "
update_stmt_4 = ", ".join(a)
update_stmt_5 = update_stmt_1 + update_stmt_2 + update_stmt_3 + update_stmt_4 + ";"
print(update_stmt_5)
with self.sql_engine.begin() as cnx:
cnx.execute(update_stmt_5)
小开

这里有一个方法，我发现有些干净。这是利用炼金术。它一次只更新一列，但很容易推广。

def dataframe_update(df, table, engine, primary_key, column):
md = MetaData(engine)
table = Table(table, md, autoload=True)
session = sessionmaker(bind=engine)()
for _, row in df.iterrows():
session.query(table).filter(table.columns[primary_key] == row[primary_key]).update({column: row[column]})
session.commit()

提交答案