# --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()
