python-mysqlHow do I convert a Python object to a JSON object and store it in a MySQL database?

To convert a Python object to a JSON object and store it in a MySQL database, you can use the json.dumps() and MySQL Connector/Python functions.

Example code

import json
import mysql.connector

# Create connection to MySQL database
cnx = mysql.connector.connect(user='root', password='password',
                              host='localhost',
                              database='mydb')

# Create a Python object
my_object = {
    'name': 'John',
    'age': 25
}

# Convert the Python object to a JSON object
my_json_object = json.dumps(my_object)

# Create a MySQL cursor object
cursor = cnx.cursor()

# Create an INSERT query
query = 'INSERT INTO users (name, age) VALUES (%s, %s)'

# Execute the query
cursor.execute(query, (my_json_object,))

# Commit the changes to the database
cnx.commit()

The code above will convert the Python object to a JSON object and store it in a MySQL database.

Parts of the code:

• import json: imports the json module, which contains functions for converting Python objects to JSON objects.
• import mysql.connector: imports the MySQL Connector/Python module, which contains functions for connecting to and manipulating MySQL databases.
• cnx = mysql.connector.connect(...): creates a connection to the MySQL database with the specified credentials.
• my_object = {...}: creates a Python object.
• my_json_object = json.dumps(my_object): converts the Python object to a JSON object.
• cursor = cnx.cursor(): creates a MySQL cursor object.
• query = 'INSERT INTO ...': creates an INSERT query for inserting the JSON object into the database.
• cursor.execute(query, (my_json_object,)): executes the query.
• cnx.commit(): commits the changes to the database.

Helpful links

• MySQL Connector/Python
• json — JSON encoder and decoder

Edit this code on GitHub