Py-Postgresql and Raritan PowerIQ - Can't seem to find table?

Question

Hi,

I'm trying to write some Python3 to interface with the backend PostgreSQL server on a Raritan Power IQ (http://www.raritan.com/products/power-management/power-iq/) system.

I've used pgAdminIII to connect to the server, and it connects fine with my credentials. I can see the databases, as well as the schemas in each database.

I'm now using py-postgresql to attempt to script it, and I'm hitting some issues.

I use the following to connect:

postgresql.open("pq://odbcuser:[email protected]:5432/raritan")

to connect to the raritan database, using user "odbcuser" and password "password" (no, that's not the real one...lol).

It appears to connect successfully. I'm able to to run some queries, e.g.

ps = db.prepare("SELECT * from pg_tables;")
ps()

manages to list all the tables/views in the "raritan" database.

However, I then try to access a specific view and it breaks. The "raritan" database has two schemas, "odbc" and "public".

I can access views from the public schema. E.g.:

ps = db.prepare("SELECT * from public.qrypwrall;")
ps()

works to an extent - I get a permission denied error, same as I under pgAdminIII, as my account doesn't have access to that view, but syntactally, it seems fine and it does find the table.

However, when I try to access a view under "odbc", it just breaks. E.g.:

>>> ps = db.prepare("SELECT * from odbc.Aisles;")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python31\lib\site-packages\postgresql\driver\pq3.py", line 2291, in prepare
ps._fini()
  File "C:\Python31\lib\site-packages\postgresql\driver\pq3.py", line 1393, in _
fini
    self.database._pq_complete()
  File "C:\Python31\lib\site-packages\postgresql\driver\pq3.py", line 2538, in _
pq_complete
    self.typio.raise_error(x.error_message, cause = getattr(x, 'exception', None
))
  File "C:\Python31\lib\site-packages\postgresql\driver\pq3.py", line 471, in ra
ise_error
    self.raise_server_error(error_message, **kw)
  File "C:\Python31\lib\site-packages\postgresql\driver\pq3.py", line 462, in ra
ise_server_error
    raise server_error
postgresql.exceptions.UndefinedTableError: relation "odbc.aisles" does not exist

  CODE: 42P01
  LOCATION: File 'namespace.c', line 268, in RangeVarGetRelid from SERVER
STATEMENT: [parsing]
  statement_id: py:0x10ca1b0
  string: SELECT * from odbc.Aisles;
CONNECTION: [idle]
  client_address: 10.180.9.213/32
  client_port: 2612
  version:
    PostgreSQL 8.3.7 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 2
0071124 (Red Hat 4.1.2-42)
CONNECTOR: [IP4] pq://odbcuser:***@10.180.138.121:5432/raritan
  category: None
DRIVER: postgresql.driver.pq3.Driver

However, I can access the same table (Aisles) fine under pgAdminIII, using the same credentials (and unlike public, I actually have permissions to all these tables.

Is there any reason that py-postgresql might not see these views? Or anything you can pick out from the error messages?

I have a suspicion that it's to do with PowerIQ using mixed-case for the table names (e.g. "Aisle"). However, I'm not exactly sure how to deal with these in psycopg. How exactly would I modify say, my cursor.execute like to quote the table?

cursor.execute('SELECT * from "public.Aisles"')

also doesn't work.

Cheers, Victor

Answer 1

Have you tried it this way: 'SELECT * from public."Aisles"?

Quoting the whole thing makes it a non-qualified (no schema) table name which has a dot in it.