pymysql和pandas组合灵活实现数据库操作

石头会说话20

已于 2023-04-11 12:56:54 修改

阅读量1k

点赞数 1

分类专栏： python 文章标签： python pandas 开发语言数据库

于 2023-04-10 23:19:15 首次发布

本文链接：https://blog.csdn.net/hpuexplorer/article/details/130072157

版权

python 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

背景说明

在做数据分析工作过程中，经常会有访问数据库和数据处理的场景。访问数据库通常可以使用python的数据包，如pymysql，cx_Oracle等；数据处理可以采用pandas来进行，但是在使用的时候，总是有不顺手的地方。比如：

pymysql、cx_oracle等返回的结果并不是DataFrame结构，还是需要自己转换。
pymysql、cx_oracle等在做数据库写入的时候，需要再写获取连接、游标等，操作比较繁琐。
pymysql、cx_oracle等需要再代码中编写数据库账号密码，这样会有不安全的情况。
pandas虽然能访问数据库，没办法做数据删除的操作。
pandas没有办法执行mysql的插入更新操作等。

基于此，根据自己的实际工作情况，编写属于自己的数据库访问的python库是比较好的。

代码说明

话不多说，先上代码为敬：

编写如下代码，命名为PySQL.py作为自己的库文件。以下代码实现的是MySQL库的访问，其他数据库需要更换导入的数据库的库文件。

import pymysql
import numpy as np
import pandas as pd

class DB1(object):
    def __init__(self) -> None:
        self.conn = pymysql.connect(user = "db_user",
            password = "db_passwd",
            port = 3306,
            host = 'xxx.xxx.xxx.xxx',
            database ="database1")
    
    def query_data(self, sql):
        cursor = self.conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        # pandas 版本过低语法
        # data = list(data)
        description = cursor.description
        fields = [item[0] for item in description]
        df_data = pd.DataFrame(data, columns=fields)
        return df_data
    
    def execute(self, sql, data=None):
        """
        可以执行INSERT和UPDATE
        建议采用防止sql注入的方式执行
        把数据字段放到参数里。
        """
        cursor = self.conn.cursor()
        if data is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, data)
        self.conn.commit()
    
    def insert_dataframe(self, table_name, df_data:pd.DataFrame, columns=None):
        # df_data.fillna("$$$$$$$$$$$$", inplace=True)
        # print(df_data)
        # print("****************")
        # df_data.replace("$$$$$$$$$$$$", "##########", inplace=True)
        # print(df_data)
        df_data = df_data.where(df_data.notnull(), None)
        if columns is None:
            columns = df_data.columns.values.tolist()
        sql_insert = """
            INSERT INTO {table_name} ({col_list}) VALUES({data_holder})
        """.format(table_name=table_name,
                   col_list=",".join(columns),
                   data_holder=",".join(["%s" for _ in columns])
                   )
        data = df_data.values.tolist()
        cursor = self.conn.cursor()
        cursor.executemany(sql_insert, data)
        cursor.close()
        self.conn.commit()
    
    def insert_list(self, table_name, columns, data:list):
        sql_insert = """
            INSERT INTO {table_name} ({col_list}) VALUES({data_holder})
        """.format(table_name=table_name,
                   col_list=",".join(columns),
                   data_holder=",".join(["%s" for _ in columns])
                   )
        cursor = self.conn.cursor()
        cursor.executemany(sql_insert, data)
        cursor.close()
        self.conn.commit()
    
    def close(self):
        return self.conn.close()
    
    def __del__(self):
        try:
            print("执行析构函数")
            self.conn.close()
        except:
            pass
        return None
    
class DB2(DB1):
    def __init__(self) -> None:
        super().__init__()
        self.conn = pymysql.connect(user = "db_user",
            password = "db_passwd",
            port = 3306,
            host = 'xxx.xxx.xxx.xxx',
            database ="database2")

在编写的类中，主要包含四个方法，分别如下：

【query_data】：这个方法主要是将读到的数据，转换为DataFrame，方便使用pandas进行数据处理操作。fetch_all返回的原本是二维的list结构，这里通过读取字段信息，将list转换为了DataFrame格式。
【execute】：这个可以执行正常的SQL语句，通过这层封装，其实是做了一个简易的SQL客户端。实际在应用的过程中，主要执行DELETE语句。
【insert_list】：能够将二位表的数据，插入到数据库中。因为list本身不包含字段信息，这里入参除了数据和插入表的表名外，还需要指定字段信息columns，并且，columns中的字段顺序和data里的数据的位置顺序一致。
【insert_dataframe】这个方法是将DataFrame结构插入到数据库当中。DataFrame本身包含列的信息，如果不指定columns，执行时，是按照DataFrame的列信息往数据库中插入数据，这个时候，要保证DataFrame的列信息和要写入的数据库表的列信息是一致的，或者是数据库表的子集。
其他说明：
1. 除了以上方法外，还有初始化方法和del方法。初始化方法为了构建数据库连接；del方法为了在程序正常退出或者异常退出时，关闭连接，不占用连接资源。
2. 当想再生成另一个库的的类时，可以直接继承编写，把初始化方法覆盖即可。
3. 我不建议在初始化方法里传入数据库密码来生成连接。一方面，使用时，特别是多人使用时，需要传数据库密码，不安全。另一方面，如果数据库密码有修改，在调用地方都得修改，这样不友好。

代码使用

使用时，可以直接导入PySQL包，然后实例化类，调用上述方法，执行数据操作。示例如下：

from PySQL import DB1, DB2
import pandas as pd
from datetime import datetime
import random
if __name__=="__main__":
    o = DB1()
    df_data = pd.read_excel("./file.xlsx",dtype="object")
    cols = [
        "field1", # field1
        "field2", # field2
        "field3", # field3
        "field4",  # field4
        "UPT_TIME" # 更新时间

    ]

    insert_cols = df_data.columns.tolist() + ["UPT_TIME"]
    df_data["UPT_TIME"] = datetime.now()
    df_data = df_data[insert_cols]
    o.execute("truncate table table_1")
    o.insert_dataframe(table_name="table_1",
                  df_data=df_data,
                  columns=cols)
    # print(df_data.columns)

当然，也可以直接当成SQL客户端工具用直接写SQL。如下SQL完成从表2中取两个字段，放入表1中。注意，as后面的名称要在表1中出现。

from PySQL import DB1, DB2
import pandas as pd
from datetime import datetime
import random
if __name__=="__main__":
    o = DB1()
    sql = """
    	select f1 as field1, f2 as field2 from table_2
    """
    df_data = o.query_data(sql)

    insert_cols = df_data.columns.tolist() + ["UPT_TIME"]
    df_data["UPT_TIME"] = datetime.now()
    df_data = df_data[insert_cols]
    o.execute("truncate table table_1")
    o.insert_dataframe(table_name="table_1",
                  df_data=df_data,
                  columns=cols)
    # print(df_data.columns)

补充说明

为了适应更多的场景，还可以通过添加更多逻辑，以适应更多的场景。比如：

insert方法里分多次插入，以提高插入效率，避免长时间锁表。
可以添加try…catch语句，这样可以自定义异常输出。

石头会说话20

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
pymysql和pandas组合灵活实现数据库操作

在做数据分析工作过程中，经常会有访问数据库和数据处理的场景。访问数据库通常可以使用python的数据包，如pymysql，cx_Oracle等；数据处理可以采用pandas来进行，但是在使用的时候，总是有不顺手的地方。基于此，根据自己的实际工作情况，编写属于自己的数据库访问的python库是比较好的。
复制链接

扫一扫