creat a list from sql query output with the attribute name instead of the index using python

Question

I have this code.

cursor.execute("select id, name from client")
clientids= cursor.fetchall()
clientidList = []
for clientid in clientids:
    #I can do that
    clientidList.append(clientid [0])
    #but I can't do that.
    clientidList.append(clientid ['id'])

Whit the second try I get an error TypeError: 'tuple' object is not callable
any idea why this is not possible? is there is any other way to accomplish that, because it's more comprehensive when I put the attribute name than the index, exactly in a query that has more than 20 columns output. I tried this but it didn't work for me

Thanks!

score 1 · Answer 1 · 2013-03-24T22:44:57.047

1

Try this:

import mysql.connector

db_config = {
    'user': 'root',
    'password': 'root',
    'port' : '8889',
    'host': '127.0.0.1',
    'database': 'clients_db'
}
cnx = {} # Connection placeholder

cnx = mysql.connector.connect(**db_config)

cur = cnx.cursor()
cur.execute('SELECT id FROM client')

columns = cur.column_names

clientids = []

for (entry) in cur:
    count = 0
    buffer = {}

    for row in entry:
        buffer[columns[count]] = row
        count += 1

    clientids.append(buffer)


cur.close()

clientidList = []

for client in clientids:
   clientidList.append(client['id'])

pprint.pprint(clientids)
pprint.pprint(clientidList)

Update

Updated the code to select row names too. Not foolproof I guess. Test it some :)

edited Mar 24 '13 at 22:44

answered Mar 24 '13 at 22:00

this works perfect if we have 1 output! How about if we have 2 outputs from a sql query? Thanks a lot Allendar!!!! – mongotop Mar 24 '13 at 22:05
1

I've updated the answer. It gets all output. If you still get one row, than you have used either LIMIT on the query or reading the output wrong. If you paste this code clean into a new file (without any interference) it should work. Change `clients_db` to your Database name. – Mar 24 '13 at 22:11
yes it works!! maybe my question was how can I change the index "0" to "id" reference to the index with the column name. – mongotop Mar 24 '13 at 22:15
1

I've updated the answer again. It should pick row names too now. – Mar 24 '13 at 22:45

score 1 · Accepted Answer · answered Mar 24 '13 at 22:44

1

after 35 minutes of reshearch, I found this post: and the solution was to add this line to change the indices to columns names using the description built in function.

name_to_index = dict( (d[0], i) for i, d in enumerate(cursor.description) )

and all what I have to do after is to call the new function like :

clientidList = []
for clientid in clientids:
    clientidList.append(clientid[name_to_index['id']])

answered Mar 24 '13 at 22:44

mongotop

7,114
14
51
76

1

Cool; that one shows all row properties :). I just found `cursor.column_names`, which practically just return the row name (`cursor.description[n]`). Nice example! – Mar 24 '13 at 22:48

creat a list from sql query output with the attribute name instead of the index using python

2 Answers2