How to automatically reflect database to sqlalchemy declarative?
Well I went through that, tried on Northwind database and it looks promising. Although, I had to add relationship field to be able to follow database relations.
Let's consider that I don't know relations between tables at the moment of starting the application so I need is a way to generate automatically.
import unittest
from sqlalchemy import *
from sqlalchemy.orm import create_session
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
from sqlalchemy.orm import contains_eager, joinedload
from sqlalchemy.orm import relationship
#Create and engine and get the metadata
Base = declarative_base()
engine = create_engine('mssql://user:pass@Northwind', echo=True)
metadata = MetaData(bind=engine)
#Reflect each database table we need to use, using metadata
class Customer(Base):
__table__ = Table('Customers', metadata, autoload=True)
orders = relationship("Order", backref="customer")
class Shipper(Base):
__table__ = Table('Shippers', metadata, autoload=True)
orders = relationship("Order", backref="shipper")
class Employee(Base):
__table__ = Table('Employees', metadata, autoload=True)
# orders = relationship("Order", backref="employee")
territories = relationship('Territory', secondary=Table('Employeeterritories', metadata, autoload=True))
class Territory(Base):
__table__ = Table('Territories', metadata, autoload=True)
region = relationship('Region', backref='territories')
class Region(Base):
__table__ = Table('Region', metadata, autoload=True)
class Order(Base):
__table__ = Table('Orders', metadata, autoload=True)
products = relationship('Product', secondary=Table('Order Details', metadata, autoload=True))
employee = relationship('Employee', backref='orders')
class Product(Base):
__table__ = Table('Products', metadata, autoload=True)
supplier = relationship('Supplier', backref='products')
category = relationship('Category', backref='products')
class Supplier(Base):
__table__ = Table('Suppliers', metadata, autoload=True)
class Category(Base):
__table__ = Table('Categories', metadata, autoload=True)
class Test(unittest.TestCase):
def setUp(self):
#Create a session to use the tables
self.session = create_session(bind=engine)
def tearDown(self):
self.session.close()
def test_withJoins(self):
q = self.session.query(Customer)
q = q.join(Order)
q = q.join(Shipper)
q = q.filter(Customer.CustomerID =='ALFKI')
q = q.filter(Order.OrderID=='10643')
q = q.filter(Shipper.ShipperID=='1')
q = q.options(contains_eager(Customer.orders, Order.shipper))
res = q.all()
cus = res[0]
ord = cus.orders[0]
shi = ord.shipper
self.assertEqual(shi.Phone, '(503) 555-9831')
In theory reflection in sqlalchemy should work for you. In this case I'm using an mssql database with two tables which have a simple Many-to-one relation:
"Tests" with fields:
- id
- testname
- author_id (foreign key to the Users table, Users.id field)
"Users" with fields:
- id
- fullname
So the following should reflect the database:
from sqlalchemy import *
from sqlalchemy.orm import create_session
from sqlalchemy.schema import Table, MetaData
from sqlalchemy.ext.declarative import declarative_base
#Create and engine and get the metadata
Base = declarative_base()
engine = create_engine('put your database connect string here')
metadata = MetaData(bind=engine)
#Reflect each database table we need to use, using metadata
class Tests(Base):
__table__ = Table('Tests', metadata, autoload=True)
class Users(Base):
__table__ = Table('Users', metadata, autoload=True)
#Create a session to use the tables
session = create_session(bind=engine)
#Here I will just query some data using my foreign key relation, as you would
#normally do if you had created a declarative data mode.
#Note that not all test records have an author so I need to accomodate for Null records
testlist = session.query(Tests).all()
for test in testlist:
testauthor = session.query(Users).filter_by(id=test.author_id).first()
if not testauthor:
print "Test Name: {}, No author recorded".format(test.testname)
else:
print "Test Name: {}, Test Author: {}".format(test.testname, testauthor.fullname)
So this appears to work with table relations. Although you still haven't given much detail to exactly what you are trying to do.