python database insert formatting

Question

I am trying to insert into a postgresql database in python 3.6 and currently am trying to execute this line

        cur.execute("INSERT INTO "+table_name+"(price, buy, sell, timestamp) VALUES (%s, %s, %s, %s)",(exchange_rate, buy_rate, sell_rate, date))

but every time it tries to run the table name has ' ' around it so it turns out like INSERT INTO table_name('12', ..., ..., ...) ... instead of INSERT INTO table_name(12, ..., ..., ...) ... how can I make the string formatter leave the quotes out or remove them or something? It is causing a syntax error around the 12 because it doesn't need the single quotes.

Don't do that, i.e. don't use the string formatting, this will make your code vulnerable to sql inject attacks. Use query parameters instead. — redneb
– redneb, Commented Dec 9, 2017 at 2:31
always put full error message (Traceback) in question (as text, not screenshot). There are other useful informations. — furas
– furas, Commented Dec 9, 2017 at 2:33

Tuğca Eker · Accepted Answer · 2017-12-09 02:47:10Z

1

Use it with triple quotes. Also you may pass table_name as a element of second parameter, too.

 cur.execute("""INSERT INTO %s (price, buy, sell, timestamp) VALUES (%s, %s, %s, %s)""",(table_name, exchange_rate, buy_rate, sell_rate, date))

More detailed approach;

Triple qoutes give developers a change to write SQL query as multi-lines.
Also it allows you to use single and double qoutes without escaping from them. (It is beneficiary for complex SQL Queries but you don't need that on your case)

edited Dec 9, 2017 at 2:47

answered Dec 9, 2017 at 2:32

Tuğca Eker

1,49313 silver badges20 bronze badges

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

3 Comments

user1771791 Over a year ago

That works except when i added the tablename back it complains about the quotes around the table INSERT INTO 'my_table' (... should not have the quotes around the my_table. how is this avoided?

Tuğca Eker Over a year ago

Why you are not using it as we shared and explained? As @redneb said, your method is vulnerable to injection attacks. Explain your purpose and we may found another solution for your needs.

user1771791 Over a year ago

I did exactly what you commented with and as a result of that the first %s for the table name is wrapped in ' '. This is a syntax error and is breaking the query. Am i missing something?

user1767754 · Accepted Answer · 2017-12-09 03:01:00Z

0

Use the new string formatting to have a clean representation. %s is explicitly converting to a string, you don't want that. Format chooses the most fitting type for you.

table_name = "myTable"
exchange_rate = 1
buy_rate = 2
sell_rate = 3
date = 123
x = "INSERT INTO {0} (price, buy, sell, timestamp) VALUES ({1}, {2}, {2}, {4})".format(
    table_name, exchange_rate, buy_rate, sell_rate, date)

print x
>INSERT INTO myTable (price, buy, sell, timestamp) VALUES (1, 2, 2, 123)

answered Dec 9, 2017 at 3:01

user1767754

25.5k21 gold badges167 silver badges177 bronze badges

2 Comments

user1771791 Over a year ago

I did this an it seems to be working except the date no longer has the quotes around it. is there a way to make the {4} have quotes on it only?

user1771791 Over a year ago

\'{}\' did it for me

Collectives™ on Stack Overflow

python database insert formatting

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related