ORM-sqlalchemy

最新推荐文章于 2020-11-16 16:40:27 发布

liuzhen087

最新推荐文章于 2020-11-16 16:40:27 发布

阅读量178

点赞数 3

本文链接：https://blog.csdn.net/weixin_44800414/article/details/93880022

版权

ORM

ORM,对象关系映射，对象和关系之间的映射，使用面向对象的方式来操作数据库。

关系模型和Python对象之间的映射  
table   => class      ,表映射为类
row     => object     ,行映射为实例
column  => property   ,字段映射为属性

SQLAlchemy是一个ORM框架
安装 $ pip install sqlalchemy
文档官方文档http://docs.sqlalchemy.org/en/latest/

查看版本

import sqlalchemy
print(sqlalchemy.__version__)

开发
SQLAlchemy内部使用了连接池

创建连接
数据库连接的事情，交给引擎

dialect+driver://username:password@host:port/database

mysqldb的连接 
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> 
engine = sqlalchemy.create_engine("mysql+mysqldb://uesr:password@127.0.0.1:3306/test")

pymysql的连接 
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] 
engine = sqlalchemy.create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test") 
engine = sqlalchemy.create_engine("mysql+pymysql://user:password@127.0.0.1:3306/test", echo=True)

echo = true
引擎是否打印执行的语句，调试的时候打开很方便。
lazy connecting：懒连接。创建引擎并不会马上连接数据库，直到让数据库执行任务时才连接。

Declare a Mapping创建映射

创建基类

from sqlalchemy.ext.declarative import declarative_base
#创建基类，便于实体类继承，SQLAlchemy使用了大量元编程
Base = declarative_base

创建实体类

CREATE TABLE student (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    age INTEGER,
    PRIMARY KEY (id)
 )

#创建实体类
class Student(Base):
    #定义表名
    __tablename__ = "student"
    #定义类属性对应字段
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(64),nullable=False)
    age = Column(Integer,nullable=False)

    def __repr__(self):
        return "{} id={} neme={} age={}".format(
            self.__class__.__name__,self.id,self.name,self.age
        )

#查看表结构 
print(Student)
print(repr(Student.__table__))

__tablename__指定表名
Column类指定对应的字段，必须指定

创建表
可以使用SQLAlchemy来创建、删除表

# 删除继承自Base的所有表
Base.metadata.drop_all(engine)
# 创建继承自Base的所有表 
Base.metadata.create_all(engine)

生产环境很少这样创建表，都是系统上线的时候由脚本生成。
生成环境很少删除表，宁可废弃都不能删除。

创建会话
在一次会话中操作数据库，会话建立在连接上，连接被引擎管理
当第一次使用数据库时，从引擎维护的连接池中获取一个连接使用。

#创建session  
Session = sessionmaker(bind=engine) # 工厂方法返回类  
session = Session() #实例化
#在第一次使用时连接数据库

session对象线程不安全。所以不同线程应该使用不同的session对象。
Session类和engine有一个就行了。

CRUD操作

增
add():增加一个对象
add_all():可迭代对象，元素是对象

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

Base = declarative_base() #基类

# mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
    username,password,ip,port,database
), echo=True)

# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)

#OPM Mspping
class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(64),nullable=False)
    age = Column(Integer,nullable=False)

    def __repr__(self):
        return "{} id={} neme={} age={}".format(
            self.__class__.__name__,self.id,self.name,self.age
        )

from sqlalchemy.orm.session import Session

#Session = sessionmaker(bind=engine)
session:Session = sessionmaker(bind=engine)()

s = Student(name='tom')
s.age = 20

session.add(s)
session.commit()

try:
    session.add_all([s])
    session.commit()
except:
    session.rollback()

add_all()方法不会提交成功的，不是因为它不对，而是s,s成功提交后，s的主键就有了值，所以，只要s没有修改过，就认为没有改动

s.name = 'jerry' #修改
session.add_all([s])

s主键没有值，就是新增；主键有值，就是找到主键对应的记录修改。
简单查询
使用query()方法，返回一个Query对象

students = session.query(Student) #无条件  
print(students) #无内容，惰性的
for student in students:
    print(student)

student = session.query(Student).get(2) #通过主键(id)查询  
print(student)

query方法将实体类传入，返回类的对象可迭代对象，这时候并不查询。迭代它就执行SQL来查询数据库，封装数据到指定类的实例。
get方法使用主键查询，返回一条传入类的实例

改

student = session.query(Student).get(2)
student.name = 'Tom'
student.age = 30

session.add(student)
session.commit()

先查回来，修改后，再提交更改。

删除

try:
    student = Student(id=2,name='Tom',age=30)

    session.delete(student)
    session.commit()

except Exception as e:
    session.rollback()

会产生一个异常
Instance ‘<Student at 0x3e654e0>’ is not persisted 未持久的异常！

状态
每一个实体，都有一个状态属性_sa_instance_state，其类型是sqlalchemy.orm.state.InstanceState，可以使用 sqlalchemy.inspect(entity)函数查看状态。
常见的状态值有transient、pending、persistent、deleted、detached。

状态	说明
transient	实体尚未加入到session中，同时并没有保存到数据库中
pending	transient的实体被add()到session中，状态切换到pending，但它还没有flush到数据库中
persistent	session中的实体对象对应着数据库中的真实记录。pending状态在提交成功后可以变成persistent状态，或者查询成功返回的实体也是persistent状态
deleted	实体被删除且已经flush但未commit完成，事务提交成功了，实体变成detached，事务失败，返回persistent状态
detached	删除成功的实体进入这个状态

新建一个实体，状态是transient临时的
一旦add()后从transient变成pending状态
成功commit()后从pending变成persistent状态。
成功查询返回的实体对象，也是persistent状态。

persistent状态的实体，修改依然是persistent状态。

persistent状态的实体，删除后，flush后但没有commit，就变成deteled状态，成功提交，变为detached状态，提交失败，还原到persistent状态。flush方法，主动把改变应用到数据库中去。
删除，修改操作，需要对应一个真实的记录，所以要求实体对象是persistent状态。

删除

stdent = session.query(Student).get(5)

try:
    session.delete(student) #删除的前提是persistent
    session.flush()  #deleted
    session.commit() #deteched

except Exception as e:
    session.rollback()

复杂查询

实体类

from sqlalchemy import  create_engine,Column,String,Integer,Date,ForeignKey,Enum,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import enum

Base = declarative_base() #基类

class MyEnum(enum.Enum):
    M = 'M'
    F = 'F'


class Employees(Base):
    __tablename__ = 'employees'
    emp_no = Column(Integer,primary_key=True)
    birth_date = Column(Date,nullable=False)
    first_name=Column(String(14),nullable=False)
    last_name=Column(String(16),nullable=False)
    gender = Column(Enum(MyEnum),nullable=False)
    hire_date = Column(Date,nullable=False)

    departments = relationship('DeptEmp')

    def __repr__(self):
        return "{} no={} name={} {} gender={} {}".format(
            self.__tablename__,self.emp_no,self.first_name,self.last_name,
            self.gender.value,self.departments
        )

rom sqlalchemy.orm.session import Session

# Session = sessionmaker(bind=engine)
session:Session = sessionmaker(bind=engine)()

def show(emps):
    for x in emps:
        print(x)


#简单条件查询  
emps = session.query(Employee).filter(Employee.emp_no > 10015) 
show(emps)

#与或非  
from sqlalchemy import and_,or_,not_

# AND 条件  
emps = session.query(Employees).filter(Employees.emp_no>10015).filter(Employees.emp_no<10018)
show(emps)

emps = session.query(Employees).filter(and_(Employees.emp_no>10015,Employees.emp_no<10018))
show(emps)

emps = session.query(Employees).filter((Employees.emp_no>10015,Employees.emp_no<10018))
show(emps)

emps = session.query(Employees).filter((Employees.emp_no>10015)&(Employees.emp_no<10018))
show(emps)

#OR 条件  

emps = session.query(Employees).filter((Employees.emp_no<10005)|(Employees.emp_no>10018))
show(emps)

emps = session.query(Employees).filter(or_(Employees.emp_no<10015,Employees.emp_no>10018))
show(emps)

#Not

emps = session.query(Employees).filter(not_(Employees.emp_no<10015))
show(emps)

emps = session.query(Employees).filter(~(Employees.emp_no<10015))
show(emps)
#与或非的运算符&、|、~要在表达式上加上括号
# in
emplist = [10001,10010,10020]
emps = session.query(Employees).filter(Employees.emp_no.in_(emplist))
show(emps)

emps = session.query(Employees).filter(Employees.emp_no.notin_(emplist))
show(emps)

#like
emps = session.query(Employees).filter(Employees.last_name.like('P%'))
show(emps)

emps = session.query(Employees).filter(Employees.last_name.notlike('P%'))
show(emps)

排序

#排序  
#升序  
emps = session.query(Employees).filter(Employees.emp_no > 10010).order_by(Employees.emp_no)
show(emps)

emps = session.query(Employees).filter(Employees.emp_no > 10010).order_by(Employees.emp_no.asc())
show(emps)

#降序  
emps = session.query(Employees).filter(Employees.emp_no > 10010).order_by(Employees.emp_no.desc())
show(emps)

#多列排序  
emps = session.query(Employees).filter(Employees.emp_no > 10010).order_by(Employees.last_name).order_by(Employees.emp_no.desc())
show(emps)

分页

emps = session.query(Employees).limit(6)
show(emps)

emps = session.query(Employees).limit(6).offset(6)
show(emps)

消费者方法
消费者方法调用后,Query对象(可迭代)就转换成了一个容器

emps = session.query(Employees)
print(len(list(emps))) # 查询得到结果集，转成list，然后取长度
print(emps.count())  #聚合函数count(*)的查询

#去所有数据
print(emps.all()) #返回列表,查不到返回空列表

#取首行  
print(emps.first()) #返回首行,查不到返回None,等价limit

#有且只能有一行
print(emps.one()) #如果查询结果是多行抛异常
print(emps.limit(1).one())
# 删除 delete by query 
session.query(Employee).filter(Employee.emp_no > 10018).delete() #session.commit() # 提交则删除

聚合、分组

from sqlalchemy import func

emps = session.query(func.count(Employees.emp_no))
print(emps.all())   #列表中一个元素
print(emps.first()) #一个只有一个元素的元组
print(emps.one())   #只能有一行返回,一个元组
print(emps.scalar())#取one()的第一个元素

#max/min/avg/sum
emps = session.query(func.max(Employees.emp_no))
emps = session.query(func.min(Employees.emp_no))
emps = session.query(func.avg(Employees.emp_no))
emps = session.query(func.sum(Employees.emp_no))

#分组  
emps = session.query(Employees.gender,func.count(Employees.emp_no)).group_by(Employees.gender)
for x,y in emps:
    print(x.value,y)

关联查询
查询10010员工的员工的所在的部门编号和员工信息

使用隐私连接

emps = session.query(Employees,DeptEmp).filter(Employees.emp_no==DeptEmp.emp_no).filter(Employees.emp_no==10010)
show(emps)
# 查询结果2行  
(employees no=10010 name=Duangkaew Piveteau gender=F, dept_emp emp_no=10010  dept_nod004 )
(employees no=10010 name=Duangkaew Piveteau gender=F, dept_emp emp_no=10010  dept_nod006 )

这种方式隐式连接的语句

SELECT *
FROM employees, dept_emp 
WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = 10010

使用join


emps = session.query(Employees).join(DeptEmp).filter(Employees.emp_no==10010)
show(emps)

emps = session.query(Employees).join(DeptEmp,Employees.emp_no==DeptEmp.emp_no).filter(Employees.emp_no==10010)
show(emps)

这两种写法,返回都只有一行数据
原因在于query(Employees)这个只能返回一个实体对象中去,为了解决这个问题,需要修改实习类Employees,增加属性用来存放部门信息
sqlalchemy.orm.relationship(实体类字符串)

from sqlachemy.orm import relationship

class Employees(Base):
    __tablename__ = 'employees'
    emp_no = Column(Integer,primary_key=True)
    birth_date = Column(Date,nullable=False)
    first_name=Column(String(14),nullable=False)
    last_name=Column(String(16),nullable=False)
    gender = Column(Enum(MyEnum),nullable=False)
    hire_date = Column(Date,nullable=False)

    departments = relationship('DeptEmp')

    def __repr__(self):
        return "{} no={} name={} {} gender={}".format(
            self.__tablename__,self.emp_no,self.first_name,self.last_name,
            self.gender.value,self.departments
        )

查询信息

#第一种
emps = session.query(Employees).join(DeptEmp).filter(Employees.emp_no==DeptEmp.emp_no).filter(Employees.emp_no==10010)

#第二种 
emps = session.query(Employees).join(DeptEmp,Employees.emp_no==DeptEmp.emp_no).filter(Employees.emp_no==10010)

#第三种
emps = session.query(Employees).join(DeptEmp,(Employees.emp_no==DeptEmp.emp_no)&(Employees.emp_no==10010))

第一种方法join(Dept_emp)中没有等值条件，会自动生成一个等值条件，如果后面有ﬁlter，哪怕是 ﬁlter(Employee.emp_no == Dept_emp.emp_no)，这个条件会在where中出现。第一种这种自动增加join的等值条件的方式不好，不要这么写
第二种方法在join中增加等值条件，阻止了自动的等值条件的生成。这种方式推荐
第三种方法就是第二种，这种方式也可以。

emps = session.query(Employees).join(DeptEmp,(Employees.emp_no==DeptEmp.emp_no)&(Employees.emp_no==10010))

for x in emps:
    print(x.emp_no)
    print(x.departments) #观察有无词条语句打印的结果及生成SQL语句的变化
    print(x)  #查询结果

可以看出只要不访问departments属性,就不会查dept_emp这张表

总结

在开发中,一般都会采用ORM框架,这样就可以使用对象操作表了.
定义表映射的类,使用Column的描述器定义类属性,使用ForeignKey来定义外键约束
如果在一个对象中,想查看其它表对应的对象的内容,就要使用relationship来定义关系.

是否使用外键约束?
1、力挺派能使数据保证完整性一致性
2、弃用派开发难度增加，大量数据的时候影响插入、修改、删除的效率。在业务层保证数据的一致性。

liuzhen087

关注

3
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
ORM-sqlalchemy

ORMORM,对象关系映射，对象和关系之间的映射，使用面向对象的方式来操作数据库。关系模型和Python对象之间的映射 table => class ,表映射为类row => object ,行映射为实例column => property ,字段映射为属性SQLAlchemy是一个ORM框架安装 $ pip install ...
复制链接

扫一扫