connection schema handling on inserts

[smnorris]

On my postgresql database (9.3.2), executing the quickstart examples works when connection schema is not specified:

>>>import dataset
>>>db = dataset.connect()
>>>table = db['person']
>>>print(table.columns)
['id']
>>>table.insert(dict(name='John Doe', age=46))
1

However, when connection schema is specified, the created table is not found when using insert - it seems to be looking instead in the current search_path:

>>>import dataset
>>>db = dataset.connect(schema="myschema")
>>>table = db['person']
>>>print(table.columns)
['id']
>>>table.insert(dict(name='John Doe', age=46))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 66, in insert
    self._ensure_columns(row, types=types)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 198, in _ensure_columns
    self.create_column(column, _type)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/dataset/persistence/table.py", line 224, in create_column
    Column(name, type)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/operations.py", line 365, in add_column
    schema=schema
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/ddl/impl.py", line 127, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/alembic/ddl/impl.py", line 76, in _exec
    conn.execute(construct, *multiparams, **params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1651, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 717, in execute
    return meth(self, multiparams, params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 67, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 771, in _execute_ddl
    compiled
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 927, in _execute_context
    context)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1076, in _handle_dbapi_exception
    exc_info
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 920, in _execute_context
    context)
  File "/usr/local/Cellar/python/2.7.6/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 425, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) relation "person" does not exist
 'ALTER TABLE person ADD COLUMN age INTEGER' {}

[bevanj]

I am also experiencing this issue. Sorry I don't have anything more to add (Postgresql 9.3)

[pudo]

Fixed in a95c7be

[smnorris]

Thanks!

[jensfinnas]

I'm still experiencing the same issue here.

>>> db = dataset.connect(os.environ["DB_URL"], schema="1")
>>> table = db['person']
>>> print(table.columns)
['id']
>>> table.insert(dict(name='John Doe', age=46))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/dataset/persistence/table.py", line 72, in insert
    self._ensure_columns(row, types=types)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/dataset/persistence/table.py", line 214, in _ensure_columns
    self.create_column(column, _type)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/dataset/persistence/table.py", line 241, in create_column
    Column(name, type)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/alembic/operations.py", line 595, in add_column
    schema=schema
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/alembic/ddl/impl.py", line 176, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/alembic/ddl/impl.py", line 122, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1989, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/Jens/Dropbox/journalism++/jsurveillance/Scraper/env/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "person" does not exist
 [SQL: 'ALTER TABLE person ADD COLUMN age INTEGER']

[smnorris]

Yes, me too. I'm going to try and make a test for this.

[smnorris]

I can fix this one issue by specifying the schema in create_column of table.py

    def create_column(self, name, type):
        """
        Explicitely create a new column ``name`` of a specified type.
        ``type`` must be a `SQLAlchemy column type <http://docs.sqlalchemy.org/en/rel_0_8/core/types.html>`_.
        ::

            table.create_column('created_at', sqlalchemy.DateTime)
        """
        self._check_dropped()
        self.database._acquire()

        try:
            if normalize_column_name(name) not in self._normalized_columns:
                self.database.op.add_column(
                    self.table.name,
                    Column(name, type),
                    self.table.schema
                )
                self.table = self.database.update_table(self.table.name)
        finally:
            self.database._release()

However, running the complete test_persistence.py when connected to a specific postgres schema has more problems.

======================================================================
ERROR: test_query (test.test_persistence_pg.DatabaseTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test/test_persistence_pg.py", line 135, in test_query
    r = self.db.query('SELECT COUNT(*) AS num FROM weather').next()
  File "dataset/persistence/database.py", line 294, in query
    return ResultIter(self.executable.execute(query, **kw),
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1989, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (psycopg2.ProgrammingError) relation "weather" does not exist
LINE 1: SELECT COUNT(*) AS num FROM weather
                                    ^
 [SQL: 'SELECT COUNT(*) AS num FROM weather']

Sending a plain query text string fails unless table names in the string are schema qualified. This makes sense and https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas has some ideas on how to deal with it. I tried the to set the path via event listener but the test seems to be creating too many connections and postgres complains.

Also, the test has failures like this when connected to postgres

======================================================================
ERROR: test_find_one (test.test_persistence_pg.RowTypeTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test/test_persistence_pg.py", line 372, in test_find_one
    assert d.temperature == -10, d
AttributeError: 'OrderedDict' object has no attribute 'temperature'

Maybe just a setting somewhere in sqlalchemy or psycopg2 to get the query results accessible as properties but I'm leaving this for now.

[jvidin]

Hi all , wondering if you can help, i can't find how to specify the schema to make my inserts, im using postgres. Thank you for all the great work with dataset

[pudo]

@jvidin I've incorporated the fix suggested by @smnorris - can you check that fixes the issue for you, as well?

[pudo]

Fixed.