pymysql返回字典对象

最新推荐文章于 2024-07-15 12:05:45 发布

moxiao1995071310

最新推荐文章于 2024-07-15 12:05:45 发布

阅读量2.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：问题解决文章标签： Python MySQL dict

本文链接：https://blog.youkuaiyun.com/moxiao1995071310/article/details/89069241

问题解决专栏收录该内容

29 篇文章

订阅专栏

本文介绍了一个使用 Python 和 pymysql 库进行 MySQL 数据库操作的方法。包括连接数据库、插入单条及多条记录、按条件查询、执行 SQL 语句删除及更新数据等基本功能，并采用线程锁来保证多线程环境下的数据安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import pymysql
import threading

MYSQL_HOST = '192.168.1.248'
MYSQL_USER = 'moxiao'
MYSQL_PASSWORD = 'xxxx'
MYSQL_PORT = 3306
DATABASE = 'xxxx'
lock = threading.Lock()


class MysqlConn:
    def __init__(self, host=MYSQL_HOST, username=MYSQL_USER, password=MYSQL_PASSWORD, port=MYSQL_PORT,
                 database=DATABASE):
        """
        mysql 初始化
        :param host:
        :param username:
        :param password:
        :param port:
        """
        try:
            self.db = pymysql.Connection(host=host, user=username, password=password,
                                         database=database, port=port)
            self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor) # TODO 最主要的一行。。。
        except pymysql.MySQLError as e:
            print(e.args)

    def insert(self, table, data):
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'insert into %s (%s) values (%s)' % (table, keys, values)
        try:
            self.cursor.execute(sql, tuple(data.values()))
            self.db.commit()
        except pymysql.MySQLError as e:
            print(e.args)
            self.db.rollback()

    def insert_many(self, table, data):
        if isinstance(data, list) or isinstance(data, tuple):
            keys = str(tuple(data[0].keys())).replace('(', '').replace(')', '').replace("'", '')
            sql = 'insert into %s (%s) value' % (table, keys) + ' (' + '%s, ' * (len(data[0].keys()) - 1) + '%s);'
            values = [tuple(d.values()) for d in data]
            if lock.acquire():
                try:
                    self.cursor.executemany(sql, values)
                    self.db.commit()
                except pymysql.MySQLError as e:
                    print(e.args)
                    self.db.rollback()
                finally:
                    lock.release()

    def find_key_val(self, table, key_val):
        sql = None
        if isinstance(key_val, str):
            sql = 'SELECT * FROM `%s` WHERE %s ;' % (table, key_val)
        elif isinstance(key_val, dict):
            sql_where = ''
            for item in key_val.items():
                row_str = str(item[0]) + '=' + str(item[1]) + ' and '
                sql_where += row_str
            sql_where = sql_where[:-4]
            sql = 'SELECT * FROM `%s` WHERE %s ;' % (table, sql_where)
        if sql:
            try:
                self.cursor.execute(sql)
                data = self.cursor.fetchall()
                if len(data) > 0:
                    return data
                return None
            except pymysql.MySQLError as e:
                print(e.args)
        return None

    def find_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                data = self.cursor.fetchall()
                if len(data) > 0:
                    return data
                return None
            except pymysql.MySQLError as e:
                print(e.args)
        return None

    def del_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                self.db.commit()
                return True
            except pymysql.MySQLError as e:
                print(e.args)
                self.db.rollback()
        return None

    def update_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                self.db.commit()
                return True
            except pymysql.MySQLError as e:
                print(e.args)
                self.db.rollback()
        return None

    def close(self):
        try:
            self.cursor.close()
        except:
            pass
        try:
            self.db.close()
        except:
            pass


if __name__ == '__main__':
    mc = MysqlConn()
    result = mc.find_by_sql('select * from tst;')
    print(result)

感谢 https://www.cnblogs.com/xibuhaohao/p/9996571.html