SQLAlchemy with DSN InterSystems ODBC35

[Maki]

Im on a Windows OS and Python 3.6.4 and im stucked with a problem trying to connect to a DSN Datasource using SQLAlchemy 1.2.5

Using pyodbc to connect to the DSN Datasource works great but using SQLAlchemy create_engine method:

engine = create_engine("mssql+pyodbc://user:pass@mydsn", echo=True)

get me the following error:

 File "mentrix.py", line 28, in <module>
  cnxn = engine.connect()
TypeError: '<' not supported between instances of 'str' and 'int'

Can anyone point me on the right direction. Like ive said im trying to use SQLAlchemy to connect to a InterSystem ODBC35 DSN Datasource.

Thanks in advanced!

Code:

from sqlalchemy import create_engine
import pyodbc

engine = create_engine("mssql+pyodbc://user:pass@mydsn", echo=True)
cnxn = engine.connect()
rows = cnxn.execute("SELECT name FROM sys.tables").fetchall()
print(rows)

Full traceback:

2018-03-29 11:33:44,631 INFO sqlalchemy.engine.base.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2018-03-29 11:33:44,631 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "mentrix.py", line 28, in <module>
    cnxn = engine.connect()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 784, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 532, in checkout
    rec = pool._do_get()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 1189, in _do_get
    self._dec_overflow()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 1186, in _do_get
    return self._create_connection()
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\pool.py", line 677, in __connect
    exec_once(self.connection, self)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\event\attr.py", line 274, in exec_once
    self(*args, **kw)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\event\attr.py", line 284, in __call__
    fn(*args, **kw)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1334, in go
    return once_fn(*arg, **kw)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\strategies.py", line 183, in first_connect
    dialect.initialize(c)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1931, in initialize
    super(MSDialect, self).initialize(connection)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\engine\default.py", line 267, in initialize
    self._get_default_schema_name(connection)
  File "C:\Users\m.m\Envs\mentrix\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1958, in _get_default_schema_name
    if self.server_version_info < MS_2005_VERSION:
TypeError: '<' not supported between instances of 'str' and 'int'

[Mike Bayer]

Hi -

unfortunately the stack trace indicates a bug in SQLAlchemy in that it
is not interpreting your database's version string correctly.

can you please provide me with the output of:

SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)

Additionally, the likely cause of your issue can be worked around at
the moment with this patch:

diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py
b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 14e8ae838..30b8b8b50 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -291,7 +291,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
try:
version.append(int(n))
except ValueError:
- version.append(n)
+ pass
return tuple(version)

def is_disconnect(self, e, connection, cursor):


https://bitbucket.org/zzzeek/sqlalchemy/issues/4227/sql-server-pyodbc-allows-non-int-tokens-in
is added.

I need to see your version string so I can understand what tokens I
need to weed out, thanks!

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Maki]

Hi Mike!

I think there's a misunderstanding. Im trying to connect to a InterSystem Cache Database thru ODBC (Datasource) but my MS SQL Server Management Studio has this version (12.0.5207.0)

I render the code in pyodbc.py and add the pass to the except clause but it doesnt help getting the same error message with the str and int like before.

[Mike Bayer]

> I render the code in pyodbc.py and add the pass to the except clause but it doesnt help getting the same error message with the str and int like before.

OK that may suggest that it is falling back to the base pyodbc version
management, so here is the updated patch:

diff --git a/lib/sqlalchemy/connectors/pyodbc.py
b/lib/sqlalchemy/connectors/pyodbc.py
index d8c3fcec4..ae2b740b0 100644
--- a/lib/sqlalchemy/connectors/pyodbc.py
+++ b/lib/sqlalchemy/connectors/pyodbc.py
@@ -145,7 +145,7 @@ class PyODBCConnector(Connector):

try:
version.append(int(n))
except ValueError:
- version.append(n)
+ pass
return tuple(version)

def set_isolation_level(self, connection, level):

diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py
b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 14e8ae838..30b8b8b50 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -291,7 +291,7 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
try:
version.append(int(n))
except ValueError:
- version.append(n)
+ pass
return tuple(version)

def is_disconnect(self, e, connection, cursor):

can you connect with pyodbc directly, then run this query:

cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)")
print(cursor.fetchone())

it may raise an exception, send me that if so, then run this:


print(pyodbc_connectiongetinfo(pyodbc.SQL_DBMS_VER)

so again I can see what we are getting

[Mike Bayer]

missing a dot:

import pyodbc

pyodbc_connection = pyodbc.connect(...)

print(pyodbc_connection.getinfo(pyodbc.SQL_DBMS_VER))

[Maki]

Hi Mike!

Both gives me the following error message:

Traceback (most recent call last):

  File "db_test.py", line 17, in <module>

    cursor.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)")

pyodbc.Error: ('HY000', "[HY000] [Cache ODBC][State : HY000][Native Code 359]\r\n[C:\\Users\\m.m\\Envs\\mentrix\\Scripts\\python.exe]\r\n[SQLCODE: <-359>:<SQL Function (function stored procedure) not found>]\r\n[Location: <Prepare>]\r\n[%msg: < User defined SQL function 'SQLUSER.SERVERPROPERTY' does not exist>] (359) (SQLExecDirectW)")

[Maki]

What i think is, in Cache Monitor Database there is no such thing as: SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) 

Not like in a MSSQL Server.

Im looking for ways to get the SERVERPROPERTY ProductVersion in Cache Monitor Database. Cant find how to query that in a Cache Monitor Database

[Mike Bayer]

On Thu, Mar 29, 2018 at 9:18 AM, Maki <mark....@randstad.at> wrote:

> Hi Mike!
>
> I think there's a misunderstanding. Im trying to connect to a InterSystem
> Cache Database thru ODBC (Datasource) but my MS SQL Server Management Studio
> has this version (12.0.5207.0)

oh, so the "InterSystem Cache Database", which I've never heard of, is
not a SQL Server variant. This would normally mean you can't use the
"mssql" dialect, at least directly, you'd have to locate / create a
dialect that is specific to this system.

Can you give me some background on the relationship of this database
to SQL Server and why you are using the "mssql" dialect? Depending
on how similar this system is to SQL Server (if at all) would
determine what a SQLAlchemy dialect looks like.

Additionally, the second pyodbc call I gave you is not specific to SQL Server:

import pyodbc

pyodbc_connection = pyodbc.connect(...)

print(pyodbc_connection.getinfo(pyodbc.SQL_DBMS_VER))

that should not be raising an exception, can you give me the output of
the above please?

[Maki]

Hi Mike,

here is the information about Cache Monitor. Its likely similar to MSSQL thats why i used the msql+pyodbc dialect.

https://www.cachemonitor.de/

Secondly about the print(pyodbc_connection.getinfo(pyodbc.SQL_DBMS_VER)) ==> OUTPUT: Cache Objects Version 2012.2.5.962 Cache xDBC Server 8.01

[Mike Bayer]

OK this database is an entirely distinct product that has no
particular resemblance to SQL server or anything else. You'd have
to create your own dialect for this. The most fundamental thing to
get working with a dialect is how to fetch identity values for integer
primary keys, for this database it looks like the LAST_IDENTITY
function: http://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_last_identity.
You would want to use the pyodbc connector. An overview for
custom dialect creation is at
https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst .

[Maki]

I would love to code a new dialect for SQLAlchemy but i guess my python skills are not there yet just started to learn python a few months ago.

What i dont understand is why i can connect to that Datasource using pyodbc.connect(mydsn) with no problems but SQLAlchemy cant?

Is there any other way to achieve this in SQLAlchemy. I needed to copy all the tables from the InterSystem Cache ODBC Datasource to my MS SQL Server

[Mike Bayer]

On Thu, Mar 29, 2018 at 10:46 AM, Maki <mark....@randstad.at> wrote:
> I would love to code a new dialect for SQLAlchemy but i guess my python
> skills are not there yet just started to learn python a few months ago.
> What i dont understand is why i can connect to that Datasource using
> pyodbc.connect(mydsn) with no problems but SQLAlchemy cant?

SQLAlchemy does a lot more with the database connection once it gets
it. It has to know the server version in use, it queries about things
like unicode support and transaction isolation, and these queries all
need to work and deliver results in the expected format. It then
knows all about how to check for the existence of tables, how to get
all the data elements of tables and other structures, all of this is
database specific.

In this case it wouldn't be that hard to modify a few parts of the SQL
Server dialect just to get a connection up, that is, just fix that
version string issue, but you wouldn't be able to use much more than
that and you'd likely hit more problems as you go further.

[Maki]

In your knowledge do you think there is a way to use pyodbc to copy all the tables and data in the table in the datasource to ms sql server and then after copying it in the sql server use SQLAlchemy for further process?

[Mike Bayer]

sure but you need to know the structure of those tables. you might
want to see if there's a tool that already exists for this database
which does this. it seems to be heavy on the JDBC side so perhaps
there are java tools that do it.