Is there a more pythonic way to build an INSERT statement?

Question

Trying to build an SQL statement for execute. This works but now sure how pythonic it might be:

user_fields = ("id", "name", "email", "password", "phone")

fields = [field for field in user_fields if field != "id"]  # The only field we don't want is "id"
percent_s = ["%s"] * len(fields)
fields = ",".join(fields)
percent_s = ",".join(percent_s)

sql = "INSERT INTO user_table (" + fields + ") VALUES (" + percent_s + ")"
cursor.execute(sql, row_data)

Before anyone reflexively screams "SQL injection", note that what's being concatenated is just a list of placeholders. — Craig Ringer
– Craig Ringer, Commented Aug 24, 2015 at 0:42
IMO this is a deficiency of the Python DB-API. It should specify a placeholder like %P or something, which inserts a number of comma-separated placeholders equal to the number of query parameters passed. A variant that lets you specify how many placeholders as a parameter would also be nice. — Craig Ringer
– Craig Ringer, Commented Aug 24, 2015 at 0:47
I like portions of both answers so far. With regards to "%s" being visually confusing in the sense of one potentially thinking "Oh no! SQL Injection!" I like @rofls answer with the idea of using {} and .format. In other words, I am currently seeing it mostly as a visual differentiator. — martin's
– martin's, Commented Aug 24, 2015 at 21:24

rofls · Accepted Answer · 2015-08-23 21:44:45Z

1

You could replace:

sql = "INSERT INTO user_table (" + fields + ") VALUES (" + percent_s + ")"

with

sql = "INSERT INTO user_table ({}) VALUES ({})".format(fields,percent_s)

answered Aug 23, 2015 at 21:44

rofls

5,1454 gold badges31 silver badges38 bronze badges

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

Comments

Daniel · Accepted Answer · 2015-08-23 21:46:32Z

1

If your user_fields has a fixed order, row_data must have a corresponding order. You have to ensure this somewhere. Then the position of id is also fixed. Then you can use string formatting, which leads to:

user_fields = ("id", "name", "email", "password", "phone")

sql = "INSERT INTO user_table (%s) VALUES (%s)" % (','.join(user_fields[1:], ','.join(['%s'] * (len(user_fields) - 1))
cursor.execute(sql, row_data)

answered Aug 23, 2015 at 21:46

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

Collectives™ on Stack Overflow

Is there a more pythonic way to build an INSERT statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related