12

Just a beginner with the python/postgres combo so forgive me if this is trivial. I'm executing a raw SQL query with sqlalchemy along the lines of:

SELECT * FROM table WHERE pk_table_id IN ()

For the example below I tried self.ids as a tuple containing string or integers as well as an array containing string or integers. Either way it didn't work.

When I use this line:

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id IN (%s)', self.ids)

I get the error:

TypeError: not all arguments converted during string formatting

Any suggestions?

ScottyUCSD
  • 3,736
  • 1
  • 17
  • 11

6 Answers6

10

I ended up ditching SqlAlchemy for straight psycopg2, so I don't know if it applies 100%. What I found out was that psycopg2 will correctly compile the IN clause if you pass it a tuple rather than an array/list. I passed a tuple of integers and it worked just fine.

ScottyUCSD
  • 3,736
  • 1
  • 17
  • 11
  • Thanks, this works very well. Instead of using `params=[list]` one should use `params=[tuple(list)]` – jnns Apr 25 '11 at 15:06
  • 1
    +1 - also see the docs: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries – moooeeeep Jun 08 '12 at 11:52
9

You can use the cur.mogrify method:

cur = my_connection.cursor()
cur.execute(cur.mogrify('SELECT * FROM public.table WHERE pk_table_id IN %s', (tuple(self.ids),)))
Pykler
  • 14,565
  • 9
  • 41
  • 50
  • do not forget to close your cursor `cur.close()` – Pykler Dec 18 '12 at 21:36
  • I don't think you need to use`cur.mogrify` inside the `cur.execute`, this works directly in the `execute` method – Anentropic Jun 30 '16 at 16:40
  • @Anentropic if it works then this is a new version. If you mean by works that you use the % sign, then you are introducing a security concern and I hope you don't mean that! – Pykler Jun 30 '16 at 20:03
2

The %s placeholder in execute expects a scalar, not a tuple. You either need to replace it with ','.join(('%s',) * len(mytuple)), or use string substitution instead!

Lukáš Lalinský
  • 40,587
  • 6
  • 104
  • 126
Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • Fixed the answer, because the original version would produce `'%,s,%,s,%,s'`. – Lukáš Lalinský Oct 24 '09 at 08:16
  • It's not correct: single `%s` query expects sequence with single item. – Denis Otkidach Oct 24 '09 at 09:07
  • That's the solution i used to solve my problem, thanks. My usecase: ``tags_to_remove = set(['foo', 'bar'])`` ``db.execute('DELETE FROM article_tags WHERE article_id = ? AND tag_name IN (%s)' % (', '.join(('?',) * len(tags_to_remove))), article_id, *tags_remove)`` – bufh Aug 31 '12 at 19:59
2

if your ids are in a list you can use list adaptation:

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id = ANY(%s)', (self.ids,))

Taken from lists-adaptation

Tadzys
  • 1,044
  • 3
  • 16
  • 22
0

if self.ids is an array, you will have a problem doing the conversion within the execute statement. Instead, you should do it as a string operation before calling the execute statement.

Try this:

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id IN (%s)' % 
                      ",".join(str(x) for x in self.ids))
Walter
  • 7,809
  • 1
  • 30
  • 30
vy32
  • 28,461
  • 37
  • 122
  • 246
  • Note: The given call to `join` would require the IDs in `self.ids` to be strings. – Walter May 04 '12 at 09:11
  • No, it would not. Python uses %s for all variable substitutions. – vy32 May 05 '12 at 09:21
  • Sure, passing `int`s to `%s` string formatting parameters would work, but `str.join()` is not that liberal. Try `','.join(range(10))` in a Python shell. – Walter May 07 '12 at 13:35
  • No problem at all: `",".join([str(x) for x in range(10)])` – vy32 May 07 '12 at 15:01
  • Your call to `join` in your answer assumes that the items in `self.ids` are strings, hence my original comment. – Walter May 07 '12 at 15:32
  • not when I use the list comprehension. They are all transformed into strings now. – vy32 May 08 '12 at 12:36
  • 2
    [Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.](http://initd.org/psycopg/docs/usage.html#sql-injection) – moooeeeep Jun 08 '12 at 11:50
  • Just want to reiterate the above. This answer suggests an approach that the psycopg documentation goes to great lengths to admonish its users to never, ever do, as it creates a gaping security hole. – Magenta Nova Oct 10 '17 at 15:02
  • What is the security hole? It's only a security hole if self.ids comes from the user, not if you generate it yourself. – vy32 Oct 11 '17 at 04:26
0

cur.execute(query, vars=None) expects variables (emphasis on plural) to be passed to it.

When passing a single parameter that is iterable: a list, a string, or, in this case, a tuple, that parameter needs to be wrapped as a tuple otherwise the execute function interprets it as multiple parameters being passed, even though the query is only expecting one parameter, hence the error not all arguments converted (emphasis on the plural).

The general fix is to always do cur.execute(query, vars=(var1,). It's helpful to specify the vars keyword to remind yourself the function is expecting multiple arguments.

The specific fix in this case is

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id IN %s)', (self.ids,))
raphael
  • 2,762
  • 5
  • 26
  • 55