SQLAlchemy

sqlalchemy模块

• sqlalchemy不需要写sql语句，只需要使用python语法即可
• ORM：对象关系映射
  • Object：对象
  • Relationship：关系
  • Mapper：映射
  • 数据库的每张表与python的一个class映射
  • 表的字段与class类变量映射
  • 数据库字段的数据类型，映射为sqlalchemy定义的类

[root@localhost day04] pip3 install sqlalchemy
MariaDB [None]> CREATE DATABASE mydb DEFAULT CHARSET utf8;

'''创建表'''
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建到数据库的引擎
engine = create_engine(
    # mysql+pymysql://用户名:密码@服务器地址/数据库名?参数
    'mysql+pymysql://root:123456@192.168.4.52/tedu1910?charset=utf8',
    encoding='utf8',
    # echo=True  # 在屏幕上打印debug日志，生产环境不要打开
)

# 如果需要对数据库进行增删改查，需要创建到服务器的会话
Session = sessionmaker(bind=engine)

# 创建实体类的基类，固定写法
Base = declarative_base()

# 创建实体类
class Departments(Base):
    __tablename__ = 'departments'  # 与库中哪张表映射
    dep_id = Column(Integer, primary_key=True)  # 每个字段都是一个Column
    dep_name = Column(String(20))

class Employees(Base):
    __tablename__ = 'employees'
    emp_id = Column(Integer, primary_key=True)
    emp_name = Column(String(20))
    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)
    emp_id = Column(Integer, ForeignKey('employees.emp_id'))
    basic = Column(Integer)
    awards = Column(Integer)

if __name__ == '__main__':
    Base.metadata.create_all(engine)  # 库中无表则创建，有表不会创建

'''增删改查
sqlalchemy1为建表的py文件名'''

from sqlalchemy1 import Session, Departments

# 创建一个会话实例
session = Session()

# 增
hr = Departments(dep_id=1, dep_name='人事部' )
cw = Departments(dep_id=2, dep_name='财务部' )
ops = Departments(dep_id=3, dep_name='运维部' )
dev = Departments(dep_id=4, dep_name='开发部' )
qa = Departments(dep_id=5, dep_name='测试部' )
market = Departments(dep_id=6, dep_name='市场部' )
session.add(hr)   # 插入一条记录
session.add_all([cw, ops, dev, qa, market])  # 同时插入多条记录

# 查
# 查询时，使用类名作为查询条件，返回的是实例集合
qset1 = session.query(Departments)
print(qset1)
for dep in qset1:
    print(dep.dep_id, dep.dep_name)
# 查询时，使用变量名作为查询条件，返回的是元组
qset2 = session.query(Departments.dep_id, Departments.dep_name)
for data in qset2:
    print(data)
# 排序
qset3 = session.query(Departments).order_by(Departments.dep_id)
for i in qset3:
    print(i.dep_id, i.dep_name)
# 过滤
qset4 = session.query(Departments).filter(Departments.dep_id > 2)
for i in qset4:
    print(i.dep_id, i.dep_name)
# 叠加过滤
qset5 = session.query(Departments).filter(Departments.dep_id > 2).filter(Departments.dep_id < 5)
for i in qset5:
    print(i.dep_id, i.dep_name)
# %
qset6 = session.query(Departments).filter(Departments.dep_name.like('%事%'))
for i in qset6:
    print(i.dep_id, i.dep_name)
# 查询id为1，3，5的部门
qset7 = session.query(Departments).filter(Departments.dep_id.in_([1, 3, 5]))
for i in qset7:
    print(i.dep_id, i.dep_name)
# 查询id不是1，3，5的部门
qset8 = session.query(Departments).filter(~Departments.dep_id.in_([1, 3, 5]))
for i in qset8:
    print(i.dep_id, i.dep_name)
# 多表查询，query参数先写Employees.xxx，join时，参数为Departments
# 如果query参数先写Departments.xxx, join时，参数就是Employee
qset9 = session.query(Employees.emp_name, Departments.dep_name).join(Departments)
for data in qset9:
    print(data)
# 从查询结果中取出数据
# all方法取出所有数据，构成列表
# first取出数据的第一个结果
qset10 = session.query(Departments.dep_name, Employees.emp_name).join(Employees)
print(qset10.all())
print(qset10.first())

# 改
qset11 = session.query(Departments).filter(Departments.dep_name == '人事部')
hr = qset11.first()
hr.dep_name = '人力资源部'

# 删
qset12 = session.query(Departments).filter(Departments.dep_name == '市场部')
market = qset12.first()
session.delete(market)

# 确认
session.commit()

# 关闭会话
session.close()