#1.导入SQLALchemyfrom sqlalchemy.ext.declarative import declarative_base#2.创建ORM模型基类Base declarative_base()#3.导入ORM对应数据类型的字段from sqlalchemy import Column,Integer,String#4.创建ORM对象class User(Base): __tablename__ user #创建表名字 id Column(Integer,primary_key True,autoincrement True) #创建id主键自增长 name Column(String(32),index True)#5创建数据库链接from sqlalchemy import create_engineengine create_engine( mysql pymysql://root: 127.0.0.1:3306/day127?charset utf8 )#6去数据库中创建与User所对应的数据库Base.metadata.create_all(engine)

from sqlalchemy.ext.declarative import declarative_baseBase declarative_base()from sqlalchemy import Column,String,ForeignKey,Integerfrom sqlalchemy.orm import relationshipclass Student(Base): __tablename__ student id Column(Integer,primary_key True) name Column(String(32)) school_id Column(Integer,ForeignKey( school.id )) stu2sch relationship( School ,backref sch2stu )class School(Base): __tablename__ school id Column(Integer,primary_key True) name Column(String(32))from sqlalchemy import create_engineengine create_engine( mysql pymysql://root: 127.0.0.1:3306/127?charset utf8 )Base.metadata.create_all(engine)

# 通过SQLAlchemy对数据表进行增删改查# crud 增删改查# 想要操纵数据库 打开数据库连接# from sqlalchemy import create_engine# engine create_engine( mysql pymysql://root: 127.0.0.1:3306/day127?charset utf8 )from create_table import engine# 创建会话 - 打开数据库连接from sqlalchemy.orm import sessionmaker# 创建会话窗口Session sessionmaker(engine)# 打开会话窗口db_session Session()# 1.增加数据 insert# # insert into user(name) values( DragonFire )# from create_table import User# user_obj User(name Ywb )# # 通过db_session已经打开会话窗口 提交数据# db_session.add(user_obj) # insert into# # 执行会话窗口中的所有操作# db_session.commit() #rud# db_session.close()# 2.增加批量数据# from create_table import User# db_session.add_all([# User(name AlexDSB ),# User(name DragonFire ),# User(name 赵丽颖 )# ])## db_session.commit()# db_session.close()#扩展 # from create_table import User# user1 User(name 666 )# user2 User(name 777 )# user3 User(name 888 )## db_session.add(user1)# db_session.add(user2)# db_session.add(user3)## db_session.commit()# db_session.close()

4.1外键增加数据

# 添加数据from sqlalchemy.orm import sessionmakerfrom create_table_ForeignKey import engine,Student,SchoolSession sessionmaker(engine)db_session Session()# 1.添加数据 - 笨# sch_obj School(name OldBoyBeijing )# db_session.add(sch_obj)# db_session.commit()## sch db_session.query(School).filter(School.name OldBoyBeijing ).first()# stu_obj Student(name DragonFire ,school_id sch.id)# db_session.add(stu_obj)# db_session.commit()# db_session.close()# 2.添加数据 - 正向 relationship 版# stu_obj Student(name 小笼包 ,stu2sch School(name OldBoyShenzhen ))# db_session.add(stu_obj)# db_session.commit()# db_session.close()# 3.添加数据 - 反向 relationship 版# sch_obj School(name OldBoyShanghai )# sch_obj.sch2stu [Student(name 赵丽颖 ),Student(name 陈妍希 )]# db_session.add(sch_obj)# db_session.commit()# db_session.close()

# 添加数据from sqlalchemy.orm import sessionmakerfrom create_table_ForeignKey import engine,Student,SchoolSession sessionmaker(engine)db_session Session()# 1.添加数据 - 笨# sch_obj School(name OldBoyBeijing )# db_session.add(sch_obj)# db_session.commit()## sch db_session.query(School).filter(School.name OldBoyBeijing ).first()# stu_obj Student(name DragonFire ,school_id sch.id)# db_session.add(stu_obj)# db_session.commit()# db_session.close()# 2.添加数据 - 正向 relationship 版# stu_obj Student(name 小笼包 ,stu2sch School(name OldBoyShenzhen ))# db_session.add(stu_obj)# db_session.commit()# db_session.close()# 3.添加数据 - 反向 relationship 版# sch_obj School(name OldBoyShanghai )# sch_obj.sch2stu [Student(name 赵丽颖 ),Student(name 陈妍希 )]# db_session.add(sch_obj)# db_session.commit()# db_session.close()

5.1外键删除数据

# 添加数据from sqlalchemy.orm import sessionmakerfrom create_table_ForeignKey import engine,Student,SchoolSession sessionmaker(engine)db_session Session()# 删除数据sch db_session.query(School).filter(School.name OldBoyShanghai ).first()db_session.query(Student).filter(Student.school_id sch.id).delete()db_session.commit()db_session.close()

# 查询# 会话窗口from sqlalchemy.orm import sessionmakerfrom create_table import engineSession sessionmaker(engine)db_session Session()from create_table import User# 1.简单查询# select * from table# user_list db_session.query(User).all()# for row in user_list:# print(row.id,row.name)# user db_session.query(User).first()# print(user.id,user.name)# 2.带条件的查询# user_list db_session.query(User).filter(User.id 4).all()# print(user_list[0].id,user_list[0].name)# user db_session.query(User).filter_by(id 4).first()# print(user.id,user.name)# user_list db_session.query(User).filter(User.id 4).all()# for row in user_list:# print(row.name,row.id)# 扩展 查看sql语句# sql db_session.query(User).filter(User.id 4)# print(sql)

6.1外键查询

# 添加数据from sqlalchemy.orm import sessionmakerfrom create_table_ForeignKey import engine,Student,SchoolSession sessionmaker(engine)db_session Session()# 1.查询数据 - relationship 版 正向# stu db_session.query(Student).all()# for row in stu:# print(row.id,row.name,row.school_id,row.stu2sch.name)# 2.查询数据 - relationship 版 反向# sch db_session.query(School).all()# for school in sch:# for student in school.sch2stu:# print(school.id,school.name,student.name)

6.2补充--查询

# 高级版查询操作,厉害了哦# 老规矩from create_table import User, enginefrom sqlalchemy.orm import sessionmakerSession sessionmaker(engine)db_session Session()# 查询数据表操作# and or# res db_session.query(User).filter(User.id 4 or User.name DragonFire ).all()# print(res[0].name,res)## if False and True:# print( lihaile )# from sqlalchemy.sql import and_ , or_# ret db_session.query(User).filter(and_(User.id 3, User.name DragonFire )).all()# print(ret)# ret db_session.query(User).filter(or_(User.id 4, User.name DragonFire )).all()# print(ret)# ret db_session.query(User).filter_by(id 4,name DragonFire ).all()# print(ret)# 查询所有数据# r1 db_session.query(User).all()# 查询数据 指定查询数据列 加入别名# r2 db_session.query(User.id.label( uid ),User.name.label( username )).first()# print(r2.uid,r2.username) # 15 NBDragon# 表达式筛选条件# r3 db_session.query(User).filter(User.name DragonFire ).all()# 原生SQL筛选条件# r4 db_session.query(User).filter_by(name DragonFire ).all()# r5 db_session.query(User).filter_by(name DragonFire ).first()# 字符串匹配方式筛选条件 并使用 order_by进行排序# from sqlalchemy.sql import text# r6 db_session.query(User).filter(text( id :value and name :name )).params(value 3, name DragonFire ).first()# print(r6.name)# 原生SQL查询# r7 db_session.query(User).from_statement(text( SELECT * FROM User where name :name )).params(name DragonFire ).all()# 筛选查询列# query的时候我们不在使用User ORM对象,而是使用User.name来对内容进行选取# user_list db_session.query(User.name).all()# print(user_list)# for row in user_list:# print(row.name)# 别名映射 name as nick# user_list db_session.query(User.name.label( nick )).all()# print(user_list)# for row in user_list:# print(row.nick) # 这里要写别名了# 筛选条件格式# user_list db_session.query(User).filter(User.name DragonFire ).all()# user_list db_session.query(User).filter(User.name DragonFire ).first()# user_list db_session.query(User).filter_by(name DragonFire ).first()# for row in user_list:# print(row.nick)# 复杂查询# from sqlalchemy.sql import text# user_list db_session.query(User).filter(text( id :value and name :name )).params(value 3,name DragonFire )# 查询语句# from sqlalchemy.sql import text# user_list db_session.query(User).filter(text( select * from User id :value and name :name )).params(value 3,name DragonFire )# 排序 :# user_list db_session.query(User).order_by(User.id.asc()).all()# user_list db_session.query(User).order_by(User.id.desc()).all()# for row in user_list:# print(row.name,row.id)# 其他查询条件 ret session.query(User).filter_by(name DragonFire ).all()ret session.query(User).filter(User.id 1, User.name DragonFire ).all()ret session.query(User).filter(User.id.between(1, 3), User.name DragonFire ).all() # between 大于1小于3的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() 子查询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 funcret 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() # ret db_session.query(User).filter(User.id.between(1, 3)).all() # between 大于1小于3的# print(ret)# ret db_session.query(User).filter(User.id.in_([1,4])).all() # in_([1,3,4]) 只查询id等于1,3,4的# ret db_session.query(User).filter(~User.id.in_([1,2,3])).all() # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的# print(ret)# from sqlalchemy.sql import and_, or_## ret db_session.query(User).filter(# or_(# User.id 2,# and_(User.name eric , User.id 3),# User.name ! # ))# print(ret)# 关闭连接db_session.close()

# 更新修改数据from sqlalchemy.orm import sessionmakerfrom create_table import engine, UserSession sessionmaker(engine)db_session Session()# 1.修改数据 - 先查询预修改的数据# update user set name 666 where id 1# res db_session.query(User).filter(User.name Ywb ).update({ name : 武配齐 })# print(res)# db_session.commit()# db_session.close()# res db_session.query(User).filter(User.id 5).update({ name :123456})# print(res)# db_session.commit()# db_session.close()

7.1外键更新数据

# 添加数据from sqlalchemy.orm import sessionmakerfrom create_table_ForeignKey import engine,Student,SchoolSession sessionmaker(engine)db_session Session()# 修改数据sch db_session.query(School).filter(School.name OldBoyShanghai ).first()db_session.query(Student).filter(Student.name 小笼包 ).update({ school_id :sch.id})db_session.commit()db_session.close()

7.2补充更新

#高级版更新操作from create_table import User,enginefrom sqlalchemy.orm import sessionmakerSession sessionmaker(engine)db_session Session()#直接修改# db_session.query(User).filter(User.id 0).update({ name : 099 })#在原有值基础上添加 - 1db_session.query(User).filter(User.id 0).update({User.name: User.name 099 }, synchronize_session False)db_session.commit()#在原有值基础上添加 - 2# db_session.query(User).filter(User.id 0).update({ age : User.age 1}, synchronize_session evaluate )# db_session.commit()

from sqlalchemy.ext.declarative import declarative_baseBase declarative_base()from sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import relationshipclass Girls(Base): __tablename__ girl id Column(Integer(),primary_key True,autoincrement True) name Column(String(32)) girl2boy relationship( Boy ,secondary Hote ,backref boy2girl )class Boy(Base): __tablename__ boy id Column(Integer(),primary_key True,autoincrement True) name Column(String(32))class Hote(Base): __tablename__ hote id Column(Integer(),primary_key True,autoincrement True) name Column(String(32)) girl_id Column(Integer, ForeignKey( girl.id )) boy_id Column(Integer, ForeignKey( boy.id ))from sqlalchemy import create_engineengine create_engine( mysql pymysql://root: 127.0.0.1:3306/day127?charset utf8 )Base.metadata.create_all(engine)

8.1多对多的增加数据

from sqlalchemy.orm import sessionmakerfrom app.M2m import engineSession sessionmaker(engine)db_session Session()#1.增加 # 1.通过Boy添加Girl和Hotel数据boy Boy(name DragonFire )boy.girl2boy [Girls(name 赵丽颖 ),Girls(name Angelababy )]db_session.add(boy)db_session.commit() # 2.通过Girl添加Boy和Hotel数据girl Girls(name 珊珊 )girl.boys [Boy(name Dragon )]db_session.add(girl)db_session.commit()

8.2多对多的查询数据

 1 from my_M2M import Girl,Boy,Hotel,engine 2 3 # 创建连接 4 from sqlalchemy.orm import sessionmaker 5 # 创建数据表操作对象 sessionmaker 6 DB_session sessionmaker(engine) 7 db_session DB_session() 8 9 # 1.通过Boy查询约会过的所有Girl10 hotel db_session.query(Boy).all()11 for row in hotel:12 for row2 in row.girl2boy:13 print(row.name,row2.name)14 15 # 2.通过Girl查询约会过的所有Boy16 hotel db_session.query(Girl).all()17 for row in hotel:18 for row2 in row.boys:19 print(row.name,row2.name)

转载于:https://www.cnblogs.com/RootEvils/p/10374147.html