数据库连接-模板

一、数据库连接模块

# 获取配置文件信息
import os
from configparser import ConfigParser
from urllib import parse
import pymysql
import psycopg2
import pandas as pd
from sqlalchemy import create_engine
class ConfigUtil(object):
    def __init__(self,config_file):
        # 读取配置文件
        current_dir = os.getcwd()
        self.config_path = os.path.join(current_dir,config_file)
        self.config = ConfigParser()
        self.config.read(self.config_path,encoding='utf8')
    def getConfig(self,param1,param2):
        return  self.config.get(param1,param2)

class DBUtil(ConfigUtil):
    def __init__(self,cofile_file='config/dbconfig.ini'):
        # 读取继承类的初始化方法
        super().__init__(cofile_file)
        # 获取数据库配置信息
        self.host = self.config.get("MYSQL",'host')
        self.port = self.config.get("MYSQL", 'port')
        self.database = self.config.get("MYSQL", 'database')
        self.user = self.config.get("MYSQL", 'user')
        # 防止乱码
        self.password =  parse.quote(self.config.get("MYSQL", 'password'))
    def getConn(self,model):
        if model == 'mysql':
            # conn = pymysql.connect(host="localhost",port = 3306,database="Test_DB", user="root", password="123")
            conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'
                                  .format(self.user, self.password, self.host, self.port, self.database))
        elif model == 'pg':
            # import warnings
            # # 设置程序警告
            # warnings.filterwarnings("ignore")
            # option_str = "-c search_path=test_schame "
            # conn = psycopg2.connect(host="localhost", port=5432, database="Test_DB", user="postgres", password="123",
            #                         options=option_str)
            conn = create_engine(f'postgresql+psycopg2://{self.user}:{self.password}@{self.host}:{self.port}/postgres')
        else:
            raise Exception('当前没有配置该数据库')
        return conn

    def read_data(self,sql_str,model):
        conn = self.getConn(model)
        df = pd.read_sql(sql_str,conn)
        return df
    def write_data(self,df1,tablename,writemodel,model):
        conn = self.getConn(model)
        # df.to_sql(name ='test4' ,con = conn1, if_exists=  "append",index = False, index_label=['a','b','c','d'])
        df1.to_sql(name = tablename , con = conn,if_exists=  writemodel,index=False)

    def run_sql(self,sql_str,model):
        conn1 = self.getConn(model)
        conn = conn1.connect()
        conn.execute(sql_str)

二、调用方法

from UtilConfig import DBUtil
import time
dbutil = DBUtil()
df1 = dbutil.read_data('select * from test033','mysql')
print(df1)
dbutil.run_sql('delete from test033 where user_id >3','mysql')


dbutil.write_data(df1,'test033','append','mysql')