Pandas更新sql

74 人关注

有什么方法可以从一个数据框架中进行SQL更新-where，而不需要迭代每一行？我有一个postgresql数据库，如果要从一个数据框架中更新一个表，我会使用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中（没有update-where），那么我将直接使用pandas和sqlalchemy。就像这样。
engine = create_engine('postgresql+psycopg2://user:pswd@mydb')
df.to_sql('table', engine, if_exists='append')
有一个使用to_sql的 "单线程 "就很不错了。难道就没有类似的东西来从pandas到postgresql做一个update-where吗？还是像我上面做的那样，只能通过迭代每一行来实现。遍历每一行不是一种低效的方法吗？


           
            
             你能把数据框架写到一个带有
             
              to_sql
             
             的临时表里，然后在postgresql里做UPDATE吗？


         
          python


         
          postgresql


         
          pandas


          
           已采纳


          
           
            考虑一个临时表，它将是最终表的精确复制品，每次运行都会被清理掉。
           
           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)


           
            
             
              Luke Rehmann
             
             ：


           
            
             
              或者，"REPLACE INTO final_table (SELECT * FROM temp_table)"，它将根据final_table的唯一键更新所有列（并插入新的列）。


           
            
             
              Does Postgres support
              
               REPLACE INTO
              
              ?


           
            
             
              @cqcn1991, 你必须在
              
               UPDATE
              
              中为每一列添加
              
               final_col = temp_col
              
              条款。这里只显示一列。然后就可以一次性运行这个动作了。


           
            
             
              Georgi Ivanov Dimitrov
             
             ：


           
            
             
              我正在寻找解决同样的问题。当我尝试使用这个sql字符串时，我得到MySQLdb._exceptions.ProgrammingError）（1064，"你的SQL语法有错误；检查与你的MySQL服务器版本相对应的手册，以了解附近使用的正确语法。在2019年它会是什么样子？


           
            
             
              @GeorgiIvanovDimitrov ...这是一个Postgres解决方案。MySQL在
              
               UPDATE
              
              查询中不使用
              
               FROM
              
              。它确实使用
              
               UPDATE ... JOIN
              
              。阅读文档、教程，甚至SO帖子。


          
           
            
             
             
              jeffery_the_wind
             
            
            
             发布于
             
             2021-12-26


          
           
            
             看起来你正在使用一些存储在
             
              df
             
             中的外部数据作为更新数据库表的条件。  如果可以的话，为什么不直接做一个单行的sql更新？
            
            
             如果你正在处理一个小型的数据库（将整个数据加载到Python数据框对象中不会让你丧命），那么你可以
             
              可以
             
             definitely conditionally update the dataframe after loading it using
             
              read_sql
             
             .  Then you 可以 use a keyword arg
             
              if_exists="replace"
             
             to replace the DB table with the new updated table.
            
            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")
Pandas是一个强大的工具，其中有限的SQL支持起初只是一个小功能。  随着时间的推移，人们正在尝试使用pandas作为他们唯一的数据库接口软件。  我不认为pandas曾经是要成为数据库交互的全部，但是有很多人一直在研究新的功能。  见。https://github.com/pandas-dev/pandas/issues


           
            
             
              
               MarredCheese
              
              ：


           
            
             
              
               该表是否在一个事务中被删除并重新创建？  在看了一下
               
                to_sql() source code
               
               我认为答案是 "不"，但我不确定。我担心的是，当其他用户试图查询该表时，可能会发现它不存在。


           
            
             
              
               probitaille
              
              ：


           
            
             
              
               如果to_sql()方法的属性被设置为 "if_exists='replace'"，它将删除该表。所以在这个例子中，是的。见。
               
                pandas.pydata.org/pandas-docs/stable/reference/api/...


           
            
             
              
               这样做的问题是，它也将不得不删除所有取决于视图。


           
            
             
              
               替换代码0】被丢弃的事实也意味着表的约束和数据库中设置的数据类型被丢失并自动替换，这在大多数情况下不是一个理想的行为。


          
           
            
             
              到目前为止，我还没有看到一种情况，即pandas sql连接器可以以任何可扩展的方式被用于
              
               更新
              
              数据库数据。建立一个数据库可能看起来是个好主意，但实际上，对于业务工作来说，它根本没有规模。
             
             
              我建议使用以下方法将你的整个数据框架转储为CSV文件
             
             df.to_csv('filename.csv', encoding='utf-8')
然后用CSV将其加载到数据库中COPY for PostgreSQL or 在文件中加载数据 for MySQL.
如果在数据被pandas操作的时候，你不对相关的表做其他的改变，你可以直接加载到表中。
If there are concurrency issues, you will have to load the data into a staging table that you then use to 更新 your primary table from.
在后一种情况下，你的主表需要有一个数据时间，告诉你它的最新修改时间，这样你就可以确定你的pandas修改是否是最新的，或者数据库的修改是否应该保留。


           
            
             
              
               
                Vanya Usalko
               
               ：


           
            
             
              
               
                这当然更具可扩展性，但也更难））因为我们必须考虑CSV的交付方式）。


          
           
            
             
              
               我在想，你为什么不先根据你的公式更新df，然后将df存储到数据库中，你可以使用if_exists='replace'，存储在同一个表中。


          
           
            
             
              
               在列名没有改变的情况下，我倾向于删除所有的行，然后将数据追加到现在的空表中。否则，从属视图也必须重新生成。
              
              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)


          
           
            
             
              
               
                
                
                 Welsonlemon
                
               
               
                发布于
                
                2021-12-26


          
           
            
             
              
               
                我尝试了第一个答案，发现效果不是很好，然后我改变了一些部分，通过使用pandas+sqlalchemy进行更新来传递所有情况。
               
               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)


           
            
             
              
               
                
                 
                  Alex Metsai
                 
                 ：


           
            
             
              
               
                
                 
                  你能补充说明一下你的代码是做什么的吗？


          
           
            
             
              
               
                
                 这里有一个我觉得有点干净的方法。这是利用了sqlalchemy。它一次只更新一列，但可以很容易地被概括。
                
                def dataframe_update(df, table, engine, primary_key, column):
  md = MetaData(engine)
  table = Table(table, md, autoload=True)