Python SQLAlchemy and connecting to Denodo

Has anyone had any luck in connecting to Denodo via SQLAlchemy (ORM for Python)? Are there any python drivers that with both SQLAlchemy and Denodo? For example, I am able to run queries against Dendo using Python's Psycopg2 driver. However when I try to connect through SQLAlchemy I get errors because it's trying to query system catalogs: ``` engine = create_engine(cnxn_str) result = engine.execute(r'select COUNT(*) FROM employee;') for row in result: print (row) ``` Error: sqlalchemy.exc.DatabaseError: (psycopg2.DatabaseError) The user does not have READ privileges on the view 'pg_type' DETAIL: java.sql.SQLException: The user does not have READ privileges on the view 'pg_type' For example: The below query works: ``` sqlstr = "SELECT COUNT(*) FROM employee;" conn = pg.connect(host=pg_server_hostname, port=pg_port_number, user=db_user, password=db_pwd, dbname=pg_db_name) cur = conn.cursor() cur.execute(sqlstr) for row in cur: print(row) ```
user
15-03-2018 11:01:55 -0400

3 Answers

Hi, The error "The user does not have READ privileges on the view ‘pg_type’" is thrown when the user connecting to the view does not have the required privileges. I would check if the user connecting to the data source and view has the right privileges to read them. To know more about user access and rights read the section[ "User and Access Right in Virtual DataPort"](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/databases_users_and_access_rights_in_virtual_dataport/user_and_access_right_in_virtual_dataport/user_and_access_right_in_virtual_dataport#user-and-access-right-in-virtual-dataport) of the Virtual DataPort Administration Guide. Hope this helps.
Denodo Team
19-03-2018 00:39:33 -0400
From the documentation: "SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database, and includes support for the most popular [databases](https://docs.sqlalchemy.org/en/latest/dialects/index.html)." * PostgreSQL * MySQL * SQLite * Oracle * Microsoft SQL Server * Firebird * Sybase There are a number of additional externally supported drivers such as DB2, Snowflake, and redshift. Connecting to Denodo with SQLAlchemy would require developing and maintaining a new dialect. As far as I can tell, there is [no support for any jdbc driver](https://stackoverflow.com/questions/47407988/is-there-sqlalchemy-dialect-support-jdbc).
user
26-02-2019 13:02:12 -0500
Hi, As mentioned in the above response, connecting to Denodo Platform from SQLAlchemy would require developing a new dialect and currently, there is no support for any JDBC Drivers. In order to overcome “The user does not have READ privileges on the view ‘pg_type'” error, I would create a new view called **pg_type** in the Virtual DataPort as per the schema described in the [pg_type](https://www.postgresql.org/docs/8.4/catalog-pg-type.html) documentation. For example, you could execute the below statement in the VQL shell of the Virtual DataPort Administration Tool. `CREATE OR REPLACE VIEW pg_type as SELECT NULL AS oid, NULL AS typarray, NULL AS typname, NULL AS typnamespace FROM dual();` You could refer the section [Defining a Derived View](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/defining_a_derived_view/defining_a_derived_view#defining-a-derived-view) of the Virtual DataPort VQL Guide for more information. Hope this helps!
Denodo Team
07-03-2019 08:03:02 -0500
You must sign in to add an answer. If you do not have an account, you can register here