Sqlalchemy existing database query

Question

I am using SQLAlchmey as an ORM for a python project. I have created several models / circuits and it works fine. Now I need to query the existing mysql database, there is no insert / update only select statement.

How to create a wrapper around the tables of this existing database? I looked briefly at the sqlalchemy and SO docs, but couldn't find anything suitable. All suggest an execute method where I need to write raw sql queries. Although I want to use the SQLAlchmey query method in the same way as I use with SA models.

For example, if an existing db has a User table name, then I want to query it using dbsession (only select operation, possibly with a join)

Answer 1

You get the impression that SQLAlchemy can only work with the database structure created by SQLAlchemy (possibly with MetaData.create_all()) - this is not true. SQLAlchemy can work perfectly with an existing database, you just need to define your models according to the database tables. One way to do this is to use reflection, as Ilya Everilya suggests:

class MyClass(Base):
    __table__ = Table('mytable', Base.metadata,
                    autoload=True, autoload_with=some_engine)

(which, in my opinion, would be completely fine for one-time scripts, but can lead to incredibly frustrating errors in a "real" application, if there is potential that can change the structure of the database over time)

, , , . , . , 10 , users, id, name email:

class User(Base):
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    email = sa.Column(sa.String)

( , , , DDL, String , email )

SQLAlchemy INSERT/UPDATE, . , , SELECT. , .

Answer 2

, :

from sqlalchemy.orm import Session

Base = automap_base()
Base.prepare(engine, reflect=True)
Users = Base.classes.users
session = Session(engine)
res = session.query(Users).first()