首页 > 代码库 > sqlalchemy数据模型

sqlalchemy数据模型

sqlalchemy在python里作为orm还是比较有名气的,以下是建立的几个简单模型,完全可和flask的数据持久层分离。

  1 # coding: utf8
  2 from sqlalchemy import Column, String, Integer, DateTime, BigInteger, Numeric, ForeignKey, SmallInteger, create_engine
  3 from sqlalchemy.ext.declarative import declarative_base
  4 from sqlalchemy.orm import create_session, relationship
  5 
  6 Base = declarative_base()
  7 _db_session = None
  8 
  9 
 10 def get_engine():
 11     return create_engine(mysql+pymysql://root:1234@localhost/wms?charset=utf8, echo=True)
 12 
 13 
 14 def get_session():
 15     if _db_session is None:
 16         engine = get_engine()
 17         return create_session(engine)
 18     else:
 19         return _db_session
 20 
 21 
 22 def create_all():
 23     engine = get_engine()
 24     Base.metadata.create_all(engine)
 25 
 26 
 27 def drop_all():
 28     engine = get_engine()
 29     Base.metadata.drop_all(engine)
 30 
 31 
 32 class Role(Base):
 33     __tablename__ = a_role
 34     code = Column(String(20), primary_key=True)
 35     label = Column(String(50))
 36 
 37 
 38 class User(Base):
 39     __tablename__ = a_user
 40     id = Column(BigInteger, primary_key=True)
 41     code = Column(String(20), nullable=False, index=True, unique=True)
 42     label = Column(String(50))
 43     pwd = Column(String(50))
 44     create_date = Column(DateTime)
 45     memo = Column(String(50))
 46     role_code = Column(String(20), ForeignKey(a_role.code))
 47     role = relationship(Role, backref=users)
 48 
 49 
 50 class SupplierClass(Base):
 51     __tablename__ = a_supplier_class
 52     code = Column(String(30), primary_key=True)
 53     label = Column(String(50))
 54 
 55 
 56 class Supplier(Base):
 57     __tablename__ = a_supplier
 58     id = Column(BigInteger, primary_key=True)
 59     code = Column(String(30), index=True)
 60     label = Column(String(50))
 61     tel = Column(String(30))
 62     address = Column(String(50))
 63     contacts = Column(String(30))
 64     level = Column(SmallInteger)
 65     create_date = Column(DateTime)
 66     memo = Column(String(50))
 67     class_code = Column(String(30), ForeignKey(a_supplier_class.code))
 68     supplier_class = relationship(SupplierClass, backref=suppliers)
 69 
 70 
 71 class Warehouse(Base):
 72     __tablename__ = a_warehouse
 73     code = Column(String(30), primary_key=True)
 74     label = Column(String(50))
 75     address = Column(String(50))
 76     create_date = Column(DateTime)
 77     memo = Column(String(50))
 78     manager_id = Column(BigInteger, ForeignKey(a_user.id))
 79     manager = relationship(User)
 80 
 81 
 82 class LocationClass(Base):
 83     __tablename__ = a_location_class
 84     code = Column(String(30), primary_key=True)
 85     label = Column(String(50))
 86 
 87 
 88 class Location(Base):
 89     __tablename__ = a_location
 90     id = Column(BigInteger, primary_key=True)
 91     code = Column(String(30), index=True)
 92     label = Column(String(50))
 93     max_qty = Column(Numeric(10, 2))
 94     create_date = Column(DateTime)
 95     memo = Column(String(50))
 96     warehouse_code = Column(String(30), ForeignKey(a_warehouse.code))
 97     manager_id = Column(BigInteger, ForeignKey(a_user.id))
 98     class_code = Column(String(30), ForeignKey(a_location_class.code))
 99     manager = relationship(User)
100     warehouse = relationship(Warehouse)
101     location_class = relationship(LocationClass)
102 
103 
104 class ItemClass(Base):
105     __tablename__ = a_item_class
106     code = Column(String(30), primary_key=True)
107     label = Column(String(50))
108 
109 
110 class Item(Base):
111     __tablename__ = a_item
112     id = Column(BigInteger, primary_key=True)
113     code = Column(String(30), index=True)  # 物料代码
114     label = Column(String(100))  # 品名
115     type = Column(SmallInteger)  # 0原材料,1半成品,2成品
116     class_code = Column(String(30), ForeignKey(a_item_class.code))
117     location_id = Column(BigInteger, ForeignKey(a_location.id))
118     item_class = relationship(ItemClass)  # 物料分类
119     location = relationship(Location)  # 推荐库位
120     safety_stock = Column(Numeric(10, 2))  # 安全库存
121     mpq = Column(Numeric(10, 2))  # 最小包装数量
122     create_date = Column(DateTime)
123     memo = Column(String(50))
124 
125 
126 # drop_all()
127 # create_all()

 

sqlalchemy数据模型