13

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?

sanyassh
  • 8,100
  • 13
  • 36
  • 70
  • 2
    Normally `Table.drop()` accepts an optional bind as the first positional argument, so you'd pass `conn` like `tbl.drop(conn, checkfirst=True)`, but I don't know if that's supported with async. There's btw. nothing wrong in executing actual SQL when working with SQLAlchemy, it even provides some helpers for doing that, such as `text()`. – Ilja Everilä Nov 20 '19 at 11:21
  • 1
    What's wrong with catching `UndefinedTable`? – Andrea Corbellini Nov 29 '19 at 05:07
  • 1
    @AndreaCorbellini it will be 4 rows (try, execute, except, pass) to execute this query, even worse than with raw sql. I am looking for the best (shortest, most pythonic) way to do it. I expect sqlalchemy to have built-in functionality for such simple queries. – sanyassh Nov 29 '19 at 06:57

1 Answers1

5

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

aaron
  • 39,695
  • 6
  • 46
  • 102