CODEWITHSUNDEEP
pythonsqlstring-formattingpsycopg2
sc28
sc28

Reputation: 1213

Python string formatting: issue with single quotess

I'm trying to insert some strings from an array of strings using string formatting.

But for some reason, Python is filling the first %s with the desired string but surrounded by single quotes.

Is there an explicit way to prevent this behavior?

Example:

myString = [['000b',
  '1',
  'Text1',
  '1.74',
  'Text2',
  'None'],
 ['000b',
  '1',
  'TextA',
  '12.92',
  'TextB',
  'None']]

When I run the following:

cursor.executemany("INSERT INTO schema.myTable_%s_name (col1, col2, col3, col4, col5) VALUES (%s, NULLIF(%s,'None')::decimal, NULLIF(%s,'None')::decimal, %s, NULLIF(%s,'None')::int)", myString[0:len(myString)])

the expected behavior is that the table name becomes schema.myTable_000b_name but Python writes schema.myTable_'000b'_name which naturally prevents the SQL from running. How could I specify that I don't want the single quotes to be inserted?

Upvotes: 0

Views: 77

Answers (1)

9000
9000

Reputation: 40884

This does not work the way you think it should.

The executemany procedure creates an SQL statement, and then executes the same statement, binding it to different parameters. This way you can e.g. quickly insert many rows into a table.

This does not allow to run multiple different SQL statements, each with its own parameters. It does not support interpolating table names from parameters; the fact that it sort of tried to work is a curiosity, not a feature.

If you need to insert into many tables, issue as many insert statements. Compute the table name using normal Python interpolation. The DB driver tries to interpret your parameter, found in the middle of a table name, as a varchar constant, properly quoting it.

Upvotes: 1

Related Questions