1

Is it possible to execute query for hive using jinjasql and pyhive? When I tried it I got following error.

pyhive.exc.ProgrammingError: Unsupported param format: 
odict_values(['XXXXXXX'])

I can execute query with jinjasql as well as with pyhive but I need to use both together to prevent sql injection.

Environment (all on the same VM.)

user9756398
  • 13
  • 5

2 Answers2

0

Pyhive supports list, tuple and dict in params. While jinjasql supports multiple param style, and as mentioned in the documentation here

It should return a list, except for 'named' or 'pyformat' which would return a dictionary. Since Jinjasql creates a Ordereddict it is throwing this exception. Solution should be to use a param style which returns a list.

Hope this helps :)

UPDATE for python 3: In python 3 you will have to convert the parameters to list as dict.values() returns a view of the dictionary values.

Vaibhav Singh
  • 932
  • 7
  • 20
0

As you mentioned that PyHive allows list for execute parameter I used list function following your suggestion to convet bind_params to a list.

from pyhive import hive
from jinjasql import JinjaSql
j = JinjaSql()

template = "SELECT * FROM sample_07 WHERE code = {{ codex }}"
data = {'codex': '13-1061'}

query, bind_params = j.prepare_query(template, data)
updated_bind_params = list(bind_params)

cursor = hive.connect('sandbox.hortonworks.com').cursor()
cursor.execute(query, updated_bind_params)
print(cursor.fetchall())

It can fetch record from hive.

user9756398
  • 13
  • 5