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?
Lazy parameter determines how the related objects get loaded when querying through relationships. Below listed are the four main lazy parameters. Typically when you query the database, the data get loaded at once; however, lazy parameter allows you to alternate the way they get loaded. lazy = 'select' (or True)
Session. delete() marks an object for deletion, which will result in a DELETE statement emitted for each primary key affected.
first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present). Calling Query. first() results in an execution of the underlying query.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With