sqlalchemy existing database query

Question

I am using SQLAlchemy as ORM for a python project. I have created few models/schema and it is working fine. Now I need to query a existing MySQL database, no insert/update just the select statement.

How can I create a wrapper around the tables of this existing database? I have briefly gone through the sqlalchemy docs and SO but couldn't find anything relevant. All suggest execute method, where I need to write the raw sql queries, while I want to use the SQLAlchemy query method in same way as I am using with the SA models.

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

Solution 1

You seem to have an impression that SQLAlchemy can only work with a database structure created by SQLAlchemy (probably using MetaData.create_all()) - this is not correct. SQLAlchemy can work perfectly with a pre-existing database, you just need to define your models to match database tables. One way to do that is to use reflection, as Ilja Everilä suggests:

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

(which, in my opinion, would be totally fine for one-off scripts but may lead to incredibly frustrating bugs in a "real" application if there's a potential that the database structure may change over time)

Another way is to simply define your models as usual taking care to define your models to match the database tables, which is not that difficult. The benefit of this approach is that you can map only a subset of database tables to you models and even only a subset of table columns to your model's fields. Suppose you have 10 tables in the database but only interested in users table from where you only need id, name and email fields:

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

(note how we didn't need to define some details which are only needed to emit correct DDL, such as the length of the String fields or the fact that the email field has an index)

SQLAlchemy will not emit INSERT/UPDATE queries unless you create or modify models in your code. If you want to ensure that your queries are read-only you may create a special user in the database and grant that user SELECT privileges only. Alternatively/in addition, you may also experiment with rolling back the transaction in your application code.

Solution 2

Create a table with autoload enabled that will inspect it. Some example code:

from sqlalchemy.sql import select
from sqlalchemy import create_engine, MetaData, Table

CONN_STR = '…'
engine = create_engine(CONN_STR, echo=True)
metadata = MetaData()
cookies = Table('cookies', metadata, autoload=True,
                           autoload_with=engine)
cols = cookies.c


with engine.connect() as conn:

    query = (
        select([cols.created_at, cols.name])
                .order_by(cols.created_at)
                .limit(1)
    )
    for row in conn.execute(query):
        print(row)

Solution 3

You can access an existing table using the automap extension:

from sqlalchemy.ext.automap import automap_base
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()

Solution 4

Other answers don't mention what to do if you have a table with no primary key, so I thought I would address this. Assuming a table called Customers that has columns for CustomerId, CustomerName, CustomerLocation you could do;

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine, MetaData, Column, String, Table
from sqlalchemy.orm import Session

Base = automap_base()

conn_str = '...'    
engine = create_engine(conn_str)
metadata = MetaData()
# you only need to define which column is the primary key. It can automap the rest of the columns.
customers = Table('Customers',metadata, Column('CustomerId', String, primary_key=true), autoload=True, autoload_with=engine)
Base.prepare()
Customers= Base.classes.Customers

session = Session(engine)

customer1 = session.query(Customers).first()
print(customer1.CustomerName)

View more solutions

Comments

[Ilja Everilä]

You'd use reflection, and automap if you want to work with mapped classes.

[DevC]

Thanks, Although I have used automap with reflection, but your suggestion also looks good. For SO users if you get time, you can add one example of Automap also. This will make this answer complete

[techkuz]

@Sergey could you please add all the imports?

[MattSom]

@Sergey Yes, the imports could be important (pun intended).

[Max Leske]

autoload has been deprecated in 1.4 (docs.sqlalchemy.org/en/14/core/…). Use autoload_with.