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 .
On Thu, Mar 29, 2018 at 10:22 AM, Maki <[email protected]> wrote: > 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 > > > > Am Donnerstag, 29. März 2018 15:58:33 UTC+2 schrieb Mike Bayer: >> >> On Thu, Mar 29, 2018 at 9:18 AM, Maki <[email protected]> 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? >> >> >> >> > >> > 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. >> > >> > >> > Am Donnerstag, 29. März 2018 15:01:46 UTC+2 schrieb 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! >> >> >> >> >> >> >> >> On Thu, Mar 29, 2018 at 7:03 AM, Maki <[email protected]> wrote: >> >> > 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' >> >> > >> >> > -- >> >> > 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 [email protected]. >> >> > To post to this group, send email to [email protected]. >> >> > Visit this group at https://groups.google.com/group/sqlalchemy. >> >> > For more options, visit https://groups.google.com/d/optout. >> > >> > -- >> > 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 [email protected]. >> > To post to this group, send email to [email protected]. >> > Visit this group at https://groups.google.com/group/sqlalchemy. >> > For more options, visit https://groups.google.com/d/optout. > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
