Python操作PostgreSQL

最新推荐文章于 2023-07-01 21:44:16 发布

_Zephyrus_

最新推荐文章于 2023-07-01 21:44:16 发布

阅读量161

点赞数

分类专栏： PostgreSQL Python

本文链接：https://blog.csdn.net/wangxw1803/article/details/114383369

版权

Python 同时被 2 个专栏收录

48 篇文章 0 订阅

订阅专栏

PostgreSQL

8 篇文章 0 订阅

订阅专栏

Python连接PostgreSQL简单操作

依赖：psycopg2

# !/usr/bin/env python3
# _*_ encoding: utf-8 _*_
# Time     ：2021-02-25 10:12
# ++++++++++++++++++++++++++++++++++++++++++++++++++
# 调用.sql文件 执行
# 使用方法 python3 handleDB.py  
# param：文件名称
# ++++++++++++++++++++++++++++++++++++++++++++++++++
import configparser
import datetime
import os
import sys
import time
from functools import wraps

import psycopg2

workPath = "/data/etl/ETLAuto"


def decorator(function):
    @wraps(function)
    def inner(*args, **kwargs):
        try:
            print('当前运行：', function.__name__)
            return function(*args, **kwargs)
        except Exception as e:
            print(e.args[-1])
            sys.exit(-1)

    return inner


def echoRuntime(func):
    def wrapper(*args, **kwargs):
        startTime = time.time()
        func(*args, **kwargs)
        endTime = time.time()
        messcs = (endTime - startTime)
        print(func.__name__ + ' running time is : %.4f' % messcs)

    return wrapper


class HandleDB(object):
    """docstring for HandleDB"""

    def __init__(self, file_path=None):
        super(HandleDB, self).__init__()
        if file_path:
            configpath = file_path
        else:
            configpath = workPath + '/app/conf.ini'
            print(configpath)
        self.cf = configparser.ConfigParser()
        self.cf.read(configpath)

    def execute_sql(self, sqlCode):
        """:param 连接数据库"""
        host = self.cf.get('test', 'host')
        port = self.cf.get('test', 'port')
        user = self.cf.get('test', 'user')
        password = self.cf.get('test', 'password')
        database = self.cf.get('test', 'database')
        with psycopg2.connect(host=host, port=port, user=user, password=password, database=database) as conn:
            with conn.cursor() as cursor:
                cursor.execute(sqlCode)
                # print(cursor.fetchall())

    def get_tableName(self, tablename):
        tmp_ls = tablename.split('.')[0].split('_')
        return '_'.join(tmp_ls)

    def read_sqlfile(self):
        sqlpath = '%s/app/sql/' % workPath
        if (sys.argv[1].find('.dir') == -1):
            sqlfile = sys.argv[1]
        else:
            sqlfile = self.get_tableName(sys.argv[1])
        print(sqlpath)
        # sqlfile = sys.argv[1]
        if not os.path.exists(sqlpath):
            os.makedirs(sqlpath)
        else:
            pass
        with open(sqlpath + sqlfile + '.sql', 'r', encoding='utf-8', errors='ignore') as f:
            sqlCode = f.read()
            print('执行语句：', sqlCode)
            self.execute_sql(sqlCode)


@echoRuntime
@decorator
def main():
    print('开始执行：:%s' % (datetime.datetime.now()))
    obj = HandleDB()
    obj.read_sqlfile()
    print('执行结束：%s' % (datetime.datetime.now()))


if __name__ == '__main__':
    main()