Reflecting a database in Flask SQLAlchemy

Question

I have an existing database that I want to reflect into my flask application so I can interact with the database. I can reflect the database but then am unsure how to declare the classes representing the tables in my code.

I have done a lot of searching around stackoverflow and other sources including SQLAlchemy and flask-SQLAlchemy but answers are a bit to advanced for me.

The following code works.

    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy

    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///.../app.db'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    db = SQLAlchemy(app)

    db.reflect(bind='__all__', app=None)
    model = db.Model
    meta = db.metadata
    engine = db.engine

    print(db.get_tables_for_bind())

But I am not sure how to get from the above code to classes. For example the print statement above shows tables User and Post exist. But if I append the following code it says User and Post are undefined.

u1 = User(username='john', email='[email protected]')
p1 = Post(body="post from john",author=u1,timestamp=now + timedelta(seconds=1))

Here is the output from the print statement.

[
Table('alembic_version', MetaData(bind=None), Column('version_num', VARCHAR(length=32), table=<alembic_version>, primary_key=True, nullable=False), schema=None), 

Table('followers', MetaData(bind=None), Column('follower_id', INTEGER(), ForeignKey('user.id'), table=<followers>), Column('followed_id', INTEGER(), ForeignKey('user.id'), table=<followers>), schema=None), 

Table('user', MetaData(bind=None), Column('id', INTEGER(), table=<user>, primary_key=True, nullable=False), Column('username', VARCHAR(length=64), table=<user>), Column('email', VARCHAR(length=120), table=<user>), Column('password_hash', VARCHAR(length=128), table=<user>), Column('about_me', VARCHAR(length=140), table=<user>), Column('last_seen', DATETIME(), table=<user>), Column('last_message_read_time', DATETIME(), table=<user>), schema=None), 

Table('message', MetaData(bind=None), Column('id', INTEGER(), table=<message>, primary_key=True, nullable=False), Column('sender_id', INTEGER(), ForeignKey('user.id'), table=<message>), Column('recipient_id', INTEGER(), ForeignKey('user.id'), table=<message>), Column('body', VARCHAR(length=140), table=<message>), Column('timestamp', DATETIME(), table=<message>), schema=None), 

Table('notification', MetaData(bind=None), Column('id', INTEGER(), table=<notification>, primary_key=True, nullable=False), Column('name', VARCHAR(length=128), table=<notification>), Column('user_id', INTEGER(), ForeignKey('user.id'), table=<notification>), Column('timestamp', FLOAT(), table=<notification>), Column('payload_json', TEXT(), table=<notification>), schema=None), 

Table('post', MetaData(bind=None), Column('id', INTEGER(), table=<post>, primary_key=True, nullable=False), Column('body', VARCHAR(length=140), table=<post>), Column('timestamp', DATETIME(), table=<post>), Column('user_id', INTEGER(), ForeignKey('user.id'), table=<post>), Column('language', VARCHAR(length=5), table=<post>), schema=None), 

Table('task', MetaData(bind=None), Column('id', VARCHAR(length=36), table=<task>, primary_key=True, nullable=False), Column('name', VARCHAR(length=128), table=<task>), Column('description', VARCHAR(length=128), table=<task>), Column('user_id', INTEGER(), ForeignKey('user.id'), table=<task>), Column('complete', BOOLEAN(), table=<task>), schema=None)
]

Can someone indicate how I declare the classes corresponding to the tables in the database.

Answer 1

In order to use a reflected table in a query with SQLAlchemy, you should establish a variable to be a reference to the table itself.

E.g. considering the followers table, this would translate to do something like this:

followers = metadata.tables['followers']

you can now query it like this

from sqlalchemy import select
s = select([followers]).limit(10)
engine.execute(s).fetchall()

This is explained in detail in the book Essential SQLAlchemy (2nd ed) by Jason Myers and Rick Copeland (O'Reilly), at page 66.

Bonus: the sample code used to illustrate the concept in the book itself