We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Parameterized Query fails via PyODBC — Vertica Forum

Parameterized Query fails via PyODBC

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

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file