pymysql的应用

最新推荐文章于 2023-03-25 16:38:45 发布

hidespace

最新推荐文章于 2023-03-25 16:38:45 发布

阅读量110

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/navioo/article/details/118486752

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

该博客介绍了如何使用Python的Pymysql库进行数据库连接、查询、插入、更新和删除操作。首先通过pip安装pymysql，然后创建一个MysqlHelper类，实现了连接、游标管理、执行SQL语句等功能。示例中展示了选择数据库、插入数据以及查询数据的用法。

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

1.安装pymysql扩展

pip install pymysql

2.pymysql的基本应用

#!/usr/bin/env python3
# -*- coding: UTF-8 -*-


import pymysql


class MysqlHelper(object):

    db_conn: pymysql.connections.Connection = None

    db_cursor: pymysql.connections.Cursor = None

    def __init__(self, config=None):
        if config and type(config) is dict:
            self.connect(config)

    def get_handler(self, config=None):
        if not self.db_conn:
            self.connect(config)
        return self.db_conn

    def get_cursor(self):
        if not self.db_cursor:
            self.set_cursor()
        return self.db_cursor

    def set_cursor(self, is_dict=False):
        if is_dict:
            self.db_cursor = self.db_conn.cursor(pymysql.cursors.DictCursor)
        else:
            self.db_cursor = self.db_conn.cursor(pymysql.cursors.Cursor)

    def connect(self, config: dict) -> (pymysql.connections.Connection, str):
        try:
            db_conn = pymysql.connect(
                host=config.get('host'),
                port=config.get('port'),
                user=config.get('user'),
                passwd=config.get('pass'),
                database=config.get('database'),
                charset=config.get('charset', 'utf8')
            )
            self.db_conn = db_conn
            # return db, 'ok'
        except pymysql.Error as e:
            print(e)
            exit()
            # return None, str(e)

    def select_db(self, database_name: str):
        self.db_conn.select_db(database_name)

    def query_one(self, sql: str, binds=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, binds)
            return cursor.fetchone()
        except pymysql.Error as e:
            print(e)
        finally:
            cursor.close()

    def query_all(self, sql: str, binds=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, binds)
            return cursor.fetchall()
        except pymysql.Error as e:
            print(e)
        finally:
            cursor.close()

    def query_column(self, sql: str, binds=None):
        self.set_cursor(False)
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, binds)
            rows = cursor.fetchall()
            # result = []
            # for row in rows:
            #     result.append(row[0])
            return [row[0] for row in rows]
        except pymysql.Error as e:
            print(e)
        finally:
            cursor.close()

    def query_scalar(self, sql: str, binds=None):
        self.set_cursor(False)
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, binds)
            row = cursor.fetchone()
            return row[0] if row else row
        except pymysql.Error as e:
            print(e)
        finally:
            cursor.close()

    def execute_command(self, sql: str, binds=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, binds)
            self.db_conn.commit()
            return cursor.rowcount
        except pymysql.Error as e:
            self.db_conn.rollback()
            # print(e)
            return -1
        finally:
            cursor.close()


    def close(self):
        if self.db_cursor:
            self.db_cursor.close()
        if self.db_conn:
            self.db_conn.close()

    def __del__(self):
        self.close()


if __name__ == '__main__':
    db_config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'pass': 'root',
        'charset': 'utf8'
    }
    # 获取操作mysql对象实例
    db = MysqlHelper(db_config)
    # 选择数据库
    db.select_db('test')

    # 插入单条数据
    bind = ("jerry", "1", "1988-12-25", 18312345678)
    sql = "insert into tbl_user (name, gender, birth, mobile) values (%s, %s, %s, %s)"
    # 更新语句
    # sql = "update tbl_user set gender=%s where mobile=%s"
    # 删除语句
    # sql = "delete tbl_user where mobile=%s"
    
    # 插入多条数据
    # binds = [("", "", "", ""),("", "", "", "")]

    # 执行DDL、DML等
    affected_rows = db.execute_command(sql, bind)
    # 打印受影响的记录数
    # print(affected_rows)

    # 设置游标
    db.set_cursor(True)
    sql = "select name from tbl_user where id < %s"
    res = db.query_all(sql, 10)
    print(res)