How to get fields by name in query Python?

Question

I use Mysql Connection in Python script.

How can I get results from table by names?

cursor = conn.cursor()
cursor.execute("SELECT * FROM local")

Now I do this by index:

results = cursor.fetchall()
for row in results:
   print row[0] //

Instead that I wanna get fields by names like as: print row["name"]

Check this http://stackoverflow.com/questions/10195139/how-to-retrieve-sql-result-column-value-using-column-name-in-python — Anoop, Nov 28 '16 at 08:58

score 11 · Answer 1 · edited May 16 '20 at 16:32

11

If you are using mysql-connector, Try this

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM myTable")
results = cursor.fetchall()
for row in results:
    print( row )

If dictionary is True, the cursor returns rows as dictionaries.

edited May 16 '20 at 16:32

mike rodent

14,126
11
103
157

answered Nov 28 '16 at 12:08

Anoop

2,748
4
18
27

You Sir deserve a medal. 2 hours of research on Internet, no solutions works except yours ! Fast, small, clean. Thanks so much. I upvoted you obviously. – Florian Doyen Aug 08 '18 at 20:05
I second the medal: Hero Pythonista First Class. – mike rodent May 16 '20 at 16:33

score 4 · Accepted Answer · answered Nov 28 '16 at 10:37

4

Instead you can use pandas library

import pandas as pd
sql = "SELECT * FROM local"
df = pd.read_sql(sql,conn)

for index,row in df.iterrows():
    print(row['name'])

I hope this helps

answered Nov 28 '16 at 10:37

Aakash Makwana

734
5
9

How to install `pandas` in Cent OS? – Goga Nov 28 '16 at 10:43
pip install pandas – Aakash Makwana Nov 30 '16 at 06:29

score -1 · Answer 3 · edited May 23 '17 at 12:24

-1

If you are using the MySQLdb for python you can transmit to your cursor instance a DictCursor.

Here is a full example using MySQLdb:

>>> import MySQLdb
>>> db = MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
>>> cursor = db.cursor(MySQLdb.cursors.DictCursor)
>>> cursor.execute("SELECT * FROM test_user")
2
>>> results = cursor.fetchall()
>>> for result in results:
...    print(result["username"])
test1
test2

Hope it's useful.

Also, you can find answers here suitable for your tuple situation. python mysql.connector DictCursor?

edited May 23 '17 at 12:24

Community

1
1

answered Nov 28 '16 at 09:25

Banuta Alin Alexandru

113
1
2
10

It does not work "TypeError: tuple indices must be integers, not str" – Goga Nov 28 '16 at 10:24
What module for mysql are you using ? – Banuta Alin Alexandru Nov 28 '16 at 11:13
`import mysql.connector` – Goga Nov 28 '16 at 11:42
For mysql connector, use the answer from @Anoop – Banuta Alin Alexandru Nov 28 '16 at 13:10

How to get fields by name in query Python?

3 Answers3

Linked

Related