Flask-SQLAlchemy check if table exists in database

Question

Flask-SQLAlchemy check if table exists in database. I see similar problems, but I try not to succeed.

Flask-SQLAlchemy check if row exists in table

I have create a table object ,like this:

<class'flask_sqlalchemy.XXX'>,

now how to check the object if exists in database.

I do many try: eg:

   for t in db.metadata.sorted_tables:
            print("tablename",t.name)

some table object is created before,but it doesnt exists in database,and now they. all print. eg:print content is

tablename： table_1
tablename： table_2
tablename： table_3

but only table_1 is exist datable，table_2 and table_3 is dynamica create，now I only want use the table_1.

very thanks.

Solution 1

I used these methods. Looking at the model like you did only tells you what SHOULD be in the database.

import sqlalchemy as sa

def database_is_empty():
    table_names = sa.inspect(engine).get_table_names()
    is_empty = table_names == []
    print('Db is empty: {}'.format(is_empty))
    return is_empty

def table_exists(name):
    ret = engine.dialect.has_table(engine, name)
    print('Table "{}" exists: {}'.format(name, ret))
    return ret

There may be a simpler method than this:

def model_exists(model_class):
    engine = db.get_engine(bind=model_class.__bind_key__)
    return model_class.metadata.tables[model_class.__tablename__].exists(engine)

Solution 2

SQL Alchemy's recommended way to check for the presence of a table is to create an inspector object and use its has_table() method. The following example was copied from sqlalchemy.engine.reflection.Inspector.has_table, with the addition of an SQLite engine (in memory) to make it reproducible:

In [17]: from sqlalchemy import create_engine, inspect
    ...: from sqlalchemy import MetaData, Table, Column, Text
    ...: engine = create_engine('sqlite://')
    ...: meta = MetaData()
    ...: meta.bind = engine
    ...: user_table = Table('user', meta, Column("first_name", Text))
    ...: user_table.create()
    ...: inspector = inspect(engine)
    ...: inspector.has_table('user')
Out[17]: True

You can also use the user_table metadata element name to check if it exists as such:

inspector.has_table(user_table.name)

Comments

[Serguei Fedorov]

Note: engine reference isn't defined

[Noki]

Notev2: engine reference can be obtained from SQLAlchemy(). Also import from flask_sqlalchemy import SQLAlchemy

[Zobayer Hasan]

Table.exists() method has been deprecated since version 1.4

[Harvey]

@ZobayerHasan It might be useful to mention what replaces it.

[Paul Rougieux]

The documentation of dialect.has_table() says that "This method is used internally by SQLAlchemy, and is published so that third-party dialects may provide an implementation. It is not the public API for checking for table presence. Please use the Inspector.has_table() method."

[Harvey]

@PaulRougieux Thanks for the update. Inspector.has_table() was added 2 years after this answer according to the docs. Please add an answer using it so we can point people to the newer, more correct answer.