Search code examples
pythonpostgresqlsqlalchemydrop-tableaiopg

aiopg + sqlalchemy: how to "drop table if exists" without raw sql?

I am looking at examples of aiopg usage with sqlalchemy and these lines scare me:

async def create_table(conn):
    await conn.execute('DROP TABLE IF EXISTS tbl')
    await conn.execute(CreateTable(tbl))

I do not want to execute raw sql queries when using sqlalchemy. However I can't find any other way to implement the same logic. My attempts were:

1)

await conn.execute(tbl.drop(checkfirst=True))

This raises:

sqlalchemy.exc.UnboundExecutionError: Table object 'tbl' is not bound to an Engine or Connection. Execution can not proceed without a database to execute against.

Also I can't find a way to bind the table to engine because aiopg doesn't support metadata.create_all

2)

await conn.execute(DropTable(tbl))

This raises:

psycopg2.errors.UndefinedTable: table "tbl" does not exist

Seems like DropTable construct doesn't support IF EXISTS part in any way.

So, the question is, is there any way to rewrite await conn.execute('DROP TABLE IF EXISTS tbl') statement into something without raw sql when using aiopg + sqlalchemy?

Solution

  • This question was posted when the latest version was SQLAlchemy 1.3.11.

    As of SQLAlchemy 1.4.0, DropTable supports if_exists=True.

    await conn.execute(DropTable(tbl, if_exists=True))

    Reference: https://docs.sqlalchemy.org/en/14/core/ddl.html#sqlalchemy.schema.DropTable