python数据库orm,Python SQLAlchemy的ORM模块用例，多数据库连接封装-CSDN博客

fromsqlalchemyimportTable, Column, Integer, Float, Numeric, String, DateTime, Date, Timefromsqlalchemy.dialects.postgresqlimportBIGINT as Int8fromsqlalchemyimportForeignKey, PrimaryKeyConstraint, UniqueConstraint, CheckConstraintfromsqlalchemyimporttext, func, and_, or_, not_, asc, inspect, desc, distinctfromsqlalchemy.ormimportrelationship, backreffromsqlalchemy.ext.declarativeimportdeclarative_base, declared_attrimportreimportdatetimedefxor_(exp1, exp2):returnand_(or_(exp1, exp2), not_(and_(exp1, exp2)))defnxor_(exp1, exp2):returnnot_(xor_(exp1, exp2))classdbModelBase(object):

@declared_attrdef__tablename__(cls):

clsname=cls.__name__#数据库表名对应类名(驼峰转小写加下划线)returnre.sub(r'([A-Z])', r'_\1', clsname[0].lower()+clsname[1:] ).lower()def__repr__(self):

attrs=[]forcinself.__table__.columns:

attr=getattr(self, c.name)iftype(attr)in(str, datetime.date, datetime.time, datetime.datetime):

attrs.append(f"{c.name}='{attr}'")else:

attrs.append(f"{c.name}={attr}")returnf"{self.__class__.__name__}({', '.join(attrs)})"defkeys(self):return[c.nameforcinself.__table__.columns]def__getitem__(self, item):returngetattr(self, item)defto_dict(self):return{c.name: getattr(self, c.name)forcinself.__table__.columns}defto_dict(db_obj):returndb_obj.to_dict()defto_list(db_objs):return[db_obj.to_dict()fordb_objindb_objs]#把ORM对象转成可序列化成JSON的对象，对于ORM对象的list转换为dict的list，对于ORM对象转换成dictdefto_jsonable(o):iftype(o)==list:returnto_list(o)else:returnto_dict(o)

Base=declarative_base(cls=dbModelBase)classFund(Base):

id=Column(Integer(), primary_key=True, autoincrement=True)

code=Column(String(), unique=True, nullable=False, comment="基金编码")

name=Column(String(), comment="基金名称")classStrategy(Base):

id=Column(Integer(), primary_key=True, autoincrement=True)

code=Column(String(), unique=True, nullable=False, comment="策略编码")

name=Column(String(), comment="策略名称")

fund_id=Column(Integer(), ForeignKey('fund.id'), comment="基金ID")

fund=relationship('Fund', backref=backref('strategies', order_by=id))classTrader(Base):

id=Column(Integer(), primary_key=True, autoincrement=True)

username=Column(String(), unique=True, nullable=False, comment="交易员用户名")

password=Column(String(), nullable=False, comment="密码")

name=Column(String(), comment="交易员姓名")

strategies=relationship('Strategy', secondary='strategy_trader', backref=backref('traders'))#多对多映射关系#多对多关系表strategy_trader=Table('strategy_trader', Base.metadata,

Column('strategy_id', Integer(), ForeignKey('strategy.id'), primary_key=True),

Column('trader_id', Integer(), ForeignKey('trader.id'), primary_key=True))fromsqlalchemyimportcreate_enginefromsqlalchemy.ormimportsessionmakerfromcontextlibimportcontextmanager

Session=sessionmaker(expire_on_commit=False)

@contextmanagerdefsession_scope():"""Provide a transactional scope around a series of operations."""session=Session()try:yieldsession

session.commit()exceptException as e:

session.rollback()raisefinally:

session.close()definit_engine(dialect=None, username=None, password=None, server=None, dbname=None,

url=None, recreate_all=False,**kwargs):ifurl==None:

url='{}://{}:{}@{}/{}'.format(dialect, username, password, server, dbname)

engine=create_engine(url,**kwargs)

Session.configure(bind=engine)ifrecreate_all:

Base.metadata.drop_all(engine)

Base.metadata.create_all(engine)returnengineif__name__=='__main__':#init_engine(dialect = 'postgresql+psycopg2', username = 'dbuser', password = 'xxxxxx', server = 'ip_address', dbname = 'dbname',#client_encoding='utf8',#echo=True,#isolation_level="REPEATABLE_READ")init_engine(url='sqlite:///:memory:', echo=True)

with session_scope() as session:

fund=Fund(

code="fund001",

name="基金一号")#创建一个fundfund.strategies.append(Strategy(

code="s01",

name="策略一号"))#创建strategy加入到fund.strategies中，在fund插入的时候会被级联插入fund.strategies.append(Strategy(

code="s02",

name="策略二号"))assert(inspect(fund).transient)#创建出来但未调用session.add的对象处于transient态session.add(fund)assert(inspect(fund).pending)#调用过session.add之后还尚未flush到数据库中，这时处于pending态session.flush()assert(inspect(fund).persistent)#手动调用flush 或者等session结束(确切的说应该是事务被commit的时候)，会变成persistent态strategy=Strategy(

code="s03",

name="三号策略",

fund_id=fund.id#只有fund对象真的写入了数据库也就是变成persistent态的时候才能取得fund.id，因为其自增key是数据库维护的)

session.add(strategy)#单独插入一个strategy，手动维护fund_idwith session_scope() as session:

strategies=session.query(Strategy).filter(

Strategy.name.like('%策略%')

).all()#返回查询结果listforsinstrategies:print(s)

with session_scope() as session:

trader=Trader(

username="daimingzhuang",

password="xxxxxx")

strategy=session.query(Strategy).filter(