question creating tables

[vsuarezro]

I can't figure out this part, the orm.start_mapper should start the tables, but I can't find a metadata.create_all() anywhere, and then I am not sure if I should put it in here, or you are assuming the tables are already created before the start of the application.

https://github.com/cosmicpython/code/blob/7959ae6df225489eb3a08d6ff440e32727518195/src/allocation/adapters/orm.py#L37

Thank you for a nice book, I think it has opened my mind a bit more.

[hjwp]

good question! two-part answer:

• you'll find the create_all() in conftest.py -- for tests, we assume an empty database, so we can create all the tables from scratch

• in a production app, we usually use alembic, which is the equivalent of django-migrations, to generate migrations from the table definitions that currently live in orm.py. in fact we usually split out a separate file called db_tables.py

[vsuarezro]

Thank you,

I went to check alembic. I think that answers my question. In my small case I just added a metadata.create_all(engine) and an optional engine parameter to the start_mappers in the orm.py

[hjwp]

In my small case I just added a metadata.create_all(engine) and an optional engine parameter to the start_mappers in the orm.py

I think this will only work if you delete the database and recreate it from scratch every time... let me know how you get on?

[vsuarezro]

first and most important, I was following the book as a guide meanwhile trying to put together something simpler. I reached chapter 6 before the messaging system was put together. I haven't tested this enough, but I did something like the following:

-- ui.py or flask_app.py engine = create_engine(config.get_uri(), echo=True) orm.start_mappers(engine)

-- orm.py def start_mappers(engine=None): (...) if engine: metadata.create_all(engine)

-- when db.sqlite3 does not exists, it creates the DB python ui.py output is:

(sqlalchemy) λ python ui.py 2020-04-14 11:35:25,664 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2020-04-14 11:35:25,664 INFO sqlalchemy.engine.base.Engine () x2020-04-14 11:35:25,665 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2020-04-14 11:35:25,665 INFO sqlalchemy.engine.base.Engine () 2020-04-14 11:35:25,666 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("routers") 2020-04-14 11:35:25,666 INFO sqlalchemy.engine.base.Engine () 2020-04-14 11:35:25,667 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info("routers") 2020-04-14 11:35:25,667 INFO sqlalchemy.engine.base.Engine () (...) CREATE TABLE routers ( id INTEGER NOT NULL, sitename VARCHAR(60), hostname VARCHAR(60), region INTEGER, ip_management VARCHAR(60 (...)

-- A second time running does not create the DB and does not gives an error. (sqlalchemy) λ python ui.py 2020-04-14 11:49:59,135 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2020-04-14 11:49:59,136 INFO sqlalchemy.engine.base.Engine () 2020-04-14 11:49:59,137 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2020-04-14 11:49:59,137 INFO sqlalchemy.engine.base.Engine () 2020-04-14 11:49:59,139 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("routers") 2020-04-14 11:49:59,140 INFO sqlalchemy.engine.base.Engine ()

[hjwp]

ah ok, looks like create_all() checks whether tables exist first before creating them https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.create_all

i guess it'll work fine, as long as you never actually need formal migrations. carry on!

[abdulhaq-e]

First, thanks for the awesome book, the best reference ever.

Would you like me to create a PR that mentions that table creation for production is omitted? I've spent a good amount of (enjoyable) time looking through SQLAlchemy docs thinking that I will find the magic function that will somehow scan our metadata objects and creates the tables automatically :D, until I found this PR

[hjwp]

hey! yes i'd welcome an MR I think -- were you thinking some words of text in the book, or just a comment in the example code somewhere?