python 操作数据库
import pymysql
from openpyxl.reader.excel import load_workbook
def importExcelToMysql(cur, path):
num = 1
# 读取excel文件
workbook = load_workbook(path)
# 获得所有工作表的名字
sheets = workbook.get_sheet_names()
# 获得第一张表
worksheet = workbook.get_sheet_by_name(sheets[0])
# 将表中每一行数据读到sqlstr数组中
for row in worksheet.rows:
if num == 1:
num += 1
continue
sqlstr = []
for cell in row:
sqlstr.append(cell.value)
valuestr = [str(sqlstr[0]), int(sqlstr[1]), int(sqlstr[2]), int(sqlstr[3])]
# valuestr = tuple(valuestr)
# 将每行数据存到数据库中
cur.execute("insert into student(name1, language1, math1, english1) VALUES(%s, %s, %s, %s)", valuestr)
# 输出数据库中的内容
def readTable(cursor):
# 选择全部
cursor.execute("select * from student")
# 获得返回值, 返回多条记录, 若没有结果则返回
results = cursor.fetchall()
for i in range(0, results.__len__()):
for j in range(0, 4):
print(results[i][j], end='\t')
print('\n')
if __name__ == '__main__':
# 与数据库建立连接
conn = pymysql.connect(host='127.0.0.1', user='root', password='mysql', database='wangfei3',
port=3306, charset='utf8')
# 创建游标链接
cur = conn.cursor()
# 新建一个database
# cur.execute("drop database if exists students")
# cur.execute("create database students")
# cur.execute("use students")
sql = "CREATE TABLE student(name1 VARCHAR (20),language1 INT,math1 INT,english1 INT)"
# 如果存在students这个表则删除
cur.execute("drop table if exists student")
# 创建表
cur.execute(sql)
# 将excel中的数据导入数据库中
importExcelToMysql(cur, "./student.xlsx")
readTable(cur)
# 关闭游标链接
cur.close()
conn.commit()
# 关闭数据库服务连接, 释放内存
conn.close()