博客

pymysql模块

pymysql安装

本地安装

• 下载模块：https://pypi.org/
• 安装方法一：

[root@localhost 下载]# tar xf xxxx.tar.gz   # 解压
[root@localhost 下载]# cd xxxx              # 切换至解压目录
# 注意，python setup.py install将会将模块安装到python2中
[root@localhost xxxx]# python3 setup.py install

• 安装方法二：

[root@localhost zzg_pypkgs]# pip3 install wordcloud_pkgs/*

在线安装

• pip工具类似于yum，yum安装rpm包，pip安装python包
• 在线直接安装

[root@localhost zzg_pypkgs]# pip3 install wget

• 如果直接安装的话，有可能速度比较慢，因为使用的是国外站点。
• 设置安装时，使用国内镜像站点

[root@localhost ~]# mkdir ~/.pip
[root@localhost ~]# vim ~/.pip/pip.conf
[global]
index-url = http://mirrors.aliyun.com/pypi/simple/
[install]
trusted-host=mirrors.aliyun.com

[root@localhost ~]# pip3 install pymysql

pymysql应用

• 准备mariadb数据库

[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
[root@localhost ~]# mysqladmin password tedu.cn
[root@localhost ~]# mysql -uroot -ptedu.cn
MariaDB [(none)]> CREATE DATABASE nsd1909 DEFAULT CHARSET utf8;

• 准备数据库
  
  你正在为一个小公司编写程序，需要记录员工的基本信息和发工资的情况。
  
  需要记录的字段：姓名、性别、出生日期、联系方式、部门、工资日、基本工资、奖金、工资总额.
  
  在建表的时候，要注意尽量避免数据冗余。为了减少数据冗余，可以将字段放到不同的表中。
  
  员工表：姓名、性别、出生日期、联系方式、部门ID
  
  部门表：部门ID、部门名称
  
  工资表：姓名、工资日、基本工资、奖金、工资总额
  
  数据库的字段是有要求的，否则不是标准的关系型数据库。指导原则参见数据库范式。
• 所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的。原子性，就是不可
  
  分割。根据1NF，联系方式不满足原子性的要求，因为它还可以再分成家庭住址、电话号码、email
• 第二范式（2NF）必须建立在1NF的基础之上，要求数据库表中的每个实例或记录必须可以被唯一地区分。简单来说，>每张表都需要有一个主键。根据2NF，需要在每张表中有一个主键。员工表中应该添加员工编号字段作为主键；部门表中
  
  ，部门ID作为主键；工资表中的姓名为了解决冗余等问题，应该是员工ID，工资表中无论用现有的哪个字段作为主键都>不合适，所以强行加入一个名为id的字段作为主键。
• 3NF必须建立在2NF的基础之上，要求非主属性不能信赖于其他非主属性。工资总额是通过基本工资和奖金计算出来的>，它不应该出现在数据库中。

根据数据库范式，最终得到的3张表是：

员工表：员工ID、姓名、email、部门ID

部门表：部门ID、部门名称

工资表：id、员工ID、工资日、基本工资、奖金

使用pymysql模块创建表

import pymysql

# 建立连接
conn = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', passwd='123qqq...A',
    db='compary', charset='utf8mb4'
)

# 创建游标。游标类似于文件对象，通过文件对象可以对文件读写，通过游标对数据库进行操作
cur = conn.cursor()

# 编写并执行相应的sql语句
# departments -> 部门
mk_dep = '''CREATE TABLE departments(
dep_id INT, dep_name VARCHAR (20),
PRIMARY KEY (dep_id)
)'''
# employees -> 雇员
mk_emp = '''CREATE TABLE employees(
emp_id INT, emp_name VARCHAR (20), email VARCHAR (50), dep_id INT,
PRIMARY KEY (emp_id), FOREIGN KEY (dep_id) REFERENCES departments(dep_id)
)'''
# salary -> 工资
mk_sal = '''CREATE TABLE salary(
id INT, date DATE, emp_id INT, basic INT, awards INT,
PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES employees(emp_id)
)'''
cur.execute(mk_dep)   # execute -> 执行
cur.execute(mk_emp)
cur.execute(mk_sal)

# 确认
conn.commit()

# 关闭游标、关闭连接
cur.close()
conn.close()

• 实现增删改查

# crud: create增/retrieve查/update改/delete删
import pymysql

# 建立连接
conn = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', passwd='123qqq...A',
    db='nsd1909', charset='utf8mb4'
)

# 创建游标。游标类似于文件对象，通过文件对象可以对文件读写，通过游标对数据库进行操作
cur = conn.cursor()

# 编写并执行相应的sql语句
# 添加部门的语句
sql1 = 'INSERT INTO departments VALUES (%s, %s)'
# 添加1个部门
# cur.execute(sql1, (1, '人事部'))
# 添加多个部门
# cur.executemany(sql1, [(2, '运维部'), (3, '开发部'), (4, '测试部'), (5, '财务部'), (6, '市场部')])

# 查询
sql2 = 'SELECT * FROM departments ORDER BY dep_id'
# cur.execute(sql2)
# result1 = cur.fetchone()    # 取出一条记录
# print(result1)
# print('*' * 50)
# result2 = cur.fetchmany(2)  # 继续向后取出2条记录
# print(result2)
# print('*' * 50)
# result3 = cur.fetchall()    # 继续向后取出全部记录
# print(result3)

# 修改
sql3 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s'
# cur.execute(sql3, ('人力资源部', '人事部'))

# 删除
sql4 = 'DELETE FROM departments WHERE dep_id=%s'
cur.execute(sql4, (6,))  # 即使只有一项内容，第2个参数也必须是元组

# 确认
conn.commit()

# 关闭游标、关闭连接
cur.close()
conn.close()

SQLAlchemy

• 安装sqlalchemy
  
  
pip install sqlalchemy


简介

• 架构
  
  

ORM模型

• 创建表

#此文件名为dbconn.py
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey,Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建引擎，用于指定操作的数据库
engine = create_engine(
    # mysql+pymysql://用户名:密码@服务器/数据库?选项
    'mysql+pymysql://root:123qqq...A@127.0.0.1/alchemy?charset=utf8mb4',
    encoding='utf8',  # 字符编码
    echo=True
)

# 创建会话类，用于连接到数据库，通过会话连接操作数据库
Session = sessionmaker(bind=engine)

# 创建实体类(数据库中表对应的类)的基类
Base = declarative_base()


# 创建实体类
class Department(Base):
    __tablename__ = 'departments'  # 声明该类与哪个表关联
    dep_id = Column(Integer, primary_key=True)  # dep_id字段，整型，主键
    dep_name = Column(String(20), unique=True)  # dep_name字段，VARCHAR(20)，唯一


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

sqlalchemy进阶

• 实现增删改查

# 调用上面创建表的文件
from dbconn import Session, Department, Employee

# 创建到数据库的会话连接
session = Session()

# 执行增删改查
# 创建部门
# hr = Department(dep_id=1, dep_name='人事部')
# ops = Department(dep_id=2, dep_name='运维部')
# dev = Department(dep_id=3, dep_name='开发部')
# qa = Department(dep_id=4, dep_name='测试部')
# finance = Department(dep_id=5, dep_name='财务部')
# market = Department(dep_id=6, dep_name='市场部')
# session.add_all([hr, ops, dev, qa, finance, market])

# 创建员工
# xcw = Employee(
#     emp_id=1, emp_name='许陈碗',
#     email='xcw@tedu.cn', dep_id=1
# )
# lyx = Employee(
#     emp_id=2, emp_name='刘元新',
#     email='lyx@tedu.cn', dep_id=2
# )
# sw = Employee(
#     emp_id=3, emp_name='沈炜',
#     email='sw@qq.com', dep_id=2
# )
# zl = Employee(
#     emp_id=4, emp_name='钟力',
#     email='zl@163.com', dep_id=3
# )
# hn = Employee(
#     emp_id=5, emp_name='浩宁',
#     email='hn@163.com', dep_id=4
# )
# session.add_all([xcw, lyx, sw, zl, hn,])

# 基础查询1：将类作为参数，返回实例构成的列表
qset1 = session.query(Department)
# print(qset1)  # 只是一个SQL语句，当向它取值时，sql语句才执行
# for bumen in qset1:
#     print(bumen.dep_id, bumen.dep_name)

# 基础查询2：将类变量作为参数，返回的是各个属性构成的元组，元组构成列表
qset2 = session.query(Employee.emp_name, Employee.email)
# for data in qset2:
#     print(data)

# 可以对查询的结果进一步应用其他方法,使用order_by()指定字段名排序
qset3 = session.query(Department).order_by(Department.dep_id)
# for bumen in qset3:
#     print(bumen.dep_id, bumen.dep_name)

# get方法，可以根据主键取出实例
# dep = session.query(Department).get(1)
# print(type(dep))
# print(dep.dep_id, dep.dep_name)

# filter方法，实现sql语句中的where，它返回的是列表，列表长度是0到多
qset4 = session.query(Department).filter(Department.dep_id>2).order_by(Department.dep_id)
# for bumen in qset4:
#     print(bumen.dep_id, bumen.dep_name)

# filter方法，可以多次使用
qset5 = session.query(Department).filter(Department.dep_id>2).filter(Department.dep_id<5)
# for bumen in qset5:
#     print(bumen.dep_id, bumen.dep_name)

# 模糊查询，找出email以.com为结尾的员工
qset6 = session.query(Employee.emp_name, Employee.email).filter(Employee.email.like('%.com'))
# for data in qset6:
#     print(data)

# 查询2、3、4号部门是哪些部门
qset7 = session.query(Department).filter(Department.dep_id.in_([2, 3, 4]))
# for bumen in qset7:
#     print(bumen.dep_id, bumen.dep_name)

# 查询部门ID不是2，3，4的部门
qset8 = session.query(Department).filter(~Department.dep_id.in_([2, 3, 4]))
# for bumen in qset8:
#     print(bumen.dep_id, bumen.dep_name)

# 查询结果可以使用for循环遍历取出数据，也可以使用all方法返回列表
# print(qset7)
deps = qset7.all()
# print(deps)

# # first方法返回all方法结果中的第一项
# bumen = qset7.first()
# # print(bumen)
#
# # 多表查询。两张表有主外键关系，在query中，先写Employee.emp_name，join的时候就要写Department
# # 在query中，先写Department.dep_name，join的时候就要写Employee
# qset9 = session.query(Employee.emp_name, Department.dep_name).join(Department)
# result = qset9.all()
# print(result)0
#
# 修改，将人事部改为人力资源部
# qset3 = session.query(Department).filter(Department.dep_id==1)
# hr = qset3.first()
# hr.dep_name = '人力资源部'


# 删除，删除市场部
#qset4 = session.query(Department).filter(Department.dep_id==6)
#market = qset4.first()
#session.delete(market)

# 确认
session.commit()

# 关闭会话连接
session.close()