使用光标.说明如何获取不同服务器上多个数据库的列名

Question

配置.json：

"Mysqlservers":{    
    "server1":{
        "host": "192.168.0.121",
        "username": "testuser",
        "passwordlocal": "123456",
        "port": "3306",
        "get_status_sql": "select * from Organization.error_log"
    },
    "server2":{
        "host": "192.168.0.107",
        "username": "testuser",
        "passwordlocal": "123456",
        "port": "3306",
        "get_status_sql": "select * from testdb.errorlog"
    }
}

代码：

def queryData:
    try:

        connections={
            'conn1':mysql.connector.connect(
            host=datastore["Mysqlservers"]["server1"]["host"],
            user=datastore["Mysqlservers"]["server1"]["username"],
            passwd=datastore["Mysqlservers"]["server1"]["passwordlocal"]),

            'conn2':mysql.connector.connect(
            host=datastore["Mysqlservers"]["server2"]["host"],
            user=datastore["Mysqlservers"]["server2"]["username"],
            # passwd=datastore["Mysqlservers"]["server2"]["passwordlocal"]
            )
        }

        mycursor=[conn.cursor() for key, conn in connections.items()]
        print("-----------",mycursor)

        connections['conn1']._execute_query(datastore["Mysqlservers"]["server1"]["get_status_sql"])
        connections['conn2']._execute_query(datastore["Mysqlservers"]["server2"]["get_status_sql"])


        myresult = [conn.fetchall() for key, conn in enumerate(mycursor,0)]

        field_names = [i[0] for i in mycursor.description]

错误：

Traceback (most recent call last):
File "ETL-Error-Reporter_Test.py", line 140, in queryData
    field_names = [i[0] for i in mycursor.description]
AttributeError: 'list' object has no attribute 'description'

无法访问列名

Answer 1

mycursor=[conn.cursor() for key, conn in connections.items()]

您将mycursor定义为游标列表。所以mycursor是一个list而不是一个cursor对象。在这种情况下，列表没有description属性。您需要遍历您的游标并获取每个游标的列名。为server -> connection处理创建了servers字典。重构代码：

def queryData():
    servers = dict()

    for name, server in datastore["Mysqlservers"].items():
        conn = mysql.connector.connect(host = server["host"], user = server["username"], passwd = server["passwordlocal"])
        cursor = conn.cursor()
        servers[name] = {
          "connection" : conn,
          "cursor" : cursor
        }

    results = dict()
    fieldNames = dict()

    for name, server in servers.items():
        server["cursor"].execute(datastore["Mysqlservers"][name]["get_status_sql"])
        results[name] = server["cursor"].fetchall()
        fieldNames[name] = [i[0] for i in server["cursor"].description]