如何自动将数据库反映到 sqlalchemy 声明? [英] How to automatically reflect database to sqlalchemy declarative?

问题描述

sqlautocode - 多对多关系存在问题

sqlautocode - has issues with many-to-many relations

sqlsoup - 不支持关系

sqlsoup - doesn't support relations

elixir - 笔记自动生成

elixir - it's note auto-generate

还有什么我可以尝试的吗?

Is there something else I could try?

推荐答案

好吧，我经历了那个，尝试了 Northwind 数据库，它看起来很有希望.虽然，我必须添加关系字段才能遵循数据库关系.

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, '[phone]')

这篇关于如何自动将数据库反映到 sqlalchemy 声明?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！