0

I tried and sadly failed to use Python SQLAlchemy ORM with MonetDB and database schema.

A minimal example to demonstrate my problem is the following:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

engine = create_engine(f"monetdb://monetdb:monetdb@localhost:50000/demo")
connection = engine.connect()

Session = sessionmaker(bind=engine)
session = Session()

class Template(object):

    __table_args__ = ({"schema": "test"}, )

Base = declarative_base(cls=Template)

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)

schemas = [name[0] for name in connection.execute("SELECT name FROM sys.schemas")]
if not "test" in schemas:
    connection.execute("CREATE SCHEMA test")

Base.metadata.create_all(bind=engine)

session.add_all([User(name="a"), User(name="b"), User(name="c")])
session.commit()

print(session.query(User).one())

This should work with a clean/empty MonetDB database (e.g. the demo one in Windows).

If the above example is run, it throws an error similar to the following:

Traceback (most recent call last):
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1278, in _execute_context
    cursor, statement, parameters, context
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\cursors.py", line 165, in execute
    block = self.connection.execute(query)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 140, in execute
    return self.command('s' + query + '\n;')
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 145, in command
    return self.mapi.cmd(command)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\mapi.py", line 266, in cmd
    raise exception(msg)
pymonetdb.exceptions.OperationalError: 42000!TODO: column names of level >= 3


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "test.py", line 42, in <module>
    print(session.query(User).one())
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3458, in one
    ret = self.one_or_none()
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3427, in one_or_none
    ret = list(self)
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3503, in __iter__
    return self._execute_and_instances(context)
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\orm\query.py", line 3528, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1014, in execute
    return meth(self, multiparams, params)
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1133, in _execute_clauseelement
    distilled_params,
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1318, in _execute_context
    e, statement, parameters, cursor, context
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1512, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
    raise exception
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\base.py", line 1278, in _execute_context
    cursor, statement, parameters, context
  File "C:\some\path\Anaconda3\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\cursors.py", line 165, in execute
    block = self.connection.execute(query)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 140, in execute
    return self.command('s' + query + '\n;')
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\sql\connections.py", line 145, in command
    return self.mapi.cmd(command)
  File "C:\some\path\Anaconda3\lib\site-packages\pymonetdb\mapi.py", line 266, in cmd
    raise exception(msg)
sqlalchemy.exc.OperationalError: (pymonetdb.exceptions.OperationalError) 42000!TODO: column names of level >= 3

[SQL: SELECT test.users.id AS test_users_id, test.users."name" AS test_users_name
FROM test.users]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

And here is how the log from a freshly started MonetDB server on Windows could like in this scenario:

# MonetDB 5 server v11.37.7 (Jun2020)
# Serving database 'demo', using 8 threads
# Compiled for x86_64-pc-winnt/64bit
# Found 63.847 GiB available main-memory of which we use 52.036 GiB
# Copyright (c) 1993 - July 2008 CWI.
# Copyright (c) August 2008 - 2020 MonetDB B.V., all rights reserved
# Visit https://www.monetdb.org/ for further information
# Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
# SQL catalog created, loading sql scripts once
# loading sql script: 09_like.sql
# loading sql script: 10_math.sql
# loading sql script: 12_url.sql
# loading sql script: 13_date.sql
# loading sql script: 14_inet.sql
# loading sql script: 15_querylog.sql
# loading sql script: 16_tracelog.sql
# loading sql script: 17_temporal.sql
# loading sql script: 18_index.sql
# loading sql script: 20_vacuum.sql
# loading sql script: 21_dependency_views.sql
# loading sql script: 22_clients.sql
# loading sql script: 23_skyserver.sql
# loading sql script: 25_debug.sql
# loading sql script: 26_sysmon.sql
# loading sql script: 27_rejects.sql
# loading sql script: 39_analytics.sql
# loading sql script: 40_json.sql
# loading sql script: 41_md5sum.sql
# loading sql script: 45_uuid.sql
# loading sql script: 46_profiler.sql
# loading sql script: 51_sys_schema_extension.sql
# loading sql script: 58_hot_snapshot.sql
# loading sql script: 60_wlcr.sql
# loading sql script: 61_wlcr.sql
# loading sql script: 75_storagemodel.sql
# loading sql script: 80_statistics.sql
# loading sql script: 80_udf.sql
# loading sql script: 81_tracer.sql
# loading sql script: 90_generator.sql
# loading sql script: 99_system.sql
# MonetDB/SQL module loaded
# MonetDB server is started. To stop server press Ctrl-C.
#client1: createExceptionInternal: !ERROR: ParseException:SQLparser:42000!TODO: column names of level >= 3

It seems that the query

SELECT test.users.id AS test_users_id, test.users."name" AS test_users_name FROM test.users

can't be handeled correctly by the MonetDB API/driver.

Related bug reports can be found, too:

Sadly, as the bugs were first mentioned in approx. 2010 this issue probably won't get fixed soon (or never at all).

And finally here is some version information:

  • System: Windows 10 1809
  • MonetDB: 20200529
  • python: 3.7.7
  • pymonetdb: 1.3.1
  • sqlalchemy: 1.3.18
  • sqlalchemy-monetdb: 1.0.0

Does anyone know a way to workaround this issue e.g by telling SQLAlchemy ORM to use temporary aliases, etc.?

P. B.
  • 587
  • 6
  • 12

1 Answers1

0

Indeed, MonetDB still doesn't support more than two levels of naming. It's still on our list though and your question has just increased its position.

I don't know much about SQLAlchemy. Any chance you can find a workaround for this problem?

Jennie
  • 345
  • 1
  • 8