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

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:password@XX.XX.XX.XX:5432/raritan")

to connect to the raritan database, using user "odbcuser" and pas开发者_运维百科sword "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

---

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.