-1

I have a list of IDs

L1=['A1','A14','B43']

I am trying to use a SQL script to extract information from a table where the ID is in the above list.

sqlquery= "select * from table where ID in " + L1
cur.execute(sqlquery)

I've connected to vertica using vertica_python and sqlalchemy_vertica. But I'm not sure how to incorporate my variable (the list L1) into the sql query.

Updated Code:
data = ['A1', 'A14', 'B43', ...]
placeholders = ','.join('?' * len(data))  # this gives you e.g. '?,?,?'
sqlquery = 'SELECT * FROM table WHERE id IN (%s)' % placeholders
cur.execute(sqlquery, tuple(data))
Lax Mandis
  • 131
  • 4
  • 14
  • `data` in my example is what was `L1` in your original code. – ThiefMaster Apr 11 '19 at 16:17
  • Yep, I got that. – Lax Mandis Apr 11 '19 at 16:20
  • passing the data to `cur.execute()` in the second parameter sends the data that will be used for the placeholders, so you do not need to put the data in the sql query itself at all. – ThiefMaster Apr 11 '19 at 16:21
  • Thanks, but now I'm dealing with: raise errors.QueryError.from_error_response(self._message, operation) vertica_python.errors.VerticaSyntaxError: Severity: b'ERROR', Message: b'Syntax error at or near ","' – Lax Mandis Apr 11 '19 at 16:24

1 Answers1

0

The docs on https://github.com/vertica/vertica-python shows that the Vertica DBAPI implementation uses ? for positional placeholders, so you can use a parametrized query.

Unfortunately lists cannot be passed nicely and need once parameter per element, so you need to generate this part dynamically:

data = ['A1', 'A14', 'B43', ...]
placeholders = ','.join('?' * len(data))  # this gives you e.g. '?,?,?'
sqlquery = 'SELECT * FROM table WHERE id IN (%s)' % placeholders
cur.execute(sqlquery, data)

But you still keep data and SQL separate that way, so there's no risk of SQL injection!

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • I'm getting this error: not all arguments converted during string formatting – Lax Mandis Apr 11 '19 at 16:00
  • Tried again and again. I'm getting the same error: operation = operation % tuple(tlist) TypeError: not all arguments converted during string formatting – Lax Mandis Apr 11 '19 at 16:14
  • Why are you converting it to a tuple? `placehoders` is a string and should remain one. – ThiefMaster Apr 11 '19 at 16:15
  • Oh, it fails when executing it. Try `cur.execute(sqlquery, tuple(data))` in case it doesn't like getting a list th ere. – ThiefMaster Apr 11 '19 at 16:17
  • 1
    Sorry to trouble you, but its still showing an error: operation = operation % tuple(tlist) TypeError: not all arguments converted during string formatting – Lax Mandis Apr 11 '19 at 16:19