Python string formatting: issue with single quotess

Question

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?

9000 · Accepted Answer · 2017-03-09 19:58:41Z

1

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.

answered Mar 9, 2017 at 19:58

9000

41k9 gold badges69 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

Python string formatting: issue with single quotess

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related