Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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.

On Thu, Mar 29, 2018 at 10:59 AM, Maki  wrote:
> 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  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  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  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:

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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?

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

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[Mike Bayer]

On Thu, Mar 29, 2018 at 10:46 AM, Maki  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  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  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)

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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

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  
> 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  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  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 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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  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  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  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 
>> >> >   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 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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*



Am Donnerstag, 29. März 2018 15:58:33 UTC+2 schrieb Mike Bayer:
>
> On Thu, Mar 29, 2018 at 9:18 AM, Maki  
> 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  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  
> >> >   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  
> >> > 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() 
> >> >   

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[Mike Bayer]

On Thu, Mar 29, 2018 at 9:18 AM, Maki  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  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 
>> >   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 
>> > 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 = 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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

Am Donnerstag, 29. März 2018 15:31:02 UTC+2 schrieb Mike Bayer:
>
> missing a dot: 
>
> import pyodbc 
>
> pyodbc_connection = pyodbc.connect(...) 
>
> print(pyodbc_connection.getinfo(pyodbc.SQL_DBMS_VER)) 
>
>
>
>
> On Thu, Mar 29, 2018 at 9:29 AM, Mike Bayer  > wrote: 
> >> 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 
> > 
> > 
> > On Thu, Mar 29, 2018 at 9:18 AM, Maki  > 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) 
> >> 
> >> 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  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  
> >>> >   cnxn = engine.connect() 
> >>> > TypeError: 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[Maki]

Hi Mike!

Both gives me the following error message:

Traceback (most recent call last):
  File "db_test.py", line 17, in 
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>:]\r\n[Location: 
]\r\n[%msg: < User defined SQL function 'SQLUSER.SERVERPROPERTY' 
does not exist>] (359) (SQLExecDirectW)")




Am Donnerstag, 29. März 2018 15:29:20 UTC+2 schrieb 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 
>
>
> On Thu, Mar 29, 2018 at 9:18 AM, Maki  
> 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) 
> > 
> > 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  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  
> >> >   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. 
> 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[Mike Bayer]

missing a dot:

import pyodbc

pyodbc_connection = pyodbc.connect(...)

print(pyodbc_connection.getinfo(pyodbc.SQL_DBMS_VER))




On Thu, Mar 29, 2018 at 9:29 AM, Mike Bayer  wrote:
>> 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
>
>
> On Thu, Mar 29, 2018 at 9:18 AM, Maki  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)
>>
>> 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  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 
>>> >   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 ()
>>> > 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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


On Thu, Mar 29, 2018 at 9:18 AM, Maki  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)
>
> 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  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 
>> >   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 
>> > 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, 

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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.


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  
> 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  
> >   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  
> > 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__ 
> > 

Re: [sqlalchemy] Using __getattr__ and __setattr__ with sqlalchemy

[Mike Bayer]

send a complete MCVE please

On Thu, Mar 29, 2018 at 2:08 AM, Tolstov Sergey  wrote:
> Before update it worked. But today i have a exception AttributeError: type
> object 'Base' has no attribute '__getattr__'
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] SQLAlchemy with DSN InterSystems ODBC35

[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  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 
>   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 
> 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
> 

[sqlalchemy] Re: Using __getattr__ and __setattr__ with sqlalchemy

[Tolstov Sergey]

def __getattr__ (self2, key2):
  if False:
pass
  elif key2 == "attr_new":
 self2.attr_new = sqlalchemy.Column(UUID, sqlalchemy. 
ForeignKey(self.RELATED_CLASS.mRID), nullable = True)
  else:
return getattr(self2.__class__, key2)
  return getattr(self2, key2)

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Using __getattr__ and __setattr__ with sqlalchemy

[Tolstov Sergey]

Solved,
return getattr(self2.__class__, key2)

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] rollback() practice

[Simon King]

On Thu, Mar 29, 2018 at 8:57 AM,   wrote:
> Hello,
>
> I had some database problems (unfinished transactions) so I have wrapped
> sqlalchemy database handling functions in following way:
>
> def add(entry, session):
> """Wrapper for db_session.add"""
> try:
> return session.add(entry)
> except Exception as e:
> session.rollback()
> raise e
>
> def query(ModelClass, session):
> """Wrapper for session.query(Class)"""
> try:
> return session.query(ModelClass)
> except Exception as e:
> session.rollback()
> raise e
>
> def commit(session):
> """Wrapper for session.commit"""
> try:
> return session.commit()
> except Exception as e:
> session.rollback()
> raise e
>
> def delete(entry, session):
> """Wrapper for session.delete"""
> try:
> return session.delete(entry)
> except Exception as e:
> session.rollback()
> raise e
>
> Is this a good practice?
>

In general, you should do what makes the most sense for your
application, and if this style works for you, that's fine. It's
difficult to say more without seeing how you use these functions. A
more common pattern is to put the session management "around" the rest
of your application code, and handle exceptions in just one place.
There's a section in the docs with more details:

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate

Hope that helps,

Simon

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] rollback() practice

[l00p1n6]

Hello,

I had some database problems (unfinished transactions) so I have wrapped 
sqlalchemy database handling functions in following way:

def add(entry, session):
"""Wrapper for db_session.add"""
try:
return session.add(entry)
except Exception as e:
session.rollback()
raise e

def query(ModelClass, session):
"""Wrapper for session.query(Class)"""
try:
return session.query(ModelClass)
except Exception as e:
session.rollback()
raise e

def commit(session):
"""Wrapper for session.commit"""
try:
return session.commit()
except Exception as e:
session.rollback()
raise e

def delete(entry, session):
"""Wrapper for session.delete"""
try:
return session.delete(entry)
except Exception as e:
session.rollback()
raise e

Is this a good practice?

Note: database problems did not repeat so I don't really know if those 
rollbacks helped me

Thank you!

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to create multiple TABLES and INSERTS programatically ?

[Prashanth Budihal]

>
> Thanks Mike for that quick help. I will try write a new code and test it 
> out.

All 200+ tables are same except the inserted data and table names vary.
I will get back to you after I modify my code. 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Using __getattr__ and __setattr__ with sqlalchemy

[Tolstov Sergey]

Before update it worked. But today i have a exception AttributeError: type 
object 'Base' has no attribute '__getattr__'

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.