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

待我称王封你为后i 2022-04-16 06:30 250阅读 0赞

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.csdn.net/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处理