Parameterized Query fails via PyODBC

jalemmer

Hi,

 

I'm currently working on a small Python script that queries my VerticaDB using pyodbc. In general I was able to get pyodbc up and running but I fail to use parameters when querying the db with a more complex query.

 

So to narrow down the issue I tried the following with success:

 

import pyodbc
myID = 37711

cnxn = pyodbc.connect('DSN=MyVertica', ansi=True)

cursor = cnxn.cursor()

cursor.execute(
"""
SELECT * FROM public.item i
WHERE i.item_id = ?
""", myID)

result = cursor.fetchall()
for row in result:
  print(row. end='\n')

 

This works like a charm.

 

Now I tried a query which is more complex:

 

import pyodbc
myID = 37711

cnxn = pyodbc.connect('DSN=MyVertica', ansi=True)

cursor = cnxn.cursor()

cursor.execute(
                """
                CREATE TEMP TABLE tmpTable ON COMMIT PRESERVE ROWS AS
                 SELECT to_timestamp(if.tstamp),
                  if.tstamp,
                  if.item_id,
                  EXTRACT(DAY FROM to_timestamp(if.tstamp)) as day_of_month,
                  SUM(if.im_utilization) OVER (PARTITION BY EXTRACT(DAY FROM to_timestamp(if.tstamp)),
                  if.item_id ORDER BY 2 ASC ROWS BETWEEN CURRENT ROW AND 59 FOLLOWING) as hour_ifUtil_sum
                 FROM public.item i
                  JOIN public.ifstats_rate if ON if.item_id = i.item_id
                  JOIN public.v_group_membership gm ON i.item_id = gm.member_item_id
                 WHERE gm.group_item_id = ? AND EXTRACT(MONTH FROM to_timestamp(if.tstamp)) = 7 
                """, myID)

result = cursor.fetchall()
for row in result:
  print(row. end='\n')

This fails to run with an exception:

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

Does anybody know why this is happening? I specified the parameter marker "?" within the WHERE-Clause

 

WHERE gm.group_item_id = ?

so I don't see any reason why this fails.

 

Any help or hint will be much appreciated :-)

 

Regards,

Jan

kaurora

Try using https://github.com/uber/vertica-python

 

It hasn't been updated in a while but it worked the last time I tried it which wasn't too long ago