CODEWITHSUNDEEP
pythonmysqlpython-3.x
user_12
user_12

Reputation: 2129

How to use variables to insert data in python to a mysql database?

How can I use variables directly in place of string when inserting data into the query?

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', '["+1 917 547 7813", "+1 917 547 7823"]', 
    '["[email protected]"]', '["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

The email address data is in json_data variable,

print(json_data['email'])
# returns
["[email protected]"]

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

Now I want to pass the variable email_array in sql query email column rather than manually writing the json list in the query variable like I did above?

How can I do it in python?

Upvotes: 1

Views: 147

Answers (1)

hakki
hakki

Reputation: 6527

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

if email variable is an array use {} otherwise [{}]

Upvotes: 2

Related Questions