CODEWITHSUNDEEP
pythonmysql
Kearney Taaffe
Kearney Taaffe

Reputation: 695

Python MySQL mysql.connector use column_names property to build a return

I'm using the MySQL connector provided by MySQL in a Python script. I can successfully query data, however, building a return is causing some problems for me.

Here's a basic database table

CREATE TABLE users(
uuid char(64) not null primary key,
firstname varchar(32) not null,
lastname varchar(32) not null,
email varchar(64) not null,
user_name varchar(32) not null,
password varchar(64) not null
);

INSERT INTO users VALUES
('7503637e-b8a7-44c2-822b-69ccb8ebfae0', 'Andy' , 'Apple'  , '[email protected]'  , 'aaple'  , 'abc123')

Here's a simple Python script

import mysql.connector

config = {
    'host' : '127.0.0.1',
    'user': 'root',
    'password' : 'password',
    'database' : 'database'
}

fields = ('uuid', 'firstname', 'lastname')
query = 'SELECT ' + ', '.join(fields) + ' FROM users'

cnx = mysql.connector.connect(**mysqlConfig)
cursor = cnx.cursor()
cursor.execute(query)

results = {}
for (uuid, firstname, lastname) in cursor:
    results[uuid] = {
        'first_name': firstname,
        'last_name' : lastname
    }

cursor.close()
cnx.close()

While this works, I'm trying to figure out a way to dynamically build the result variable. I don't want to have to add a new field name both to the fields variable as well as the tuple in the for loop.

I've been trying figure out a way to accomplish this using this code

columnNames = cursor.column_names
# columnNames = (u'uuid', u'fname', u'lname')

However, cursor.column_names returns a tuple of strings.

How can build a result dictionary containing the values found in the "fields" variable without having to add the new column name in 2 separate places?

Upvotes: 2

Views: 562

Answers (1)

Kearney Taaffe
Kearney Taaffe

Reputation: 695

So, after many hours, I finally found the answer through straight trial and error. Hopefully this will help someone

import mysql.connector

config = {
    'host' : '127.0.0.1',
    'user': 'root',
    'password' : 'password',
    'database' : 'database'
}

fields = ('uuid', 'firstname', 'lastname')
query = 'SELECT ' + ', '.join(fields) + ' FROM users'

cnx = mysql.connector.connect(**mysqlConfig)
cursor = cnx.cursor()
cursor.execute(query)

results = []

row = self.cursor.fetchone()
while row is not None:
    recordData = dict(zip(self.cursor.column_names, row))
    results.append(recordData)
    row = self.cursor.fetchone()


cursor.close()
cnx.close()

This will return a list of dictionaries containing the data. If you want to accomplish this via SQLite, use the following code in the while loop

resultSet = []
rowPosition = 0

row = self.cursor.fetchone()

while row is not None:
    rowDictionary = {}

    for columnName in row.keys():
        rowDictionary[columnName] = row[rowPosition]
        rowPosition += 1

    rowPosition = 0
    resultSet.append(rowDictionary)
    row = self.cursor.fetchone()

Upvotes: 2

Related Questions