数据库-orm-SQLalchemy

1 ORM

orm:Object-Relational Mapping，把关系数据库的表结构映射到对象上。使用面向对象的方式来操作数据库。

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

2 SQLalchemy

SQLAlchemy是一个ORM框架。内部是使用了连接池来管理数据库连接。要使用sqlalchemy，那么需要先进行安装：

pip install sqlalchemy
查看版本

In [1]: import sqlalchemy
In [2]: print(sqlalchemy.__version__)
1.3.5

文档

官方文档 http://docs.sqlalchemy.org/en/latest/

先来总结一下使用sqlalchemy框架操作数据库的一般流程：

创建引擎(不同类型数据库使用不同的连接方式)engine
创建基类(类对象要继承，因为基类会利用元编程为我们的子类绑定关于表的其他属性信息)Base
创建实体类(用来对应数据库中的表)class
编写实体类属性(用来对应表中的字段/属性)
创建表(如果表不存在,则需要执行语句在数据库中创建出对应的表)create_all
实例化(具体的一条record记录)
创建会话session(用于执行sql语句的连接)
使用会话执行SQL语句
关闭会话

3.1 创建连接

sqlalchemy 使用引擎管理数据库连接(DATABASE URLS)，连接的一般格式为：

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

dialect：表示什么数据库(比如,mysql,sqlite,oracle等)
driver：用于连接数据库的模块(比如pymysql,mysqldb等)
username：连接数据库的用户名
password：连接数据库的密码
host: 数据库的主机地址
port: 数据库的端口
database: 要连接的数据库名称

pymysql模块是较长用于连接mysql的模块，使用pymysql的连接的语句为：
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

import sqlalchemy

engine=sqlalchemy.create_engine("mysql+pymysql://zxy:zxy@196.168.213.128:3306/test",echo=True)

具体查看文档
https://blog.youkuaiyun.com/beyondlee2011/article/details/88706793

个人总结

需求
查询10010员工的所在的部门编号及员工信息

from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine, String, Column, Integer, Date, Enum, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session
#导入模块

Base = declarative_base()  #创建Base类

engine=create_engine("mysql+pymysql://zxy:zxy@192.168.213.128:3306/test", echo=True)
#创建引擎

CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum(‘M’,‘F’) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#根据上面的DDL创建实体类
class Employee(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)
# 第一参数是字段名，需要和属性名一致

def __repr__(self):
return "{} no={} name={} {} gender={}".format(
self.__class__.__name__, self.emp_no, self.first_name, self.last_name,
self.gender.value
)#输出格式

CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY dept_name (dept_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

同上
class Department(Base):
    __tablename__ = 'departments'
    dept_no = Column(String(4), primary_key=True)
    dept_name = Column(String(40), nullable=False, unique=True)

    # dep_name = relationship('Dept_emp')

    def __repr__(self):
        return '{} no ={} name = {}'.format(self.__class__.__name__, self.dept_no, self.dept_name)

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no),
KEY dept_no (dept_no),
CONSTRAINT dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINT dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

class Dept_emp(Base):
    __tablename__ = 'dept_emp'
    emp_no = Column(Integer, ForeignKey('employees.emp_no', ondelete='CASCADE'), primary_key=True)
    dept_no = Column(String(4), ForeignKey('department.dept_no', ondelete='CASCADE'), primary_key=True) 

    #ForeignKey('employees.emp_no', ondelete='CASCADE') 定义外键约束

    from_date = Column(Date, nullable=False)
    to_date = Column(Date, nullable=False)

    def __repr__(self):
        return '{} empno = {} deptno = {} '.format(self.__class__.__name__, self.emp_no, self.dept_no)

session: Session = sessionmaker(bind=engine)() #创建会话

query(Employee) 这个只能返回一个实体对象中去,需要修改实体类Employee，
增加属性用来存放部门信息
sqlalchemy.orm.relationship(实体类名字符串)

class Employee(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('Dept_emp') #

def __repr__(self): # 注意增加self.dept_emps
    return "{} no={} name={} {} gender={} depts={}".format(
self.__class__.__name__, self.emp_no, self.first_name, self.last_name,
self.gender.value, self.departments
)

#实例化
join方法(1)
results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no ==
Dept_emp.emp_no).filter(Employee.emp_no == 10010)
join方法(2)
results = session.query(Employee).join(Dept_emp, Employee.emp_no ==
Dept_emp.emp_no).filter(Employee.emp_no == 10010)
join方法(3)
results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) &
(Employee.emp_no == 10010))

show(results.all()) # 打印结果

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

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

只要不访问departments属性，就不会查dept_emp这张表。

先查(query)后改,先查后删 (数据状态持久化才能操作)

attached, (附属的,挂上的)
transient, (临时的,短暂的,仅仅实例化一个)
pending, (预备,add一个)
persistent, (持久化,commit一个,已持久的再修改state一直是持久的)
deleted,
(已删除的,delete后flush一个)
detached (分离的,delete后commit一个)