SQLAlchemy, table def from MetaData

I’m currently designing an application and want to use SQLAlchemy (SA) to enable an easy switch between PostgreSQL and CockroachDB if I can prove to interested parties that there’s no downside to using a more modern approach.

I’m having a problem with my initial DB setup. I can successfully build all of the tables and constraints using SA but when I attempt to pull the table definition back from the database to set up an insert I get an error of:

2017-06-08 14:31:28,599 INFO sqlalchemy.engine.base.Engine SHOW COLUMNS FROM "title_dict"
2017-06-08 14:31:28,599 INFO sqlalchemy.engine.base.Engine {}
Traceback (most recent call last):
  File "bin/base_schema.py", line 54, in <module>
    populate_dicts(engine)
  File "$HOME/CGPaaS-db/cgpaas_db/dicts.py", line 21, in populate_dicts
    cockroachdb_populate_dicts(engine)
  File "$HOME/CGPaaS-db/cgpaas_db/dicts.py", line 29, in cockroachdb_populate_dicts
    ins = Table(tname, metadata, autoload=True).insert()
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 439, in __new__
    metadata._remove_table(name, schema)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 434, in __new__
    table._init(name, metadata, *args, **kw)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 514, in _init
    include_columns, _extend_on=_extend_on)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 540, in _autoload
    _extend_on=_extend_on
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2045, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1534, in run_callable
    return callable_(self, *args, **kwargs)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 372, in reflecttable
    table, include_columns, exclude_columns, **opts)
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 598, in reflecttable
    table_name, schema, **table.dialect_kwargs):
  File "$HOME/homebrew/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py", line 369, in get_columns
    **kw)
  File "$HOME/homebrew/lib/python3.6/site-packages/cockroachdb/sqlalchemy/dialect.py", line 96, in get_columns
    name, type_name, nullable, default = row
ValueError: too many values to unpack (expected 4)

I’ve included a minified version of the code, which is attempting to use a generic function to do dictionary inserts. vanilla_populate_dicts works for PostgreSQL and SQLite.

from sqlalchemy import *
from cockroachdb.sqlalchemy import run_transaction
import sqlalchemy.orm

dict_entries = {
                'title_dict': [
                    {'title': 'Mr.'},
                    {'title': 'Mrs.'},
                    {'title': 'Ms.'},
                    {'title': 'Dr.'},
                    {'title': 'Prof.'},
                ],
                'user_class_dict': [
                    {'description': 'owner'},
                    {'description': 'user'},
                ]
}

def populate_dicts(engine):
    if str(engine.url).startswith('cockroachdb'):
        cockroachdb_populate_dicts(engine)
    else:
        vanilla_populate_dicts(engine)

def cockroachdb_populate_dicts(engine):
    metadata = MetaData(engine)
    for tname in dict_entries:
        sessionmaker = sqlalchemy.orm.sessionmaker(engine)
        ins = Table(tname, metadata, autoload=True).insert()
        def callback(session):
            for vals in dict_entries[tname]:
                item = ins.values(**vals)
                session.add(item)
    run_transaction(sessionmaker, callback)


def vanilla_populate_dicts(engine):
    metadata = MetaData(engine)
    with engine.begin() as connection:
        for tname in dict_entries:
            with connection.begin() as trans:
                ins = Table(tname, metadata, autoload=True).insert()
                for vals in dict_entries[tname]:
                    connection.execute(ins, **vals)

I’m aware that I may not quite have the run_transaction bit quite right just yet but I can’t get past this.

Tables:

root@:26257/> SHOW COLUMNS FROM caas.title_dict;
+-------+------------+-------+----------------+--------------------------------+
| Field |    Type    | Null  |    Default     |            Indices             |
+-------+------------+-------+----------------+--------------------------------+
| id    | INT        | false | unique_rowid() | {primary,title_dict_title_key} |
| title | STRING(16) | false | NULL           | {title_dict_title_key}         |
+-------+------------+-------+----------------+--------------------------------+
(2 rows)
root@:26257/> SHOW COLUMNS FROM caas.user_class_dict;
+-------------+-------------+-------+----------------+-------------------------------------------+
|    Field    |    Type     | Null  |    Default     |                  Indices                  |
+-------------+-------------+-------+----------------+-------------------------------------------+
| id          | INT         | false | unique_rowid() | {primary,user_class_dict_description_key} |
| description | STRING(128) | false | NULL           | {user_class_dict_description_key}         |
+-------------+-------------+-------+----------------+-------------------------------------------+
(2 rows)

It turns out the implementation is out of date and requires functionality not in cockroachDbs core. The problem with column mapping is fixed with:

$ git diff cockroachdb/sqlalchemy/dialect.py
diff --git a/cockroachdb/sqlalchemy/dialect.py b/cockroachdb/sqlalchemy/dialect.py
index 3388332..e92e44f 100644
--- a/cockroachdb/sqlalchemy/dialect.py
+++ b/cockroachdb/sqlalchemy/dialect.py
@@ -97,7 +97,7 @@ class CockroachDBDialect(PGDialect_psycopg2):
         res = []
         # TODO(bdarnell): escape table name
         for row in conn.execute('SHOW COLUMNS FROM "%s"' % table_name):
-            name, type_name, nullable, default = row
+            name, type_name, nullable, default, indices = row
             # TODO(bdarnell): when there are type parameters, attach
             # them to the returned type object.
             try:

This only moves to the next issue as pg_table_is_visible() needs to be implemented.

Raised as missing core functionality already here: https://github.com/cockroachdb/cockroach/issues/12538#issuecomment-307140045

And reported cockroachdb/sqlalchemy issue here: https://github.com/cockroachdb/cockroachdb-python/issues/27