MySQL: pymysql or mysqldb to access a dictionary cursor functionality

Question

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:

sudo apt-get install mariadb-server
sudo apt-get install python-mysqldb
sudo apt-get -y install python-mysql.connector
sudo apt-get install libmariadbclient-dev
sudo pip install PyMySQL

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

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

The references did not come through: https://stackoverflow.com/questions/34034943/populate-a-dictionary-with-the-result-of-a-query and https://stackoverflow.com/questions/7475223/mysql-config-not-found-when-installing-mysqldb-python-interface — RDK, May 08 '19 at 12:41
One of the references suggested that I add "/usr/local/mysql/bin/" to my path, but that folder does not exist on this MariaDB Debian setup. Any ideas? — RDK, May 10 '19 at 04:41

score 1 · Accepted Answer · answered May 08 '19 at 12:50

1

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"])

answered May 08 '19 at 12:50

mixhowie

36
3

Thanks,, but it throws this error" File "/mnt/usbdrive/pgms/modbus/sqltest.py", line 24, in print("row ", mycursor.rowcount, "id ", x['id'], " date ", x['daate_time']) TypeError: tuple indices must be integers, not str " – RDK May 08 '19 at 13:03
Not sure what I did or what I changed but now before by your changes do work. – RDK May 10 '19 at 17:54
However, when I now do an mycursor.execute("insert into … ) I get this message, but the data seems to have been inserted ok. "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py:329: Warning: (1265, "Data truncated for column 'ArrayV' at row 1") self._do_get_result()". Any ideas why? That same statement does not generate those message when I'm not referencing pymysql in the connect or cursor statements. – RDK May 10 '19 at 18:01

score 0 · Answer 2 · answered May 11 '19 at 15:32

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

MySQL: pymysql or mysqldb to access a dictionary cursor functionality

2 Answers2