sqlalchemy: TypeError: '<' not supported between instances of 'datetime.datetime' and 'int'

Describe the bug

After the SQLALCEMY library upgrade to 1.4.9 Before that, on SQLALCHEMY 1.3.23 the code worked. Update to Version 1.4.36 did not solve this problem. And cannot change to the old version, a many dependencies on the 1.4.9

To Reproduce

ttables = self.get_table(db_name, source=False)
tconn = self.get_connect(db_name, source=False)
inserting = insert(ttables.ticket)

inserting = inserting.on_conflict_do_update(
    index_elements=[tt_ticket.c.id],
    set_={
        'status': inserting.excluded.status,
        'last_updated_date': inserting.excluded.last_updated_date,
        'exchanged_to_id': inserting.excluded.exchanged_to_id,
        'extra_data': inserting.excluded.extra_data,
        'is_cleaned': inserting.excluded.is_cleaned,
        'is_hold': inserting.excluded.is_hold,
        'version': inserting.excluded.version,
        'basket_id': inserting.excluded.basket_id,
   }
)

with tconn.begin():
    if tickets:
        tconn.execute(inserting, tickets)

Error

[2022-05-12, 17:26:46 EEST] {taskinstance.py:1889} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/summus/airflow/plugins/operators/data_sampler.py", line 572, in execute
    self.get_tickets()
  File "/home/summus/airflow/plugins/operators/data_sampler.py", line 290, in get_tickets
    self.select_tickets_to_copy()
  File "/home/summus/airflow/plugins/operators/data_sampler.py", line 302, in select_tickets_to_copy
    self.__copy_data(ticket_ids_to_copy)
  File "/home/summus/airflow/plugins/operators/data_sampler.py", line 313, in __copy_data
    self.__copy_ticket(db_name, tickets_ids)
  File "/home/summus/airflow/plugins/operators/data_sampler.py", line 396, in __copy_ticket
    tconn.execute(inserting, tickets)
  File "/home/summus/airflow/.venv13/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/home/summus/airflow/.venv13/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/summus/airflow/.venv13/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1365, in _execute_clauseelement
    keys = sorted(distilled_params[0])
TypeError: '<' not supported between instances of 'datetime.datetime' and 'int'

Versions

  • OS: ubuntu 18.04
  • Python: 3.8
  • SQLAlchemy: 1.4.9
  • Database: PostgreSQL 12
  • DBAPI (eg: psycopg, cx_oracle, mysqlclient): psycopg2-binary==2.8.6

Additional context

No response

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 15 (8 by maintainers)

Commits related to this issue

Most upvoted comments

When using text in execute you need to use the named param style and pass list of dicts. If you want to use a statement that’s passed to the driver as is use exec_driver_sql and pass the string directly

hi -

we need a lot more information here to understand the issue. Most importantly is the structure of tickets. If you are sending a list of tuples for example, this is deprecated API and it would be easier for you to switch to the correct form.