CODEWITHSUNDEEP
pythonmysql
Thomas
Thomas

Reputation: 846

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 like the Java construct:

Object id = rs.get("CUSTOMER_ID");

I've a table with quite a number of columns and it is a real pain to constantly work out the index for each column I need to access. Furthermore the index is making my code hard to read.

Thanks!

Upvotes: 68

Views: 241885

Answers (12)

Rishabh Sharma
Rishabh Sharma

Reputation: 1

I use this method and by this I can access all the columns by there name.

import mysql.connector

mydb_local = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='db_test'
)
my_cursor = mydb_local.cursor(buffered=True)


def get_data(email):
    query = f"select * from user where email = '{email}';"
    my_cursor.execute(query)
    columns = [column[0] for column in my_cursor.description]
    user_id = my_cursor.fetchall()
    for row in user_id:
        user_data = dict(zip(columns, row))
    return user_data


def get():
    user_data = get_data('rabg')
    # Access the 'firstName' column
    print(f'{user_data["firstName"]},,,{user_data["lastName"]},,,{user_data["last_login_time"]}')


get()

Upvotes: 0

Aung Ko Man
Aung Ko Man

Reputation: 786

Dictionary Cursor might save your day

cursor = db.cursor(dictionary=True)

Upvotes: 2

GK10
GK10

Reputation: 371

import pymysql

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

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT * from user")

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

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

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

# print the dictionary
print(row)

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

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

# close database connection
db.close()

Upvotes: 17

voam
voam

Reputation: 1016

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:")
for row in cursor:
    print("* {Name}".format(Name=row['Name']))

Upvotes: 35

Irfan Ashraf
Irfan Ashraf

Reputation: 2460

import mysql
import mysql.connector

db = mysql.connector.connect(
   host = "localhost",
    user = "root",
    passwd = "P@ssword1",
    database = "appbase"
)

cursor = db.cursor(dictionary=True)

sql = "select Id, Email from appuser limit 0,1"
cursor.execute(sql)
result = cursor.fetchone()

print(result)
# output =>  {'Id': 1, 'Email': '[email protected]'}

print(result["Id"])
# output => 1

print(result["Email"])
# output => [email protected]

Upvotes: 7

Mohammed Breky
Mohammed Breky

Reputation: 395

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)

Upvotes: 23

Johan Snowgoose
Johan Snowgoose

Reputation: 394

import mysql.connector as mysql
...
cursor = mysql.cnx.cursor()
cursor.execute('select max(id) max_id from ids')
(id) = [ id for id in cursor ]

Upvotes: 0

user10888197
user10888197

Reputation:

selecting values from particular column:

import pymysql
db = pymysql.connect("localhost","root","root","school")
cursor=db.cursor()
sql="""select Total from student"""
l=[]
try:
    #query execution
    cursor.execute(sql)
    #fetch all rows 
    rs = cursor.fetchall()
    #iterate through rows
    for i in rs:
        #converting set to list
        k=list(i)
        #taking the first element from the list and append it to the list
        l.append(k[0])
    db.commit()
except:
    db.rollback()
db.close()
print(l)

Upvotes: 2

Bala Sundaram
Bala Sundaram

Reputation: 79

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]

Upvotes: 6

Steven Rumbalski
Steven Rumbalski

Reputation: 45552

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()
for row in result_set:
    print "%s, %s" % (row["name"], row["category"])

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

Upvotes: 100

Diego
Diego

Reputation: 7562

You didn't provide many details, but you could try something like this:

# conn is an ODBC connection to the DB
dbCursor = conn.cursor()
sql = ('select field1, field2 from table') 
dbCursor = conn.cursor()
dbCursor.execute(sql)
for row in dbCursor:
    # Now you should be able to access the fields as properties of "row"
    myVar1 = row.field1
    myVar2 = row.field2
conn.close()

Upvotes: 1

TaoJoannes
TaoJoannes

Reputation: 594

Of course there is. In Python 2.7.2+...

import MySQLdb as mdb
con =  mdb.connect('localhost', 'user', 'password', 'db');
cur = con.cursor()
cur.execute('SELECT Foo, Bar FROM Table')
for i in range(int(cur.numrows)):
    foo, bar = cur.fetchone()
    print 'foo = %s' % foo
    print 'bar = %s' % bar

Upvotes: 3

Related Questions