0

I frequently see (in this forum and elsewhere) SQL insert queries wrapped in three consecutive quotation marks, example:

query = """INSERT INTO test (value0, value1) VALUES (%s, %s)"""
mycursor.execute(query, ("foo","bar"))

I want to be able to iterate over table names. So when I use one quotation mark only, the query works just as well. 

table1 = "test1" 
query = "".join(("INSERT INTO ", table1," (value0, value1) VALUES (%s, %s)"))
mycursor.execute(query, ("foo","bar"))

Which syntax is correct now?

Marc Steffen
  • 113
  • 1
  • 7
  • 1
    The three quotation string syntax is only needed for multiline strings. That being said, it _works_ for your first version, but just isn't necessary. It isn't necessary in the second version either. – Tim Biegeleisen Sep 12 '19 at 09:15

1 Answers1

1

Triple quotes are used for strings that span multiple lines. They're a convenient shorthand to write readable indented code in strings. If your string is just one line you don't need them and 'abc' == '''abc''' (same with double quotes).

See the following example of a longer query string: 

"select column1, column2, column3 "\
"from table1 t1"\
"  join table2 t2 on t1.some_id_field = t2.another_id_field "\
"where column4 = 1"\
"   and column5 is not null"\
"   -- and so on"

This is a lot of typing and error prone if you forget the trailing ' ' (so that you get ... column3from ...). The same with triple quotes is much easier to read and write:

"""select column1, column2, column3
   from table1 t1
      join table2 t2 on t1.some_id_field = t2.another_id_field
   where column4 = 1
      and column5 is not null
      -- and so on
"""

P.S.: Instead of 
table1 = "test1" 
query = "".join(("INSERT INTO ", table1," (value0, anycode) VALUES (%s, %s)"))

I'd always use

query = f"INSERT INTO {table1} (value0, anycode) VALUES (%s, %s)"

because it's not only easier to read but it makes it far less probable to forget the spaces around the table name.

Stef
  • 28,728
  • 2
  • 24
  • 52
  • thx a lot for the explanations, I appreciate. As to the f-strings: I can't use them since still on 3.5, but that would indeed be a better choice. – Marc Steffen Sep 12 '19 at 19:06
  • you still could use `"INSERT INTO {} (value0, anycode) VALUES (%s, %s)".format(table1)` on 3.5 – Stef Sep 12 '19 at 19:16