SQLALchemy之增删改查、一对多、多对多

SQLALchemy之增删改查、一对多、多对多

• Django 的 Models 仅仅只是配置和使用比较简单,因为他是Django自带的ORM框架,也正是因为是Django原生的,所以兼容性远远不如SQLAlchemy

• 真正算得上全面的ORM框架必然是我们的SQLAlchemy ORM框架,它可以在任何使用SQL查询时使用

建表

# ORM中的数据表是什么呢?
# Object Relation Mapping
# Object - Table 通过 Object 去操纵数据表
# 从而引出了我们的第一步创建数据表 - 创建Object
# 1. 创建Object
# class User(object):
#     pass

# 让object与table产生某种关系，也就是让object与数据表格式极度相似
# 导入官宣基本模型
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()  #实例化官宣模型----base就是官宣模型
# 当前的这个Object继承了Base也就是代表了Object继承了ORM的模型
class User(Base):  #相当于django中的Models中的Model
    #为table创建名称
    __tablename__ = "User"
    #创建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:123@127.0.0.1:3306/chartset=utf8")
# base 自动检索所有继承orm的对象  并且创建所有的数据表
Base.metadata.create_all(engine)

添加数据

# insert into user(name) values('maqian')  ----sql语句

# orm中的操作
from SQLAlchemy_test.my_table import User  # 导入之前创建好的对象

# 创建一条数据
user1 = [User(id=1, name='maqian'),
         User(id=2, name='aqian'),
         User(id=3, name='baqian'),
         User(id=4, name='caqian')]
# 写入数据库: 打开数据库会话，即创建一个操作数据库的窗口
from sqlalchemy.orm import sessionmaker
from SQLAlchemy_test.my_table import engine  # 导入之前创建好的数据库引擎

Session = sessionmaker(engine)  # 创建会话对象，并将engine交给会话对象
db_session = Session()  # 打开会话对象
db_session.add_all(user1)  # 在会话中添加orm中user模型中的一条数据
db_session.commit()  # 会话提交
db_session.close()  # 关闭会话

查找

from SQLAlchemy_test.my_table import User, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)  # 创建session会话对象
db_session = Session()  # 开启session会话

user_list = db_session.query(User).all()  #取出所有数据
# [<SQLAlchemy_test.my_table.User object at 0x00000273D4F13C08>,
#   <SQLAlchemy_test.my_table.User object at 0x00000273D4F13EC8>]
print(user_list)
for i in user_list:
    print(i.id,i.name)  #取出真正的数据

db_session.close()  #关闭session会话

user_list = db_session.query(User).filter(User.id <= 1).all()  #取出id小于1的所有数据
print(user_list)
for i in user_list:
    print(i.id,i.name)  #取出真正的数据
db_session.close()

查找进阶

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

#查询数据表操作
from sqlalchemy.sql import or_,and_
ret = db_session.query(User).filter(and_(User.id >=0,User.name=='maqian')).all()
ret1 = db_session.query(User).filter(or_(User.id >=0,User.name=='maqian')).all()
db_session.close()

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

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

#表达式删选条件
r3 = db_session.query(User).filter(User.name=='haven').all()
print(r3)
#原生sql筛选条件
r4 = db_session.query(User).filter_by(name='haven').all()
r5 = db_session.query(User).filter_by(name='haven').first()

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

#原生sql查询
# r7 = db_session.query(User).from_statement(text("select * from user where name =:haven")).parmas(
#     name = 'haven').all()

#筛选查询列
r8 = db_session.query(User.name).all()
for i in r8:
    print(i.name)

#别名映射 name as nick
r9 = db_session.query(User.name.label('nick')).all()
for i in r9:
    print(i.nick)

#复杂查询
from sqlalchemy.sql import text
r10 = db_session.query(User).filter(text('id<:value and name=:name')).params(value=224,name='haven').order_by(User.id).all()
print(r10)

#排序
# user_list = db_session.query(User).order_by(User.id).all()
user_list = db_session.query(User).order_by(User.id.desc()).all()
for i in user_list:
    print(i.id,i.name)
db_session.close()

改

from SQLAlchemy_test.my_table import User,engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

#跟新表里面某条sql语句  update 表名 set 字段名=‘值’ where 条件
res = db_session.query(User).filter(User.id==1).update({'name':'haven'})  #更新一条
print(res)  # 1 res是更新语句所更新的行数

db_session.commit()  #数据的增、改、删操作要进行提交
db_session.close()

res = db_session.query(User).filter(User.id<=1).update({'name':'haven'})  #更新多条
print(res)  # 2 res是更新语句所更新的行数

db_session.commit()  #数据的增、改、删操作要进行提交
db_session.close()

改--进阶

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

#直接修改
db_session.query(User).filter(User.id > 0).update({'name':'maqian'})

#在原有的基础上添加 -1
db_session.query(User).filter(User.id > 0).update({User.name:User.name + '099'},synchronize_session=False)
lst = db_session.query(User).all()
for i in lst:
    print(i.id,i.name)

#在原有基础值上添加 -2
db_session.query(User).filter(User.id>1).update({"age":User.age + 1},synchronize_session = 'evaluate')
db_session.commit()

删

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

#删除一条数据 sql  delete from 表名 where 条件

res = db_session.query(User).filter(User.id == 1).delete()
print(res)  # 1  删除数据的行数
db_session.commit()
db_session.close()

一对多

建表

# 创建表及表关系relationship
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Class_Table(Base):
    __tablename__ = 'classtable'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    # 关联字段，使class_id与classtable的id进行关联，这里的外键一定是表名.字段
    class_id = Column(Integer, ForeignKey('classtable.id'))
    # 将Student与Class_Table创建关系，这个不是字段，只是关系，back_ref是反向关联的关键字
    to_class = relationship('Class_Table', backref='s_class')


from sqlalchemy import create_engine

# 连接数据库，创建数据库引擎
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/orm_test?charset=utf8')
Base.metadata.create_all(engine)

添加

from SQLAlchemy_test.my_foreign_key import Class_Table, Student, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# 添加两个班级
lst = [Class_Table(id=0, name='class1'),
       Class_Table(id=1, name='c_21'),
       Class_Table(id=2, name='c_31'),
       Class_Table(id=3, name='c_41'),
       Class_Table(id=4, name='c_51')]
db_session.add_all(lst)
db_session.commit()


# 添加一个学生 douzi  班级是 class1
#
# 查询要添加的班级
class_obj = db_session.query(Class_Table).filter(Class_Table.name == 'class1').first()
# 创建学生
stu = Student(name='douzi', class_id=class_obj.id)
db_session.add(stu)
db_session.commit()

#relationship版添加数据，通过 to_class 可以做到两件事
       # 1.在classtable中添加一条数据
       # 2.在student中添加一条数据，并将刚才添加到classtable中的数据id与student中的class_id关联起来
# 反向添加数据
class_obj = Class_Table(name='python')
# 通过class_obj中的反向关联字段backref='s_class'
#在sutdent中添加两条数据，并将2条数据的class_id写成class_obj的id
class_obj.s_class = [Student(name='zhouzhou'),Student(name='yueyue')]
db_session.add(class_obj)
db_session.commit()
db_session.close()

查

from SQLAlchemy_test.my_foreign_key import Class_Table,Student,engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

#显示所有数据，班级名称，链表查询
stu_list = db_session.query(Student).all()
for i in stu_list:
    print(i.id,i.name,i.class_id)

#反向查询
class_lst = db_session.query(Class_Table).all()
for i in class_lst:
    for n in i.s_class:
        print(i.name,n.name)
        #i.s_class 通过backref中的s_class反向关联到student表中的ID获取name

改

from SQLAlchemy_test.my_foreign_key import Class_Table,Student,engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

class_info = db_session.query(Class_Table).filter(Class_Table.name=='python').first()
db_session.query(Student).filter(Student.class_id==class_info.id).update({'name':'java'})

db_session.commit()
db_session.close()

删

from SQLAlchemy_test.my_foreign_key import Class_Table,Student,engine
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

#删除
class_info = db_session.query(Class_Table).filter(Class_Table.name == 'class1').first()
db_session.query(Student).filter(Student.id == class_info.id).delete()
db_session.commit()
db_session.close()

多对多

建表

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
from sqlalchemy import Column, String, ForeignKey, Integer
from sqlalchemy.orm import relationship


class School(Base):
    __tablename__ = 'school'
    id = Column(Integer, primary_key=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    # 创建关系
    girls = relationship('Girl', secondary='school', backref='girl2boy')


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)


from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/orm_test?charset=utf8')
Base.metadata.create_all(engine)

添加

from SQLAlchemy_test.my_M2M import School, Boy, Girl, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# 通过Boy添加girl和school数据
# boy = Boy(name='吴亦凡3')
boy = db_session.query(Boy).filter(Boy.name == "吴亦凡2").first()
girl = db_session.query(Girl).filter(Girl.name == "赵丽颖").first()
boy.girls = [girl]
# db_session.add(boy)
db_session.commit()

# 通过girl添加BOY和school数据

# girl = Girl(name='杨幂')
# girl.girl2boy = [Boy(name='周杰伦')]
# db_session.add(girl)
# db_session.commit()

查找

from SQLAlchemy_test.my_M2M import School, Boy, Girl, engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# 通过boy查询所有的girl----反向查找
school = db_session.query(Girl).all()
for row in school:
    for row2 in row.girl2boy:
        print(row.name, row2.name)

# 通过girl查询boy----正向查找
school = db_session.query(Boy).all()
for row in school:
    for row2 in row.girls:
        print(row.name, row2.name)