python能和wincc一起用_python通过ado连接wincc数据库

python通过ado连接wincc数据库

注意点：

1，python需要安装32位，64位测试没通过，具体原因不明

2，若使用Provider=WinCCOLEDBProvider.1需要安装WINCC连通性数据包

3，若直连SQL，使用Provider=SQLOLEDB.1需要安装msoledbsql_18.2.2.0_x64（微软官网有下载）

4，测试请先运行wincc

参考资料：

1，官方文档WinCC_ConnectivityPack_zh-CHS_zh-CHS

2，ADO基础

程序

import win32com.client

import traceback

import time

# 获取运行时的数据库和计算机名称

def getDatasourceNameRT():

runTime = win32com.client.Dispatch(r'CCHMIRuntime.HMIRuntime')

catalog = runTime.Tags("@DatasourceNameRT").Read() # 获取数据库名称

# dataSource = runTime.Tags("@LocalMachineName").Read() # 获取计算机名称

return catalog

# 获取变量记录的变量名

def getArchiveTagNames():

tagNames = {}

try:

catalog = getDatasourceNameRT()

conn = win32com.client.Dispatch(r'ADODB.Connection')

# 因为数据库无账号密码（使用Windows NT集成安全设置），所以必须设定Integrated Security=SSPI; 连通性手册有说明

DSN = "Provider=SQLOLEDB.1;Data Source=37IK1BOAKVD0VE6WINCC;Initial Catalog={0};Integrated Security=SSPI;".format(

catalog)

# print(DSN)

conn.ConnectionString = DSN

conn.CursorLocation = 3

conn.Open()

oRs = win32com.client.Dispatch(r'ADODB.Recordset')

sSql = "SELECT [ValueID], [ValueName] FROM[{0}].[dbo].[Archive]".format(catalog)

# print(sSql)

oRs.Open(sSql, conn, 1, 1)

# print(oRs.RecordCount)

if not oRs.EOF:

oRs.MoveFirst()

while not oRs.EOF:

# for i in range(len(oRs.Fields)):

# print(oRs.Fields(i).Name, "=", oRs.Fields(i).Value)

tagNames[int(oRs.Fields(0).Value)] = oRs.Fields(1).Value

oRs.MoveNext()

oRs.Close()

conn.Close()

except:

traceback.print_exc()

finally:

return tagNames

# 获取变量记录的值

def getTagValue():

try:

# 获取数据库名

catalog = getDatasourceNameRT()

conn = win32com.client.Dispatch(r'ADODB.Connection')

DSN = "Provider=WinCCOLEDBProvider.1;Catalog={0};Data Source=.WinCC".format(catalog)

conn.ConnectionString = DSN

conn.CursorLocation = 3

conn.Open()

oRs = win32com.client.Dispatch(r'ADODB.Recordset')

# 获取变量名称

tagNames = getArchiveTagNames()

# 方法1：变量名称

# s = ["'%s'" % name for name in list(tagNames.values())]

# tags = ";".join(s)

# sSql = "TAG:R, ({0}), 2019-1-6 00:00:00.000,2019-1-7 00:00:00.000".format(tags)

# 方法2：变量ID

sSql = "TAG:R, ('9';'14'), '2019-1-6 00:00:00.000','2019-1-7 00:00:00.000'"

print(sSql)

t0 = time.time()

oRs.Open(sSql, conn)

print(oRs.RecordCount, oRs.PageSize, oRs.PageCount, oRs.CacheSize)

i = 0

if not oRs.EOF:

oRs.MoveFirst()

while not oRs.EOF:

# 方法1

a = oRs.GetRows(100000)

for j in range(len(a[1])):

print(i * 100000 + j, a[1][j], tagNames[a[0][j]], a[3][j])

i += 1

# 方法2

# print(oRs.GetString())

# 方法3

# for i in range(len(oRs.Fields)):

# print(oRs.Fields(i).Name, "=", oRs.Fields(i).Value)

# oRs.MoveNext()

except:

traceback.print_exc()

finally:

print(time.time() - t0)

if __name__ == '__main__':

getTagValue()

附件