简介
- sqlalchemy 是python操作sql数据库的工具,是一个对象关系映射的框架;
- 在python中提供高效、高性能的数据库访问,企业级的数据持久化模式;
- pypi 地址
- sqlalchemy官网
- sqlalchemy架构
- sql操作是独立于ORM操作的,实现对一个数据的增删改查;基于命令的;
- ORM 基于Core 实现对象模型映射到数据库;基于状态的;
- sqlalchemy底层封装 pymysql / psycopg2等,通过创建engine,连接数据库;
安装
- 主要版本 2.0 、1.4、1.3;
- 官方文档
- pip install sqlalchemy==1.4.49
- 查看版本 可以使用pip list/ pip freeze ; 或者ipython中sqlalchemy._version_
ORM简单使用
- 声明模型类,在python项目中,需要先声明对应的模型类;
模型类,对应数据库中的表;
类属性,对应表字段;
实例对象, 对应表中的一行记录;
# sqlalchemy 没有Double类型, 10种类型
from sqlalchemy import Column, Integer, Float, String, Boolean, DECIMAL, ForeignKey, Enum, Date, Time, DateTime, Text #
from sqlalchemy.orm import declarative_base, relationship, Session, sessionmaker
# sessionmaker(engine) 返回一个Session类
from sqlalchemy import create_engine, select
# 基类
Base = declarative_base()
# 用户类
class User(Base):
__tablename__ = "user_account"
# id 主键
id = Column(Integer, primary_key=True)
# 姓名 varchar(30)
name = Column(String(30), name="n1") # name参数 可以指定对应到表中的字段名
# 全名
fullname = Column(String)
# 枚举字段
sex = Column(Enum("male", "female", name='sex')) # 必须给name
# 获取传入一个枚举类 Enum(SexEnum)
# class SexEnum(Enum): # from enum import Enum
# 模型类 间使用的关系(并非表字段)
addresses = relationship( # 必须用引号表示类
"Address", back_populates="user", cascade="all, delete-orphan"
)
# back_populates (双向)模型类之间反向引用的属性
# back_ref 单向的反向引用
# delete-orphan 子表取消关联时,删除记录
# 打印对象时的输出
def __repr__(self):
return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
class Address(Base):
__tablename__ = "address"
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
# 外键字段
user_id = Column(Integer, ForeignKey("user_account.id"), nullable=False)
# 关系,仅供模型类使用 必用引号表示类
user = relationship("User", back_populates="addresses")
def __repr__(self):
return f"Address(id={self.id!r}, email_address={self.email_address!r})"
- 创建表结构
使用基类的metadata创建所有的表
sqlalchemy_database_uri = "postgresql://user:pw@ip:5432/xxdb"
engine = create_engine(sqlalchemy_database_uri, echo=True) # sqlalchemy 日志输出到标准输出
# 使用基类 元数据 创建表
# Base.metadata.drop_all(engine) # 删除所有已存在的表
Base.metadata.create_all(engine)
- 添加数据
# 上下文管理器 创建session
with Session(engine) as session:
# User 对象
user1 = User(
name="spongebob",
fullname="Spongebob Squarepants",
sex="male",
# 一对多关系, 所以给列表,插入用户时,连带插入地址
addresses=[Address(email_address="spongebob@sqlalchemy.org")],
)
user2 = User(
name="sandy",
fullname="Sandy Cheeks",
sex="female", # 枚举
addresses=[ # 模型类之间的关系
Address(email_address="sandy@sqlalchemy.org"),
Address(email_address="sandy@squirrelpower.org"),
],
)
# 仅仅赋值字段,不处理关系
user3 = User(name="patrick", fullname="Patrick Star", sex="male")
# 添加用户,同时会添加地址
session.add_all([user1, user2, user3]) # 添加一个用add
session.commit()
- 简单查询
from sqlalchemy import select
# 创建会话,建议使用上下文管理器 操作
session = Session(engine)
# 查询声明
# select * from user_t where name in ("spongebob", "sandy");
stmt = select(User).where(User.name.in_(["spongebob", "sandy"]))
# 查询某些字段
# stmt = select(User.name, User.sex).where(User.id==1) # > 1
# 执行声明
scalar_result = session.scalars(stmt)
#
scalar_result.one() # 获取一条数据(仅有一条时)
scalar_result.one_or_none() # 获取一条(仅有一条时)
scalar_result.all()
scalar_result.fetchall() # 生成器,只能获取一次
# 也可以遍历获取数据
for user in scalar_result:
print(user)
也可会话查询
filter() 复杂条件;
filter_by(field=xx) 等值条件过滤;
limit() 限制行数
order_by() 根据指定条件进行排序
group_by() 根据指定条件进行分组
# 获取一个对象
user = session.query(User).filter_by(name="jack").first() # 根据name字段查询
# first() 返回一个对象
# all() 返回对象列表
# 查询某些字段
user = session.query(User.name, User.sex).filter_by(id=1).all()
# 返回 具体字段值元组 列表 [('jack', 'male')]
- 更新
# 查询对象
user = session.query(User).filter_by(id=1).one_or_none()
# 更新对象的sex
user.sex = "female"
session.commit()
- 删除记录
# 查询对象
# user = session.get(User, ident=2) 根据id查询
user = session.query(User).filter_by(id=1).one_or_none()
# 会话删除对象
session.delete(user)
# 提交
session.commit()
- 聚合查询
from sqlalchemy import func, and_, or_ # 与条件 或条件
# 查询 id > 1 的所有记录的 id列,并计数
count_num = session.query(func.count(User.id)).filter(User.id > 1).first()
# (5,)
- 多条件查询
from sqlalchemy import or_, and_
# 查询 id>3 且name 以2结尾的用户 % 多个字符 _ 一个字符
user = session.query(User).filter(User.id > 3, User.name.like("%2")).all()
# [lucy2]
# 查询id>4 或者name 以1结尾的用户
user = session.query(User).filter(or_(User.id > 4, User.name.like("%1"))).all()
# [jack1, lucy1, jack3, lucy3]
# 分页
user = session.query(User).filter(User.id > 1).offset(3).limit(3).all()
# 排序
from sqlalchemy import text
user = session.query(User).filter(User.id > 1).order_by(text("id desc")).all() # age asc 升序
# 分组 聚合
user = session.query(func.count(User.sex)).filter(User.id >= 1).group_by(text("sex")).all()
# [(3,), (3,)]
基于sql的CRUD
from sqlalchemy import create_engine
import psycopg2 # 底层基于psycopg2
sqlalchemy_database_uri = "postgresql://jack:jack@192.168.0.112:5432/jack_db"
sql = "create table if not exists stu(id int primary key , name varchar(50), age int)"
engine = create_engine(sqlalchemy_database_uri)
conn = engine.connect()
cursor = conn.exec_driver_sql(sql)
# insert_one = "insert into stu values (1, 'jack', 23), (2, 'lucy', 34)"
# conn.exec_driver_sql(insert_one)
# delete_one = "delete from stu where id = 2"
# conn.exec_driver_sql(delete_one)
update_one = "update stu set age=35 where id=1;"
conn.exec_driver_sql(update_one)
select_one = "select * from stu"
cursor = conn.exec_driver_sql(select_one)
print(cursor.fetchall())