python使用pymysql操作mysql数据库

操作之前：需要安装pymysql，这里使用的是pip工具，py3的话是有pip工具的，执行下面这条命令安装pymysql。

pip install pymysql

在命令行python交互页面使用import pymysql检查是否安装成功。

脚本测试的环境：

1. Python版本：3.7.3
2. Mysql版本：5.5.62
3. IDE：PyCharm

github地址：https://github.com/kxianghui/python-mysql

代码见下：

mysql操作类：

#!/usr/bin/python3
# -*-coding:utf-8-*-

# author:天空城
# date:2019年6月29日

import pymysql


class MysqlOperation:
    """
    mysql具体操作类 use pymysql
    """
    def __init__(self, url, username, password, database_name):
        self.url = url
        self.username = username
        self.password = password
        self.database_name = database_name
        self.__connect()

    def __connect(self):
        """
        私有连接方法，
        :return:
        """
        self.__connection = pymysql.connect(self.url, self.username, self.password, self.database_name)
        self.__cursor = self.__connection.cursor()

    def close(self):
        """
        关闭连接
        :return:
        """
        self.__cursor.close()
        self.__connection.close()

    def execute_sql(self, sql):
        """
        insert,update,delete
        无参
        :param sql: 待执行的sql
        :return: None
        """
        try:
            self.__cursor.execute(sql)
            self.__connection.commit()
        except Exception as e:
            self.__connection.rollback()
            print("执行MySQL: %s 时出错：%s" % (sql, e))

    def execute_sql_args(self, sql, args, many_flag=False):
        """
        insert,update,delete 有参数，使用占位符形式
        [insert|update|delete]
        where param1 = %s and param2 = %s
        :param sql: 占位符sql
        :param args: 参数，形式为一个tuple或list
        :param many_flag: 是否批量执行的
        :return: None
        """
        try:
            if not args:
                return
            if many_flag:
                self.__cursor.executemany(sql, args)
            else:
                self.__cursor.execute(sql, args)
            self.__connection.commit()
        except Exception as e:
            self.__connection.rollback()
            print("执行MySQL: %s 时出错：%s" % (sql, e))

    def select(self, sql, args=None, all_flag=True):
        """
        查询方法，
        :param sql: 待执行的sql
        :param args: 参数列表 [tuple|list]
        :param all_flag: 返回一条或全部
        :return: list 形如[{},{},{}]每一个字典是一行数据
        """
        if not args:
            self.__cursor.execute(sql, args)
        else:
            self.__cursor.execute(sql)
        return self.__cursor.fetchall() if all_flag else self.__cursor.fetchone()

测试类：

注：这个是解决一个问题的一个实例，但是包含操作，所以没有做专门的测试类。

#!/usr/bin/python3
# -*-coding:utf-8-*-

# author:天空城
# date:2019年6月29日
# 将time列按固定格式更新

import datetime
import traceback
from common import mysql_operation

# 数据库连接常量
DB_URL = "localhost"
DB_USERNAME = "root"
DB_PASSWORD = "root"
DB_NAME = "testdb"


# 日期处理逻辑
def get_date(diff_day, curr_date=datetime.datetime.now()):
    """
        function_name: get_date
        function_params:
        diff_day 与curr_date相差时间，往前推算取负值
        curr_date 传入的当前时间，默认为当前时间
        return:返回一个与curr_date相差diff_day的日期
    """
    # date = datetime.datetime(1971, 1, 1, 0, 0, 40)
    result_date = None
    try:
        time_diff_day = datetime.timedelta(days=-diff_day)
        # %Y-%m-%d %H:%M:%S
        result_date = (curr_date + time_diff_day).strftime("%Y-%m-%d 06:15:00")
    except:
        traceback.print_exc()
    return result_date


# 具体情况具体编写
# 存储为{"kpi_name":[],"kpi_name2":[]...}，然后遍历存time_spot
def create_new_dict(result_set):
    data_dict = {}
    for row in result_set:
        key = row[0]
        kpi_name = row[1]
        if kpi_name not in data_dict:
            # 不包含，则创建存储list
            temp_list = [key, kpi_name]
            data_list = [temp_list]
            data_dict[kpi_name] = data_list
        else:
            data_list = data_dict[kpi_name]
            temp_list = [key, kpi_name]
            data_list.append(temp_list)
    return data_dict


def update_time(data_dict):
    execute_many_list = []
    for kpi_name in data_dict:
        data_list = data_dict[kpi_name]
        # 遍历处理data_list中所有数据的time_spot
        for i in range(len(data_list)):
            time_spot = get_date(i)
            data_list[i].append(time_spot)
            # 适配批量，更换0,2，去除1
            temp_value = data_list[i][0]
            data_list[i][0] = data_list[i][2]
            data_list[i][2] = temp_value
            data_list[i].remove(data_list[i][1])
            # transfer tuple
            temp_tuple = tuple(data_list[i])
            data_list[i] = temp_tuple
        execute_many_list.extend(data_list)
    return execute_many_list


if __name__ == "__main__":
    mysql_operation = mysql_operation.MysqlOperation("localhost", "root", "root", "testdb")
    sql = "select id,kpi_name,time_spot from t_rtm_kpi_value order by id desc"
    result = mysql_operation.select(sql)
    data_dict = create_new_dict(result)
    execute_many_list = update_time(data_dict)
    update_sql = "update t_rtm_kpi_value set time_spot = %s where id = %s"
    mysql_operation.execute_sql_args(update_sql, execute_many_list, True)
    mysql_operation.close()