0

I'm trying to get a table from Denodo using Python and sqlalchemy library. That's my code 

from sqlalchemy import create_engine
import os

sql = """SELECT * FROM test_table LIMIT 10 """
engine = create_engine('mssql+pyodbc://DenodoODBC', encoding='utf-8')

con = engine.connect().connection
cursor = con.cursor()
cursor.execute(sql)

df = cursor.fetchall()

cursor.close()
con.close()

When I'm trying to run it for the first time I get the following error.

DBAPIError: (pyodbc.Error) (' \x10#', "[ \x10#] ERROR: Function 'schema_name' with arity 0 not found\njava.sql.SQLException: Function 'schema_name' with arity 0 not found;\nError while executing the query (7) (SQLExecDirectW)") [SQL: SELECT schema_name()]

I think the problem might be with create_engine because when I'm trying to run the code for the second time without creating an engine again, everything is fine.

I hope somebody can explain me what is going on. Thanks :)

Katarzyna Malina
  • 21
  • 1
  • Are you sure that the MS SQL Server dialect, the `mssql` in your DB URL, is meant to be compatible with Denodo (what ever that is)? – Ilja Everilä Jul 13 '19 at 14:06
  • Denodo is using the postgesql dialect, so maybe try that? I have not used sqlalchemy to access denodo so no idea if that helps. Also the error suggest that you are missing the schema. Denodo uses the vdb name as a schema name so maybe you need to add that before the table name? like this: SELECT * FROM virtualdatabasename.test_table LIMIT 10 – Letimogo Jul 17 '19 at 05:51
  • Have you tried the JDBC driver? It seems to be preferred over ODBC according to Denodo. – firefly2442 Nov 03 '22 at 01:15

0 Answers0