一.SQLAlchemy (相当于Django里的model)

注：环境中没有包先下载

1.通过SQLAlchemy创建数据表

#1.导入SQLAlchemyfrom 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_engineengine=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 sessionmakerSession=sessionmaker(engine) #创建会话窗口db_session=Session() #打开会话窗口# 3.增加数据from create_table import Useruser_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 sessionmakerSession=sessionmaker(engine) #创建会话窗口db_session=Session() #打开会话窗口# 3.增加数据from create_table import Useruser1=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 sessionmakerSession=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 sessionmakerfrom sqlalchemy import create_engineengine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8")Session=sessionmaker(engine) #创建会话窗口db_session=Session() #打开会话窗口# 1.查询所有from create_table import Useruser_list1=db_session.query(User) #得到 SELECT user.id AS user_id, user.name AS user_name FROM useruser_list2=db_session.query(User).all() #得到 [
     
      , 
      
       , 
       
        , 
        
         ]for row in user_list2:    print(row.id,row.name)'''结果为：2 aaa1 amy3 bbb4 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 cccuser_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 sessionmakerfrom sqlalchemy import create_engineengine=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 Userres=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 Userres=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 sessionmakerfrom sqlalchemy import create_engineengine=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 Userres=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_baseBase=declarative_base() #基类from sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import relationshipclass 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_engineengine=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 sessionmakerSession=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 sessionmakerSession=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 sessionmakerSession=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 sessionmakerSession=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 sessionmakerSession=sessionmaker(engine) #创建会话窗口db_session=Session() #打开会话窗口# 3.更新数据from create_table_foreign import School,Studentsch=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 sessionmakerSession=sessionmaker(engine) #创建会话窗口db_session=Session() #打开会话窗口# 3.删除数据from create_table_foreign import School,Studentsch_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()#betweenret = session.query(User).filter(User.id.between(1, 3), User.name == 'DragonFire').all() # between 大于1小于3的#inret = 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 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()#高级版更新操作from my_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)#在原有值基础上添加 - 2db_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_baseBase=declarative_base()  #基类from sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import relationshipclass 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_engineengine=create_engine("mysql+pymysql://root:@127.0.0.1:3306/day127?charset=utf8")#去数据库中创建User对象所对应的数据表Base.metadata.create_all(engine)

(2)添加

from sqlalchemy.orm import sessionmakerfrom m2m import engine,Boys,GirlsSession=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 sessionmakerfrom m2m import engine,Boys,GirlsSession=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)