要將JSON數(shù)據(jù)存入MySQL數(shù)據(jù)庫,您需要執(zhí)行以下步驟:
JSON
數(shù)據(jù)類型來存儲(chǔ)JSON數(shù)據(jù)。例如:CREATE TABLE my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
json_data JSON
);
mysql-connector-python
庫來連接數(shù)據(jù)庫:import mysql.connector
cnx = mysql.connector.connect(
host="your_host",
user="your_user",
password="your_password",
database="your_database"
)
cursor = cnx.cursor()
INSERT INTO
語句將其插入到表中。例如:json_data = {
"name": "John Doe",
"age": 30,
"city": "New York"
}
json_data_str = json.dumps(json_data) # 將JSON對(duì)象轉(zhuǎn)換為字符串
query = "INSERT INTO my_table (json_data) VALUES (%s)"
cursor.execute(query, (json_data_str,))
cnx.commit()
cursor.close()
cnx.close()
這樣,您就將JSON數(shù)據(jù)成功存入了MySQL數(shù)據(jù)庫。如果需要查詢存儲(chǔ)的JSON數(shù)據(jù),可以使用JSON_EXTRACT()
函數(shù)。例如:
SELECT JSON_EXTRACT(json_data, '$.name') as name FROM my_table;