Problem creating connection to PostgreSQL database

[emiliom]

After installing ODM2PythonAPI using the overhauled setup I described in issue #11 (with the setup_em branch I've created), I got an error trying to make a test connection to one of my local PostgreSQL ODM2 databases:

from odm2api.ODMconnection import dbconnection
from odm2api.ODM2.services import *

session_factory = dbconnection.createConnection('postgresql', 'localhost', 'odm2_rivers', 
                                                 'myuser', 'mypwd')

**** {'engine': 'postgresql', 'password': 'mypwd', 'db': 'odm2_rivers', 'user': 'myuser', 'address': 'localhost'}
****** postgresql+psycopg2://myuser:mypwd@localhost/odm2_rivers
Connection was unsuccessful  (psycopg2.ProgrammingError) relation "Variables" does not exist
LINE 2: FROM "Variables"

The messages relation "Variables" does not exist and LINE 2: FROM "Variables" suggest that it's trying to issue a query using a case-sensitive (double-quoted) table name for "Variables". I was able to track the error to probably this line in odm2api/ODMconnection.py, but I wasn't able to go beyond that.

@sreeder, can you look into it? I can help fix it if it's a case issue and you can point me to where the problem is. BTW, this may be related to the case handling issues discussed a few months ago in issue #2. Back then I was able to change the code and get it to work for me with postgresql, but that was before you overhauled the case handling.

[cdesyoun]

Emilio, it seems to me it is not a case-sensitive problem. When looking into ODM2 sqlalchemy models, in case of postgreSQL, there need "odm2" schema. For example, "odm2.Variables".
I guess you got this error message. Does your postgresql db have a schema name?

[emiliom]

Thanks, @cdesyoun! Yes, I use an "odm2" schema name. That's the convention we've adopted across ODM2, so the ODM2PythonAPI needs to handle it.

But if a query is being issued that does have a FROM "Variables" statement with double quotes around the table name, there will be a case sensitivity issue. So maybe the problem is both not having the schema name and case sensitivity?

Hopefully Stephanie can look into it on Monday, and with the information she provides we'll be able to fix it quickly.

[sreeder]

This issue has been addressed in the setup branch

[emiliom]

Excellent. I'll test with postgresql.

[emiliom]

Problem fixed (in setup_em, not setup, branch). I no longer get this error during testing with postgresql. Closing issue.