美文网首页
4.SQLAlchemy的使用

4.SQLAlchemy的使用

作者: 隔壁python | 来源:发表于2019-07-14 13:53 被阅读0次
    • 安装
      pip install SQLAlchemy
    • 创建数据库
      create database test default charset utf8;
      
    • 使用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()
      
      

    以上代码,经过测试,可以正常运行,如有错误,请批评指正,不用客气.

    相关文章

      网友评论

          本文标题:4.SQLAlchemy的使用

          本文链接:https://www.haomeiwen.com/subject/yfpbkctx.html