possible to insert with `psycopg2.sql.Composed` objects?

[WillForan]

Is there a way or any suggestions for using psycopg2 objects in execute()?

  def code_to_test(conn):
      from psycopg2.sql import SQL, Identifier, Placeholder
      ins = SQL("insert into {} ({}) values ({})").\
          format(Identifier('mytest'), Identifier('id'), Placeholder('id'))
      conn.execute(ins, {'id': 2}) 

  def test_composed_obj(transacted_postgresql_db):
      conn = transacted_postgresql_db.connection
      # no errors
      conn.execute("create table mytest (id int)")
      conn.execute("insert into mytest values (1)")

     # fails
     code_to_test(conn)

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: Composed([SQL('insert into '), Identifier('mytest'), SQL(' ('), Identifier('id'), SQL(') values ('), Placeholder('id'), SQL(')')])

[dargueta]

This is a SQLAlchemy limitation -- it's a generalized ORM so it's not expecting you to hand it a Psycopg2 object it didn't build itself. Your problem seems to be quoting identifiers; all engine fixtures allow quoting identifiers like so:

import sqlalchemy as sqla

def code_to_test(transacted_postgresql_db)
    quote = transacted_postgresql_db.id_quoter    # this is what you need
    query = "INSERT INTO {} ({}) VALUES (:id)".format(quote('mytest'), quote('id'))
    transacted_postgresql_db.execute(query, {'id': 2})

[WillForan]

Thanks for the quick response! Fortunately, there aren't too many places that will need to be abstracted from psycopg2. Being able to test with pytest-pgsql will make the effort worth it!