python操作mysql数据库

1.需要安装第三方库  import pymysql

===============1.增删改查==============

import pymysql
#格式:pymysql.connect(mysql服务器地址,用户名,密码,数据库名,编码)
database = pymysql.connect('127.0.0.1','root','root','rk18',port=3307,charset = 'utf8')
#初始化指针
cursor = database.cursor()
#====增====
#格式:"insert into 表名(字段1,字段2,字段3) values(内容1,内容2,内容3)"
#sql = "insert into score(name,lilun,jineng) values('小露露',98,90)"
#====修改====
#格式:"update 表名 set 字段1=内容1,字段2=内容2 where 条件"
#sql = "update score set lilun=99 where id=30"
#====删除====
sql = "delete from score where id>34"
cursor.execute(sql)
database.commit()
database.close()

#====查询====
#格式:"select 字段 from 表名 where 条件"
"""
sql = "select name,lilun,jineng from score where id>30"
cursor.execute(sql)
result = cursor.fetchall()
print(result)
"""

===========2.将数据库中的数据拷贝到excel文件中========

import xlwt,xlrd
from xlutils.copy import copy
import pymysql
database = pymysql.connect('127.0.0.1','root','root','rk18',port=3307,charset='utf8')
cursor = database.cursor()

sql = 'select name,count(name),sum(lilun),sum(jineng) from score group by name'
cursor.execute(sql)
result = cursor.fetchall()
#print(result)
for i in result:
    if i[0] == '李晓奎':
        a_name = '李晓奎'
        a_num = i[1]
        a_lilun = i[2]
        a_jineng = i[3]
    elif i[0] == '曾晨':
        b_name = '曾晨'
        b_num = i[1]
        b_lilun = i[2]
        b_jineng = i[3]
# 新创建一个文档薄
#new_workbook = xlwt.Workbook()
#work_sheet = new_workbook.add_sheet('目录')
#复制文件簿
tem_excel = xlrd.open_workbook('d:/test1.xls',formatting_info=True)
#tem_sheet = tem_excel.sheet_by_index(0)
new_workbook = copy(tem_excel)
work_sheet = new_workbook.get_sheet(0)
style = xlwt.XFStyle()

font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
font.height = 360
style.font = font

borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment
work_sheet.write(1,0,a_name,style)
work_sheet.write(1,1,a_num,style)
work_sheet.write(1,2,a_lilun,style)
work_sheet.write(1,3,a_jineng,style)
work_sheet.write(2,0,b_name,style)
work_sheet.write(2,1,b_num,style)
work_sheet.write(2,2,b_lilun,style)
work_sheet.write(2,3,b_jineng,style)
new_workbook.save('d:/test4.xls')