SQLAlchemy
注：环境中没有包先下载
1.通过SQLAlchemy创建数据表
#1.导入SQLAlchemy from sqlalchemy.ext.declarative import declarative_base #2.创建orm模型基类 Base=declarative_base() #此处的base相当于Django中的创建表都要继承的model #3.导入orm对应数据库数据类型的字段 from sqlalchemy import Column,Integer,String #4.创建orm对象 class User(Base): __tablename__="user" #私有属性，代表生成的表的名字叫user id=Column(Integer,primary_key=True,autoincrement=True) #表的一个列名叫id，数据类型为int型，设置该列为主键，自增 name=Column(String(32),index=True) #表的一个列名叫name，数据类型是字符串，索引 #5.创建数据库连接引擎 from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") #6.去数据库中创建User对象所对应的数据表 Base.metadata.create_all(engine) #去engine数据库中创建所有继承Base的orm对象
2.通过 SQLAlchemy对数据表进行增删改查
(1)增加数据
# 1.想要操纵数据库，先打开数据库连接 from create_table import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.增加数据 from create_table import User user_obj=User(name="amy") # 4.通过db_session(已经打开的会话窗口)提交数据 db_session.add(user_obj) # 5.执行会话窗口中的所有操作 db_session.commit() #提交 db_session.close()
(2)增加批量数据
方式一
# 1.想要操纵数据库，先打开数据库连接 from create_table import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.增加数据 from create_table import User user1=User(name="ddd") user2=User(name="eee") user3=User(name="fff") # 4.通过db_session(已经打开的会话窗口)提交数据 ***********不同之处************ db_session.add(user1) db_session.add(user2) db_session.add(user3) *********************** # 5.执行会话窗口中的所有操作 db_session.commit() #提交 db_session.close()
方式二
# 1.想要操纵数据库，先打开数据库连接 from create_table import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.增加数据 from create_table import User # 4.通过db_session(已经打开的会话窗口)提交数据 *********************** db_session.add_all([ User(name="aaa"), User(name="bbb"), User(name="ccc")]) *********************** # 5.执行会话窗口中的所有操作 db_session.commit() #提交 db_session.close()
(3)查询
# 0.创建会话并打开会话窗口 from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 1.查询所有 from create_table import User user_list1=db_session.query(User) #得到 SELECT user.id AS user_id, user.name AS user_name FROM user user_list2=db_session.query(User).all() #得到 [<create_table.User object at 0x0000017A0DF09208>, <create_table.User object at 0x0000017A0DF09278>, <create_table.User object at 0x0000017A0DF092E8>, <create_table.User object at 0x0000017A0DF09358>] for row in user_list2: print(row.id,row.name) ''' 结果为： 2 aaa 1 amy 3 bbb 4 ccc ''' # 2.查询第一个 user_obj=db_session.query(User).first() print(user_obj.id,user_obj.name) #2 aaa(按照name排序) # 3.带条件的查询 # 方式一 user_list3=db_session.query(User).filter(User.id==4).all() print(user_list3[0].id,user_list3[0].name) #4 ccc user_list5=db_session.query(User).filter(User.id <=4).all() for row in user_list5: print(row.id,row.name) # 方式二 user_list4=db_session.query(User).filter_by(id=4).first() #filter_by中写SQL语句 print(user_list4.id,user_list4.name) # 4.查看SQL语句 user_list6=db_session.query(User).filter_by(id=4) print(user_list6) ''' 结果为： SELECT user.id AS user_id, user.name AS user_name FROM user WHERE user.id = %(id_1)s '''
(4)更新修改
# 0.创建会话并打开会话窗口 from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 1.修改单条数据 #SQL语句：update `user` set `name`=`ddd` where `name`=`ccc` from create_table import User res=user_list2=db_session.query(User).filter(User.name=="ccc").update({"name":"ddd"}) print(res) #返回值为受影响的行数 db_session.commit() db_session.close() # 2.修改多条数据 from create_table import User res=user_list2=db_session.query(User).filter(User.id>=3).update({"name":"ddd"}) print(res) #返回值为受影响的行数 db_session.commit() db_session.close()
(5)删除
# 0.创建会话并打开会话窗口 from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 #删除数据 #SQL语句：delete from table where `name`=`ddd` from create_table import User res=user_list2=db_session.query(User).filter(User.name=="ddd").delete() print(res) #返回受影响的行数 db_session.commit() db_session.close()
3.ForeignKey(多表的增删改查)
(1)创建含有外键的数据表
from sqlalchemy.ext.declarative import declarative_base Base=declarative_base() #基类 from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import relationship class Student(Base): __tablename__="student" id=Column(Integer,primary_key=True) name=Column(String(32)) # **********school表中的id列************* school_id=Column(Integer,ForeignKey("school.id")) stu2sch=relationship("School",backref="stu2sch") # **********此处一定要注意表名的大小写************* class School(Base): __tablename__="school" id=Column(Integer,primary_key=True,autoincrement=True) name=Column(String(32)) from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") Base.metadata.create_all(engine) #去engine数据库中创建所有继承Base的orm对象
(2)多表添加数据
方式一
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 from create_table_foreign import School,Student # 3.通过db_session(已经打开的会话窗口)添加数据 sch_obj=School(name="红星小学1") db_session.add(sch_obj) sch=db_session.query(School).filter(School.name=="红星小学").first() stu_obj=Student(name="尚宏运1",school_id=sch.id) db_session.add(stu_obj) db_session.commit() db_session.close()
方式二：添加数据relationship版
正向添加
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 from create_table_foreign import School,Student # 3.通过db_session(已经打开的会话窗口)提交数据 stu_obj=Student(name="尚宏运4",stu2sch=School(name="红星小学6")) db_session.add(stu_obj) db_session.commit() db_session.close()
反向添加
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 from create_table_foreign import School,Student # 3.通过db_session(已经打开的会话窗口)提交数据 sch_obj=School(name="红星小学2") sch_obj.sch2stu=[Student(name="尚宏运2"),Student(name="尚宏运3")] db_session.add(sch_obj) db_session.commit() db_session.close()
(3)多表查询数据
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.查询数据 from create_table_foreign import School,Student #**************正向跨表查询*************** stu_list=db_session.query(Student).all() for row in stu_list: print(row.id,row.name,row.stu2sch.name) #**************反向跨表查询*************** sch_list=db_session.query(School).all() for row in sch_list: for row2 in row.sch2stu: print(row.id,row.name,row2.name)
(4)多表数据更新
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.更新数据 from create_table_foreign import School,Student sch=db_session.query(School).filter(School.name=="红星小学1").first() db_session.query(Student).filter(Student.name=="尚宏运3").update({"school_id":sch.id}) db_session.commit() #提交 db_session.close()
(5)多表数据删除
# 1.想要操纵数据库，先打开数据库连接 from create_table_foreign import engine # 2.创建会话 from sqlalchemy.orm import sessionmaker Session=sessionmaker(engine) #创建会话窗口 db_session=Session() #打开会话窗口 # 3.删除数据 from create_table_foreign import School,Student sch_list=db_session.query(School).filter(School.name=="红星小学2").all() for row in sch_list: db_session.query(Student).filter(Student.school_id==row.id).delete() db_session.commit() #提交 db_session.close()
4.关于查询的更多操作
(1)and or
from sqlalchemy.sql import and_ , or_ ret = db_session.query(User).filter(and_(User.id ==1, User.name == 'amy')).all() for row in ret: print(row.name) ret = db_session.query(User).filter(or_(User.id ==1, User.name == 'aaa')).all() for row in ret: print(row.name)
(2)指定查询数据列 加入别名
r2 = db_session.query(User.name.label('username'), User.id).first() print(r2.id,r2.username)
(3)字符串匹配方式筛选条件
from sqlalchemy.sql import text #查询id小于value，name等于name的数据 r6 = db_session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='DragonFire').order_by(User.id).all()
(4)原生SQL查询
r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='DragonFire').all()
(5) 排序
user_list = db_session.query(User).order_by(User.id).all() user_list = db_session.query(User).order_by(User.id.desc()).all() for row in user_list: print(row.name,row.id)
(6)其他查询条件
ret = session.query(User).filter_by(name='DragonFire').all() ret = session.query(User).filter(User.id > 1, User.name == 'DragonFire').all() #between ret = session.query(User).filter(User.id.between(1, 3), User.name == 'DragonFire').all() # between 大于1小于3的 #in ret = session.query(User).filter(User.id.in_([1,3,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的 ret = session.query(User).filter(~User.id.in_([1,3,4])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的 #子查询 ret = session.query(User).filter(User.id.in_(session.query(User.id).filter_by(name='DragonFire'))).all() 子查询 # and_, or_ from sqlalchemy import and_, or_ ret = session.query(User).filter(and_(User.id > 3, User.name == 'DragonFire')).all() ret = session.query(User).filter(or_(User.id < 2, User.name == 'DragonFire')).all() ret = session.query(User).filter( or_( User.id < 2, and_(User.name == 'eric', User.id > 3), User.extra != "" )).all() # select * from User where id<2 or (name="eric" and id>3) or extra != "" # 通配符 ret = db_session.query(User).filter(User.name.like('e%')).all() ret = db_session.query(User).filter(~User.name.like('e%')).all() # 限制 ret = db_session.query(User)[1:2] # 排序 ret = db_session.query(User).order_by(User.name.desc()).all() ret = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all() # 分组 from sqlalchemy.sql import func ret = db_session.query(User).group_by(User.extra).all() ret = db_session.query( func.max(User.id), func.sum(User.id), func.min(User.id)).group_by(User.name).all() ret = db_session.query( func.max(User.id), func.sum(User.id), func.min(User.id)).group_by(User.name).having(func.min(User.id) >2).all() #高级版更新操作 from my_create_table import User,engine from sqlalchemy.orm import sessionmaker Session = sessionmaker(engine) db_session = Session() #直接修改 db_session.query(User).filter(User.id > 0).update({"name" : "099"}) #在原有值基础上添加 - 1 db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False) #在原有值基础上添加 - 2 db_session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate") db_session.commit()
5.多对多时的增删改查
(1)创建含有多对多的数据表
from sqlalchemy.ext.declarative import declarative_base Base=declarative_base() #基类 from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import relationship class Girls(Base): __tablename__="girl" id=Column(Integer,primary_key=True) name=Column(String(32)) #创建关系 girl2boy=relationship("Boys",secondary="Hotel",backref="boy2girl") class Boys(Base): __tablename__="boy" id=Column(Integer,primary_key=True) name=Column(String(32)) #第三张表 class Hotel(Base): __tablename__="hotel" id=Column(Integer,primary_key=True) girl_id=Column(Integer,ForeignKey("girl.id")) boy_id=Column(Integer,ForeignKey("boy.id")) #创建数据库连接 from sqlalchemy import create_engine engine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8") #去数据库中创建User对象所对应的数据表 Base.metadata.create_all(engine)
(2)添加
from sqlalchemy.orm import sessionmaker from m2m import engine,Boys,Girls Session=sessionmaker(engine) db_session=Session() ###################通过boy添加girl（反向）################# boy= Boys(name="李易峰") boy.boy2girl= [Girls(name="赵丽颖"),Girls(name="李小璐")] db_session.add(boy) db_session.commit() db_session.close() #####################通过girl添加boy(正相)######################### girl_obj=Girls(name="罗玉凤",girl2boy=[Boys(name="小涛")]) db_session.add(girl_obj) db_session.commit() db_session.close()
(3)查询
from sqlalchemy.orm import sessionmaker from m2m import engine,Boys,Girls Session=sessionmaker(engine) db_session=Session() #####################通过relationship正相######################### b_list=db_session.query(Boys).all() for boy in b_list: for girl in boy.boy2girl: print(boy.name,girl.name) #####################通过relationship反向######################### b_list=db_session.query(Boys).all() for boy in b_list: for girl in boy.boy2girl: print(boy.name,girl.name)
改变世界,改变自己!
