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)
