sqlAlchemy
1,SQLAlchemy是Python知名的ORM工具之一,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型.SQLAlchemy的理念是SQL数据库的量级和性能重要于集合对象,而对象集合的抽象有重要于表和行.因此SQLAlchemy采用了类似于Hibernate的数据映射模型,而不是其他ORM框架采用的Active Record不过，Elixir和declarative等可选插件可以让用户使用声明语法。
2,安装SQLAlchemy.
pip install sqlalchemy
- 如果安装SQLAlchemy报错,那是因为SQLAlchemy依赖pymysql
3,创建表,并连接数据库
# ORM
# 1,Class - Obj
# 创建数据库引擎
# 将所有的Class序列化成数据表
# ORM操作 - CRUD (增删改查)
# 1,创建一个class(表)
from sqlalchemy.ext.declarative import declarative_base
# 实例化一个declaraive_base对象
Base = declarative_base()
# Base 是ORM模型 基类
# ORM模型 - Obj里面的属性 == table中创建的字段
# - Obj定义table的操作方式和属性
from sqlalchemy import Column, Integer, String
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:123456@127.0.0.1:3306/sqlalchemy?charset=utf8")
# 3,将所有的继承Base的Class序列化数据表
# Base.metadata.create_all(engine)
Base.metadata.create_all(engine)
参数说明:
- __tablename__指定在MySql表中的名字
- 类中的Column代表数据库中的一列
- Integer代表数值型
- String代表字符串
4.1,单标的增加:
# 1,选中数据库 - 创建数据库引擎, 导入数据库引擎 # 2,创建查询窗口, 必须是选中数据库的查询窗口 # 3,创建sql语句 # 4,点击运行 # 1,选中数据库 - 创建数据库引擎,导入数据库引擎 from Single_table.create_table import engine, User # 2,创建查询窗口,必须是选中数据库的查询窗口 from sqlalchemy.orm import sessionmaker Session_window = sessionmaker(engine) # 打开查询窗口 db_session = Session_window() # 实例化查询窗口对象 # 1,增加数据 - 创建sql语句 user_obj = User(name="帅爆太阳的男人") # 创建sql语句 db_session.add(user_obj) # 将sql语句粘贴到查询窗口中 # 点击运行 db_session.commit() # 执行全部的sql语句 db_session.close() # 关闭会话窗口 # 2,创建多条数据 user_obj_list = [User(name="雪雪"), User(name="小雪")] db_session.add_all(user_obj_list) # 将多个创建的值粘贴到查询窗口 db_session.commit() # 提交要执行的sql语句 db_session.close()
4.2,单标的查
# 创建窗口
from Single_table.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)
# 查询多条数据
user_obj_list = db_session.query(User).all()
print(user_obj_list)
for row in user_obj_list:
print(row.id, row.name)
# 带条件的查询
user_obj_list = db_session.query(User).filter(User.id <= 2, User.name == "雪雪").all()
print(user_obj_list)
for row in user_obj_list:
print(row.id, row.name)
user_obj_list = db_session.query(User).filter_by(id=2, name="雪雪").all()
print(user_obj_list)
for row in user_obj_list:
print(row.id, row.name)
# 3 小雪
# [<Single_table.create_table.User object at 0x000002C0D560EAC8>, <Single_table.create_table.User object at 0x000002C0D560EEF0>, <Single_table.create_table.User object at 0x000002C0D560EE80>]
# 3 小雪
# 1 帅爆太阳的男人
# 2 雪雪
# [<Single_table.create_table.User object at 0x000002C0D560EE80>]
# 2 雪雪
# [<Single_table.create_table.User object at 0x000002C0D560EE80>]
# 2 雪雪
4.3,单标的更新
# 创建窗口
from Single_table.create_table import User, engine
from sqlalchemy.orm import sessionmaker # 会话窗口
Session = sessionmaker(engine) # 创建数据查询窗口
db_session = Session() # 打开数据查询窗口
# 1,修改一条数据
user_obj = db_session.query(User).filter(User.id == 1).update({"name": "帅爆太阳那人"})
db_session.commit() # 提交sql语句
# 2,修改多条数据
user_obj - db_session.query(User).filter(User.id >= 2).update({"name": "miss"})
db_session.commit() # 提交sql语句
4.4,单标的删除
from sqlalchemy.orm import sessionmaker from Single_table.create_table import engine, User # 1,创建数据窗口 Session = sessionmaker(engine) # 库好里放的参数是要对那个数据库进行操作 db_session = Session() # 打开数据查询窗口 # 1,删除数据 res = db_session.query(User).filter(User.id == 1).delete() # 直接queryset对象就可以直接删除 db_session.commit() # 2,删除多条数据 res = db_session.query(User).filter(User.id >=1).delete() db_session.commit() # 提交sql语句
5,Foreignkey(一对多)
- 5.1创建表和连接
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, INT, VARCHAR, ForeignKey
from sqlalchemy.orm import relationship
# 实例化基类
Base = declarative_base()
class Student(Base):
__tablename__ = "student"
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
school_id = Column(INT, ForeignKey("school.id"))
stu2sch = relationship("School", backref="sch2stu") # backref反向查询的用到的字段
class School(Base):
__tablename__ = "school"
id = Column(INT, primary_key=True)
name = Column(VARCHAR(32))
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy?charset=utf8") # utf8, 不要写成utf-8
print(engine)
Base.metadata.create_all(engine)
student和school多对一,relationship指定外键关系
- 5.2外键关系的增加
from sqlalchemy.orm import sessionmaker
from foreignKey.create_table import Student, School, engine
Session = sessionmaker(engine) # 创建数据查询窗口
db_session = Session() # 打开数据查询窗口
# 1增加数据
sch_obj = School(name="OldBoyBeijing")
db_session.add(sch_obj) # 把要执行的orm操作语句
db_session.commit() # 提交成要操作的sql语句
sch_obj = db_session.query(School).filter(School.name=="OldBoyBeijing").first()
stu_obj = Student(name="帅爆太阳的男人", school_id=sch_obj.id) # 通过学校,查找该学校所有的学生
db_session.add(stu_obj) # 把orm的语句放到数据查询窗口
db_session.commit() # 提交sql语句
# 2,Relationship 添加数据操作 ---正向
stu_obj = Student(name="帅爆太阳的男人", stu2sch=School(name="OldBoyBeijing")) # 直接通过relationship直接定位到学校
db_session.add(stu_obj)
db_session.commit()
# Relationship - 反向
sch_obj = School(name="OldBoyBeijing") # 先查到学校对象
sch_obj.sch2stu = [Student(name="雪雪"), Student(name="小雪")] # 在根据relationship查找到这个学校的所有学生
db_session.add(sch_obj)
db_session.commit()
- 5.2外键关系的查询
from sqlalchemy.orm import sessionmaker
from foreignKey.create_table import engine, Student, School
Session = sessionmaker(engine) # 创建数据查询窗口
db_session = Session()
# 1,查询
sch_obj = db_session.query(School).filter(School.name=="OldBoyBeijing").first()
beijing_stu_obj = db_session.query(Student).filter(Student.school_id==sch_obj.id).first()
print(beijing_stu_obj.name, beijing_stu_obj.id) # 帅爆太阳的男人 1
# 2,relatership 正向查询
stu_obj = db_session.query(Student).filter(Student.name == "帅爆太阳的男人").first()
print(stu_obj.name, stu_obj.stu2sch.name) # 帅爆太阳的男人 OldBoyBeijing
# 3,relationship 反向查询
sch_obj_list = db_session.query(School).all()
for row in sch_obj_list:
for stu in row.sch2stu:
print(row.name, stu.name)
# OldBoyBeijing 帅爆太阳的男人
# OldBoyBeijing 帅爆太阳的男人
# OldBoyBeijing 雪雪
# OldBoyBeijing 小雪
6,多对多关系的表
- 6.1,多对多表的创建和连接
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_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))
g2b = relationship("Boys", backref="b2g", secondary="hotel") # backref是反向查询的字段
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"))
girl_id = Column(Integer, ForeignKey("girl.id"))
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")
print(engine)
Base.metadata.create_all(engine)
- 6.2多对多表的插入数据
from Many_to_Many.create_table_M2M import engine, Girls, Boys, 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="郁可唯"), Girls(name="谢大脚")] db_session.add(boy_obj) db_session.commit() # 提交到数据库
- 6.3多对多表的查询
from Many_to_Many.create_table_M2M import engine, Girls, Boys
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine) # 创建会话窗口
db_session = Session() # 打开会话窗口
# 1查询数据 - relationship 正向
girl_obj_list = db_session.query(Girls).all() # 查询所有的女生
print(girl_obj_list)
for girl in girl_obj_list:
print(girl) # <Many_to_Many.create_table_M2M.Girls object at 0x000001F6E5344908>
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)
7,SQLAlchemy的进阶操作
- 7.1创建表和建立连接
# ORM
# 1,class - obj
# 2,将所有的class序列化成数据
# 3,将所有的class序列化成数据表
# 4,ORM操作 -CRUD
# 1创建一个表class
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# Base 是ORM的模型类, 基类
# ORM模型 -obj里面的属性 == table中创建的字段
# -obj定义table的操作方式和属性
from sqlalchemy import Column, Integer, String
class People(Base):
__tablename__ = "people"
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), index=True)
age = Column(Integer)
gender = Column(String(32))
# 2创建数据库引擎
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")
# engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/sqlalchemy")
# 将所有的继承Base的Class序列化成数据表
Base.metadata.create_all(engine)
- 7.2进阶操作的增加
from sqlalchemy.orm import sessionmaker from advanced.create_table_advanced import engine, People Session_window = sessionmaker(engine) # 打开查询窗口 db_session = Session_window() # 增加多条数据 user_obj_list = [People(name="小雪", age=18, gender="女"), People(name="雪人", age=23, gender="女"), People(name="刘夏", age=22, gender="女")] db_session.add_all(user_obj_list) print(db_session) db_session.commit() db_session.close()
- 7.3进阶操作的增加
from sqlalchemy.orm import sessionmaker from advanced.create_table_advanced import engine, People Session_window = sessionmaker(engine) # 打开查询窗口 db_session = Session_window() # 增加多条数据 user_obj_list = [People(name="小雪", age=18, gender="女"), People(name="雪人", age=23, gender="女"), People(name="刘夏", age=22, gender="女")] db_session.add_all(user_obj_list) print(db_session) db_session.commit() db_session.close()
- 7.4,进阶操作的
from advanced.create_table_advanced import engine, People
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()
# 引用增加, 批量曹组,也可以筛选出来做批量操作
res = db_session.query(People).update({People.age:People.age+20}, synchronize_session=False)
print(res) # 显示操作成功几个
db_session.query(People).filter(People.id>0).update({"age":People.age+30}, synchronize_session="evaluate")
db_session.commit()
浙公网安备 33010602011771号