In Python mysqldb
I could declare a cursor as a dictionary cursor like this:
cursor = db.cursor(MySQLdb.cursors.DictCursor)
This would enable me to reference columns in the cursor
loop by name like this:
for row in cursor: # Using the cursor as iterator
city = row["city"]
state = row["state"]
Is it possible to create a dictionary cursor using this MySQL connector? http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html
Their example only returns a tuple.
I imagine the creators of MySQL would eventually do this for us?
In Python mysqldb I could declare a cursor as a dictionary cursor like this: cursor = db.cursor(MySQLdb.cursors.DictCursor) This would enable me to reference columns in the cursor loop by name like this: for row in cursor: # Using the cursor as iterator city = row["city"] state = row["state"]
The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.
The MySQLdb documentation states (scroll down a little further to threadsafety): The general upshot of this is: Don't share connections between threads. It's really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection.
According to this article it is available by passing in 'dictionary=True' to the cursor constructor: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html
so I tried:
cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(dictionary=True)
and got:
TypeError: cursor() got an unexpected keyword argument 'dictionary'
and I tried:
cnx = mysql.connector.connect(database='bananas')
cursor = cnx.cursor(named_tuple=True)
and got:
TypeError: cursor() got an unexpected keyword argument 'named_tuple'
and I tried this one too: cursor = MySQLCursorDict(cnx)
but to no avail. Clearly I'm on the wrong version here and I suspect we just have to be patient as the document at http://downloads.mysql.com/docs/connector-python-relnotes-en.a4.pdf suggests these new features are in alpha phase at point of writing.
A possible solution involves subclassing the MySQLCursor
class like this:
class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
def _row_to_python(self, rowdata, desc=None):
row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
if row:
return dict(zip(self.column_names, row))
return None
db = mysql.connector.connect(user='root', database='test')
cursor = db.cursor(cursor_class=MySQLCursorDict)
Now the _row_to_python()
method returns a dictionary
instead of a tuple
.
I found this on the mysql forum, and I believe it was posted by the mysql developers themselves. I hope they add it to the mysql connector package some day.
I tested this and it does work.
UPDATE: As mentioned below by Karl M.W... this subclass is no longer needed in v2 of the mysql.connector. The mysql.connector has been updated and now you can use the following option to enable a dictionary cursor.
cursor = db.cursor(dictionary=True)
This example works:
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']
Keep in mind that in this example, 'Name'
is specific to the column name of the database being referenced.
Also, if you want to use stored procedures, do this instead:
cursor.callproc(stored_procedure_name, args)
result = []
for recordset in cursor.stored_results():
for row in recordset:
result.append(dict(zip(recordset.column_names,row)))
where stored_procedure_name
is the name of the stored procedure to use and args
is the list of arguments for that stored procedure (leave this field empty like []
if no arguments to pass in).
This is an example from the MySQL
documentation found here: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With