How to retrieve SQL result column value using column name in Python

Question

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

User · Accepted Answer

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

User · Answer

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   gt     Id   1   Email    me gmail com    print result  Id      output   gt  1  print result  Email      output   gt  me gmail com

User · Answer

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

User · Answer

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

User · Answer

import mysql connector as mysql     cursor   mysql cnx cursor   cursor execute  select max id  max id from ids    id      id for id in cursor

User · Answer

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  quot SELECT   FROM country WHERE Continent    Europe  quot    print  quot Countries in Europe  quot   for row in cursor      print  quot    Name  quot  format Name row  Name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] How to retrieve SQL result column value using column name in Python?

Examples related to python

Examples related to mysql