python : sqlalchemy batch insert with on_conflict_update

Question

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.

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)

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'

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.

Answer 1

From your comment

id_tag is nothing but mane of my table in postgres

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'

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.