How To Retrieve Sql Result Column Value Using Column Name In Python?

Is there a way to retrieve SQL result column value using column name instead of column index in Python? I'm using Python 3 with mySQL. The syntax I'm looking for is pretty much lik

Solution 1:

The MySQLdb module has a DictCursor:

Use it like this (taken from Writing MySQL Scripts with Python DB-API):

cursor= conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute("SELECT name, category FROM animal")
result_set = cursor.fetchall()
forrowin result_set:
    print "%s, %s" % (row["name"], row["category"])

edit: According to user1305650 this works for pymysql as well.

Solution 2:

This post is old but may come up via searching.

Now you can use mysql.connector to retrive a dictionary as shown here: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html

Here is the example on the mysql site:

cnx = mysql.connector.connect(database='world')
cursor= cnx.cursor(dictionary=True)
cursor.execute("SELECT * FROM country WHERE Continent = 'Europe'")

print("Countries in Europe:")
forrowincursor:
    print("* {Name}".format(Name=row['Name']))

Solution 3:

you must look for something called " dictionary in cursor "

i'm using mysql connector and i have to add this parameter to my cursor , so i can use my columns names instead of index's

db = mysql.connector.connect(
    host=db_info['mysql_host'],
    user=db_info['mysql_user'],
    passwd=db_info['mysql_password'],
    database=db_info['mysql_db'])

cur = db.cursor()

cur = db.cursor( buffered=True , dictionary=True)

Solution 4:

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","","gkdemo1")

# prepare a cursor object usingcursor() methodcursor= db.cursor()

# executeSQL query usingexecute() method.
cursor.execute("SELECT * from user")

# Get the fields name (only once!)
field_name = [field[0] for field in cursor.description]

# Fetch a single rowusing fetchone() method.
values= cursor.fetchone()

# create the row dictionary to be able tocallrow['login']
**row= dict(zip(field_name, values))**

# print the dictionary
print(row)

# print specific field
print(**row['login']**)

# print all field
for key inrow:
    print(**key," = ",row[key]**)

# close database connection
db.close()

Solution 5:

python 2.7

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='password', db='sakila')

cur = conn.cursor()

n = cur.execute('select * from actor')
c = cur.fetchall()

for i in c:
    print i[1]