Python - Extract Values from List of Dictionary (generated by SQL command)

Question

Good Morning,

I'm trying to working with a MySQL DB in Python and I need to extract some data like table names.

So I run this code:

import pymysql.cursors
conn = pymysql.connect(host='myHost', user='user', password='password', db='test', cursorclass=pymysql.cursors.DictCursor)
a = conn.cursor()

sql="SHOW tables like '%s%'"
a.execute(sql)
rows = a.fetchall()
tables = []
for row in rows:
    tables.append(row.values())

but when I run print(tables) it give me a result like:

 ["dict_values(['table1'])", "dict_values(['table2'])"]

The only list that I need is ['table1', 'table2', 'etc', 'etc']. How can I solve this? It's the SQL statement that cause these kind of problems?

Thank you

iCart · Accepted Answer · 2016-11-02 13:32:29Z

2

row.values() returns a dict_values object, which acts like a list.

Since you can assume that there's only one value in that list, you can replace this line:

tables.append(row.values())

With this:

tables += row.values()

And you should get the expected result.

answered Nov 2, 2016 at 13:32

iCart

2,3913 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anand Tripathi · Accepted Answer · 2016-11-02 14:06:05Z

1

You can go with this code the only thing ou have to keep in mind is this tables.append(row.values()[0]) syntax cause you using the values of dictionary

import pymysql.cursors
conn = pymysql.connect(host='myHost', user='user', password='password', db='test', cursorclass=pymysql.cursors.DictCursor)
a = conn.cursor()
sql="SHOW tables"
a.execute(sql)
tables = []
rows = a.fetchall()
for row in rows:
    tables.append(list(row.values())[0])

edited Nov 2, 2016 at 14:06

answered Nov 2, 2016 at 13:42

Anand Tripathi

16.4k2 gold badges52 silver badges57 bronze badges

2 Comments

andrepogg Over a year ago

It return: TypeError: 'dict_values' object does not support indexing

Anand Tripathi Over a year ago

In python 3 row.values() returns view instead of list so you can change the last line to this tables.append(list(row.values())[0]) @andrepogg

Collectives™ on Stack Overflow

Python - Extract Values from List of Dictionary (generated by SQL command)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related