SQLAlchemy PostgreSQL UPSERT array of values raises UnsupportedCompilationError

Question

Trying to perform an UPSERT on an array of values to a PostgreSQL > 9.5.

Trying to build the statement as in the SQLALchemy docs, but there is no explanation about how to do it for an array instead of a single row. The insert statement builds properly so I suppose it's possible to do so with the on_conflict_do_update function.

Having this code :

stock_table = Table("stock_history", metadata,
            Column('date', sqlalchemy.types.NVARCHAR(length=255), primary_key=True),
            Column('product_id', sqlalchemy.types.INTEGER(), primary_key=True),
            Column('product_sku', sqlalchemy.types.NVARCHAR(length=255)), 
            Column('on_hand_qty',  sqlalchemy.dialects.postgresql.DOUBLE_PRECISION()),
            Column('available_qty',  sqlalchemy.dialects.postgresql.DOUBLE_PRECISION()),
            Column('output_qty', sqlalchemy.dialects.postgresql.DOUBLE_PRECISION())
        )
stock_today = pandas.read_sql_query(queryStock, odoo_engine)
insert_stmt = sqlalchemy.dialects.postgresql.insert(stock_table).values(stock_today)
upser_stmt = insert_stmt.on_conflict_do_update(
            index_elements=['date', 'product_id'],
            set_=stock_today.to_dict(orient='dict')
        )

I'm getting the following error:

AttributeError: 'StrSQLCompiler' object has no attribute 'visit_on_conflict_do_update'

During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
      File "pompeiiETL.py", line 15, in <module>
        pompeiiJobs.runStockJob(dwh_engine, odoo_prod_engine)
      File "/Users/alex/Development/DataLab/pompeii-datalab/pompeiiETL/jobs.py", line 54, in runStockJob
        print(upser_stmt)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 446, in __str__
        return str(self.compile())
      File "<string>", line 1, in <lambda>
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 436, in compile
        return self._compiler(dialect, bind=bind, **kw)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 442, in _compiler
        return dialect.statement_compiler(dialect, self, **kw)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 435, in __init__
        Compiled.__init__(self, dialect, statement, **kwargs)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 216, in __init__
        self.string = self.process(self.statement, **compile_kwargs)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
        return obj._compiler_dispatch(self, **kwargs)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
        return meth(self, **kw)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 2041, in visit_insert
        insert_stmt._post_values_clause, **kw)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 242, in process
        return obj._compiler_dispatch(self, **kwargs)
      File "/Users/alex/Development/DataLab/ETLenv/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch
        raise exc.UnsupportedCompilationError(visitor, cls)
    sqlalchemy.exc.UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.StrSQLCompiler object at 0x105b55be0> can't render element of type <class 'sqlalchemy.dialects.postgresql.dml.OnConflictDoUpdate'>

What I'm ding wrong? Is there a better way to do an upsert?

Thanks!

Azat Ibrakov · Accepted Answer · 2017-04-12 08:52:37Z

9

You are trying to get string representation of Insert-object that has no proper bind, if we write

db_uri = make_url('your-postgres-db-uri-here')
engine = create_engine(db_uri)
upser_stmt.bind = engine
print(upser_stmt)

it works

We can also create insert statement with bind specified

insert_stmt = sqlalchemy.dialects.postgresql.insert(stock_table,
                                                    bind=engine).values(stock_today)
upser_stmt = insert_stmt.on_conflict_do_update(
    index_elements=['date', 'product_id'],
    set_=stock_today.to_dict(orient='dict')
)

edited Apr 12, 2017 at 8:52

answered Apr 11, 2017 at 17:16

Azat Ibrakov

11.1k9 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alex Snsk Over a year ago

Hey there! Thanks! With your help I managed to perform the Insert statment as follows:

insert_stmt=sqlalchemy.dialects.postgresql.insert(stock_table, bind=dwh_engine).values(stock_today.to_dict(orient='records'))

But if I try to add the on_conflict_do_update, adding the same data I get an error:

raise ValueError("set parameter must be a non-empty dictionary") ValueError: set parameter must be a non-empty dictionary

how it's working for the insert and saying it's empty for the upsert? :S

Azat Ibrakov Over a year ago

feel free to ask another question, but not in comments: more users can help you

Azat Ibrakov Over a year ago

for me error message is clear: you need to check if your set_ argument is a non-empty dictionary

Tata Over a year ago

Hi! What if I want to use this using Sessions?

Azat Ibrakov Over a year ago

@Tata: try to use Session.execute method and pass built statement to it

|

Collectives™ on Stack Overflow

SQLAlchemy PostgreSQL UPSERT array of values raises UnsupportedCompilationError

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related