Forming sql command with json object fails

Question

I am trying to create sql command with json object that can be run by python script. I came up with below script. The method func1() works, but its not readable. func2() is the one I am aiming for, but it fails. Is there a easy way?

I am using python3.4.

#!/usr/bin/env python3

def func1():
    url = "http://example.com:9000"
    user = "apple"
    password = "orange"
    kvp = '{"host": "' + url + '"' + ', "user: "' + user + '"}'
    sqlCmd1 = '"INSERT INTO hosts (config_info) VALUES(' + kvp + ');"'
    print("sqlcmd1:", sqlCmd1)

def func2():
    url = "http://example.com:9000"
    user = "apple"
    password = "orange"
    sqlCmd2 = 'INSERT INTO hosts (config_info) VALUES ({"host": "{0}"});'.format(url)
    print("sqlCmd2: ", sqlCmd2)

if __name__ == '__main__':
    func1()
    func2()


sqlcmd1: "INSERT INTO hosts (config_info) VALUES({"host": "http://example.com:9000", "user: "apple"});"
Traceback (most recent call last):
  File "./sql.py", line 20, in <module>
    func2()
  File "./sql.py", line 15, in func2
    sqlCmd2 = 'INSERT INTO hosts (config_info) VALUES ({"host": "{0}"});'.format(url)
KeyError: '"host"'

The below code worked.

def func2():
    url = "http://example.com:9000"
    user = "apple"
    password = "orange"
    sqlCmd2 = 'INSERT INTO hosts (config_info) VALUES ({{"host": "{0}", "user": "{1}","password": "{2}"}});'.format(url, user, password)
    print("sqlCmd2: ", sqlCmd2)

When using `.format()` with literal curly braces in the string, you need to escape those braces by doubling them e.g. `{{"host" : ...` instead of `{"host" : ...` See: https://stackoverflow.com/questions/5466451/how-can-i-print-literal-curly-brace-characters-in-python-string-and-also-use-fo — AxxE, Jun 04 '19 at 18:40

Forming sql command with json object fails

0 Answers0