alembic create_table, check if table exists

Question

I have an alembic upgrade script that creates a table, however I don't want it to create the table if it already exists.

According to the alembic doc, I can pass in keyword args to op.create_tables that are acceptable to sqlalchemy.schema.table, so I'm using the keep_existing keyword:

op.create_table('foo_model',
  sa.Column('foo_id', sa.Integer(), nullable=False),
  sa.Column('foo_str', sa.String(length=255), nullable=True),
  sa.PrimaryKeyConstraint('foo_id'),
  keep_existing= True
  )

However I'm still getting the table already exists error.

sqlalchemy.exc.InternalError: (InternalError) (1050, u"Table 'foo_model' already exists") '\nCREATE TABLE foo_model (\n\tfoo_id INTEGER NOT NULL AUTO_INCREMENT, \n\tfoo_str VARCHAR(255), \n\tPRIMARY KEY (foo_id)\n)\n\n' ()

See https://stackoverflow.com/questions/24082542/check-if-a-table-column-exists-in-the-database-using-sqlalchemy-and-alembic/52865284#52865284 — Charles L., Oct 18 '18 at 00:23

score 30 · Answer 1 · edited Jun 08 '20 at 12:35

30

You can get the list of existing tables like this:

from sqlalchemy.engine.reflection import Inspector

conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()

and then check if table already exists or not

if table_name not in tables:
   op.create_table()

edited Jun 08 '20 at 12:35

Cherlepops

71
1
9

answered Mar 08 '19 at 07:26

Pindaari

408
4
9

3

Just a note: `Inspector` means `sqlalchemy.engine.reflection.Inspector` – bubak Apr 15 '20 at 00:19
1

I have a lot having 100+ tables in different revisions, checking it separably on every create_table doesn't seems like a good ideas, any generic solution for it? – Ashutosh gupta Jun 23 '20 at 20:10

score 3 · Answer 2 · answered May 25 '21 at 15:30

3

Here is a full solution if anyone wants

from alembic import op
from sqlalchemy import engine_from_config
from sqlalchemy.engine import reflection

def _has_table(table_name):
    config = op.get_context().config
    engine = engine_from_config(
        config.get_section(config.config_ini_section), prefix="sqlalchemy."
    )
    inspector = reflection.Inspector.from_engine(engine)
    tables = inspector.get_table_names()
    return table_name in tables

answered May 25 '21 at 15:30

Joao Carlos

749
1
11
32

Using this method I get an exception for `KeyError: 'url'` when it tries to execute `engine_from_config`. I do not have a `url` key in my alembic config file. – Jack Jul 18 '22 at 21:07

score 1 · Answer 3 · edited May 23 '17 at 11:53

As it has been said elsewhere ( Check if a table column exists in the database using SQLAlchemy and Alembic) alembic should reflect the full state of your database, that means it would automatically know if a table exists.

Make sure you define the upgrade and downgrade, so that if upgrade creates the table downgrade removes it.

If you do this you can just "downgrade" to the previous revision and upgrade again and it will work. Use autogenerate on a revision to create the initial state.

score -10 · Answer 4 · answered Oct 01 '15 at 23:54

-10

It's probably because the table already exists. Just drop the table from your database by using psql and running drop table foo_model;

answered Oct 01 '15 at 23:54

oshikryu

257
1
13

3

That's not really a solution to the problem of having migrations running fine by themselves when the table is already there. (plus dropping table is loosing data) – Jocelyn delalande Jun 18 '16 at 09:19

alembic create_table, check if table exists

4 Answers4

Linked