Forum

Utilising non-SQL programming languages in Kognitio
Multiple Poster
Offline
User avatar
Posts: 5
Joined: Tue May 03, 2016 10:10 am

Python ODBC

by awilliams » Tue May 03, 2016 10:33 am

Have set up an ODBC connection to/from server and kognitio.

Connection works fine in R but am struggling with python.

Using the

pyodbc.connect()

method it connects fine and can even return a list of all tables on the db. However, when I try to execute a query:

cnxn = pyodbc.connect(DSN=..., UID=..., PWD=...)
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM schema.table')

it returns the following error:

pyodbc.Error: ('07002', "[07002] [Kognitio][WX2 Driver] Number of bound columns doesn't match (0) (SQLExecDirectW)")

At a complete loss as to what is causing this issue.

Thanks
Reply with quote Top
Contributor
Offline
User avatar
Posts: 21
Joined: Mon Oct 07, 2013 12:15 pm

Re: Python ODBC

by ChakLeung » Tue May 03, 2016 4:08 pm

Hi awilliams,

I was wondering if it works for you when just selecting 1 column? Like I have in the code below, note that the <col_name> needs to be in upper case as well.

Code: Select all

import sys,os,pyodbc

cnxn = pyodbc.connect('DSN=<server_name>; UID=<username>; PWD=<password>')
cursor = cnxn.cursor()
cursor.execute('SELECT <col_name> FROM <schema>.<table>')
for row in cursor:
    print row.<col_name>
However the 07002 error for pyodbc appears to refer to too few parameters but it doesn't seem like you've used any so I wanted to see if this simple 1 column connection would work first.
Reply with quote Top
Multiple Poster
Offline
User avatar
Posts: 5
Joined: Tue May 03, 2016 10:10 am

Re: Python ODBC

by awilliams » Tue May 03, 2016 4:15 pm

Hi ChakLeung

Thanks for the reply.

Unfortunately even just selecting one column by name returns the same error.

As I said the odbc and dsn's are all working fine for all other instances apart from python.

Just as a side note, I am using python3.x could this have an influence?

Thanks
Reply with quote Top
Contributor
Offline
User avatar
Posts: 21
Joined: Mon Oct 07, 2013 12:15 pm

Re: Python ODBC

by ChakLeung » Tue May 03, 2016 4:27 pm

Hi awilliams,

It appears that there is a version of pyodbc for python 3.x so there shouldn't be any compatibility issues but if it's possible to try this on Python 2.7.x then that would be a great help too. I'll dig deeper into this in the mean time.
Reply with quote Top
Multiple Poster
Offline
User avatar
Posts: 5
Joined: Tue May 03, 2016 10:10 am

Re: Python ODBC

by awilliams » Tue May 03, 2016 4:53 pm

Hi,

Its a pyodbc for python3.x issue. Works as expected in pyodbc for python2.7.

Slightly annoying moving back to python2.7, but at least the error has been narrowed down.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 21
Joined: Mon Oct 07, 2013 12:15 pm

Re: Python ODBC

by ChakLeung » Wed May 04, 2016 1:59 pm

Hi awilliams,

I wasn't expecting it to be because of Python 3.x but glad it's been resolved somewhat. It might be worth sending this bug to the pyodbc developer so that they can fix it for future releases.
Reply with quote Top
Single Poster
Offline
User avatar
Posts: 1
Joined: Sun Mar 05, 2017 10:10 am

Re: Python ODBC

by sebc » Sun Mar 05, 2017 10:29 am

As I recently faced the same issue. I though this tip could help someone googling for the returned error.
At least with pyodbc v3.0.10 it seems that the driver needs to be utf-16

Code: Select all

odbcinst.ini
[kognitio]
Description = Kognitio ODBC driver
Driver = /opt/kognitio/wx2/current/lib64/libwcsodbc_utf16.so
as pyodbc docs reports:
By default, pyodbc uses UTF-16LE and SQL_C_WCHAR for reading and writing all Unicode as recommended in the ODBC specification. ...
Then the "SELECT" works as expected.

Code: Select all

import pyodbc
cnxn = pyodbc.connect('DSN=server;UID=user;PWD=pass')
cursor = cnxn.cursor()

cursor.execute("SELECT * from SYS.IPE_TABLE")

while 1:
	row = cursor.fetchone()
	if not row:
		break
	print(row.NAME)

cnxn.close()

Note: It looks like the encoding can be specified with `.setencoding' and `.setdecoding' with pyodbc 4.x, but I haven't tried
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron