Python操作MSSQL数据库

# !/usr/bin/env python3
# _*_ encoding: utf-8 _*_
# FileName ：mssqlhandle.py
# Time     ：2021-03-05 09:40
# ++++++++++++++++++++++++++++++++++++++++++++++++++
# 调用.sql文件 执行
# 使用方法 python3 mssqlhandle.py  ODS_SAP_EHR2_DC_HRP9350
# param： 表名称
# ++++++++++++++++++++++++++++++++++++++++++++++++++
import configparser
import datetime
import sys
import time
from functools import wraps

import pymssql

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 连接数据库"""
        with pymssql.connect(host='192.168.2.112', port=1433, user='SA', password='1234asdF'
                , database='master') as conn:
            with conn.cursor() as cursor:
                print(sqlCode)
                cursor.execute(sqlCode)
                # print(cursor.fetchone())

    def insert_sqlfile(self, table_name):
        sqlCode = "DELETE FROM dbo.auzre_task_control WHERE mpp_schema = '%s' AND run_date = '%s'; INSERT INTO dbo.auzre_task_control (mpp_schema, tablename, run_date, ods_schema, ods_tablename, data_load_start_time, data_load_end_time, data_load_status) VALUES('public', '%s', '%s', 'dbo', '%s', '', '', ''); " % (
            table_name, datetime.datetime.today().strftime('%Y-%m-%d'), table_name,
            datetime.datetime.today().strftime('%Y-%m-%d'), table_name)
        # print(sqlCode)
        self.execute_sql(sqlCode)


@echoRuntime
@decorator
def main():
    print('开始执行：%s' % (datetime.datetime.now()))
    table_name = sys.argv[1]
    obj = HandleDB()
    obj.insert_sqlfile(table_name)
    print('执行结束：%s' % (datetime.datetime.now()))


if __name__ == '__main__':
    print('Life Is Short! I Use Python!')
    main()
    # obj = HandleDB()
    # obj.insert_sqlfile('test_ods_db')