Mysql开发技巧 批量入库及检索

第一关  MySQL数据库连接

任务描述

Python可以通过MySQLdb库连接MySQL数据库，但Python3之后不再支持MySQLdb库，需要通过pymysql库连接。
pymysql通过connect方法连接数据库，如：

1. conn = pymysql.connect(host='localhost', user='root',passwd='123456',charset='utf8')

相关知识

connect方法的参数含义如下：

1. host：数据库主机名，默认是用本地主机
2. user：数据库登陆名，默认是当前用户
3. passwd：数据库登陆的密码，默认为空
4. charset:连接时的编码格式，要求与数据库的编码一致

在安装MySQL数据库环境时，会选择默认编码格式，建议选择utf8格式；若登录名或密码错误时，返回如下错误：

1. pymysql.err.OperationalError: (1045, "Access denied for user 'a'@'localhost' (using password: YES)")

编程要求

本关的编程任务是在Begin-End区域补全src/step1/connect.py文件的代码内容，实现如下功能：

• 连接平台中的数据库

平台中已配置好MySQL数据库，用户名默认为‘root’，密码为‘123123’

本文涉及的src/step1/connect.py文件的代码框架如下：

1. # coding=utf-8
3. # 请在下面添加连接数据库的代码，完成相应功能
4. ###### Begin ######
6. ####### End #######
7. print('连接成功')

测试说明

本关的测试文件是 src/step1/connect.py ，测试过程如下：
1. 读者将 src/step1/connect.py 中的代码补充完毕，然后点击评测，平台自动编译并运行；
2. 若输出‘连接成功’,则表示测试通过，否则测试失败。

测试输入：
预期输出：
连接成功

# coding=utf-8
import pymysql
def connect():
# 请在下面添加连接数据库的代码，完成相应功能
# ###### Begin ######
    conn = pymysql.connect(host='localhost',user='root',passwd='123123',charset='utf8')
####### End #######
####### 请不要修改以下代码 #######
    return conn.get_host_info()

第二关 数据库与数据表创建

任务描述

在MySQL中创建属于自己的数据库，根据需求在库中创建相应的表，将数据存入表中，方便对数据的管理与查询。如汽车零售商将汽车信息存入数据库中方便管理，可创建数据库：

1. cursor.execute('create database carinfo')

与汽车相关的信息可能包括汽车购入信息和汽车出售信息，可以分别在carinfo库中建立对应的表：

1. cursor.execute('create table ininfo (indata data,inNum int, brand varchar(255))')
2. cursor.execute('create table outinfo (outdata data,outNum int, brand varchar(255))')

本关任务是，创建enroll数据库，并在enroll创建nudt表。

相关知识

Python提供操作MySQL数据库的一系列语句。本关的目的是让读者学会如何利用Python语句创建数据库和数据表。

获取游标

前面已经介绍过如何获取数据库连接对象，但是不能在这个对象上直接对数据库进行操作， 还需要获取对应的操作游标才能进行数据库的操作：

1. cursor = conn.cursor()

游标是一种数据访问对象，可用于创建数据库和数据表，也可用于在表中迭代一组行或者向表中插入新行。

确定使用的数据库

MySQL中可能同时存在多个数据库，为了对指定的数据库进行操作，可使用：

1. dbName = 你的数据库名称
2. conn.select_db(dbName)

当然，如果在创建数据库连接对象时指定了连接的数据库时，就不需要再指定数据库对象：

1. dbName = 你的数据库名称
2. conn = pymysql.connect(host='localhost', user='root',passwd='123456',charset='utf8',db=dbName)

数据表的创建

MySQL是使用SQL语句对数据库进行操作，创建表可使用：

1. create table tablename (字段名 字段属性，字段名 字段属性，……)

MySQL支持的字段属性包括：

1. 日期和时间数据类型。
   1. data：3字节，日期，格式：2014-09-18
   2. time：3字节，时间，格式：08:42:30
   3. datetime：8字节，日期时间，格式：2014-09-18 08:42:30
   4. timestamp：4字节，自动存储记录修改的时间
   5. year：1字节，年份
2. 数值数据类型。
   1. tinyint：1字节，范围（-128~127）
   2. smallint：2字节，范围（-32768~32767）
   3. mediumint：3字节，范围（-8388608~8388607）
   4. int： 4字节，范围（-2147483648~2147483647）
   5. bigint：8字节，范围（+-9.22*10的18次方）
3. 浮点型。
   1. float(m, d)：4字节，单精度浮点型，m总个数，d小数位
   2. double(m, d)：8字节，双精度浮点型，m总个数，d小数位
   3. decimal(m, d)：decimal是存储为字符串的浮点数
4. 字符串数据类型。
   1. char(n)：固定长度，最多255个字符
   2. varchar(n)：可变长度，最多65535个字符
   3. tinytext：可变长度，最多255个字符
   4. text：可变长度，最多65535个字符
   5. mediumtext：可变长度，最多2的24次方-1个字符
   6. longtext：可变长度，最多2的32次方-1个字符

编程要求

本关的编程任务是在Begin-End区域补全src/step2/create.py文件的代码内容，实现如下功能：

• 创建数据库enroll
• 创建数据表nudt

表nudt中包含的字段及对应的属性为：

1. year：int
2. province：varchar(100)
3. firstBatch：int
4. gcMax：int
5. gcMin：int
6. gcMean：int
7. xlMax：int
8. xlMin：int
9. xlMean：int

本关涉及的代码文件src/step2/create.py的代码框架如下：

1. # coding = utf-8
3. # 连接数据库，建立游标cursor
5. import pymysql
6. def create():
7. conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
8. cursor = conn.cursor()
9. # -----------Begin----------
10. # 创建enroll数据库
12. conn.select_db('enroll')
13. # 创建nudt数据表
15. # ------------End-----------

测试说明

1. 读者将src/step2/create.py中的代码补充完整，然后点击评测，平台自动编译并运行test.py；
2. 平台去MySQL中检查是否有enroll库，并检查enroll表中是否有nudt表。

测试输入：
预期输出：
enroll库创建成功
nudt表创建成功

# coding = utf-8

# 连接数据库，建立游标cursor
import pymysql

def create():
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    # -----------Begin----------
    # 创建enroll数据库
    cursor.execute('create database enroll')
    dbName = "enroll"
    conn.select_db('enroll')
    # 创建nudt数据表
    cursor.execute('create table nudt (year int,province varchar(100),firstBatch int,gcMax int,gcMin int,gcMean int,xlMax int,xlMin int,xlMean int)')

    # ------------End-----------

第3关：批量数据入库与检索

任务描述

数据表创建好之后，可以将数据存入表中方便之后对数据进行分析。SQL中插入语句为insert，如往ininfo表中存入汽车购入信息：

1. sql = "insert into %s (indata, inNum, brand) values ('%s', '%s', '%s')" % (tablename, '2017-8-19', 1000, 'Chevrolet')
2. cursor.execute(sql)

数据插入后可以去数据库中查询，判断数据是否正确插入：

1. sql = 'select * from %s' % tablename
2. cursor.execute(sql)
3. records = cursor.fetchall()

本关任务是，向nudt表中存入国防科技大学2014在各省的录取分数线以及最高分、最低分和平均分。

相关知识

对于数据库最基本的操作包括对数据库中的数据增删查改。本关的目的是让读者掌握增、查数据的技能。

格式化字符串

插入数据时使用insert语句，需要指定每个字段的值，如：

1. sql = "insert into %s (indata, inNum, brand) values ('%s', '%s', '%s')" % (tablename, '2017-8-19', 1000, 'Chevrolet')

python中可以用%对字符串进行格式化操作，%s是优先用str()函数进行字符串转化，%左侧的字符串称为格式化字符串，右侧是希望格式化的值，格式化字符串的%s部分称为转换说明符，标记了需要插入转换值的位置。

利用游标接收返回值

Python执行完select语句后可以从数据库中获取到数据，但需要执行fetchxxx语句后才能将数据取回本地进行操作。fetchxxx语句包括：

1. fetchall()：接收全部的返回结果行
2. fetchmany(size=None)：接收size条返回结果行
3. fetchone()：返回一条结果行

另外还可以用下面的方法移动游标：

1. scroll(value, mode='relative')：指针移动value条

事务提交与关闭连接

在完成插入之后需要将插入事务提交，否则会导致相应的表死锁：

1. conn.commit()

在对数据库的所有操作完成之后，需要关闭与数据库之间的连接：

1. conn.close()

虽然不主动关闭连接也会过期，但是会较长时间占用mysql宝贵的连接资源。

编程要求

本关的编程任务是在Begin-End区域补全src/step3/insl.py文件的代码内容，实现如下功能：

• 将2014年国科录取分数线数据存入nudt表中
• 获取插入到数据库的数据

本关涉及的代码文件src/step3/insl.py的代码框架如下：

1. import pymysql
3. def insert(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean):
4. conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
5. cursor = conn.cursor()
6. conn.select_db('enroll')
7. # -----------Begin----------
8. # 请在下面输入插入数据的语句，完成相应功能
9. sql =
10. cursor.execute(sql)
11. # ------------End-----------
13. # 提交数据到数据库
14. conn.commit()
15. # 关闭数据库连接
16. cursor.close()
17. conn.close()
19. def select():
20. conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
21. cursor = conn.cursor()
22. conn.select_db('enroll')
23. # -----------Begin----------
24. # 请在下面输入查询数据的语句，完成相应功能
25. sql =
26. cursor.execute(sql)
27. # 请在下面输入获取数据的语句，完成相应功能
28. record =
29. # ------------End-----------
30. for record in records:
31. print(record)

测试说明

1. 读者将src/step3/insl.py中的代码补充完整，然后点击评测，平台自动编译并运行test.py；
2. 平台获取程序的输出，然后将其与预期输出对比，如果一致则测试通过；否则测试失败。

测试输入：
预期输出：
(2014,'新疆',475,642,598,617,647,549,579)
(2014,'宁夏',473,630,566,590,570,525,553)
(2014,'广西',520,644,601,622,626,536,589)
(2014,'上海',423,476,437,454,None,None,None)
(2014,'广东',560,657,635,646,640,601,621)
(2014,'陕西',503,664,626,645,638,560,608)

import pymysql

def insert(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean):
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    conn.select_db('enroll')
    # -----------Begin----------
    # 请在下面输入插入数据的语句，完成相应功能
    sql = 'insert into nudt(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean) values (%s,"%s",%s,%s,%s,%s,%s,%s,%s)' % (year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean)  
    cursor.execute(sql) 
    # ------------End-----------
    
    # 提交数据到数据库
    conn.commit()
    # 关闭数据库连接
    cursor.close() 
    conn.close()

def select():
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    conn.select_db('enroll')
    # -----------Begin----------
    # 请在下面输入查询数据的语句，完成相应功能
    sql = 'select * from nudt'  
    cursor.execute(sql)
    # 请在下面输入获取数据的语句，完成相应功能
    records = cursor.fetchall() 
    # ------------End-----------
    for record in records:
        print(record)

    # 关闭数据库连接
    cursor.close() 
    conn.close()

第4关：多表创建与连接查询

任务描述

在存储信息时可能出现不能将所有信息存入同一张表中的情况，如汽车购入记录和汽车出售记录，为了查询汽车剩余数量，就需要同时获取汽车购入记录及对应的出售记录。汽车购入记录中有一个字段为brand，汽车销售记录中同样有一个brand字段，则可通过该字段将两表关联起来，获取汽车剩余数量：

1. select A.brand, A.inNum - B.outNum from ininfo A, outinfo B where A.brand = B.brand

这种查询方式即为关联查询，brand为两表的共有字段。
本关任务是，在enroll库中创建provincialEntryScore（各省分数线表），nudtTechScore（科大技术类分数线表），nudtMilScore（科大指挥类分数线表）三张表，往表中插入数据，并根据要求查询。

相关知识

多表直接汇总

在多表查询时，可直接将每张表中的数据全部取出汇总：

1. select * from ininfo, outinfo

这种查询会使得表中的一条记录出现多次，如ininfo表中有两条记录a、b，outinfo表中有两条记录c、d，则查询结果为：

1. a c
2. a d
3. b c
4. b d

另外，这种查询方式会将ininfo，outinfo两张表中的全部字段取出汇总，上述查询语句得到的结果为：

1. `indata`，`inNum`，`brand`，`outdata`，`outNum`，`brand`

当两张表没有共有字段时这种操作方式没问题，但是如果表与表之间有共有字段，如ininfo，outinfo表中的brand字段，这种查询方式会使结果中出现重复字段。

等值连接

这种查询方式会将两张表中共有字段相同的记录连接前来输出，这种查询方式只适合于表与表之间有共有字段，即表与表之间存在关联。查询方式为：

1. select * from ininfo A, outinfo B where A.brand = B.brand

如ininfo中a的brand为Chevrolet，outinfo中b的brand为Chevrolet，则等值连接的结果为：

1. a.indata，a.inNum，a.brand，b.indata，b.outNum，b.brand

这种查询方式同样存在共有字段多次输出的问题。

自然连接

自然连接查询保证多表之间的共有字段只输出一次，如：

1. select A.brand, A.indata, A.inNum, B.outdata, B.outNum from ininfo A, outinfo B

查询结果如下：

1. a.brand， a.indata， a.inNum， b.outdata， b.outNum

编程要求

本关的编程任务是在Begin-End区域补全src/step4/mtinsl.py文件的代码内容，实现如下功能：

• 创建provincialEntryScore、nudtTechScore、nudtMilScore表
• 往三张表中插入数据
• 以直接汇总、等值连接及自然连接三种方式汇总三张表中的数据

provincialEntryScore表中包含的字段及对应的属性为：

1. year：int
2. province：varchar(100)
3. entryScore：int

nudtTechScore表中包含的字段及对应的属性为：

1. year：int
2. province：varchar(100)
3. techMax：int
4. techMin：int
5. techMean：int

nudtMilScore表中包含的字段及对应的属性为：

1. year：int
2. province：varchar(100)
3. milMax：int
4. milMin：int
5. milMean：int

import pymysql
def create(cursor):  
    # -----------Begin----------  
    # 创建provincialEntryScore表  
    sql = 'create table provincialEntryScore(year int, province varchar(100), entryScore int)'  
    cursor.execute(sql)  
    # 创建nudtTechScore表  
    sql = 'create table nudtTechScore(year int, province varchar(100), techMax int, techMin int, techMean int)'  
    cursor.execute(sql)  
    # 创建nudtMilScore表  
    sql = 'create table nudtMilScore(year int, province varchar(100), milMax int, milMin int, milMean int)'  
    cursor.execute(sql)  
    # ------------End-----------
def insert(cursor,year,province,entryScore,techMax,techMin,techMean,milMax,milMin,milMean):  
    # -----------Begin----------  
    # 请在下面输入将数据插入provincialEntryScore表中的语句  
    sql = 'insert into provincialEntryScore(year, province, entryScore) values (%s, "%s", %s)' % (year, province, entryScore)  
    cursor.execute(sql)  
    # 请在下面输入将数据插入nudtTechScore表中的语句  
    sql = 'insert into nudtTechScore(year, province, techMax, techMin, techMean) values (%s, "%s", %s, %s, %s)' % (year, province, techMax, techMin, techMean)  
    cursor.execute(sql)  
    # 请在下面输入将数据插入nudtMilScore表中的语句  
    sql = 'insert into nudtMilScore(year, province, milMax, milMin, milMean) values (%s, "%s", %s, %s, %s)' % (year, province, milMax, milMin, milMean)  
    cursor.execute(sql)  
    # ------------End-----------
def selectAll(cursor):  
    # -----------Begin----------  
    # 请在下面输入多表直接汇总的语句  
    sql = 'select * from provincialEntryScore, nudtTechScore, nudtMilScore'  
    cursor.execute(sql)  
    records = cursor.fetchall()  
    return records  
    # ------------End-----------
def selectEqual(cursor):  
    # -----------Begin----------  
    # 请在下面输入等值连接的语句  
    sql = 'select * from provincialEntryScore A,nudtTechScore B,nudtMilScore C where A.year = B.year and A.year = C.year and A.province = B.province and A.province = C.province'  
    cursor.execute(sql)  
    records = cursor.fetchall()  
    return records  
    # ------------End-----------
def selectNatural(cursor):  
    # -----------Begin----------  
    # 请在下面输入自然连接的语句  
    sql = 'select A.year, A.province, A.entryScore, B.techMax, B.techMin, B.techMean, C.milMax, C.milMin, C.milMean from provincialEntryScore A, nudtTechScore B, nudtMilScore C where A.year = B.year and A.year = C.year and A.province = B.province and A.province = C.province'  
    cursor.execute(sql)  
    records = cursor.fetchall()  
    return records  
    # ------------End-----------