【Educoder作业】MySQL开发技巧 - 批量数据入库及检索
这个期末是不是不考?还是更一下啦
T1 MySQL数据库连接
这题没啥好说的,就是每次用 P y t h o n Python Python写数据库题的时候必须做的。
# 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()
T2 数据库与数据表创建
看题面完成操作即可,换行的话是\不是/。
# 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')
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-----------
T3 批量数据入库与检索
这个题有说法的,关键在于 y e a r year year等关键字的变量类型和 p r o v i n c e province province不同。一个是 v a r c h a r varchar varchar,其余是 i n t int int。所以在格式化的时候 p r o v i n c e province province需要格式化成 ′ % s ′ '\%s' ′%s′而其余的 i n t int int只需要格式化成 % s \%s %s.
import pymysql
def insert(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean):
# print(type(year))
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()
T4 多表创建与连接查询
在
s
e
l
e
c
t
select
select的
f
r
o
m
from
from可以有
t
a
b
l
e
n
a
m
e
table name
tablename这种格式出现,之后整行语句都可以用
n
a
m
e
name
name来代替
t
a
b
l
e
table
table。
当然,题面里的自然链接必然是缺东西的。后面需要有
w
h
e
r
e
where
where来限制条件。
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 %s a, %s b, %s c \
where a.year = b.year and\
b.year = c.year and \
a.province = b.province and \
b.province = c.province' % ('provincialEntryScore', 'nudtTechScore', 'nudtMilScore')
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 %s a, %s b, %s c where a.year = b.year and b.year = c.year and a.province = b.province and b.province = c.province' %('provincialEntryScore', 'nudtTechScore', 'nudtMilScore')
cursor.execute(sql)
records = cursor.fetchall()
return records
# ------------End-----------