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)