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

Question

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?

hakki · Accepted Answer · 2020-11-11 15:26:25Z

2

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 [{}]

answered Nov 11, 2020 at 15:26

hakki

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related