python-mysqlHow to use Python to add a JSON column to a MySQL database?

To add a JSON column to a MySQL database using Python, the following steps should be taken:

1. Establish a connection to the MySQL database using the mysql.connector library.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="username",
  passwd="password",
  database="mydatabase"
)

2. Create a JSON column in the database using the ALTER TABLE command.

ALTER TABLE mytable ADD json_column JSON;

3. Insert the JSON data into the column using the INSERT INTO command.

mycursor = mydb.cursor()

sql = "INSERT INTO mytable (json_column) VALUES (%s)"
val = ('{"name":"John","age":30,"city":"New York"}',)

mycursor.execute(sql, val)
mydb.commit()

4. Check that the data was successfully inserted by querying the table.

SELECT * FROM mytable;

Output example

+----+------------------------------------+
| id | json_column                        |
+----+------------------------------------+
|  1 | {"name":"John","age":30,"city":"NY"}|
+----+------------------------------------+

Helpful links

• MySQL Connector/Python Documentation
• MySQL ALTER TABLE Syntax
• MySQL INSERT INTO Syntax

Edit this code on GitHub