SQLAlchemy概述
安装
- SQLAlchemy由官方收录,可以直接安装
[root@localhost packages]# pip3 install sqlalchemy
简介
- SQLAlchemy是Python编程语下的一款开源软件。提供 SQL 具包及对象关系映 射(ORM) 工具,使用MIT许可证发
- SQLAlchemy“采用简单的Python语言,为高效和高性能的数据库访问设计,实现了完整的企业级持久模型”
- SQLAlchemy的理念是,SQL数据库的量级和性能重要于对象集合;而对象集合的抽象又重要于表和行
- 目标是提供能兼容众多数据库(如 SQLite、MySQL、Postgresql、Oracle、MS-SQL、SQLServer 和Firebird)的企业级持久性模型
架构
ORM模型
- ORM即对象关系映射
- 数据库表是一个二维表,包含多行多列。把一个表的内容用Python的数据结构表示出来的话,可以用一个list表示多行,list的每一个元素是tuple,表示一行记录
[
('1', 'Michael'),
('2', 'Bob'),
('3', 'Adam')
]
- 用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示,就可以更容易地看出表的结构来
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
[
User('1', 'Michael'),
User('2', 'Bob'),
User('3', 'Adam')
]
数据库对象管理
连接mysql
- 通过create_engine实现数据库的连接
[root@bogon bin]# mysql -uroot -p123456
MariaDB [(none)]> create database TestDB default charset utf8;
>>> from sqlalchemy import create_engine
>>> engine = create_engine(
'mysql+pymysql://root:123456@localhost/TestDB?charset=utf8',
encoding='utf8',
echo=True
)
//echo=True表示将日志输出到终端屏幕,默认为False
声明映射
- 当使用ORM的时候,配置过程从描述数据库表开始
- 通过自定义类映射相应的表
- 通过声明系统实现类映射
- 首先通过声明系统,定义基类
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
创建映射类
- 一旦创建了基类,就可以创建自定义映射类了
>>> from sqlalchemy import Column, Integer, String
>>> class Departments(Base):
... __tablename__ = 'departments'
... dep_id = Column(Integer, primary_key=True)
... dep_name = Column(String(20))
... def __repr__(self):
... return <Department(dep_name='%s')>"%self.dep_name
//__repr__是可选项
创建架构
- 类构建完成后,表的信息将被写入到表的元数据
(metadata)
>>> Departments.__table__
Table('departments', MetaData(bind=None), Column('dep_id', Integer(),
table=<departments>, primary_key=True, nullable=False),
Column('dep_name', String(), table=<departments>), schema=None)
- 通过表的映射类,在数据库中创建表
>>> Base.metadata.create_all(engine)
创建映射类的实例
- 创建实例时,并不会真正在表中添加记录
dep_dev = Departments(dep_name='developments')
print(dep_dev.dep_name)
print(str(dep_dev.dep_id))
创建会话类
- ORM访问数据库的句柄被称作Session
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)
如果在创建session前还未创建engine,操作如下
>>> Session = sessionmaker()
>>> Session.configure(bind=engine) //创建engine后执行
添加新对象
- 会话类的实例对象用于绑定到数据库
- 实例化类的对象,并不打开任何连接
- 当实例初次使用,它将从Engine维护的连接池中获得一个连接
- 当所有的事务均被commit或会话对象被关闭时,连接结束
>>> session = Session()
>>> session.add(dep_dev)
>>> session.commit()
>>> print(str(dep_dev.dep_id))
>>> session.close()
- 可以创建多个实例,批量添加记录
dep_hr = Departments(dep_name='hr')
dep_op = Departments(dep_name='operations')
dep_finance = Departments(dep_name='财务')
dep_xz = Departments(dep_name='行政’)
Session = sessionmaker(engine)
session = Session()
session.add_all([dep_hr, dep_op, dep_finance, dep_xz])
session.commit()
session.close()
外键约束
- ORM映射关系也可用于表间创建外键约束
class Employees(Base):
__tablename__ = 'employees'
emp_id = Column(Integer, primary_key=True)
name = Column(String(20))
genda = Column(String(10))
phone = Column(String(11))
dep_id = Column(Integer, ForeignKey('departments.dep_id'))
def __repr__(self):
return "<Employees(name='%s')>" % self.name
完整实现
sql_alchemy.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Date, ForeignKey
from sqlalchemy.orm import sessionmaker
#创建到数据库的引擎
engine = create_engine(
# mysql+pymysql://用户名:密码@服务器/数据库?字符集参数
'mysql+pymysql://root:123456@127.0.0.1/TestDB?charset=utf8',
encoding = 'utf8',
echo=True #打开调试模式,生产环境不要设置
)
#创建一个会话类,只有在增删改查的时候需要,本模块文件实际上用不到
#只是为了在其他模块中可以直接调用,在本模块中创建
Session = sessionmaker(bind=engine)
#生成实体类的基类
Base = declarative_base()
#创建部门表的实体类
class Departments(Base):
__tablename__ = 'departments' #此类关联departments表
dep_id = Column(Integer,primary_key=True)
dep_name = Column(String(20),unique=True)
class Employees(Base):
__tablename__ = 'employees'
emp_id = Column(Integer,primary_key=True)
emp_name = Column(String(20))
birth_date = Column(Date)
email = Column(String(50))
dep_id = Column(Integer,ForeignKey('departments.dep_id'))
class Salary(Base):
__tablename__ = 'salary'
id = Column(Integer,primary_key=True)
date = Column(Date)
basic = Column(Integer)
awards = Column(Integer)
emp_id = Column(Integer,ForeignKey('employees.emp_id'))
if __name__ == '__main__':
#库中无表则创建,有表只是关联,不会再覆盖
Base.metadata.create_all(engine)
from sql_alchemy import Departments
from sql_alchemy import Employees
from sql_alchemy import Salary
from sql_alchemy import Session
session = Session()
#创建部门
hr = Departments(dep_id=1,dep_name='人事部')
ops = Departments(dep_id=2,dep_name='运维部')
dev = Departments(dep_id=3,dep_name='开发部')
qa = Departments(dep_id=4,dep_name='测试部')
finance = Departments(dep_id=5,dep_name='财务部')
market = Departments(dep_id=6,dep_name='市场部')
sales = Departments(dep_id=7,dep_name='销售部')
#在数据库中创建记录
deps = [hr,ops,dev,qa,finance,market,sales]
session.add_all(deps)
session.commit() #确认至数据库
session.close() #关闭会话
from dbconn import Departments, Employees, Salary, Session
# 创建会话实例,用于连接数据库
session = Session()
# 创建员工实例
qqq = Employees(
emp_id=1,
emp_name='qqq',
birth_date='1994-05-28',
email='qqq@163.com',
dep_id=2
)
www = Employees(
emp_id=2,
emp_name='www',
birth_date='1996-03-21',
email='www@qq.com',
dep_id=2
)
eee = Employees(
emp_id=3,
emp_name='eee',
birth_date='1995-08-09',
email='eee@qq.com',
dep_id=3
)
rrr = Employees(
emp_id=4,
emp_name='rrr',
birth_date='1995-12-20',
email='rrr@sina.cn',
dep_id=2
)
ttt = Employees(
emp_id=5,
emp_name='ttt',
birth_date='1992-01-15',
email='ttt@163.com',
dep_id=3
)
yyy = Employees(
emp_id=6,
emp_name='yyy',
birth_date='1995-02-18',
email='yyy@qq.com',
dep_id=1
)
uuu = Employees(
emp_id=7,
emp_name='uuu',
birth_date='1997-08-19',
email='uuu@126.cn',
dep_id=3
)
iii = Employees(
emp_id=8,
emp_name='iii',
birth_date='1996-11-08',
email='iii@163.com',
dep_id=2
)
ooo = Employees(
emp_id=9,
emp_name='ooo',
birth_date='1994-06-10',
email='ooo@qq.com',
dep_id=4
)
# 在数据库中创建记录
emps = [qqq, www, eee, rrr, ttt, yyy, uuu, iii, ooo]
session.add_all(emps)
session.commit() # 确认至数据库
# 关闭会话
session.close()
常用过滤操作符
- 相等
query.filter(Employees.name=='john')
- 不相等
query.filter(Employees.name!='john')
- 模糊查询
query.filter(Employees.name.like(' %j '))
- in
query.filter(new_emp.name.in_(['bob', 'john'])
- not in
query.filter(~new_emp.name.in_(['bob', 'john'])
- 字段为空
query.filter(new_emp.name.is_(None))
- 字段不为空
query.filter(new_emp.name.isnot(None))
from dbconn import Session, Departments, Employees
# 1. 创建一个会话实例
session = Session()
#################################
# 查询数据库,返回实体类的实例
# qset1 = session.query(Departments)
# print(qset1) # 此时只是一条SQL语句,不真正连接数据库
# print(list(qset1)) # 取值的时候,才会连接数据库
# for dep in qset1:
# print('部门ID: %s, 部门名称: %s' % (dep.dep_id, dep.dep_name))
#################################
# 如果查询某些字段,返回的是元组
# qset2 = session.query(Employees.emp_name, Employees.email)
# print(qset2) # qset2是SQL语句
# print(list(qset2)) # 取值是元组
#################################
# 排序,可以对执行结果进一步操作
# qset3 = session.query(Departments).order_by(Departments.dep_id)
# for dep in qset3:
# print(dep.dep_id, dep.dep_name)
#################################
# 排序,取切片
# qset4 = session.query(Departments).order_by(Departments.dep_id)[2:4]
# print(qset4) # 因为qset4执行了切片取值,所以它不是sql语句了
# for dep in qset4:
# print(dep.dep_id, dep.dep_name)
#################################
# 过滤,查找2号部门的员工
# qset5 = session.query(Employees).filter(Employees.dep_id==2)
# for emp in qset5:
# print(emp.emp_name, emp.email)
#################################
# 过滤,查找2号部门使用163邮箱的员工
# qset6 = session.query(Employees).filter(Employees.dep_id==2)\
# .filter(Employees.email.like('%163.com'))
# for emp in qset6:
# print(emp.emp_name, emp.email)
#################################
# all方法返回列表, first方法返回结果的第一项
# qset7 = session.query(Departments).order_by(Departments.dep_id)
# print(qset7.all())
# print(qset7.first())
# dep = qset7.first()
# print(dep.dep_id, dep.dep_name)
#################################
# 多表查询,查询员工所在部门
# qset8 = session.query(Employees.emp_name, Departments.dep_name)\
# .join(Departments)
# for item in qset8:
# print(item)
# 多表查询时,query的第一个参数是Employees.emp_name,join时要写Departments
# 如果query的第一个参数是Departments.dep_name, join时要写Employees
# qset9 = session.query(Departments.dep_name, Employees.emp_name)\
# .join(Employees)
# for item in qset9:
# print(item)
#################################
# 更新,首先找到记录对应的实例,然后对实例重新赋值即可
# 注意,filter的结果是列表的形式
# qset10 = session.query(Departments).filter(Departments.dep_name=='人事部')
# hr = qset10[0] # 从列表中取出第一个元素
# hr.dep_name = '人力资源部'
# session.commit() # 增删改都要commit
#################################
# 删除,将7号部门删除
qset11 = session.query(Departments).filter(Departments.dep_id==7)
sales = qset11[0]
session.delete(sales)
session.commit()