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 Am Donnerstag, 29. März 2018 16:37:58 UTC+2 schrieb 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 . > > > > On Thu, Mar 29, 2018 at 10:22 AM, Maki <[email protected] <javascript:>> > 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
