Python 访问远程数据库，使用pandas库处理查询结构数据，保存为CSV格式文件。

Python 访问远程数据库，使用pandas库处理查询结构数据，保存为CSV格式文件。

import pymssql
import pandas as pd
import time
import sys
import os


class linkDB():
    def linkdb(hostIP, user, password, database):
        # 数据库远程连接
        # conn = pymssql.connect(host="172.17.72.113:1433", user="sa", password="Coslight123", database="QCJKGCK", charset="utf8")
        conn = pymssql.connect(host=hostIP + ":1433", user=user, password=password, database=database, charset="utf8")

        # 查询语句
        sql = "  select  P_BLOCK.Type as Type,P_BLOCK.TagName as BlockName,P_RTU.name as RTUName,P_IED.name as IEDName," \
              "P_BLOCK.Description " \
              "from P_RTU left join P_VOL on dbo.P_VOL.idRTU = P_RTU.id " \
              "left join P_BAY on dbo.P_BAY.idVOL = P_VOL.id " \
              "left join P_IED on dbo.P_IED.idBAY = P_BAY.id " \
              "left join P_BLOCK on P_BLOCK.idIED = P_IED.id  " \
              "where  (P_BLOCK.TagName is not NULL) order BY P_RTU.ID"
        try:
            print("连接数据库成功，开始生成数据，请等待……")

            df = pd.read_sql_query(sql, conn)
            # 返回列数：
            # print(df.shape[1])
            # 返回行数：
            # print(df.shape[0])
            # print(sys.path[0])
            df['DSC'] = df['RTUName'].str.cat(df['IEDName'].str.cat(df['Description']))
            df.to_csv('data_config.csv', index=False, columns=['BlockName', 'Type', 'DSC'], encoding='utf_8_sig')
            print(f"data_config.csv 文件创建成功！ 文件共存储:{df.shape[0]} 条数据")
            # 获取执行程序所在路径的三种方法：1.sys.path[0]/sys.argv[0] 2-os.getcwd() ；3- os.path.split(os.path.realpath(__file__))[0]
            print(f"data_config.csv 文件存储路径：{os.getcwd() }")
        except:
            print("连接数据库报错了！")
        # 关闭数据库连接
        conn.close()


if __name__ == '__main__':
    print('''eg: host = "172.17.72.113", user = "sa", password = "Coslight123", database = "QCJKGCK"''')
    hostIP = input("host = ")
    user = input("user = ")
    password = input("password = ")
    database = input("database = ")

    # hostIP = "172.17.72.113"
    # user = "sa"
    # password = "Coslight123"
    # database = "QCJKGCK "
    linkDB.linkdb(hostIP, user, password, database)

    time.sleep(15)