个人记录：连接、查询、写入PG数据库类文件

# --coding:utf-8--
import pandas as pd
import yaml
from sqlalchemy import create_engine
import psycopg2


class ControlPGSQL:
    def __init__(self, path):
        '''
        path:记录有pg数据库账号密码的yaml配置文件地址
        '''
        self.path = path

    def CreatePGengine(self):
        '''
        :return: PG链接
        '''
        with open(f"{self.path}", "r", encoding="utf-8") as config:
            cfg = yaml.safe_load(config)
            db_info = cfg['db_info']
        con_database = create_engine(
            'postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}'.format(**db_info))
        return con_database

    def ConPG(self):
        with open(f"{self.path}", "r", encoding="utf-8") as config:
            cfg = yaml.safe_load(config)
            db_info = cfg['db_info']
        conn = psycopg2.connect(database=db_info['database'],
                                user=db_info['user'],
                                password=db_info['password'],
                                host=db_info['host'],
                                port=db_info['port'],)
        return conn

    def read_sql(self, sql):
        '''
        读取postgresSQL数据库中的表
        :param sql: 数据库的链接指令
        :return:表dataframe
        '''
        con_database = self.CreatePGengine()
        # 从公司数据库读取 原始的入库 数据表
        databaseData = pd.read_sql(sql, con=con_database)
        return databaseData

    def run_read(self, mt_info):
        '''
        :param mt_info: sql的表名与字段，以及字段对应的重命名columns列表
        :return: 数据表
        '''
        sql = "SELECT {field} FROM {modename}.{tablename} where {condition}".format(
            **mt_info)
        table = self.read_sql(sql)
        table.columns = mt_info['columns']

        return table

    def write_sql(self, sql):
        '''
        写入postgresSQL数据库中
        :param sql: 数据库的链接指令
        :return:
        '''
        con_database = self.ConPG()
        cursor = con_database.cursor()
        cursor.execute(sql)

        # 提交SQL命令
        con_database.commit()