python连接mysql的相关操作（代码）

最新推荐文章于 2024-01-16 07:00:00 发布

zwhj12345

最新推荐文章于 2024-01-16 07:00:00 发布

阅读量116

点赞数

文章标签： python mysql 数据库

本文链接：https://blog.csdn.net/zwhj12345/article/details/130452533

版权

该代码定义了一个名为Mysqlutils的类，用于处理与MySQL数据库的连接和交互。类中包含了初始化配置、建立连接、关闭连接、执行查询SQL、执行非查询SQL、批量执行SQL等方法。异常处理通过自定义Throwexception类进行。示例展示了如何插入数据。

摘要由CSDN通过智能技术生成

#!/usr/bin/python
# -*- coding:utf-8 -*-
import datetime

import pymysql


# @Version : 1.0
# @Time    : 2023/4/30 15:09
# @Author  : Ethan
# @File    : Mysqlutils.py
# @Software: PyCharm

class Throwexception(Exception):
    pass


class Mysqlutils(object):

    def __init__(self, application, env_type):
        self.application = application
        self.cnd = {}
        self.env = env_type
        self.isError = None
        self.msg = None

    def init_conf(self):
        try:
            with open(self.application, 'r', encoding='utf-8') as LINES:
                for LINE in LINES:
                    if LINE.find("=") > -1 and LINE.find("#") < 0:
                        item = LINE.strip().strip('\t').strip('\n').split("=")
                        self.cnd.setdefault(item[0].strip(), item[1].strip())
                    else:
                        continue

        except FileNotFoundError as e:
            raise Throwexception(e)

    def init_conn(self):
        data_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        self.init_conf()
        try:
            conn = \
                pymysql.connect(host=self.cnd.get('MYSQL-U-HOST-{}'.format(self.env))
                                     or exit("MYSQL-U-HOST-{} 失效!".format(self.env)),
                                port=int(self.cnd.get('MYSQL-U-PORT-{}'.format(self.env)))
                                     or exit('MYSQL-U-PORT-{} 失效!'.format(self.env)),
                                user=self.cnd.get('MYSQL-U-USER-{}'.format(self.env))
                                     or exit('MYSQL-U-USER-{} 失效!'.format(self.env)),
                                passwd=self.cnd.get('MYSQL-U-PASSWD-{}'.format(self.env))
                                       or exit('MYSQL-U-PASSWD-{} 失效!'.format(self.env)),
                                database=self.cnd.get('MYSQL-U-DATABASE-{}'.format(self.env))
                                        or exit('MYSQL-U-DATABASE-{} 失效!'.format(self.env)))
            # 设置编码
            cur = conn.cursor()
            cur.execute('SET NAMES utf8;')
            cur.execute('SET character_set_connection=utf8;')

            print('[{}][INFO]: INIT MYSQL CONNECTION SUCCESS'.format(data_now))
            return [conn, cur]
        except ConnectionError as e:
            print('[{}][INFO]: INIT MYSQL CONNECTION FAILED'.format(data_now))
            raise Throwexception(e)

    @staticmethod
    def close_conn(r):
        if r[1]:
            r[1].close()
        if r[0]:
            r[0].close()

    def execute_query_sql(self, sql):
        data_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        try:
            if self.isError:
                return {'msg': self.msg, 'data': None, 'count': 0}
            r = self.init_conn()
            r[0].autocommit(True)
            print('[{}][INFO]: START EXECUTE SQL:{}'.format(data_now, sql))
            sql_start = datetime.datetime.now().timestamp()
            r[1].execute(sql)
            sql_end = datetime.datetime.now().timestamp()
            rcount = r[1].rowcount
            row = r[1].fetchall()
            self.close_conn(r)
            print('[{}][INFO]: EXECUTE SQL FINISHED, Number of affected rows: {}S, execution time: {}'
                  .format(data_now, rcount, sql_end - sql_start))
            return {'msg': 'SUCCEED', 'data': row, 'count': rcount}
        except Exception as e:
            print('[{}][ERROR]: EXECUTE SQL FIELD, PLEASE CHECK YOUR SQL OR OTHER'.format(data_now))
            raise Throwexception(e)

    def execute_sql(self, sql):
        data_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        try:
            if self.isError:
                return {'msg': self.msg, 'data': None, 'count': 0}
            r = self.init_conn()
            r[0].autocommit(True)
            print('[{}][INFO]: START EXECUTE SQL:{}'.format(data_now, sql))
            sql_start = datetime.datetime.now().timestamp()
            r[1].execute(sql)
            sql_end = datetime.datetime.now().timestamp()
            rcount = r[1].rowcount
            self.close_conn(r)
            print('[{}][INFO]: EXECUTE SQL FINISHED, Number of affected rows: {}s, execution time: {}S'
                  .format(data_now, rcount, sql_end - sql_start))
            return {'msg': 'SUCCEED', 'data': ' ', 'count': rcount}

        except Exception as e:
            print('[%s][ERROR]: EXECUTE SQL FIELD, PLEASE CHECK YOUR SQL OR OTHER' % data_now)
            raise Throwexception(e)

    def execute_many_sql(self, sqlps):
        data_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        if len(sqlps) > 0:
            sqm = str(sqlps).strip().strip('\t').strip('\n').rstrip(';').split(';')
            if len(sqm) > 1:
                try:
                    if self.isError:
                        return {'msg': self.msg, 'data': None, 'count': 0}
                    r = self.init_conn()
                    print('[{}][INFO]: START EXECUTE BATCH SQL: (NUM SQL:{})'.format(data_now, len(sqm)))
                    batch_sql_start = datetime.datetime.now().timestamp()
                    # 提供计数
                    row_index = 1
                    rows = []
                    start_row = 0
                    t_index = 1
                    for index in range(len(sqm)):
                        try:
                            print('[{}][INFO]: START EXECUTE {} SQL: {}'.format(data_now, row_index, sqm[index]))
                            sql_start = datetime.datetime.now().timestamp()
                            r[1].execute(sqm[index])
                            sql_end = datetime.datetime.now().timestamp()
                            rcount = r[1].rowcount
                            print('[{}][INFO]: EXECUTE SQL FINISHED, Number of affected rows: {}, execution time: {}S'
                                  .format(data_now, rcount, sql_end - sql_start))
                            rows.append({'msg': 'SUCCEED', 'data': ' ', 'count': rcount})
                            if row_index >= 100 * t_index:
                                t_index += 1
                                end_row = row_index
                                r[0].commit()
                                self.close_conn(r)
                                r = self.init_conn()
                                print("[{}][INFO]: 批量执行SQL,第{}个到第{}个提交完成!".format(data_now, start_row, end_row))
                                start_row = end_row
                            else:
                                row_index += 1
                        except Exception as e:
                            raise Throwexception(e)
                    batch_sql_end = datetime.datetime.now().timestamp()
                    # 批量执行完再提交
                    r[0].commit()
                    self.close_conn(r)
                    print("[{}][INFO]: 批量执行SQL,第{}个到第{}个提交完成!".format(data_now, start_row, len(sqm)))
                    print('[{}][INFO]: EXECUTE BATCH SQL FINISHED,execution time: {}S'.format(
                        data_now, (batch_sql_end - batch_sql_start)))
                    return rows
                except Exception as e:
                    raise Throwexception(e)
            else:
                self.execute_sql(sqm[0])
        else:
            print("[{}][INFO]: SQL IS NULL! PEALACE CHECK YOUR SQL".format(data_now))

    def execute_sql_batch(self, sql, datalist):
        data_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        try:
            if self.isError:
                return {'msg': self.msg, 'data': None, 'count': 0}
            r = self.init_conn()
            start_time = datetime.datetime.now().timestamp()
            r[1].executemany(sql, datalist)
            rcount = r[1].rowcount
            end_time = datetime.datetime.now().timestamp()
            print('[{}][INFO]: EXECUTE SQL FINISHED, Number of affected rows: {}, execution time: {}S'
                  .format(data_now, rcount, end_time - start_time))
            return {'msg': 'SUCCEED', 'data': ' ', 'count': rcount}
        except Exception as e:
            raise Throwexception(e)

    def excute_sql_txt(self, execute_type, file):

# 测试
if __name__ == '__main__':
    mut = Mysqlutils("D:\workspace\python\com.wz\\test", '100')
    sql = []
    for index in range(210):
        sql.append("insert into test values({},'xiaohu{}','qqxiaoxue');".format(index, index))

    print(str(sql).replace('[', '').replace(']', '').replace(';",', '";'))
    mut.execute_many_sql(str(sql).replace('[', '').replace(']', '').replace(';",', ';').replace('"',''))