- 安装
pip install SQLAlchemy - 创建数据库
create database test default charset utf8;- 此库的表参考3.pymysql的使用
- 使用SQLAlchemy的步骤
1.创建到数据库的引擎
2.生成实体类的基类
3.创建实体类
4.执行创建操作
5.为了执行CRUD操作,需要创建到达数据库的会话类from sqlalchemy import create_engine, Column, Integer, String, Date, ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker # 创建到数据库的引擎 engine = create_engine( # mysql+pymysql://用户名:密码@服务器/数据库?字符集参数 'mysql+pymysql://root:b@192.168.1.10/test?charset=utf8', # 编码方案 encoding='utf8', # echo=True # 打开调试模式，生产环境不要设置 ) # 创建一个会话类，只有在CRUD，即增删改查时需要，本模 块文件实际上用不到 # 为了在其他模块文件中可以直接调用，在本模块中创建 Session = sessionmaker(bind=engine) # 生成实体类的基类 Base = declarative_base() # 创建部门表的实体类，类名自定义，必须继承自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与departments的dep_id构成外键约束关系 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) ) - 创建部门表的会话实例
from dbconn import Departments, Employees, Salary, 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, Salay, Session # 创建会话实例，用于连接数据库 session = Session() # 创建员工实例 z1 = Employees( emp_id = 1, emp_name = '张一', birth_date = '1999-12-12', email = 'z1@163.com', dep_id = 1 ) z2 = Employees( emp_id = 2, emp_name = '张二', birth_date = '2000-12-12', email = 'z2@163.com', dep_id = 2 ) z3 = Employees( emp_id = 3, emp_name = '张三', birth_date = '2001-12-12', email = 'z3@163.com', dep_id = 3 ) z4 = Employees( emp_id = 4, emp_name = '张四', birth_date = '1994-12-12', email = 'z4@163.com', dep_id = 4 ) z5 = Employees( emp_id = 5, emp_name = '张五', birth_date = '1995-12-12', email = 'z5@163.com', dep_id = 3 ) z6 = Employees( emp_id = 6, emp_name = '张六', birth_date = '1996-12-12', email = 'z6@163.com', dep_id = 5 ) z7 = Employees( emp_id = 7, emp_name = '张七', birth_date = '1997-12-12', email = 'z7@163.com', dep_id = 3 ) z8 = Employees( emp_id = 8, emp_name = '张八', birth_date = '1998-12-12', email = 'z8@163.com', dep_id = 2 ) # 在数据库中创建记录 emps = [z1, z2, z3, z4, z5, z6, z7, z8] session.add_all(emps) session.commit() # 确认至数据库 # 关闭会话 session.close() - 对数据进行CRUD操作
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()
以上代码,经过测试,可以正常运行,如有错误,请批评指正,不用客气.
