【Python】pymysql模块处理Mysql数据库

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
在使用 PyMySQL 之前，我们需要确保 PyMySQL 已安装,可以直接pip安装：pip install pymysql
本文主要介绍了pymysql连接数据库；删除、创建表；将数据存储到数据库；查询数据库数据输出到excel

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.youkuaiyun.com/sinat_37967865
#文件：pymysqlModel.py
#日期：2018-10-22
#备注：pip install pymysql  pymysql是Python中操作MySQL的模块  
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import pymysql
import time
import xlrd
from xlutils.copy import copy
import os

start_time = time.time()  #计算程序运行时间

db = pymysql.connect(
    host="localhost",
    user="root",
    password="123456",
    port=3306,
    use_unicode=True,
    charset="utf8",
    database="sunshine")

cursor = db.cursor()

def createTable():
    sql1 = 'drop table if exists save_amount;'
    sql2 = 'create table save_amount(No INT(11) NOT NULL AUTO_INCREMENT,billNo VARCHAR(50),amount FLOAT DEFAULT 0.0,PRIMARY KEY (No));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()


def get_excel_data(xlsFile):
    data = xlrd.open_workbook(xlsFile)
    table = data.sheets()[0]
    nrows = table.nrows
    sqlData = []
    # 获取指定（第二B）列数据
    for i in range(1, nrows):
        billNo = table.cell_value(i, 1)
        try:
            billNo = int(billNo)
            billNo = str(billNo)

        except Exception as f:
            print(str(f))

        row = []
        if billNo != '' and (billNo[0:3] == '718' or billNo[0:3] == '444' or billNo[0:3] == '639'):
            row.append(billNo)
            row.append(0.0)
        else:
            continue              # 不符合要求就跳过
        sqlData.append(row)
    return sqlData


def data_to_mysql(billNo,amount):
    sql = "insert into save_amount(billNo,amount) values('%s','%f')" % (billNo,amount)
    try:
        # 使用 cursor() 方法创建一个游标对象 cursor
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        print('事务处理成功')


# 将根据对账单Excel中数据库查找的信息copy到一个新的excel中(不能循环)
def mysql_to_excel(xlsFile,newFile):
    a = 1
    data = xlrd.open_workbook(xlsFile, formatting_info=True)
    table = data.sheets()[0]
    nrows = table.nrows
    new_excel = copy(data)
    ws = new_excel.get_sheet(0)

    for i in range(1, nrows):
        billNo = table.cell_value(i, 1)
        cursor.execute('select billNo,amount from save_amount where billNo=%s', (billNo))
        results = cursor.fetchall()  # 获取多条数据

        # row_1 = cursor.fetchone()   # 获取一条数据
        for i in range(len(results)):
            amount = results[i][1]
            print(a+1,billNo,amount)
            ws.write(a, 11, billNo)    # 12列L
            ws.write(a, 12, amount)    # 13列M
            a = a + 1
    new_excel.save(newFile)

''''''''
def main():
    createTable()      # 创建一个空白数据库表
    sqlData = get_excel_data('F:\PythonProject\PythonLession\RecordModule\oct.xls')  # 获取对账单里面不需要的运单
    for i in range(len(sqlData)):
        billNo = sqlData[i][0]
        amount = sqlData[i][1]
        data_to_mysql(billNo, amount)

    mysql_to_excel('F:\PythonProject\PythonLession\RecordModule\oct.xls', 'F:\PythonProject\PythonLession\RecordModule\oct1.xls')

    db.close()
''''''''''''''
if __name__ == '__main__':
    main()
    endtime = time.time() - start_time
    print('程序运行了%.2f秒' % endtime)

1.当我们需要传参进入到sql语句时，使用方法如下：
sql = "insert into save_amount(billNo,amount) values('%s','%f')" % (billNo,amount)
%s表示字段为str类型，%f表示字段为float类型，传参表达式：% (billNo,amount)
2.有时我们希望对原有的excel进行处理，可以复制一份excel在进行处理
from xlutils.copy import copy
data = xlrd.open_workbook(xlsFile, formatting_info=True)  # 打开需要修改的excel
new_excel = copy(data)                                    # 复制信息到新excel
ws = new_excel.get_sheet(0)                           # 获取第一个sheet,到时候对这个sheet处理