sqlalchemy 基操,勿6

 - 使用前请先安装sqlalchemy

 

 - 创建数据表

# 导入官宣基础模型
from sqlalchemy.ext.declarative import declarative_base
# 实例化官宣模型 - Base 就是 ORM 模型
Base = declarative_base()
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base):  # 相当于 Django Models中的 Model
    # 为Table创建名称
    __tablename__ = "user"
    # 创建ID数据字段 , 创建ID字段 == 创建ID数据列
    from sqlalchemy import Column,Integer,String
    # id = Column(数据类型,索引,主键,外键,等等)
    # int == Integer
    id = Column(Integer,primary_key=True,autoincrement=True)
    # str == char(长度) == String(长度)
    name = Column(String(32),index=True)

# 去连接数据库 创建数据引擎
from sqlalchemy import create_engine
# 创建的数据库引擎
engine = create_engine("mysql+pymysql://root:这里是sql密码没有可以不写@127.0.0.1:3306/dragon?charset=utf8")

# Base 自动检索所有继承Base的ORM 对象 并且创建所有的数据表
Base.metadata.create_all(engine)

 

 - 增删改查 (单表)

# 导入创建好的User和engine
from create_table import engine,User
# 导入 sqlalchemy.orm 中的 sessionmaker 就是创建一个操纵数据库的窗口
from sqlalchemy.orm import sessionmaker
# 创建 sessionmaker 会话对象,将数据库引擎 engine 交给 sessionmaker
Session = sessionmaker(engine)
# 打开会话对象 Session
db_session = Session()

# 方法一
# 创建数据
user_obj = User(name="jamlee")
# 在db_session会话中添加一条 UserORM模型创建的数据
db_session.add(user_obj)
# 使用 db_session 会话提交 , 这里的提交是指将db_session中的所有指令一次性提交
db_session.commit()
db_session.close()

# 方法二 提交多条数据
db_session.add_all([
    User(name="123"),
    User(name="wqz"),
    User(name="ywb"),
])

db_session.commit()
db_session.close()

from create_table import User,engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

res = db_session.query(User).filter(User.name=="ywb").update({"name":"猪"})
print(res) # res就是我们当前这句更新语句所更新的行数
db_session.commit()
db_session.close()

# 修改多条
res = db_session.query(User).filter(User.id <= 20).update({"name":"猪头"})
print(res) 
db_session.commit()
db_session.close()

from create_table import User,engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

# 查询所有
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.name)

# 查询符合条件的所有
user = db_session.query(User).filter(User.name == "alex").all()
print(user[0].name)

# 查询符合条件的第一条
user = db_session.query(User).filter_by(id=4).first()
print(user.id,user.name)

# 查询sql原始语句
user = db_session.query(User)
print(user)

最后记得都要关闭哦
db_session.close()

from create_table import engine,User
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

db_session.query(User).filter(User.id==4).delete()
db_session.commit()
db_session.close()

from my_create_table import User,engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# 查询数据表操作
# and or
from sqlalchemy.sql import and_ , or_
ret = db_session.query(User).filter(and_(User.id > 3, User.name == 'jamlee')).all()
ret = db_session.query(User).filter(or_(User.id < 2, User.name == 'jamlee')).all()

# 查询所有数据
r1 = db_session.query(User).all()

# 查询数据 指定查询数据列 加入别名
r2 = db_session.query(User.name.label('username'), User.id).first()
print(r2.id,r2.username) 

# 表达式筛选条件
r3 = db_session.query(User).filter(User.name == "jamlee").all()

# 原生SQL筛选条件
r4 = db_session.query(User).filter_by(name='jamlee').all()
r5 = db_session.query(User).filter_by(name='jamlee').first()

# 字符串匹配方式筛选条件 并使用 order_by进行排序
r6 = db_session.query(User).filter(text("id<:value and name=:name")).params(value=224, name='jamlee').order_by(User.id).all()

#原生SQL查询
r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='jamlee').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 == "jamlee").all()
user_list = db_session.query(User).filter(User.name == "jamlee").first()
user_list = db_session.query(User).filter_by(name="jamlee").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="jamlee")

# 查询语句
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="jamlee")

# 排序 :
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)

#其他查询条件
"""
ret = session.query(User).filter_by(name='jamlee').all()
ret = session.query(User).filter(User.id > 1, User.name == 'jamlee').all()
ret = session.query(User).filter(User.id.between(1, 3), User.name == 'jamlee').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='jamlee'))).all() 子查询
from sqlalchemy import and_, or_
ret = session.query(User).filter(and_(User.id > 3, User.name == 'jamlee')).all()
ret = session.query(User).filter(or_(User.id < 2, User.name == 'jamlee')).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()
"""

# 关闭连接
db_session.close()

#高级版更新操作
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()

 

 - ForeignKey (一对多)

　　 - 创建数据表+relationship

from sqlalchemy.ext.declarative import declarative_base
# 关系映射
from sqlalchemy.orm import relationship
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    # 关联字段 主外键关系(这里的ForeignKey一定要是表名.id不是对象名)
    school_id = Column(Integer,ForeignKey("school.id"))
    
    # 将student 和school创建关系 这个不是字段,只是关系,backref是反向关联的关键字
    stu2sch = relationship("School",backref = "sch2stu")

class School(Base):
    __tablename__ = "school"
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/userinfo?charset=utf8")
Base.metadata.create_all(engine)

　　 - 增删改查

from sqlalchemy.orm import sessionmaker
from create_table import engine, Student, School

Session = sessionmaker(engine)
db_session = Session()

# 先演示一下笨方法
sch = db_session.query(School).filter(School.name == "清华").first()
stu_obj = Student(name="jamlee",school_id=sch.id)
db_session.add(stu_obj)
db_session.commit()
db_session.close()

# 开始表演 正向插入
stu_obj = Student(name="wqz",stu2sch=School(name = "北大"))
db_session.add(stu_obj)
db_session.commit()
db_session.close()

# 反向插入
sch_obj = School(name="复旦")
sch_obj.sch2stu = [Student(name="123"),Student(name="ywb")]
db_session.add(sch_obj)
db_session.commit()
db_session.close()

from sqlalchemy.orm import sessionmaker
from create_table import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

# 正向查询
stu = db_session.query(Student).all()
for row in stu:
    print(row.id,row.name,row.school_id,row.stu2sch.name)

# 反向查询
sch = db_session.query(School).all()
for school in sch:
    for student in school.sch2stu:
        print(school.id,school.name,student.name)

from sqlalchemy.orm import sessionmaker
from create_table import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

sch = db_session.query(School).filter(School.name=="清华").first()
db_session.query(Student).filter(Student.name=="wqz").update({"school_id":sch.id})
db_session.commit()
db_session.close()

from sqlalchemy.orm import sessionmaker
from create_table import engine,Student,School

Session = sessionmaker(engine)
db_session = Session()

sch = db_session.query(School).filter(School.name=="北大").first()
db_session.query(Student).filter(Student.school_id==sch.id).delete()
db_session.commit()
db_session.close()

 

 

 - ManyToMany(多对多)

　　 - 创建表

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/userinfo?charset=utf8")

Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
from create_table import engine,Boys,Girls

Session = sessionmaker(engine)
db_session = Session()

# 正向
girl_obj = Girls(name="热巴",girl2boy=[Boys(name="jamlee"),Boys(name="wqz")])
db_session.add(girl_obj)
db_session.commit()
db_session.close()

# 反向
boy = Boys(name="GD")
boy.boy2girl = [Girls(name="IU"),Girls(name="YWB")]
db_session.add(boy)
db_session.commit()
db_session.close()

from sqlalchemy.orm import sessionmaker
from create_table import engine,Boys,Girls

Session = sessionmaker(engine)
db_session = Session()

# 正向
g_list =db_session.query(Girls).all()

for girl in g_list:
    for boy in girl.girl2boy:
        print(girl.name,boy.name)

# 反向
b_list = db_session.query(Boys).all()
for boy in b_list:
    for girl in boy.boy2girl:
        print(boy.name,girl.name)