ORM之SQLAlchemy

在Python中, 最有名的ORM框架是SQLAlchemy.

SQLAlchemy安装 :

pip install sqlalchemy

使用(建立数据库表) :

# 1.创建一个 Class
from sqlalchemy.ext.declarative import declarative_base
# 2.导入数据库引擎
from sqlalchemy import create_engine
# 3.导入表结构关键字
from sqlalchemy import Column,Integer,INT,INTEGER,VARCHAR,String
# base是Orm模型的基类
Base = declarative_base()
# 创建数据库引擎
engine = create_engine("mysql+pymysql://root:@127.0.0.1:3306/sqlalchemy?charset=utf8")


class User(Base):
    # 表名
    __tablename__ = "user"
    # 表的结构(Column表示字段, Integer表示数字类型, authincrement表示自增)
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),index=True)

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

---

数据的增删改查

1. 增加数据

"""
1. 选中数据库 - 创建数据库引擎, 导入数据库引擎
2. 创建查询窗口, 必须是选中数据库的查询窗口
3. 创建sql语句
4. 运行
"""

# 增加单条数据
from sqlalchemy.orm import sessionmaker
Session_win = sessionmaker(engine)
# 打开查询窗口
db_session = Session_win()

user_obj = User(name = "xxx")    # 创建sql语句
db_session.add(user_obj)    # 将sql语句在查询窗口进行添加
db_session.commit()    # 执行全部的sql语句
db_session.close()    # 关闭查询窗口


# 增加多条数据
user_obj_list = [User(name="xxx"), User(name="ddd")]
db_session.add_all(user_obj_list)
db_session.commit()
db_session.close() 

2. 更新数据

from sqlalchemy.orm import sessionmaker
Session_win = sessionmaker(engine)
# 打开查询窗口
db_session = Session_win()

# 修改单条数据
user_obj = db_session.query(User).filter(User.id == 1).update({"name":"aaa"})
db_session.commit()
# query参数确定要操作的表, filter参数表示查询条件, 用来确定要修改的数据


# 修改多条数据
user_obj = db_session.query(User).filter(User.id >= 1).update({"name":"666"})
db_session.commit()
# 表示将User表内id>=1的字段的"name"都更改为"666"

3. 查询数据

from sqlalchemy.orm import sessionmaker
Session_win = sessionmaker(engine)
# 打开查询窗口
db_session = Session_win()

# 简单查询
user_obj = db_session.query(User).first()    # 取User表内的第一个元素
print(user_obj.id, user_obj.name)

user_obj_list = db_session.query(User).all()    # 去User表内的全部元素
for row in user_obj_list:
    print(row.id, row.name)

# 带条件的查询
user_obj_list = db_session.query(User).filter(User.id <= 2, User.name == "xxx").all()
# filter参数形式是: 表名.字段名;  并且条件可以含有一定的范围性
# filter内多个参数条件是 " & " 的关系
for row in user_obj_list:
    print(row.id, row.name)

user_obj_list = db_session.query(User).filter_by(id=2, name="aaa").all()
# filter_by参数形式: 字段名;  条件是以传参的方式进行筛选
for row in user_obj_list:
    print(row.id, row.name)

4. 删除数据

from sqlalchemy.orm import sessionmaker
Session_win = sessionmaker(engine)
# 打开查询窗口
db_session = Session_win()

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


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

---

带外键关系的数据表

1. 创建表

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column,INT,VARCHAR,ForeignKey
from sqlalchemy.orm import relationship    # 多表建立关系的字段

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")  # orm层面的关系

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:@127.0.0.1:3306/sqlalchemy?charset=utf8")


Base.metadata.create_all(engine)    # 创建表
# Base.metadata.drop_all(engine)    # 删除表

2. 增加数据

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()


# 1. 笨方法增加数据
sch_obj = School(name="xxx")
db_session.add(sch_obj)
db_session.commit()

sch_obj = db_session.query(School).filter(School.name=="xxx").first()    # 查询

stu_obj = Student(name="aaa",school_id=sch_obj.id)
db_session.add(stu_obj)
db_session.commit()

# 2 Relationship 版 添加数据操作 - 正向
stu_obj = Student(name="aaa",stu2sch=School(name="xxx"))
db_session.add(stu_obj)
db_session.commit()

# 3 Relationship 版 添加数据操作 - 反向
sch_obj = School(name="xxx")
sch_obj.sch2stu = [Student(name="aaa"),Student(name="ccc")]
db_session.add(sch_obj)
db_session.commit()

3. 查询数据

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)
db_session = Session()

# 1.查询 笨方法
sch_obj = db_session.query(School).filter(School.name == "xxx").first()
beijing_stu_obj = db_session.query(Student).filter(Student.school_id == sch_obj.id).first()
print(beijing_stu_obj.name, sch_obj.name)

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

# 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)

---

多对多关系的数据表

1. 建立表

from sqlalchemy.ext.declarative import declarative_base
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")

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:@127.0.0.1:3306/sqlalchemy?charset=utf8")

Base.metadata.create_all(engine)

2. 增加数据

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
db_session = Session()

# 1.增加数据 - relationship 正向
girl_obj = Girls(name="aaa")
girl_obj.g2b = [Boys(name="bbb")]
db_session.add(girl_obj)
db_session.commit()

# 2.增加数据 - relationship 反向
boy_obj = Boys(name="ccc")
boy_obj.b2g = [Girls(name="ggg"),Girls(name="hhh")]
db_session.add(boy_obj)
db_session.commit()

3. 查询数据

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)