ORM框架-SQLAchemy使用
<table style="height: 30px; background-color: #afeeee; width: 1266px; ; width: 1266px;" border="0"> |
SQLAchemy
SQLAlchemy是编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
:@[:]/
:@/[?]
:@[:]/
安装
pip3 install sqlalchemy
注:SQLAlchemy无法修改表结构,如果需要可以使用SQLAlchemy开发者开源的另外一个软件Alembic来完成.
官网doc:http://docs.sqlalchemy.org/en/latest/core/expression_api.html
1.SQL使用
使用 Engine/ConnectionPooling/Dialect 进行数据库操作,Engine使用ConnectionPooling连接数据库,然后再通过Dialect执行SQL语句
demo:
?事务:
?2.创建表
定义数据表,才能进行sql表达式的操作,毕竟sql表达式的表的确定,是sqlalchemy制定的,如果数据库已经存在了数据表还需要定义么?当然,这里其实是一个映射关系,如果不指定,查询表达式就不知道是附加在那个表的操作,当然定义的时候,注意表名和字段名,代码和数据的必须保持一致。定义好之后,就能创建数据表,一旦创建了,再次运行创建的代码,数据库是不会创建的。
sqlalchemy内部组件调用顺序为:使用 Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 进行数据库操作。Engine使用Schema Type创建一个特定的结构对象,之后通过SQL Expression Language将该对象转换成SQL语句,然后通过?ConnectionPooling 连接数据库,再然后通过?Dialect 执行SQL,并获取结果。
。
demo1:
)
<span style="color: #008000;">#<span style="color: #008000;">?charset是字符集编码,echo=True打印输出信息和执行的sql语句默认Flase,max_overflow=5允许溢出连接池连接数量
meta=MetaData()<span style="color: #008000;">#<span style="color: #008000;">生成源类<span style="color: #008000;">
<span style="color: #008000;">定义表结构
user=Table(<span style="color: #800000;">'<span style="color: #800000;">user<span style="color: #800000;">'<span style="color: #000000;">,meta,Column(<span style="color: #800000;">'<span style="color: #800000;">id<span style="color: #800000;">',nullable=Table,autoincrement=True,primary_key=<span style="color: #000000;">True),Column(<span style="color: #800000;">'<span style="color: #800000;">name<span style="color: #800000;">',String(20),nullable=<span style="color: #000000;">True),Column(<span style="color: #800000;">'<span style="color: #800000;">age<span style="color: #800000;">',nullable=<span style="color: #000000;">True)
)
host=Table(<span style="color: #800000;">'<span style="color: #800000;">host<span style="color: #800000;">'<span style="color: #000000;">,Column(<span style="color: #800000;">'<span style="color: #800000;">ip<span style="color: #800000;">',Column(<span style="color: #800000;">'<span style="color: #800000;">hostname<span style="color: #800000;">',)
meta.create_all(engine)<span style="color: #008000;">#<span style="color: #008000;">创建表,如果存在则忽视
demo2:
使用orm基类创建
)
<span style="color: #008000;">#<span style="color: #008000;">?charset是字符集编码,echo=True打印输出信息和执行的sql语句默认Flase,max_overflow=5允许溢出连接池连接数量
base=declarative_base()<span style="color: #008000;">#<span style="color: #008000;">生成ORM基类<span style="color: #008000;"> <span style="color: #008000;">定义表结构
<span style="color: #0000ff;">class<span style="color: #000000;"> User(base):
<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">book<span style="color: #800000;">' <span style="color: #008000;">#<span style="color: #008000;">表明
id = Column(Integer,primary_key=<span style="color: #000000;">True)
name=Column(String(32<span style="color: #000000;">))
date=<span style="color: #000000;">Column(Date)
base.metadata.create_all(engine)<span style="color: #008000;">#<span style="color: #008000;">创建表,如果存在则忽视
demo3:使用orm中的mapper创建,其实上面表的创建方式是封装了mapper
)
<span style="color: #008000;">#<span style="color: #008000;">?charset是字符集编码,echo=True打印输出信息和执行的sql语句默认Flase,max_overflow=5允许溢出连接池连接数量
meta=<span style="color: #000000;">MetaData()
<span style="color: #008000;">#<span style="color: #008000;">定义表结构
person=Table(<span style="color: #800000;">'<span style="color: #800000;">person<span style="color: #800000;">'<span style="color: #000000;">,String(20<span style="color: #000000;">)),)
<span style="color: #0000ff;">class<span style="color: #000000;"> people(object):
<span style="color: #0000ff;">def <span style="color: #800080;">init<span style="color: #000000;">(self,id,name,age):
self.id=<span style="color: #000000;">id
self.name=<span style="color: #000000;">name
self.age=<span style="color: #000000;">age
mapper(people,person)<span style="color: #008000;">#<span style="color: #008000;">将类和表映射起来,把类和表关键
2.通过orm对数据进行增删改查。
使用sqlalchemy进行增删改茶之前需要映射表,然后才可以进行相应的操作。
增:
<span style="color: #0000ff;">class
user(base): <span style="color: #008000;">#<span style="color: #008000;">映射表<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">user<span style="color: #800000;">'<span style="color: #000000;">
id=Column(Integer,primary_key=<span style="color: #000000;">True)
name=Column(String(20<span style="color: #000000;">))
age=<span style="color: #000000;">Column(Integer)
sessoion_class
=sessionmaker(bind=engine)<span style="color: #008000;">#<span style="color: #008000;">创建与数据库的会话类,这里的sessoion_class是类Session=sessoion_class()<span style="color: #008000;">#<span style="color: #008000;">生成会话实例
user1=user(name=<span style="color: #800000;">'<span style="color: #800000;">wd<span style="color: #800000;">',age=22)<span style="color: #008000;">#<span style="color: #008000;">生成user对象
Session.add(user1) <span style="color: #008000;">#<span style="color: #008000;">添加user1,可以使用add_all,参数为列表或者tuple
Session.commit() <span style="color: #008000;">#<span style="color: #008000;">提交<span style="color: #008000;"> <span style="color: #008000;">Session.rollback() #回滚
Session.close() <span style="color: #008000;">#
<span style="color: #008000;">关闭会话查:
常用查询语法:
equals:
query.filter(User.name </span>== <span style="color: #800000;">'</span><span style="color: #800000;">ed</span><span style="color: #800000;">'</span><span style="color: #000000;">)
<span style="color: #0000ff;">not<span style="color: #000000;"> equals:
query.filter(User.name </span>!= <span style="color: #800000;">'</span><span style="color: #800000;">ed</span><span style="color: #800000;">'</span><span style="color: #000000;">)
LIKE:
query.filter(User.name.like(<span style="color: #800000;">'<span style="color: #800000;">%ed%<span style="color: #800000;">'<span style="color: #000000;">))
IN:
NOT IN:
query.filter(~User.name.in_([<span style="color: #800000;">'<span style="color: #800000;">ed<span style="color: #800000;">',<span style="color: #800000;">'<span style="color: #800000;">wendy<span style="color: #800000;">',<span style="color: #800000;">'<span style="color: #800000;">jack<span style="color: #800000;">'<span style="color: #000000;">]))
IS NULL:
IS NOT NULL:
AND:
2.1. ObjectRelationalTutorial 17<span style="color: #000000;">
query.filter(User.name.in([<span style="color: #800000;">'<span style="color: #800000;">ed<span style="color: #800000;">',<span style="color: #800000;">'<span style="color: #800000;">jack<span style="color: #800000;">'<span style="color: #000000;">]))
<span style="color: #008000;">#<span style="color: #008000;"> works with query objects too:
query.filter(User.name.in( session.query(User.name).filter(User.name.like(<span style="color: #800000;">'<span style="color: #800000;">%ed%<span style="color: #800000;">'<span style="color: #000000;">))
))
query.filter(User.name ==<span style="color: #000000;"> None)
<span style="color: #008000;">#<span style="color: #008000;"> alternatively,if pep8/linters are a concern
<span style="color: #000000;">query.filter(User.name.is_(None))
query.filter(User.name !=<span style="color: #000000;"> None)
<span style="color: #008000;">#<span style="color: #008000;"> alternatively,if pep8/linters are a concern
<span style="color: #000000;">query.filter(User.name.isnot(None))
SQLAlchemy Documentation,Release 1.1<span style="color: #000000;">.0b1
<span style="color: #008000;">#<span style="color: #008000;"> use and_()
<span style="color: #0000ff;">from sqlalchemy <span style="color: #0000ff;">import<span style="color: #000000;"> and
query.filter(and(User.name == <span style="color: #800000;">'<span style="color: #800000;">ed<span style="color: #800000;">',User.fullname == <span style="color: #800000;">'<span style="color: #800000;">Ed Jones<span style="color: #800000;">'<span style="color: #000000;">))
<span style="color: #008000;">#<span style="color: #008000;"> or send multiple expressions to .filter()
query.filter(User.name == <span style="color: #800000;">'<span style="color: #800000;">ed<span style="color: #800000;">',User.fullname == <span style="color: #800000;">'<span style="color: #800000;">Ed Jones<span style="color: #800000;">'<span style="color: #000000;">)
<span style="color: #008000;">#<span style="color: #008000;"> or chain multiple filter()/filterby() calls
query.filter(User.name == <span style="color: #800000;">'<span style="color: #800000;">ed<span style="color: #800000;">').filter(User.fullname == <span style="color: #800000;">'<span style="color: #800000;">Ed Jones<span style="color: #800000;">'<span style="color: #000000;">)
Note: Makesureyouuseand()andnotthePythonandoperator! ? OR:
Note: MakesureyouuSEOr_()andnotthePythonoroperator! ? MATCH:
query.filter(User.name.match(<span style="color: #800000;">'<span style="color: #800000;">wendy<span style="color: #800000;">'<span style="color: #000000;">))
Note: match() uses a database-specific MATCH <span style="color: #0000ff;">or CONTAINS f
</span><span style="color: #0000ff;">def</span> <span style="color: #800080;">__repr__</span>(self): <span style="color: #008000;">#</span><span style="color: #008000;">定义</span>
<span style="color: #0000ff;">return</span> <span style="color: #800000;">"</span><span style="color: #800000;">(%s,%s,%s)</span><span style="color: #800000;">"</span> %<span style="color: #000000;"> (self.id,self.name,self.age)
sessoion_class
=sessionmaker(bind=engine)<span style="color: #008000;">#<span style="color: #008000;">创建与数据库的会话类,这里的sessoion_class是类Session=sessoion_class()<span style="color: #008000;">#<span style="color: #008000;">生成会话实例
<span style="color: #008000;">#
<span style="color: #008000;">data=Session.query(user).get(2) #get语法获取primrykey中的关键字,在这里主键为id,获取id为2的数据<span style="color: #008000;"> <span style="color: #008000;">data=Session.query(user).filter(user.age>22,user.name=='mack').first()<span style="color: #008000;"> <span style="color: #008000;">filter语法两个等于号,filter_by语法一个等于号,可以有多个filter,如果多个数据返回列表,first代表获取第一个,为all()获取所有data=Session.query(user).filter(user.age>20,user.name.in_([<span style="color: #800000;">'<span style="color: #800000;">mack<span style="color: #800000;">',<span style="color: #800000;">'<span style="color: #800000;">wd<span style="color: #800000;">'])).all()<span style="color: #008000;">#<span style="color: #008000;">in语法
<span style="color: #0000ff;">print(data[0]) <span style="color: #008000;">#<span style="color: #008000;">打印第一个结果
Session.commit() <span style="color: #008000;">#<span style="color: #008000;">提交,如果回滚的话,数据将不存在了
Session.close() <span style="color: #008000;">#<span style="color: #008000;">关闭会话
修改
删除:
?获取所有数据:
Session.close() <span style="color: #008000;">#<span style="color: #008000;">关闭会话
统计:
分组:
3.外键关联
)
<span style="color: #008000;">#<span style="color: #008000;">?charset是连接数据库的字符集编码(和数据库的编码一样),echo=True打印输出信息和执行的sql语句默认Flase,max_overflow=5允许溢出连接池连接数量
<span style="color: #000000;">
Base
=<span style="color: #000000;">declarative_base() <span style="color: #0000ff;">class<span style="color: #000000;"> user(Base):<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">user<span style="color: #800000;">'<span style="color: #000000;">
id=Column(Integer,primary_key=True,autoincrement=<span style="color: #000000;">True)
name=Column(String(20<span style="color: #000000;">))
age=<span style="color: #000000;">Column(Integer)
<span style="color: #0000ff;">def <span style="color: #800080;">repr<span style="color: #000000;">(self):
<span style="color: #0000ff;">return <span style="color: #800000;">"<span style="color: #800000;"><id:%s,name:%s,age:%s><span style="color: #800000;">"%<span style="color: #000000;">(self.id,self.age)
<span style="color: #0000ff;">class<span style="color: #000000;"> host(Base):
<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">host<span style="color: #800000;">'<span style="color: #000000;">
user_id=Column(Integer,ForeignKey(<span style="color: #800000;">'<span style="color: #800000;">user.id<span style="color: #800000;">'))<span style="color: #008000;">#<span style="color: #008000;">user_id关联user表中的id
hostname=Column(String(20<span style="color: #000000;">))
ip=Column(String(20),primary_key=<span style="color: #000000;">True)
host_user=relationship(<span style="color: #800000;">'<span style="color: #800000;">user<span style="color: #800000;">',backref=<span style="color: #800000;">'<span style="color: #800000;">user_host<span style="color: #800000;">'<span style="color: #000000;">)
<span style="color: #008000;">#<span style="color: #008000;">通过host_user查询host表中关联的user信息,通过user_host,在user表查询关联的host,与生成的表结构无关,只是为了方便查询
<span style="color: #0000ff;">def <span style="color: #800080;">repr<span style="color: #000000;">(self):
<span style="color: #0000ff;">return <span style="color: #800000;">"<span style="color: #800000;"><user_id:%s,hostname:%s,ip:%s><span style="color: #800000;">"%<span style="color: #000000;">(self.user_id,self.hostname,self.ip)
Base.metadata.create_all(engine)
Session_class=sessionmaker(bind=<span style="color: #000000;">engine)
Session=<span style="color: #000000;">Session_class()
host1=<span style="color: #000000;">Session.query(host).first()
<span style="color: #0000ff;">print<span style="color: #000000;">(host1.host_user)
<span style="color: #0000ff;">print<span style="color: #000000;">(host1)
user1=<span style="color: #000000;">Session.query(user).first()
<span style="color: #0000ff;">print(user1.user_host)
?4.多外键关联一个表中的一个字段
应用场景:当我们购物时候,你会发现有一个收发票地址,和一个收货地址。关系如下:默认情况下,发票地址和收获地址是一致的,但是也有可能我想买东西送给别人,而发票要自己留着,那收货的地址和寄送发票的地址可以不同。即:同一个人的两个收获地址可以不同,多个收获地址关联同一个人。
demo:
<span style="color: #800080;">tablename = <span style="color: #800000;">'<span style="color: #800000;">customer<span style="color: #800000;">'<span style="color: #000000;">
id = Column(Integer,primary_key=<span style="color: #000000;">True)
name =<span style="color: #000000;"> Column(String)
billing_address_id </span>= Column(Integer,ForeignKey(<span style="color: #800000;">"</span><span style="color: #800000;">address.id</span><span style="color: #800000;">"</span><span style="color: #000000;">))
shipping_address_id </span>= Column(Integer,ForeignKey(<span style="color: #800000;">"</span><span style="color: #800000;">address.id</span><span style="color: #800000;">"</span><span style="color: #000000;">))
billing_address </span>= relationship(<span style="color: #800000;">"</span><span style="color: #800000;">Address</span><span style="color: #800000;">"</span>,foreign_keys=<span style="color: #000000;">[billing_address_id])
shipping_address </span>= relationship(<span style="color: #800000;">"</span><span style="color: #800000;">Address</span><span style="color: #800000;">"</span>,foreign_keys=<span style="color: #000000;">[shipping_address_id])
</span><span style="color: #008000;">#</span><span style="color: #008000;">同时关联同一个字段,使用relationship需要指定foreign_keys,为了让sqlalchemy清楚关联的外键</span>
<span style="color: #0000ff;">class
<span style="color: #000000;"> Address(Base):<span style="color: #800080;">tablename = <span style="color: #800000;">'<span style="color: #800000;">address<span style="color: #800000;">'<span style="color: #000000;">
id = Column(Integer,primary_key=<span style="color: #000000;">True)
street =<span style="color: #000000;"> Column(String)
city =<span style="color: #000000;"> Column(String)
state = Column(String)
?5.多对多外键关联
很多时候,我们会使用多对多外键关联,例如:书和作者,学生和课程,即:书可以有多个作者,而每个作者可以写多本书,orm提供了更简单方式操作多对多关系,在进行删除操作的时候,
orm会自动删除相关联的数据。
表结构创建:
)
Base
=<span style="color: #000000;">declarative_base()stu_cour=Table(<span style="color: #800000;">'<span style="color: #800000;">stu_cour<span style="color: #800000;">'<span style="color: #000000;">,Base.metadata,Column(<span style="color: #800000;">'<span style="color: #800000;">stu_id<span style="color: #800000;">',ForeignKey(<span style="color: #800000;">'<span style="color: #800000;">student.id<span style="color: #800000;">'<span style="color: #000000;">)),Column(<span style="color: #800000;">'<span style="color: #800000;">cour_id<span style="color: #800000;">',ForeignKey(<span style="color: #800000;">'<span style="color: #800000;">course.id<span style="color: #800000;">'<span style="color: #000000;">))
)
<span style="color: #0000ff;">class<span style="color: #000000;"> student(Base):
<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">student<span style="color: #800000;">'<span style="color: #000000;">
id=Column(Integer,primary_key=<span style="color: #000000;">True)
stu_name=Column(String(32<span style="color: #000000;">))
stu_age=Column(String(32<span style="color: #000000;">))
courses=relationship(<span style="color: #800000;">'<span style="color: #800000;">course<span style="color: #800000;">',secondary=stu_cour,backref=<span style="color: #800000;">'<span style="color: #800000;">students<span style="color: #800000;">'<span style="color: #000000;">)
<span style="color: #008000;">#<span style="color: #008000;">course是关联的第一张表,stu_cour是关联的第二张表,当然,也可以在第三张关联表中使用两个relationship关联student表和course表
<span style="color: #0000ff;">def <span style="color: #800080;">repr<span style="color: #000000;">(self):
<span style="color: #0000ff;">return <span style="color: #800000;">'<span style="color: #800000;"><%s><span style="color: #800000;">'%<span style="color: #000000;">self.stu_name
<span style="color: #0000ff;">class<span style="color: #000000;"> course(Base):
<span style="color: #800080;">tablename=<span style="color: #800000;">'<span style="color: #800000;">course<span style="color: #800000;">'<span style="color: #000000;">
id=Column(Integer,primary_key=<span style="color: #000000;">True)
cour_name=Column(String(32<span style="color: #000000;">))
<span style="color: #0000ff;">def <span style="color: #800080;">repr<span style="color: #000000;">(self):
<span style="color: #0000ff;">return <span style="color: #800000;">'<span style="color: #800000;"><%s><span style="color: #800000;">'%<span style="color: #000000;">self.cour_name
Base.metadata.create_all(engine)
插入数据:
cour_name=Column(String(32<span style="color: #000000;">))
<span style="color: #0000ff;">def <span style="color: #800080;">repr<span style="color: #000000;">(self):
<span style="color: #0000ff;">return <span style="color: #800000;">'<span style="color: #800000;"><%s><span style="color: #800000;">'%<span style="color: #000000;">self.cour_name
stu1
=student(stu_name=<span style="color: #800000;">'<span style="color: #800000;">wd<span style="color: #800000;">',stu_age=<span style="color: #800000;">'<span style="color: #800000;">22<span style="color: #800000;">'<span style="color: #000000;">)stu2=student(stu_name=<span style="color: #800000;">'<span style="color: #800000;">jack<span style="color: #800000;">',stu_age=33<span style="color: #000000;">)
stu3=student(stu_name=<span style="color: #800000;">'<span style="color: #800000;">rose<span style="color: #800000;">',stu_age=18<span style="color: #000000;">)
c1=course(cour_name=<span style="color: #800000;">'<span style="color: #800000;">linux<span style="color: #800000;">'<span style="color: #000000;">)
c2=course(cour_name=<span style="color: #800000;">'<span style="color: #800000;">python<span style="color: #800000;">'<span style="color: #000000;">)
c3=course(cour_name=<span style="color: #800000;">'<span style="color: #800000;">go<span style="color: #800000;">'<span style="color: #000000;">)
stu1.courses=[c1,c2] <span style="color: #008000;">#<span style="color: #008000;">添加学生课程关联
stu2.courses=<span style="color: #000000;">[c1]
stu3.courses=<span style="color: #000000;">[c1,c2,c3]
session_class=sessionmaker(bind=<span style="color: #000000;">engine)
session=<span style="color: #000000;">session_class()
session.add_all([stu1,stu2,stu3,c1,c3])
session.commit()
使用查询:
?删除多对多:
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!