question creating tables

[cosmicpython]

question creating tables

[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!

[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 ()

[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?