Python SQL Alchemy Multiple Databases - Binding Automap_Base

Question

I am working through SQL Alchemy but struggling with how to structure the information from the docs into my project. I have two databases, my first will be used to store all new information from the python application. Where as the second database (DB1 in this case) is an existing database that I need to access information from. What is the right way to create this structure using SQLAlchemy?

I used the suggested BINDS method for multiple databases. This seems to be working.

class BaseConfig(object):
     SECRET_KEY = "SO_SECURE"
     DEBUG = True
     SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc://sa:funpassword@(localdb)\MSSQLLocalDB/Testing?driver=SQL+Server+Native+Client+11.0'
     SQLALCHEMY_BINDS = {
         'DB1': 'mssql+pyodbc://sa:$funpassword@ProdDB/DB1?driver=SQL+Server+Native+Client+11.0'
     }
     SQLALCHEMY_TRACK_MODIFICATIONS = True

This configuration seems to work okay because I am able to create new models in both of these databases using the code below. (This was done to just confirm that I was able to connect to both). db is my SqlAlchemy(app) initialization in my index.py file.

from index import app, db

#Test Writing To Default DB
class Test(db.Model):
     id = db.Column(db.Integer(), primary_key=True)
     employeeNum = db.Column(db.String(255), unique=False)
     job = db.Column(db.String(255))

    def __init__(self, employeeNum, job):
         self.employeeNum = employeeNum
         self.job = job

# Test Writing To DB1
class Test(db.Model):
     __bind_key__ = 'DB1' 
     id = db.Column(db.Integer(), primary_key=True)
     employeeNum = db.Column(db.String(255), unique=False)
     job = db.Column(db.String(255))

    def __init__(self, employeeNum, job):
         self.employeeNum = employeeNum
         self.job = job

I have tried many combinations using the table and automap_base from SQLAlchemy docs but this does not seem to work. I am not sure how I can use the bind_key of DB1 when I am trying to map existing tables.

from index import app, db

def loadSession():
    Table('employee', db.metadata, Column('emp_num', Text, primary_key=True))
    Base = automap_base(metadata=metadata)
    Base.prepare()
    employee = Base.classes.employee
    emp = db.session.query(employee).all()
    for i in emp:
        print(i.data)

Thanks for any help you can provide.

quantumbyte · Accepted Answer · 2018-02-15 20:23:54Z

for your DB1 with existing schema & data you could use reflection to get the tables mapped into sqlalchemy.

For your example it would probably look something like this:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import MetaData
from index import app, db

def loadSession():
    # get a db engine object for DB1 configuration
    engine = db.get_engine(bind='DB1')

    # create a empty db Metadata object and bind it to DB1
    database = MetaData(bind=engine)

    # load the DB1 table/column structure into the Metadata object. This is quite a heavy operation and should optimally be done once on app setup and then the created automap_base can be stored / reused on demand until app shutdown
    database.reflect(
        bind=engine, views=True, autoload_replace=False
    )
    # create a python class structure out of the db metadata structure
    auto_base = automap_base(metadata=database)
    auto_base.prepare()

    # create a db session for DB1
    db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
    # get the reflected employees class from DB1. The employee table must exist in the database
    employee = auto_base.classes.employee

    # query DB1 with the new session object for all employee records
    emp = db_session.query(employee).all()
    for i in emp:
        print(i.data)

    # the following could also be substituted for the query without creating a session object:
    for entry in employee.query.all():
        print(entry)

Collectives™ on Stack Overflow

Python SQL Alchemy Multiple Databases - Binding Automap_Base

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related