python - 如何使用变量将python中的数据插入mysql数据库？

Question

将数据插入查询时，如何直接使用变量代替字符串？

import pymysql
import json

# Connect to the database
conn = pymysql.connect(host='localhost',
                       user='test',
                       password='br',
                       db='testing')
cur = conn.cursor()

json_data = json.loads(open("res.json").read())

# currently this is the query I want to execute
query = """INSERT INTO RF.candidate (
    first_name, last_name, phone, email, 
    social, website
) VALUES (
    'David', 'N', '["[phone]", "[phone]"]', 
    '["[e-mail]"]', '["fb.com/rtr"]', '["google.com", "sris.com"]', 
    '{"Degree": {"MBA": {"Majors": [""]}}}',
);"""

cur.execute(query)

# datatypes:
# first_name : varchar
# last_name : varchar
# phone : json
# email : json
# social : json
# website: json
# education: json

---

电子邮件地址数据是json_data可变的，

print(json_data['email'])
# returns
["[e-mail]"]

email_array = json.dumps(json_data['email'])

现在我想email_array在 sql 查询email列中传递变量，而不是像上面那样在查询变量中手动写入 json 列表？

我怎样才能在python中做到这一点？

标签： pythonmysqlpython-3.x

Answer 1

query = """INSERT INTO RF.candidate (
    first_name, last_name, phone, email, 
    social, website
) VALUES (
    'David', 'N', '["[phone]", "[phone]"]', 
    '[{}]', '["fb.com/rtr"]', '["google.com", "sris.com"]'
);""".format(EMAIL_VARIABLE)

如果电子邮件变量是一个数组，则使用{}其他方式[{}]