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:
- 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"
)
- Create a JSON column in the database using the
ALTER TABLE
command.
ALTER TABLE mytable ADD json_column JSON;
- 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()
- 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
More of Python Mysql
- How do I access MySQL using Python?
- How can I connect Python to a MySQL database using an Xserver?
- How can I use Python to retrieve data from MySQL?
- How can I access MySQL using Python?
- How do I install a Python package from PyPI into a MySQL database?
- How do I download MySQL-Python 1.2.5 zip file?
- How can I use Python to interact with a MySQL database using YAML?
- How do I connect Python with MySQL using XAMPP?
- How can I connect to MySQL using Python?
- How can I connect Python and MySQL?
See more codes...