SQLAlchemy

SQLAlchemy

ORM 框架

概念	对应数据库	说明
Engine	连接
Session	连接池、事务
Model	表
Column	列
Query	若干行

创建数据表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

# Base = ORM基类 - 要按照ORM的规则定义你的类
Base = declarative_base()  # Model = Base


class Users(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String(32), nullable=False)


# 打开数据库的连接 - 创建数据库引擎
from sqlalchemy import create_engine

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

Base.metadata.create_all(engine)

简单CRUD

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()  # Model = Base


class Users(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String(32), nullable=False)


# 创建连接
engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy666?charset=utf8")
Base.metadata.create_all(engine)
# 打开会话对象， 开始CRUD
Session = sessionmaker(engine)
db_session = Session()

# 1. 增加数据
# insert into user values (1,'123')
# user = Users(name="123")
# db_session.add(user)
# db_session.commit()
# db_session.close()

# 一次性添加多个
# db_session.add_all([Users(name="xx"), Users(name="cc")])
# db_session.commit()
# db_session.close()

# 2. 查  select * from table
# res = db_session.query(Users).all()
# for u in res:
#     print(u.id, u.name)

# res = db_session.query(Users).first()
# print(res.id, res.name)

# res = db_session.query(Users).filter(Users.id == 3).first()
# print(res.name)

# res = db_session.query(Users).filter(Users.id <= 2).all()
# for u in res:
#     print(u.id, u.name)

# res = db_session.query(Users).filter(Users.id == 2, Users.name == "xx").first()
# print(res.name)

# 3. 改
# update user set name="" where id=1
# res = db_session.query(Users).filter(Users.id == 1).update({"name": "66666"})
# print(res)
# db_session.commit()

# res = db_session.query(Users).update({"name": "哈哈哈"})
# print(res)
# db_session.commit()


# 4. 删
# delete from table where id=1
# res = db_session.query(Users).filter(Users.id == 1).delete()
# print(res)
# db_session.commit()

# res = db_session.query(Users).delete()
# print(res)
# db_session.commit()

创建外键关系 表

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    sch_id = Column(Integer, ForeignKey("school.id"))
    stu2sch = relationship("School", backref="sch2stu")  # M2M,正向 stu2sch ，反向 sch2stu


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


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

Base.metadata.create_all(engine)

CRUD 外键

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    sch_id = Column(Integer, ForeignKey("school.id"))
    stu2sch = relationship("School", backref="sch2stu")  # M2M,正向 stu2sch ，反向 sch2stu


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


engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy666?charset=utf8")
Session = sessionmaker(engine)
db_session = Session()

# 1. 添加数据
# sch = School(name="233School")
# db_session.add(sch)
# db_session.commit()
#
# sch_info = db_session.query(School).filter(School.name == "233School").first()
#
# stu = Student(name="233", sch_id=sch_info.id)
# db_session.add(stu)
# db_session.commit()
# db_session.close()

# relationship  正向添加
# stu = Student(name="stu2", stu2sch=School(name="school2"))
# db_session.add(stu)
# db_session.commit()

# relationship  反向添加
# sch = School(name="school3")
# sch.sch2stu = [Student(name="stu3"), Student(name="stu4")]
# db_session.add(sch)
# db_session.commit()

# 2.查询
# res = db_session.query(Student).all()
# for stu in res:
#     print(stu.name, stu.stu2sch.name)

# res = db_session.query(School).all()
# for sch in res:
#     for stu in sch.sch2stu:
#         print(sch.name, stu.name)

创建多对多关系表

from sqlalchemy import String, Integer, Column, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

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


class Pants(Base):
    __tablename__ = "pants"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

    p2c = relationship("Coat", backref="c2p", secondary="collocation")  # secondary 数据库表名


class Coat(Base):
    __tablename__ = "coat"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


class Collocation(Base):
    __tablename__ = "collocation"
    id = Column(Integer, primary_key=True)
    pants_id = Column(Integer, ForeignKey("pants.id"), nullable=False)
    coat_id = Column(Integer, ForeignKey("coat.id"), nullable=False)


Base.metadata.create_all(engine)

CRUD 多对多

from sqlalchemy import String, Integer, Column, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()
engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy666?charset=utf8")


class Pants(Base):
    __tablename__ = "pants"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)

    p2c = relationship("Coat", backref="c2p", secondary="collocation")  # secondary 数据库表名


class Coat(Base):
    __tablename__ = "coat"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


class Collocation(Base):
    __tablename__ = "collocation"
    id = Column(Integer, primary_key=True)
    pants_id = Column(Integer, ForeignKey("pants.id"), nullable=False)
    coat_id = Column(Integer, ForeignKey("coat.id"), nullable=False)


Session = sessionmaker(engine)
db_session = Session()
# 1. 增加数据
# relationship 正向添加
# p = Pants(name='pants1', p2c=[Coat(name='coat1'), Coat(name='coat2')])
# db_session.add(p)
# db_session.commit()

# relationship 反向添加
# c = Coat(name='coat11')
# c.c2p = [Pants(name='pants11'), Pants(name='pants12')]
# db_session.add(c)
# db_session.commit()

# 2. 查询
# relationship 正向
# res = db_session.query(Pants).all()
# for p in res:
#     for c in p.p2c:
#         print(p.name, c.name)

# relationship 反向
# res = db_session.query(Coat).all()
# for c in res:
#     for p in c.c2p:
#         print(c.name, p.name)

CRUD 高级操作

from sqlalchemy import String, Integer, Column, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/sqlalchemy666?charset=utf8")


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=ForeignKey)


Session = sessionmaker(engine)
db_session = Session()

# 查询数据表操作
# and or
from sqlalchemy.sql import and_, or_

# ret = db_session.query(User).filter(and_(User.id > 1, User.name == '哈哈哈')).all()
# ret = db_session.query(User).filter(or_(User.id < 2, User.name == '777')).all()
# ret = db_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 != ""

#
# 查询数据 指定查询数据列 加入别名
# r2 = db_session.query(User.name.label('username'), User.id).first()
# print(r2.id, r2.username)
#
# 表达式筛选条件
# r3 = db_session.query(User).filter(User.name == "哈哈哈").all()
#
# # 原生SQL筛选条件
# r4 = db_session.query(User).filter_by(name='哈哈哈').all()
# r5 = db_session.query(User).filter_by(name='哈哈哈').first()
#
# order_by进行排序
# r6 = db_session.query(User).order_by(User.id).all()
# r6 = db_session.query(User).order_by(User.id.desc()).all() # 逆序
#
# 原生SQL查询
# from sqlalchemy.sql import text
# r7 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).params(name='233').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)
#
#
# 复杂查询
# from sqlalchemy.sql import text
#
# user_list = db_session.query(User).filter(text("id<:value and name=:name")).params(value=3, name="666")
# print(user_list)
# 查询语句
# 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="233")
# 其他查询条件
# ret = db_session.query(User).filter(User.id > 1, User.name == '777')  # and
# ret = db_session.query(User).filter_by(name='777')
# ret = db_session.query(User).filter(User.id.between(1, 3), User.name == '777')  # between 大于1小于3的
# ret = db_session.query(User).filter(User.id.in_([1, 3, 4]))  # in_([1,3,4]) 只查询id等于1,3,4的
# ret = db_session.query(User).filter(~User.id.in_([1, 3, 4]))  # ~xxxx.in_([1,3,4]) 查询不等于1,3,4的
# ret = db_session.query(User).filter(User.id.in_(db_session.query(User.id).filter_by(name='777')))  # 子查询
#
# # 通配符
# 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]
#
# # 分组
# from sqlalchemy.sql import func
#
# # ret = db_session.query(User).group_by(User.name)
# # ret = db_session.query(
# #     func.max(User.id),
# #     func.sum(User.id),
# #     func.min(User.id)).group_by(User.name)
#
# 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)
# print(ret)

# 其他查询条件


# 高级版更新操作

#
# 直接修改
# db_session.query(User).filter(User.id > 0).update({"name": "099"})
#
# 在原有值基础上添加
# db_session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)

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

# 关闭连接
db_session.close()