SQLAlchemy

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

注：环境中没有包先下载

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)