python-mysqlHow do I use Python and MySQL to convert fetchall results to a dictionary?

Using Python and MySQL together, you can convert the results of a fetchall() operation into a dictionary. To do this, you'll need to use the cursor.description method to access the column names of the query result, and the dict constructor to create the dictionary. The following example code will demonstrate this:

# Create a connection to the database
conn = mysql.connector.connect(user='user', password='password',
                              host='host',
                              database='database')

# Create a cursor
cursor = conn.cursor()

# Execute a query
cursor.execute('SELECT * FROM table_name')

# Fetch all results
results = cursor.fetchall()

# Get the column names
column_names = [i[0] for i in cursor.description]

# Convert results to a dictionary
dict_results = [dict(zip(column_names, row)) for row in results]

# Print the dictionary
print(dict_results)

Output example

[{'column1': value1, 'column2': value2, ...},
 {'column1': value3, 'column2': value4, ...},
 ...]

The code above consists of the following parts:

1. Create a connection to the database: This will establish the connection to the MySQL database using the mysql.connector.connect() method.
2. Create a cursor: This will create a cursor object that can be used to execute queries.
3. Execute a query: This will execute a query against the database using the cursor.execute() method.
4. Fetch all results: This will fetch all results from the query using the cursor.fetchall() method.
5. Get the column names: This will get the column names from the query result using the cursor.description method.
6. Convert results to a dictionary: This will convert the query result into a dictionary using the dict() constructor.
7. Print the dictionary: This will print the dictionary to the console.

For further information on using Python and MySQL together, please refer to the following links:

• MySQL Connector/Python Developer Guide
• MySQL Connector/Python Reference

Edit this code on GitHub