CODEWITHSUNDEEP
pythonmysqlpython-2.7
RDK
RDK

Reputation: 385

MySQL: pymysql or mysqldb to access a dictionary cursor functionality

I would like to get a dictionary object as the result of an "execute("insert...") " SQL command in Python 2.7 using Debain Stretch on a Raspberry Pi V3. The normal object is a tuple so elements are accessed via an integer index. From the web it would appear that two libraries will supply that discionary functionality: mysqldb and pymysql. When I use "import mysqldb" I get an error "ImportError: No module named mysqldb". If I use "import pymysql" I don't get any errors but the result is a tuple and not a dictionary, which is also what I would get it I just take the default cursor. If I modify my code to specify the column name, ie

print("row ", mycursor.rowcount, "id ", x['id'], " date ", x['date_time'])

Then I get an error about using a non-interger index. Here is example code:

#!/usr/bin/env python
import sys
import pymysql
import mysql.connector
db = mysql.connector.connect(
  host="localhost",
  user="xxx",
  passwd="yyy",
  database="zzz")
print(db)
mycursor = db.cursor(pymysql.cursors.DictCursor)
#    mycursor = db.cursor()
mycursor.execute("select * from table;")
myresult = mycursor.fetchall()
for x in myresult:
    print("row ", mycursor.rowcount, "id ", x[0], " date ", x[1])
# if I use "print("row ", mycursor.rowcount, "id ", x["id"], " date ", x[date_Time])" it throws an error

This is a fresh install of Stretch on an Raspberry Pi v3. I have installed the following:

I have search the web for answers, including the two references below

But so far have not found a solution. Can anyone assist?....RDK

Upvotes: 0

Views: 1193

Answers (2)

RDK
RDK

Reputation: 385

Howie Peng answered the part of my question relating to using pymysql to get access to a DictCursor. Thanks.

Now, I have figured out the issues with using mysqldb!! The first answer is that it is not mysqldb but rather MySQLdb, note upper and lower case!! That change allowed my Python program get past the import statement but it still failed for the db = MySQLdb.connect(... statement. It seems, that unlike mysql.connector and pymysql, MySQLdb uses db instead of database to specify the database name.

Don't you love it?....RDK

Upvotes: 0

mixhowie
mixhowie

Reputation: 36

Try to use pymysql.connect instead of mysql.connector.connect

# !/usr/bin/env python
import sys
import pymysql

db = pymysql.connect(
    host="localhost",
    user="xxx",
    passwd="yyy",
    database="zzz")
print(db)
mycursor = db.cursor(pymysql.cursors.DictCursor)
mycursor.execute("select * from table;")
myresult = mycursor.fetchall()
for x in myresult:
    print("row ", mycursor.rowcount, "id ", x["id"], " date ", x["date_Time"])

Upvotes: 1

Related Questions