python:sqlalchemy批量插入与on_conflict_update [英] python : sqlalchemy batch insert with on_conflict_update

问题描述

我必须插入大约.我的 postgres 数据库中每天有 30000 行，我的数据库中有 4 列，即:id(pkey)、类别、创建日期、更新日期.我的要求是用今天的日期和新类别(如果 id 存在)更新更新日期和类别列，否则插入新行，创建日期和更新日期相同.

I have to insert approx. 30000 rows daily in my postgres database, I have 4 columns in my database namely : id(pkey), category, createddate, updatedon. My requirement is to update updatedon and category column with today's date and new category if id is present, else insert a new row with createddate and updateon being same.

我找到了 Ilja Everilä 的 [答案]:https://stackoverflow.com/a/44865375/5665430 用于批处理更新

I found Ilja Everilä's [answer]:https://stackoverflow.com/a/44865375/5665430 for batch update

insert_statement = sqlalchemy.dialects.postgresql.insert(id_tag)
upsert_statement = insert_statement.on_conflict_do_update(
        constraint='id',
    set_={ "createddate": insert_statement.excluded.createddate }
)
insert_values = df.to_dict(orient='records')
conn.execute(upsert_statement, insert_values)

它抛出 AttributeError，

Its throwing AttributeError,

Traceback (most recent call last):

File "<ipython-input-60-4c5e5e0daf14>", line 5, in <module>
    set_= dict(createddate = insert_statement.excluded.createddate)

File "/home/bluepi/anaconda2/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 764, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)

  File "/home/bluepi/anaconda2/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/dml.py", line 43, in excluded
    return alias(self.table, name='excluded').columns

  File "/home/bluepi/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 161, in alias
    return _interpret_as_from(selectable).alias(name=name, flat=flat)

AttributeError: 'TextClause' object has no attribute 'alias'

我已经尝试过一一更新，如下所示http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#postgresql-insert-on-conflict ，但我遇到了同样的错误.

I have tried one by one update as shown here http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#postgresql-insert-on-conflict , but I am getting the same error.

请帮助我了解我哪里出错了，提前致谢.

Please help me understand where I am going wrong, thanks in advance.

推荐答案

来自您的评论

id_tag 只不过是我在 postgres 中的桌子的鬃毛

id_tag is nothing but mane of my table in postgres

可以推断 id_tag 绑定到一个字符串.如果您提供了最小、完整且可验证的示例，那么猜测就少了很多.事实证明，postgresql.dml.insert() 自动将传递的字符串包装在 text() 构造中，以及尝试使用 Insert.excluded 是:

one could deduce that id_tag is bound to a string. If you'd provided a Minimal, Complete, and Verifiable example, there'd been a lot less guesswork. As it turns out, postgresql.dml.insert() automatically wraps passed strings in a text() construct, and the result when trying to use Insert.excluded is:

In [2]: postgresql.insert('fail').excluded
~/sqlalchemy/lib/sqlalchemy/sql/selectable.py:43: SAWarning: Textual SQL FROM expression 'fail' should be explicitly declared as text('fail'), or use table('fail') for more specificity (this warning may be suppressed after 10 occurrences)
  {"expr": util.ellipses_string(element)})
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-2-f176aac8b913> in <module>()
----> 1 postgresql.insert('fail').excluded

~/sqlalchemy/lib/sqlalchemy/util/langhelpers.py in __get__(self, obj, cls)
    765         if obj is None:
    766             return self
--> 767         obj.__dict__[self.__name__] = result = self.fget(obj)
    768         return result
    769 

~/sqlalchemy/lib/sqlalchemy/dialects/postgresql/dml.py in excluded(self)
     41 
     42         """
---> 43         return alias(self.table, name='excluded').columns
     44 
     45     @_generative

~/sqlalchemy/lib/sqlalchemy/sql/selectable.py in alias(selectable, name, flat)
    159 
    160     """
--> 161     return _interpret_as_from(selectable).alias(name=name, flat=flat)
    162 
    163 

AttributeError: 'TextClause' object has no attribute 'alias'

因此，与其将包含表名称的字符串传递给 postgresql.dml.insert()，不如将它传递给实际的 Table 对象，或轻量级的 table() 构造使用 column() 对象.

So, instead of passing a string containing the name of your table to postgresql.dml.insert() pass it an actual Table object, or a light weight table() construct that has been populated with column() objects.

这篇关于python:sqlalchemy批量插入与on_conflict_update的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！