Query format with f-string

Question

I have very big dictionary that I want to insert into MySQL table. The dictionary keys are the column names in the table. I'm constructing my query like this as of now:

bigd = {'k1':'v1', 'k2':10}
cols = str(bigd.keys()).strip('[]')
vals = str(bigd.values()).strip('[]')
query = "INSERT INTO table ({}) values ({})".format(cols,vals)
print query

Output:

"INSERT INTO table ('k2', 'k1') values (10, 'v1')"

And this works in Python2.7

But in Python 3.6 if I use string literals like this:

query = f"INSERT INTO table ({cols}) values ({vals})"
print(query)

It prints this:

"INSERT INTO table (dict_keys(['k1', 'k2'])) values (dict_values(['v1', 10]))"

Any tips?

• jonrsharpe: You shouldn't be forming query strings with string formatting at all, it exposes you to injection attacks. Read the docs for the database engine you're using, which tell you how to do it by putting placeholders in your query and letting them do the formatting.
• Mohammad Yusuf: @jonrsharpe Thanks for the tip. Will switch to placeholders. But is there a way to get rid of those "dict_keys" for some other similar application, which will maintain quotes around string and no quotes around ints.
• jonrsharpe: The problem was you were doing it a naive way to start with, mangling strings of list literals. ','.join(map(repr, whatever)) is neater and continues to work.
• Mohammad Yusuf: @jonrsharpe yup that works. Thanks.

Answer 1

For your curiosity, you should realize that you've cast these to str, getting the representation of dict_keys/values to be inserted into the f-string.

You could just cast to tuples and then insert:

cols = tuple(bigd.keys())
vals = tuple(bigd.values())
q = f"INSERT INTO table {cols} values {vals}"

but, as the comment notes, this isn't a safe approach.

• indianwebdevil: I tried this ` INSERT INTO user ('id', 'field2') values ('1', 'value2') ` - there is single quotes surrounding field names, and it throws the syntax error. ` syntax error at or near "'id'" LINE 1: INSERT INTO user ('id', `.
• indianwebdevil: btw, its a cool construct :)