Skip to content
Advertisement

Using cursor.description how to get column names of multiple database in different servers

config.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"
    }
}

code:

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]

Error:

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'

Not able to access the column names

Advertisement

Answer

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

You defined mycursor as a list of cursors. So mycursor is a list not a cursor object. In that case a list doesn’t have a description attribute. You need to iterate over your cursors and get column names for each one of your cursors. Created servers dictionary for server -> connection handling. Refactored code:

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]
Advertisement