python-mysqlHow do I use Python to fetch data from a MySQL database as a dictionary?

Using Python to fetch data from a MySQL database as a dictionary is a fairly straightforward process. First, you must import the necessary libraries:

import MySQLdb
import json

Next, you must establish a connection to the database:

db = MySQLdb.connect(host="localhost", user="username", passwd="password", db="database_name")

Once a connection has been established, you can execute your query:

cur = db.cursor()
cur.execute("SELECT * FROM table_name")

You can then fetch the results of the query as a dictionary:

rows = cur.fetchall()
data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in rows]

Finally, you can convert the dictionary into a JSON string:

json_string = json.dumps(data)

The result of this code will be a JSON string containing the results of your query.

Parts of the code

1. import MySQLdb: This imports the MySQLdb library, which allows us to interact with a MySQL database.
2. import json: This imports the json library, which allows us to convert our data into a JSON string.
3. db = MySQLdb.connect(host="localhost", user="username", passwd="password", db="database_name"): This establishes a connection to the database.
4. cur = db.cursor(): This creates a cursor object, which allows us to execute queries.
5. cur.execute("SELECT * FROM table_name"): This executes the query.
6. rows = cur.fetchall(): This fetches the results of the query.
7. data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in rows]: This converts the results of the query into a dictionary.
8. json_string = json.dumps(data): This converts the dictionary into a JSON string.

Relevant Links

• MySQLdb Documentation
• JSON Documentation

Edit this code on GitHub