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?
Am Donnerstag, 29. März 2018 16:54:29 UTC+2 schrieb Mike Bayer: > > On Thu, Mar 29, 2018 at 10:46 AM, Maki <[email protected] <javascript:>> > 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. > > > > > > 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]> 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] <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.
