Get CREATE query string from defined table in sqlalchemy

Question

I have an asynchronous python application with database using asyncpg to connect and I would like to avoid typing every query manually. I have found sqlalchemy core to be very helpful with that, but I strugle with one thing. I want my application to be able to create database schema on startup, instead of relying on it being already created and I haven't found an automatic way of doing so. What I would like is something like:

import asyncio

from sqlalchemy import Table, Column, Integer, String, MetaData
import asyncpg

metadata = MetaData()

people = Table(
    "people",
    metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("name", String)
)

class Database:
    def __init__(self, pool: asyncpg.pool.Pool):
        self.pool = pool

    async def ensure_schema(self):
        async with self.pool.acquire() as con:
            await con.execute(
                # this method doesn't really exist,
                # I'm just looking for something that would 
                # do the same - return following query:
                # CREATE TABLE IF NOT EXISTS people(id INTEGER PRIMARY KEY, name VARCHAR)
                people.get_create_query(checkfirst=True)
            )

async def main()
    pool = await asyncpg.create_pool(host="127.0.0.1", port=5432, user="postgres", password="postgres", database="test")
    db = Database(pool)
    await db.ensure_schema()

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

The problem here is, that sqlalchemy expects metadata to be bound to an engine, but it doesn't support asynchronous engines. So I just want to get a CREATE query string from the declared table and pass it to my connection to execute.

Analogy for this may be people.select() which doesn't get executed right away, it just creates an object that also has a string represantation that is the query to be executed.

I can have the sql schema in a separate file and load it on startup, but it doesn't feel right as on every change of schema, I need to change it in two places in my code. Any suggestions?

score 1 · Answer 1 · answered Nov 30 '19 at 11:35

Realizing sqlalchemy can print out all the statements it executes when setting create_engine("connection-string", echo=True), and on creating a new table people it prints out

CREATE TABLE people (
    id INTEGER NOT NULL,
    name VARCHAR,
    PRIMARY KEY (id)
)

I ran debugger to find out where this string comes from and it turns out it can be obtained as this

>>> from sqlalchemy.sql.ddl import CreateTable
>>> print(CreateTable(people))

I was unable to find this in documentation, so I leave it here at least.

Here's the official documentation: https://docs.sqlalchemy.org/en/13/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string — Ilja Everilä, Dec 01 '19 at 06:49

score 0 · Answer 2 · answered Nov 29 '19 at 10:28

0

SQLAlchemy creates tables with

self.Base.metadata.create_all(engine, tables=[people.__table__])

answered Nov 29 '19 at 10:28

Thomas Strub

1,275
7
20

That's right, but it has some assumptions on the engine, which aren't met with `asyncpg.pool.Pool().acquire()`. Which is why I'm looking for some alternative, to just get the query string, so I can execute that. – Libor Nov 29 '19 at 10:37

Get CREATE query string from defined table in sqlalchemy

2 Answers2