ORM之SQLALchemy

今天来聊一聊 Python 的 ORM 框架 SQLAlchemy 

SQLAlchemy 没有 Django 的 Models 好用！因为models是Django自带的ORM框架,也正是因为是Django原生的,所以兼容性远远不如SQLAlchemy

真正算得上全面的ORM框架必然是我们的SQLAlchemy ORM框架,它可以在多语言中使用SQL查询

SQLAlchemy 如何使用:

一.下载

pip isntall  SQLALchemy

二.创建数据表

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()  # Base是 ORM模型的基类
# ORM模型：
#         obj里面的属性 == table中创建的字段
#         obj定义table的操作方式和属性

from sqlalchemy import Column, Integer, INT, INTEGER, VARCHAR, String


# 1.创建一个class
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)


# 2.创建数据引擎
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy_test?charset=utf8")

# 3.将所有继承Base的class序列化成数据表
Base.metadata.create_all(engine)

三 . 增删改查操作

1.增加数据

# 可视化工具中，分四步操作数据
# 1.选中数据库 - 创建数据库引擎 导入数据库引擎
# 2.创建查询窗口,必须是选中数据库的查询窗口
# 3.创建sql语句
# 4.点击运行


# 1.选中数据库 - 创建数据库引擎 导入数据库引擎
from create_table import engine

# 2.创建查询窗口,必须是选中数据库的查询窗口
from sqlalchemy.orm import sessionmaker

Session_window = sessionmaker(engine)
# 打开查询窗口
db_session = Session_window()

# 1.增加数据   原生sql
# insert into table(字段) value('123')
# from create_table import User  # 获取User类
#
# user_obj = User(name='小明')  # 相当于创建sql语句
# db_session.add(user_obj)  # 将sql语句粘贴到查询窗口中
# db_session.commit()  # 执行全部语句
# db_session.close()  # 关闭连接

# # 2.增加多条数据
from create_table import User

user_obj_list = [User(name='赵丽颖'), User(name='江疏影')]
db_session.add_all(user_obj_list)  # 添加所有的语句
db_session.commit()  
db_session.close()

2.查询数据

# 原生sql语句
# select * from table

# 创建查询窗口
from create_table import engine, User
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)  # 创建数据引擎
db_session = Session()  # 打开查询窗口

# # 1.查询数据
user_obj = db_session.query(User).first()
print(user_obj.id, user_obj.name)  # 1 小明  # 第一个数据

user_obj_list = db_session.query(User).all()
for user in user_obj_list:
    print(user.id, user.name)  # 所有的数据

# 2.带条件的查询
# 2.1 根据表达式获取数据
user_obj_list = db_session.query(User).filter(User.id <= 2, User.name == '赵丽颖').all()
print(user_obj_list)
for user in user_obj_list:
    print(user.id, user.name)  # 根据id条件和name的名字获取到数据

# 根据指定条件获取数据
user_obj_list = db_session.query(User).filter_by(id=2, name="赵丽颖").all()
print(user_obj_list)
for user in user_obj_list:
    print(user.id, user.name)

3.修改更新数据

# 原生sql:   update table set name = '123'

# 创建查询窗口
from create_table import engine, User
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)  # 创建数据引擎
db_session = Session()  # 打开查询窗口

# 1. 修改一条数据
user_obj = db_session.query(User).filter(User.id == 1).update({'name': '小明1'})
print(user_obj)  # 打印的是库中受影响的数量
db_session.commit()

# 修改多条数据
user_obj = db_session.query(User).filter(User.id >= 1).update({'name': '111'})
db_session.commit()  # name 全部修改为  111

4.删除数据

# sql原生：    delete from table

# 创建查询窗口
from create_table import engine, User
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)  # 创建数据引擎
db_session = Session()  # 打开查询窗口

# 1.删除单条数据
res = db_session.query(User).filter(User.id == 1).delete()
db_session.commit()


# 2.删除多条数据
res = db_session.query(User).filter(User.id>=1).delete()
db_session.commit()

四 . 一对多的操作

1.创建数据表及关系relationship

from sqlalchemy.ext.declarative import declarative_base  # 导入 sqlalchemy 基类

from sqlalchemy import Column, INT, VARCHAR, ForeignKey  # 导入字段和类型
from sqlalchemy.orm import relationship  # 从orm中导入relationship的关系映射

Base = declarative_base()  # 实例化一个基类


class School(Base):
    __tablename__ = 'school'
    id = Column(INT, primary_key=True)
    name = Column(VARCHAR(32))


class Student(Base):
    __tablename__ = 'student'
    id = Column(INT, primary_key=True)  # int + 主键  默认自增长
    name = Column(VARCHAR(32))
    # 这里的ForeignKey一定要是   表名.id   不是对象名
    school_id = Column(INT, ForeignKey('school.id'))  # 对应学校的外键

    # 将student和school 创建关系，这个不是字段 ，只是关系， backref是反向关联的关键字
    stu2sch = relationship('School', backref='sch2stu')


# 创建引擎
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy_test?charset=utf8')

Base.metadata.create_all(engine)  # 创建表

2.基于relationship增加数据

from sqlalchemy.orm import sessionmaker
from foreingKey一对多.create_table_ForeignKey import engine, School, Student

# 创建操作窗口
Session = sessionmaker(engine)
db_session = Session()

# 1.增加数据
# # 添加学校表数据
# sch_obj = School(name='beijingSchool')
# db_session.add(sch_obj)
# db_session.commit()
#
#

# # 笨办法
# # 获取学校对象
# sch_obj = db_session.query(School).filter(School.name == 'beijingSchool').first()
# # 将要写入的学生对象
# stu_obj = Student(name='小明', school_id=sch_obj.id)  # 写入关联的学校
# db_session.add(stu_obj)
# db_session.commit()


# # 2 Relationship 版 添加数据操作 - 正向
# stu_obj = Student(name='小红', stu2sch=School(name='beijingSchool'))
# db_session.add(stu_obj)
# db_session.commit()



# 3 Relationship 版 添加数据操作 - 反向
sch_obj = School(name="beijingSchool")
sch_obj.sch2stu = [Student(name="赵丽颖"),Student(name="陈妍希")]
db_session.add(sch_obj)
db_session.commit()

3.基于relationship查询数据

from sqlalchemy.orm import sessionmaker
from foreingKey一对多.create_table_ForeignKey import engine, School, Student

Session = sessionmaker(engine)
db_session = Session()

# # 1.查询 笨
# sch_obj = db_session.query(School).filter(School.name == 'beijingSchool').first()
# beijing_stu_obj = db_session.query(Student).filter(Student.school_id == sch_obj.id).first()
# print(beijing_stu_obj.id, beijing_stu_obj.name)


# # 2.relationship 正向查询
# stu_obj = db_session.query(Student).filter(Student.name=='小明').first()
# print(stu_obj.name, stu_obj.stu2sch.name)


# # 3.relationship 反向查询
# sch_obj_list = db_session.query(School).all()
# for sch_obj in sch_obj_list:
#     for stu in sch_obj.sch2stu:
#         print(sch_obj.name, stu.name)

五 . 多对多的操作

1.创建表关系

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()  # d导入并创建基类

# 导入字段和属性   导入orm 的关系映射
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))
    g2b = relationship('Boys', backref='b2g', secondary='hotel')  # 建立GirlS 和Boys的关系映射


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)
    boy_id = Column(Integer, ForeignKey("boy.id"))  # boy 的外键
    girl_id = Column(Integer, ForeignKey("girl.id"))  # girl 的外键


# 创建引擎
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/sqlalchemy_test?charset=utf8')

Base.metadata.create_all(engine)  # 创建表

2.基于relationship增加数据

from 多对多.create_table_m2m import engine, Boys, Girls, Hotel
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()


# 1.增加数据 - relationship 正向
#
# girl_obj = Girls(name="赵丽颖")
# girl_obj.g2b = [Boys(name="小明")]
# db_session.add(girl_obj)
# db_session.commit()


# 2.增加数据 - relationship 反向

boy_obj = Boys(name="小刚")
boy_obj.b2g = [Girls(name="111"),Girls(name="222")]
db_session.add(boy_obj)
db_session.commit()

3.基于relationship查询数据

from 多对多.create_table_m2m import engine, Boys, Girls, Hotel
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()


# 1.查询数据 - relationship 正向
girl_obj_list = db_session.query(Girls).all()
for girl in girl_obj_list:
    for boy in girl.g2b:
        print(girl.name,boy.name)


# 2.查询数据 - relationship 反向
boy_obj_list = db_session.query(Boys).all()
for boy in boy_obj_list:
    for girl in boy.b2g:
        print(girl.name, boy.name)