Python mysql.connector - Correct way to retrive a row as dictionary

Question

I've a table with 20 columns, I use this code to get every field of a specific movie as a dictionary:

import mysql.connector

def getMovie(id):
   movie = {}
   cnx = mysql.connector.connect(**config)
   cursor = cnx.cursor()
   query = ('SELECT * FROM movies WHERE id = %s') % id
   cursor.execute(query)
   for row in cursor:
      movie['id'] = row[0]
      movie['actors'] = row[1]
      movie['title'] = row[2]
      # and so on for 20 lines
    return movie

Column names would be the dictionary keys. Is there a shorter way to archive the same result? 20 lines of variables are really bad looking....

Daniel Roseman · Accepted Answer · 2017-02-10 17:26:06Z

10

You can pass dictionary=True to the cursor to get it to return a dictionary.

cursor = cnx.cursor(dictionary=True)

See the docs on MySQLCursorDict.

answered Feb 10, 2017 at 17:26

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ZdaR · Accepted Answer · 2017-02-10 17:26:17Z

0

You may use zip to club your attribute names and values as:

attribute_names = ["id", "actors", "title"]
values = [10, 20, 30] # row in your case
d = dict(zip(attribute_names, values))

answered Feb 10, 2017 at 17:26

ZdaR

23.1k7 gold badges71 silver badges90 bronze badges

Comments

Dani · Accepted Answer · 2023-04-27 06:57:14Z

0

The

cursor = cnx.cursor(dictionary=True)

does not work anymore, we have to pass the class DictCursor as parameter of cnx.cursor

cursor = conn.cursor(pymysql.cursors.DictCursor)

answered Apr 27, 2023 at 6:57

Dani

92711 silver badges17 bronze badges

Collectives™ on Stack Overflow

Python mysql.connector - Correct way to retrive a row as dictionary

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related