python基础篇{Pymysql}

 python基础学习路线：

   · 数据分析之python基础篇{基础语法}
   · 数据分析之python基础篇{数据类型}
   · 数据分析之python基础篇{控制流语句}
   · 数据分析之python基础篇{函数}
   · 数据分析之python基础篇{模块}
   · 数据分析之python基础篇{文件操作}
   · 数据分析之python基础篇{Pymysql}
   · 数据分析之python基础篇{面向对象}

  假设你已经（或者有能力）获取大量数据（通过爬取），那么选择何种方式去存储数据非常重要

  一般而言就爬虫问题，我们可以选择：

1. 文本文件的形式保存（比如csv）
      - 优势：方便，随时使用，不需要第三方的支持
      - 劣势：健壮性差，扩展性差
2. 数据库
     - 优势：良好的扩展性，使用广泛
     - 劣势：对技术有一定要求

Pymysql 安装：Python配合MySQL,我们使用pymysql进行操作
利用 pip安装pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple，在cmd山运行。

连接数据库

import pymysql # 导入pymysql的包

首先是在MySql WorkBench内建立一个数据库。

然后，根据建表的信息，连接数据库。使用connect()

# 连接你自己的数据库
connect = pymysql.connect(host = '127.0.0.1',
                         port=3306,
                         user = 'root',
                         password= '******', # 这里输入自己的密码即可
                         charset = 'utf8')

建立光标，执行sql语句

 执行connect下的cursor()

# 建立一个光标
cursor =connect.cursor()

 建立光标后，才可以执行sql语句。

建立数据库

 大致的思路就是说，把sql语句定义成为一个字符串，然后用cursor的execute去执行。

sql = "create database psql character set utf8 collate utf8_general_ci;"
cursor.execute(sql)# 执行

 · create database cda_test2：代表的是创建数据库 test。
 · character set utf8 ： 代表的是将该库的默认编码格式设置为utf8格式。
 · collate utf8_general_ci ：代表的是数据库校对规则，utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

建立表格

 创建表格和创建数据库一个思路

# 创建表格前，要进入要使用的数据库当中
sql2='use psql'
cursor.execute(sql2)
# 创建表格
sql3 = '''create table Student (Sid varchar(10), 
                                Sname nvarchar(10),
                                Sage datetime,
                                Ssex nvarchar(10))'''
cursor.execute(sql3) # 执行

插入数据

 同样的思路，sql定义成字符串，然后去执行，但是要注意的是，pysql中的插入数据需要提交数据。

sql4 = """insert into student1 values('001','QAZ','18','男')
"""
cursor.execute(sql4)
# 插入数据特殊的地方: 需要提交
connect.commit()

 一次性插入多条语句

# 利用元组，组建多条语句
# 一次插入多条数据
data = [('002','钱电' ,'32', '男'),
       ('003' , '孙风' , '28' , '男'),
       ('004' , '李云' , '19' , '女')]
# 利用for对列表进行遍历
# 对每一条数据进行循环
for i in data:
    sql = """insert into student1 values('{}','{}','{}','{}')""".format(*i)
    print(sql)
    cursor.execute(sql)
# 插入数据特殊的地方: 需要提交
connect.commit()

  我们发现利用For循环来插入数据有点麻烦。可以利用executemany()方法

data = [('002','钱电' ,'32', '男'),
       ('003' , '孙风' , '28' , '男'),
       ('004' , '李云' , '19' , '女')]
cursor.executemany("insert into student1 values(%s, %s, %s, %s)" ,data) 
connect.commit()

执行事务

  事务机制可以确保数据一致性。可以把数据库做的一件事叫做事务
  事务应该具有以下的4个属性：这四个属性通常称为ACID特性。

• 原子性（atomicity）：一个事务是一个不可分割的工作单位，也就是说事务中的诸操作只有都做，和都不做两种选择。
• 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
• 隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（durability）：持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

 这四个属性通常称为ACID特性。

  比如说：

# 原始的data数据
data = [('002','钱电' ,'32', '男'),
       ('003' , '孙风' , '28' , '男'),
       ('004' , '李云' , '19' , '女')]
# 我们将data数据故意修改成错误的数据
data[1] = ('003','孙风','28a','男')
# 对每一条数据进行循环
for i in data:
    sql = """insert into student1 values('{}','{}','{}','{}')""".format(*i)
    print(sql)
    cursor.execute(sql)
    # 插入数据特殊的地方: 需要提交
    connect.commit() 

 运行了的同学就会发现的，表内只插入了第一条数据，第二条因为错误数据而被报错。但是插入第一条数据成功了，这就破坏了事务的原子性和一致性。

 可以使用 try...except...finally语句避免事务的原子性

try: # 尝试运行以下代码, 可能可以正常运行,可能报错
    xxxxxx
except: # 如果上面代码报错了, 就运行这块
    xxxxxx
finally: # 不管上面代码有没有报错,这个代码一定会被运行
    xxxxxx

try:
    for i in data:
        sql = """insert into student1 values('{}','{}','{}','{}')""".format(*i)
        print(sql)
        cursor.execute(sql)
    connect.commit() # 提交
except: # 万一上面报错了
    # 发生错误时回滚
    print('上面已经报错了')
    connect.rollback() # 就是 退出运行，但是不执行的意思。
    

查询数据

  同样是定义语句，然后去执行

# 定义查询语句然后执行
sql = 'select * from Student1' # 查询所有数据
cursor.execute(sql)  # 返回的一个数字

  查询语法

• cursor.fetchone ： 提取一条，每一次新的执行，就会提取到下一条数据
  - cursor.fetchall ：查询到的所有
• cursor.fetchmany(n)： 提取查询到的前n条

  查询数据，在执行execute()，然后根据需求执行以上任意一天方法，就可以看到查询到的数据了

 需要注意的是，因为光标的不可倒退性，执行以上的任意一条方法后，再去执行任意其他的两条语法，都应该先去执行select * from 表名

练习题

  练习一：创建salgrade表并添加数据 通过create和insert命令的语法练习，理解数据库的基本结构，且熟练掌握数据定义语言和数据操作语言的使用。 表结构及记录如下：

字段名	数据类型
name	varchar(10)
basic	int
award	int

name	basic	award
张三	700	1200
李四	1201	1400
王五	1401	2000
赵六	2001	3000
孙七	3001	9999

• 查询基本工资大于1400的数据
• 查询奖金在1500-5000的人有哪些

import pymysql
connect = pymysql.connect(host = '127.0.0.1',
                         port=3306,
                         user = 'root',
                         password= '******', # 这里输入自己的密码即可
                         charset = 'utf8')
# 创建表格前，要进入要使用的数据库当中
sql2='use psql'
cursor.execute(sql2)    
# 创建表格
sql = """-- 练习：创建salgrade表并插入数据
create table salgrade (
    name varchar(10),
    basic int,
    award int
);"""   
cursor.execute(sql)
# 插入数据
sql2 = """-- 插入工资等级信息
insert into salgrade values ('张三',700,1200),
                            ('李四',1201,1400),
                            ('王五',1401,2000),
                            ('赵六',2001,3000),
                            ('孙七',3001,9999);"""     
cursor.execute(sql2) 
connect.commit()
# 查询数据
sql3 = """select * from salgrade"""
cursor.execute(sql3)
sql4 = """select * from salgrade where basic>1400;"""
cursor.execute(sql4)
cursor.fetchall()
sql = """select * from salgrade where award >1500 and award<5000;"""
cursor.execute(sql)
cursor.fetchall()

  练习二：将一个txt文档插入到数据当中

# 把student_grade 存到数据库中去
import os
f = open('student_grade.txt','r')
data = f.readlines()
f.close()
# 建一个空列表
student_grade = []
for line in data:
    student_grade.append( line.split('\t') )
sql = """
create table student_grade (
    姓名 varchar(10),
    语文 int,
    数学 int,
    英语 int,
    总分 int,
    班名次 int
);"""
cursor.execute(sql)
# 插入数据
cursor.executemany("""insert into student_grade values(%s, %s , %s , %s,%s,%s)""" , 
                   student_grade[1:])
connect.commit()