Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

 OK thanks for looking into this and I'm glad we found the issue,
mostly thanks to your prompt and accurate replies so thanks!

On Wed, Jul 5, 2017 at 9:46 AM, Paul Morel
 wrote:
> It is possible that in the end that was the case, however, the reason I even
> attempted the pip uninstall/install originally was because of the error I
> was getting.  I found some SO posts that recommended rolling back to an
> earlier version of SQLAlchemy.  Rolling back had no impact.  I then rolled
> forward to the latest, also with no impact.  Presumably those
> roll-back/roll-forward attempts didn't work because they were still using
> the 1.1.9 version C extensions that ship with Anaconda.
>
> https://docs.continuum.io/anaconda/packages/pkg-docs
>
> I am trying to get my hands on another similar environment but it appears I
> don't have installation rights on that machine at the moment.  I will see if
> I can reproduce this problem if I can get install privileges.
>
>
>
>
> On Wednesday, July 5, 2017 at 7:47:25 AM UTC-5, Mike Bayer wrote:
>>
>>
>>
>> On Jul 5, 2017 12:59 AM, "Paul Morel" 
>> wrote:
>>
>> It appears that the Anaconda installer placed the .pyd files there.  I
>> discovered that pip uninstall doesn't remove those files like I had assumed.
>> Therefore, when I pip uninstalled/installed the original .pyd files were
>> still there.
>>
>>
>>
>> Ok, would this be simple issue of old version of .pyd files not matching a
>> pip updated version of the python code ?   That is, you manually updated
>> SQLAlchemy from 1.0.x to 1.1.x.   the c extension from 1.0 would not be
>> compatible as there were some changes.
>>
>>
>>
>>
>> It looks like deleting the .pyd files and pip installing again does not
>> recreate the pyd files.  So it appears the Anaconda distribution of
>> SQLAlchemy included may be the culprit here.
>>
>>
>> On Tuesday, July 4, 2017 at 8:52:22 PM UTC-5, Mike Bayer wrote:
>>>
>>> Ok how did the pyd file get there in the first place, was that pre-built
>>> as part of anaconda or did it compile on your local machine?   This is kind
>>> of a serious issue that a broken shared library got created
>>>
>>> On Jul 4, 2017 7:55 PM, "Paul Morel" 
>>> wrote:
>>>
>>> BAM!  That was it.  There must be some type of incompatibility between
>>> the c extensions and the Windows 2008 Server configuration.  I pip
>>> uninstalled/installed and found the c extensions where rebuilt.  After
>>> removing cresultproxy.pyd from the sqlalchemy site-packages directory the
>>> queries started working again.
>>>
>>> Many thanks...
>>>
>>>
>>> On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:

 it's the select schema_name() query that's failing and that
 cursor.description is fine.

 did you build the SQLAlchemy C extensions on windows?   or does your
 anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your
 installation ?my last theory here is that you have C extensions
 set up and somehow they built such that PyLong_CheckExact(0) is
 returning false.if you have any .dll's (or whatever Windows uses
 for native Python extensions these days), try blowing those away for
 SQLAlchemy.


 On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel
  wrote:
 > Sorry, I gave you the cursor.description of the schema_name() query.
 > This
 > is the output for the cursor.description for the SELECT * FROM
 > EPO_MODELS
 > query:
 >
 > pymssql Cursor Description is:
 >>
 >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
 >> None,
 >> None, N
 >> one, None), (u'MODELNAME', 1, None, None, None, None, None),
 >> (u'MEMO', 1,
 >> None,
 >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
 >> None), (u'
 >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3,
 >> None,
 >> None, No
 >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
 >> (u'DATEALTER
 >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None,
 >> None,
 >> None, No
 >> ne, None))
 >
 >
 >
 > pyodbc Cursor Description is:
 >>
 >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
 >> None,
 >> None, N
 >> one, None), (u'MODELNAME', 1, None, None, None, None, None),
 >> (u'MEMO', 1,
 >> None,
 >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
 >> None), (u'
 >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3,
 >> None,
 >> None, No
 >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
 >> (u'DATEALTER
 >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None,
 >> None,
 >> None, No
 >> ne, None))
 >
 >
 >
 > On Tuesday, July 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

It is possible that in the end that was the case, however, the reason I 
even attempted the pip uninstall/install originally was because of the 
error I was getting.  I found some SO posts that recommended rolling back 
to an earlier version of SQLAlchemy.  Rolling back had no impact.  I then 
rolled forward to the latest, also with no impact.  Presumably those 
roll-back/roll-forward attempts didn't work because they were still using 
the 1.1.9 version C extensions that ship with Anaconda.

https://docs.continuum.io/anaconda/packages/pkg-docs

I am trying to get my hands on another similar environment but it appears I 
don't have installation rights on that machine at the moment.  I will see 
if I can reproduce this problem if I can get install privileges.




On Wednesday, July 5, 2017 at 7:47:25 AM UTC-5, Mike Bayer wrote:
>
>
>
> On Jul 5, 2017 12:59 AM, "Paul Morel"  > wrote:
>
> It appears that the Anaconda installer placed the .pyd files there.  I 
> discovered that pip uninstall doesn't remove those files like I had 
> assumed.  Therefore, when I pip uninstalled/installed the original .pyd 
> files were still there.
>
>
>
> Ok, would this be simple issue of old version of .pyd files not matching a 
> pip updated version of the python code ?   That is, you manually updated 
> SQLAlchemy from 1.0.x to 1.1.x.   the c extension from 1.0 would not be 
> compatible as there were some changes.
>
>
>
>
> It looks like deleting the .pyd files and pip installing again *does not* 
> recreate the pyd files.  So it appears the Anaconda distribution of 
> SQLAlchemy included may be the culprit here.
>
>
> On Tuesday, July 4, 2017 at 8:52:22 PM UTC-5, Mike Bayer wrote:
>
>> Ok how did the pyd file get there in the first place, was that pre-built 
>> as part of anaconda or did it compile on your local machine?   This is kind 
>> of a serious issue that a broken shared library got created 
>>
>> On Jul 4, 2017 7:55 PM, "Paul Morel"  
>> wrote:
>>
>> BAM!  That was it.  There must be some type of incompatibility between 
>> the c extensions and the Windows 2008 Server configuration.  I pip 
>> uninstalled/installed and found the c extensions where rebuilt.  After 
>> removing *cresultproxy.pyd* from the sqlalchemy site-packages directory 
>> the queries started working again.
>>
>> Many thanks...
>>
>>
>> On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:
>>
>>> it's the select schema_name() query that's failing and that 
>>> cursor.description is fine. 
>>>
>>> did you build the SQLAlchemy C extensions on windows?   or does your 
>>> anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your 
>>> installation ?my last theory here is that you have C extensions 
>>> set up and somehow they built such that PyLong_CheckExact(0) is 
>>> returning false.if you have any .dll's (or whatever Windows uses 
>>> for native Python extensions these days), try blowing those away for 
>>> SQLAlchemy. 
>>>
>>>
>>> On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel 
>>>  wrote: 
>>> > Sorry, I gave you the cursor.description of the schema_name() query.  
>>> This 
>>> > is the output for the cursor.description for the SELECT * FROM 
>>> EPO_MODELS 
>>> > query: 
>>> > 
>>> > pymssql Cursor Description is: 
>>> >> 
>>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
>>> None, 
>>> >> None, N 
>>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), 
>>> (u'MEMO', 1, 
>>> >> None, 
>>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
>>> >> None), (u' 
>>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, 
>>> None, 
>>> >> None, No 
>>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
>>> >> (u'DATEALTER 
>>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, 
>>> None, 
>>> >> None, No 
>>> >> ne, None)) 
>>> > 
>>> > 
>>> > 
>>> > pyodbc Cursor Description is: 
>>> >> 
>>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
>>> None, 
>>> >> None, N 
>>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), 
>>> (u'MEMO', 1, 
>>> >> None, 
>>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
>>> >> None), (u' 
>>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, 
>>> None, 
>>> >> None, No 
>>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
>>> >> (u'DATEALTER 
>>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, 
>>> None, 
>>> >> None, No 
>>> >> ne, None)) 
>>> > 
>>> > 
>>> > 
>>> > On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote: 
>>> >> 
>>> >> in your direct examples can you show me the output of 
>>> >> "cursor.description" as well once you execute the query? 
>>> >> 
>>> >> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel 
>>> >>  wrote: 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

On Jul 5, 2017 12:59 AM, "Paul Morel" 
wrote:

It appears that the Anaconda installer placed the .pyd files there.  I
discovered that pip uninstall doesn't remove those files like I had
assumed.  Therefore, when I pip uninstalled/installed the original .pyd
files were still there.



Ok, would this be simple issue of old version of .pyd files not matching a
pip updated version of the python code ?   That is, you manually updated
SQLAlchemy from 1.0.x to 1.1.x.   the c extension from 1.0 would not be
compatible as there were some changes.




It looks like deleting the .pyd files and pip installing again *does not*
recreate the pyd files.  So it appears the Anaconda distribution of
SQLAlchemy included may be the culprit here.


On Tuesday, July 4, 2017 at 8:52:22 PM UTC-5, Mike Bayer wrote:

> Ok how did the pyd file get there in the first place, was that pre-built
> as part of anaconda or did it compile on your local machine?   This is kind
> of a serious issue that a broken shared library got created
>
> On Jul 4, 2017 7:55 PM, "Paul Morel"  wrote:
>
> BAM!  That was it.  There must be some type of incompatibility between the
> c extensions and the Windows 2008 Server configuration.  I pip
> uninstalled/installed and found the c extensions where rebuilt.  After
> removing *cresultproxy.pyd* from the sqlalchemy site-packages directory
> the queries started working again.
>
> Many thanks...
>
>
> On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:
>
>> it's the select schema_name() query that's failing and that
>> cursor.description is fine.
>>
>> did you build the SQLAlchemy C extensions on windows?   or does your
>> anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your
>> installation ?my last theory here is that you have C extensions
>> set up and somehow they built such that PyLong_CheckExact(0) is
>> returning false.if you have any .dll's (or whatever Windows uses
>> for native Python extensions these days), try blowing those away for
>> SQLAlchemy.
>>
>>
>> On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel
>>  wrote:
>> > Sorry, I gave you the cursor.description of the schema_name() query.
>> This
>> > is the output for the cursor.description for the SELECT * FROM
>> EPO_MODELS
>> > query:
>> >
>> > pymssql Cursor Description is:
>> >>
>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
>> None,
>> >> None, N
>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO',
>> 1,
>> >> None,
>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
>> >> None), (u'
>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3,
>> None,
>> >> None, No
>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
>> >> (u'DATEALTER
>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None,
>> None,
>> >> None, No
>> >> ne, None))
>> >
>> >
>> >
>> > pyodbc Cursor Description is:
>> >>
>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
>> None,
>> >> None, N
>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO',
>> 1,
>> >> None,
>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
>> >> None), (u'
>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3,
>> None,
>> >> None, No
>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
>> >> (u'DATEALTER
>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None,
>> None,
>> >> None, No
>> >> ne, None))
>> >
>> >
>> >
>> > On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote:
>> >>
>> >> in your direct examples can you show me the output of
>> >> "cursor.description" as well once you execute the query?
>> >>
>> >> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel
>> >>  wrote:
>> >> > I ran that select statement under both the pyodbc and pymssql direct
>> >> > connections and it appears to have returned a legitimate row of
>> data.
>> >> > This
>> >> > is the excerpt from the test output:
>> >> >
>> >> >>  Testing pymssql Directly
>> >> >> 
>> >> >>  Complete 
>> >> >>  Testing Get Schema Name with pymssql
>> >> >> (u'dbo',)
>> >> >>  Complete 
>> >> >>  Testing pyodbc Directly
>> >> >> 
>> >> >>  Complete 
>> >> >>  Testing Get Schema Name with pyodbc
>> >> >> (u'dbo', )
>> >> >>  Complete 
>> >> >>  Testing SQLAlchemy Connection using pymssql
>> >> >> 
>> >> >>  Complete 
>> >> >
>> >> >
>> >> > I switched over the SQLAlchemy connection string to use pymssql.  It
>> now
>> >> > looks like this:
>> >> >
>> >> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP'
>> >> >
>> >> > The same error is produced.  I attempted to run the SELECT
>> schema_name()
>> >> > query through the SQLAlchemy connection to see what was coming back
>> but
>> >> > it
>> >> > failed in the 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

It appears that the Anaconda installer placed the .pyd files there.  I 
discovered that pip uninstall doesn't remove those files like I had 
assumed.  Therefore, when I pip uninstalled/installed the original .pyd 
files were still there.

It looks like deleting the .pyd files and pip installing again *does not* 
recreate the pyd files.  So it appears the Anaconda distribution of 
SQLAlchemy included may be the culprit here.


On Tuesday, July 4, 2017 at 8:52:22 PM UTC-5, Mike Bayer wrote:
>
> Ok how did the pyd file get there in the first place, was that pre-built 
> as part of anaconda or did it compile on your local machine?   This is kind 
> of a serious issue that a broken shared library got created 
>
> On Jul 4, 2017 7:55 PM, "Paul Morel"  > wrote:
>
> BAM!  That was it.  There must be some type of incompatibility between the 
> c extensions and the Windows 2008 Server configuration.  I pip 
> uninstalled/installed and found the c extensions where rebuilt.  After 
> removing *cresultproxy.pyd* from the sqlalchemy site-packages directory 
> the queries started working again.
>
> Many thanks...
>
>
> On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:
>
>> it's the select schema_name() query that's failing and that 
>> cursor.description is fine. 
>>
>> did you build the SQLAlchemy C extensions on windows?   or does your 
>> anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your 
>> installation ?my last theory here is that you have C extensions 
>> set up and somehow they built such that PyLong_CheckExact(0) is 
>> returning false.if you have any .dll's (or whatever Windows uses 
>> for native Python extensions these days), try blowing those away for 
>> SQLAlchemy. 
>>
>>
>> On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel 
>>  wrote: 
>> > Sorry, I gave you the cursor.description of the schema_name() query.  
>> This 
>> > is the output for the cursor.description for the SELECT * FROM 
>> EPO_MODELS 
>> > query: 
>> > 
>> > pymssql Cursor Description is: 
>> >> 
>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
>> None, 
>> >> None, N 
>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 
>> 1, 
>> >> None, 
>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
>> >> None), (u' 
>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, 
>> None, 
>> >> None, No 
>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
>> >> (u'DATEALTER 
>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, 
>> None, 
>> >> None, No 
>> >> ne, None)) 
>> > 
>> > 
>> > 
>> > pyodbc Cursor Description is: 
>> >> 
>> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
>> None, 
>> >> None, N 
>> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 
>> 1, 
>> >> None, 
>> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
>> >> None), (u' 
>> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, 
>> None, 
>> >> None, No 
>> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
>> >> (u'DATEALTER 
>> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, 
>> None, 
>> >> None, No 
>> >> ne, None)) 
>> > 
>> > 
>> > 
>> > On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote: 
>> >> 
>> >> in your direct examples can you show me the output of 
>> >> "cursor.description" as well once you execute the query? 
>> >> 
>> >> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel 
>> >>  wrote: 
>> >> > I ran that select statement under both the pyodbc and pymssql direct 
>> >> > connections and it appears to have returned a legitimate row of 
>> data. 
>> >> > This 
>> >> > is the excerpt from the test output: 
>> >> > 
>> >> >>  Testing pymssql Directly 
>> >> >>  
>> >> >>  Complete  
>> >> >>  Testing Get Schema Name with pymssql 
>> >> >> (u'dbo',) 
>> >> >>  Complete  
>> >> >>  Testing pyodbc Directly 
>> >> >>  
>> >> >>  Complete  
>> >> >>  Testing Get Schema Name with pyodbc 
>> >> >> (u'dbo', ) 
>> >> >>  Complete  
>> >> >>  Testing SQLAlchemy Connection using pymssql 
>> >> >>  
>> >> >>  Complete  
>> >> > 
>> >> > 
>> >> > I switched over the SQLAlchemy connection string to use pymssql.  It 
>> now 
>> >> > looks like this: 
>> >> > 
>> >> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP' 
>> >> > 
>> >> > The same error is produced.  I attempted to run the SELECT 
>> schema_name() 
>> >> > query through the SQLAlchemy connection to see what was coming back 
>> but 
>> >> > it 
>> >> > failed in the same way because it is attempting its internal 
>> >> > schema_name() 
>> >> > query first. 
>> >> > 
>> >> > This is indeed quite strange since both the pyodbc and pymssql 
>> direct 
>> >> > connections did return a legit 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Jonathan Vanasco]

Is there a chance you updated the Python binary as part of your process?

Weird stuff like this often happens if you forget to trash the 
site-packages or virtualenv after a python version upgrade (even on a micro 
version upgrade)

-- 
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 won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

Ok how did the pyd file get there in the first place, was that pre-built as
part of anaconda or did it compile on your local machine?   This is kind of
a serious issue that a broken shared library got created

On Jul 4, 2017 7:55 PM, "Paul Morel"  wrote:

BAM!  That was it.  There must be some type of incompatibility between the
c extensions and the Windows 2008 Server configuration.  I pip
uninstalled/installed and found the c extensions where rebuilt.  After
removing *cresultproxy.pyd* from the sqlalchemy site-packages directory the
queries started working again.

Many thanks...


On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:

> it's the select schema_name() query that's failing and that
> cursor.description is fine.
>
> did you build the SQLAlchemy C extensions on windows?   or does your
> anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your
> installation ?my last theory here is that you have C extensions
> set up and somehow they built such that PyLong_CheckExact(0) is
> returning false.if you have any .dll's (or whatever Windows uses
> for native Python extensions these days), try blowing those away for
> SQLAlchemy.
>
>
> On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel
>  wrote:
> > Sorry, I gave you the cursor.description of the schema_name() query.
> This
> > is the output for the cursor.description for the SELECT * FROM
> EPO_MODELS
> > query:
> >
> > pymssql Cursor Description is:
> >>
> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
> None,
> >> None, N
> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO',
> 1,
> >> None,
> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
> >> None), (u'
> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None,
> >> None, No
> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
> >> (u'DATEALTER
> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None,
> >> None, No
> >> ne, None))
> >
> >
> >
> > pyodbc Cursor Description is:
> >>
> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None,
> None,
> >> None, N
> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO',
> 1,
> >> None,
> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
> >> None), (u'
> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None,
> >> None, No
> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
> >> (u'DATEALTER
> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None,
> >> None, No
> >> ne, None))
> >
> >
> >
> > On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote:
> >>
> >> in your direct examples can you show me the output of
> >> "cursor.description" as well once you execute the query?
> >>
> >> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel
> >>  wrote:
> >> > I ran that select statement under both the pyodbc and pymssql direct
> >> > connections and it appears to have returned a legitimate row of data.
> >> > This
> >> > is the excerpt from the test output:
> >> >
> >> >>  Testing pymssql Directly
> >> >> 
> >> >>  Complete 
> >> >>  Testing Get Schema Name with pymssql
> >> >> (u'dbo',)
> >> >>  Complete 
> >> >>  Testing pyodbc Directly
> >> >> 
> >> >>  Complete 
> >> >>  Testing Get Schema Name with pyodbc
> >> >> (u'dbo', )
> >> >>  Complete 
> >> >>  Testing SQLAlchemy Connection using pymssql
> >> >> 
> >> >>  Complete 
> >> >
> >> >
> >> > I switched over the SQLAlchemy connection string to use pymssql.  It
> now
> >> > looks like this:
> >> >
> >> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP'
> >> >
> >> > The same error is produced.  I attempted to run the SELECT
> schema_name()
> >> > query through the SQLAlchemy connection to see what was coming back
> but
> >> > it
> >> > failed in the same way because it is attempting its internal
> >> > schema_name()
> >> > query first.
> >> >
> >> > This is indeed quite strange since both the pyodbc and pymssql direct
> >> > connections did return a legit row when asking for the schema name.
> >> >
> >> > On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote:
> >> >>
> >> >> can you run this query please?
> >> >>
> >> >> SELECT schema_name()
> >> >>
> >> >> the issue is, that query is returning a result, there is a row, but
> it
> >> >> no columns, which is nonsensical.  Did you try running with the
> >> >> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need
> >> >> more info.
> >> >>
> >> >>
> >> >>
> >> >> On Tue, Jul 4, 2017 at 9:59 AM, Simon King 
> >> >> wrote:
> >> >> > The key part of the stack trace is:
> >> >> >
> >> >> > File "c:\Program
> >> >> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
>
> >> >> > line 1773, in 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

BAM!  That was it.  There must be some type of incompatibility between the 
c extensions and the Windows 2008 Server configuration.  I pip 
uninstalled/installed and found the c extensions where rebuilt.  After 
removing *cresultproxy.pyd* from the sqlalchemy site-packages directory the 
queries started working again.

Many thanks...

On Tuesday, July 4, 2017 at 4:34:38 PM UTC-5, Mike Bayer wrote:
>
> it's the select schema_name() query that's failing and that 
> cursor.description is fine. 
>
> did you build the SQLAlchemy C extensions on windows?   or does your 
> anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your 
> installation ?my last theory here is that you have C extensions 
> set up and somehow they built such that PyLong_CheckExact(0) is 
> returning false.if you have any .dll's (or whatever Windows uses 
> for native Python extensions these days), try blowing those away for 
> SQLAlchemy. 
>
>
> On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel 
>  wrote: 
> > Sorry, I gave you the cursor.description of the schema_name() query. 
>  This 
> > is the output for the cursor.description for the SELECT * FROM 
> EPO_MODELS 
> > query: 
> > 
> > pymssql Cursor Description is: 
> >> 
> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
> None, 
> >> None, N 
> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 
> 1, 
> >> None, 
> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
> >> None), (u' 
> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, 
> >> None, No 
> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
> >> (u'DATEALTER 
> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, 
> >> None, No 
> >> ne, None)) 
> > 
> > 
> > 
> > pyodbc Cursor Description is: 
> >> 
> >> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, 
> None, 
> >> None, N 
> >> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 
> 1, 
> >> None, 
> >> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
> >> None), (u' 
> >> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, 
> >> None, No 
> >> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
> >> (u'DATEALTER 
> >> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, 
> >> None, No 
> >> ne, None)) 
> > 
> > 
> > 
> > On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote: 
> >> 
> >> in your direct examples can you show me the output of 
> >> "cursor.description" as well once you execute the query? 
> >> 
> >> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel 
> >>  wrote: 
> >> > I ran that select statement under both the pyodbc and pymssql direct 
> >> > connections and it appears to have returned a legitimate row of data. 
> >> > This 
> >> > is the excerpt from the test output: 
> >> > 
> >> >>  Testing pymssql Directly 
> >> >>  
> >> >>  Complete  
> >> >>  Testing Get Schema Name with pymssql 
> >> >> (u'dbo',) 
> >> >>  Complete  
> >> >>  Testing pyodbc Directly 
> >> >>  
> >> >>  Complete  
> >> >>  Testing Get Schema Name with pyodbc 
> >> >> (u'dbo', ) 
> >> >>  Complete  
> >> >>  Testing SQLAlchemy Connection using pymssql 
> >> >>  
> >> >>  Complete  
> >> > 
> >> > 
> >> > I switched over the SQLAlchemy connection string to use pymssql.  It 
> now 
> >> > looks like this: 
> >> > 
> >> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP' 
> >> > 
> >> > The same error is produced.  I attempted to run the SELECT 
> schema_name() 
> >> > query through the SQLAlchemy connection to see what was coming back 
> but 
> >> > it 
> >> > failed in the same way because it is attempting its internal 
> >> > schema_name() 
> >> > query first. 
> >> > 
> >> > This is indeed quite strange since both the pyodbc and pymssql direct 
> >> > connections did return a legit row when asking for the schema name. 
> >> > 
> >> > On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote: 
> >> >> 
> >> >> can you run this query please? 
> >> >> 
> >> >> SELECT schema_name() 
> >> >> 
> >> >> the issue is, that query is returning a result, there is a row, but 
> it 
> >> >> no columns, which is nonsensical.  Did you try running with the 
> >> >> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need 
> >> >> more info. 
> >> >> 
> >> >> 
> >> >> 
> >> >> On Tue, Jul 4, 2017 at 9:59 AM, Simon King  
> >> >> wrote: 
> >> >> > The key part of the stack trace is: 
> >> >> > 
> >> >> > File "c:\Program 
> >> >> > 
> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", 
> >> >> > line 1773, in _get_default_schema_name default_schema_name = 
> >> >> > connection.scalar(query) 
> >> >> > 
> >> >> > ...which is in this function: 
> >> >> > 
> >> >> > 
> >> >> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

it's the select schema_name() query that's failing and that
cursor.description is fine.

did you build the SQLAlchemy C extensions on windows?   or does your
anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your
installation ?my last theory here is that you have C extensions
set up and somehow they built such that PyLong_CheckExact(0) is
returning false.if you have any .dll's (or whatever Windows uses
for native Python extensions these days), try blowing those away for
SQLAlchemy.


On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel
 wrote:
> Sorry, I gave you the cursor.description of the schema_name() query.  This
> is the output for the cursor.description for the SELECT * FROM EPO_MODELS
> query:
>
> pymssql Cursor Description is:
>>
>> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None,
>> None, N
>> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1,
>> None,
>> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
>> None), (u'
>> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None,
>> None, No
>> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
>> (u'DATEALTER
>> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None,
>> None, No
>> ne, None))
>
>
>
> pyodbc Cursor Description is:
>>
>> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None,
>> None, N
>> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1,
>> None,
>> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None,
>> None), (u'
>> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None,
>> None, No
>> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None),
>> (u'DATEALTER
>> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None,
>> None, No
>> ne, None))
>
>
>
> On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote:
>>
>> in your direct examples can you show me the output of
>> "cursor.description" as well once you execute the query?
>>
>> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel
>>  wrote:
>> > I ran that select statement under both the pyodbc and pymssql direct
>> > connections and it appears to have returned a legitimate row of data.
>> > This
>> > is the excerpt from the test output:
>> >
>> >>  Testing pymssql Directly
>> >> 
>> >>  Complete 
>> >>  Testing Get Schema Name with pymssql
>> >> (u'dbo',)
>> >>  Complete 
>> >>  Testing pyodbc Directly
>> >> 
>> >>  Complete 
>> >>  Testing Get Schema Name with pyodbc
>> >> (u'dbo', )
>> >>  Complete 
>> >>  Testing SQLAlchemy Connection using pymssql
>> >> 
>> >>  Complete 
>> >
>> >
>> > I switched over the SQLAlchemy connection string to use pymssql.  It now
>> > looks like this:
>> >
>> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP'
>> >
>> > The same error is produced.  I attempted to run the SELECT schema_name()
>> > query through the SQLAlchemy connection to see what was coming back but
>> > it
>> > failed in the same way because it is attempting its internal
>> > schema_name()
>> > query first.
>> >
>> > This is indeed quite strange since both the pyodbc and pymssql direct
>> > connections did return a legit row when asking for the schema name.
>> >
>> > On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote:
>> >>
>> >> can you run this query please?
>> >>
>> >> SELECT schema_name()
>> >>
>> >> the issue is, that query is returning a result, there is a row, but it
>> >> no columns, which is nonsensical.  Did you try running with the
>> >> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need
>> >> more info.
>> >>
>> >>
>> >>
>> >> On Tue, Jul 4, 2017 at 9:59 AM, Simon King 
>> >> wrote:
>> >> > The key part of the stack trace is:
>> >> >
>> >> > File "c:\Program
>> >> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
>> >> > line 1773, in _get_default_schema_name default_schema_name =
>> >> > connection.scalar(query)
>> >> >
>> >> > ...which is in this function:
>> >> >
>> >> >
>> >> >
>> >> > https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>> >> >
>> >> > It's failing to fetch the single value that ought to come back from
>> >> > the query "SELECT schema_name()".
>> >> >
>> >> > I don't know anything about MSSQL or ODBC, but you could try poking
>> >> > around with pdb in the scalar() function:
>> >> >
>> >> >
>> >> >
>> >> > https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>> >> >
>> >> > Simon
>> >> >
>> >> >
>> >> > On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
>> >> >  wrote:
>> >> >> Mike,
>> >> >>
>> >> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

Sorry, I gave you the cursor.description of the schema_name() query.  This 
is the output for the cursor.description for the SELECT * FROM EPO_MODELS 
query:

*pymssql Cursor Description is:*

> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None, 
> None, N
> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1, 
> None,
> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
> None), (u'
> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, 
> None, No
> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
> (u'DATEALTER
> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, 
> None, No
> ne, None))



*pyodbc Cursor Description is:*

> ((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None, 
> None, N
> one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1, 
> None,
> None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, 
> None), (u'
> MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, 
> None, No
> ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), 
> (u'DATEALTER
> ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, 
> None, No
> ne, None))



On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote:
>
> in your direct examples can you show me the output of 
> "cursor.description" as well once you execute the query? 
>
> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel 
>  wrote: 
> > I ran that select statement under both the pyodbc and pymssql direct 
> > connections and it appears to have returned a legitimate row of data. 
>  This 
> > is the excerpt from the test output: 
> > 
> >>  Testing pymssql Directly 
> >>  
> >>  Complete  
> >>  Testing Get Schema Name with pymssql 
> >> (u'dbo',) 
> >>  Complete  
> >>  Testing pyodbc Directly 
> >>  
> >>  Complete  
> >>  Testing Get Schema Name with pyodbc 
> >> (u'dbo', ) 
> >>  Complete  
> >>  Testing SQLAlchemy Connection using pymssql 
> >>  
> >>  Complete  
> > 
> > 
> > I switched over the SQLAlchemy connection string to use pymssql.  It now 
> > looks like this: 
> > 
> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP' 
> > 
> > The same error is produced.  I attempted to run the SELECT schema_name() 
> > query through the SQLAlchemy connection to see what was coming back but 
> it 
> > failed in the same way because it is attempting its internal 
> schema_name() 
> > query first. 
> > 
> > This is indeed quite strange since both the pyodbc and pymssql direct 
> > connections did return a legit row when asking for the schema name. 
> > 
> > On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote: 
> >> 
> >> can you run this query please? 
> >> 
> >> SELECT schema_name() 
> >> 
> >> the issue is, that query is returning a result, there is a row, but it 
> >> no columns, which is nonsensical.  Did you try running with the 
> >> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need 
> >> more info. 
> >> 
> >> 
> >> 
> >> On Tue, Jul 4, 2017 at 9:59 AM, Simon King  
> wrote: 
> >> > The key part of the stack trace is: 
> >> > 
> >> > File "c:\Program 
> >> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", 
> >> > line 1773, in _get_default_schema_name default_schema_name = 
> >> > connection.scalar(query) 
> >> > 
> >> > ...which is in this function: 
> >> > 
> >> > 
> >> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>  
> >> > 
> >> > It's failing to fetch the single value that ought to come back from 
> >> > the query "SELECT schema_name()". 
> >> > 
> >> > I don't know anything about MSSQL or ODBC, but you could try poking 
> >> > around with pdb in the scalar() function: 
> >> > 
> >> > 
> >> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>  
> >> > 
> >> > Simon 
> >> > 
> >> > 
> >> > On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel 
> >> >  wrote: 
> >> >> Mike, 
> >> >> 
> >> >> Sorry for the lack of information.  Please find the rest of what you 
> >> >> wanted 
> >> >> below. 
> >> >> 
> >> >> Full Stack Trace: 
> >> >> 
> >> >>>  Running Direct SQL Query 
> >> >>> Traceback (most recent call last): 
> >> >>>   File "test.py", line 45, in  result = con.execute('SELECT 
> * 
> >> >>> FROM 
> >> >>> EPO_MODELS') 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> >> >>> 1139, in 
> >> >>> execute bind, close_with_result=True).execute(clause, params or {}) 
> >> >>>   File "c:\Program 
> >> >>> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

>
>  Testing *pymssql* Directly
> 
>  Complete 
>  Testing Get Schema Name with pymssql
> (u'dbo',)
>
> *Cursor Description is:((u'', 1, None, None, None, None, None),)*
>  Complete 
>  Testing *pyodbc* Directly
> 
>  Complete 
>  Testing Get Schema Name with pyodbc
> (u'dbo', )
>
> *Cursor Description is:((u'', , None, 128, 128, 0, True),)*
>  Complete 


On Tuesday, July 4, 2017 at 1:13:12 PM UTC-5, Mike Bayer wrote:
>
> in your direct examples can you show me the output of 
> "cursor.description" as well once you execute the query? 
>
> On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel 
>  wrote: 
> > I ran that select statement under both the pyodbc and pymssql direct 
> > connections and it appears to have returned a legitimate row of data. 
>  This 
> > is the excerpt from the test output: 
> > 
> >>  Testing pymssql Directly 
> >>  
> >>  Complete  
> >>  Testing Get Schema Name with pymssql 
> >> (u'dbo',) 
> >>  Complete  
> >>  Testing pyodbc Directly 
> >>  
> >>  Complete  
> >>  Testing Get Schema Name with pyodbc 
> >> (u'dbo', ) 
> >>  Complete  
> >>  Testing SQLAlchemy Connection using pymssql 
> >>  
> >>  Complete  
> > 
> > 
> > I switched over the SQLAlchemy connection string to use pymssql.  It now 
> > looks like this: 
> > 
> > 'mssql+pymssql://:@CMPDSQL01:1433/CMP' 
> > 
> > The same error is produced.  I attempted to run the SELECT schema_name() 
> > query through the SQLAlchemy connection to see what was coming back but 
> it 
> > failed in the same way because it is attempting its internal 
> schema_name() 
> > query first. 
> > 
> > This is indeed quite strange since both the pyodbc and pymssql direct 
> > connections did return a legit row when asking for the schema name. 
> > 
> > On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote: 
> >> 
> >> can you run this query please? 
> >> 
> >> SELECT schema_name() 
> >> 
> >> the issue is, that query is returning a result, there is a row, but it 
> >> no columns, which is nonsensical.  Did you try running with the 
> >> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need 
> >> more info. 
> >> 
> >> 
> >> 
> >> On Tue, Jul 4, 2017 at 9:59 AM, Simon King  
> wrote: 
> >> > The key part of the stack trace is: 
> >> > 
> >> > File "c:\Program 
> >> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", 
> >> > line 1773, in _get_default_schema_name default_schema_name = 
> >> > connection.scalar(query) 
> >> > 
> >> > ...which is in this function: 
> >> > 
> >> > 
> >> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>  
> >> > 
> >> > It's failing to fetch the single value that ought to come back from 
> >> > the query "SELECT schema_name()". 
> >> > 
> >> > I don't know anything about MSSQL or ODBC, but you could try poking 
> >> > around with pdb in the scalar() function: 
> >> > 
> >> > 
> >> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>  
> >> > 
> >> > Simon 
> >> > 
> >> > 
> >> > On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel 
> >> >  wrote: 
> >> >> Mike, 
> >> >> 
> >> >> Sorry for the lack of information.  Please find the rest of what you 
> >> >> wanted 
> >> >> below. 
> >> >> 
> >> >> Full Stack Trace: 
> >> >> 
> >> >>>  Running Direct SQL Query 
> >> >>> Traceback (most recent call last): 
> >> >>>   File "test.py", line 45, in  result = con.execute('SELECT 
> * 
> >> >>> FROM 
> >> >>> EPO_MODELS') 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> >> >>> 1139, in 
> >> >>> execute bind, close_with_result=True).execute(clause, params or {}) 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> >> >>> 1003, in 
> >> >>> _connection_for_bind engine, execution_options) 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> 403, 
> >> >>> in 
> >> >>> _connection_for_bind conn = bind.contextual_connect() 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 
> >> >>> 2112, in 
> >> >>> contextual_connect self._wrap_pool_connect(self.pool.connect, 
> None), 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 
> >> >>> 2147, in 
> >> >>> _wrap_pool_connect return fn() 
> >> >>>   File "c:\Program 
> >> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> >>> line 387, in connect return _ConnectionFairy._checkout(self) 
> >> >>>   File "c:\Program 
> >> >>> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

in your direct examples can you show me the output of
"cursor.description" as well once you execute the query?

On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel
 wrote:
> I ran that select statement under both the pyodbc and pymssql direct
> connections and it appears to have returned a legitimate row of data.  This
> is the excerpt from the test output:
>
>>  Testing pymssql Directly
>> 
>>  Complete 
>>  Testing Get Schema Name with pymssql
>> (u'dbo',)
>>  Complete 
>>  Testing pyodbc Directly
>> 
>>  Complete 
>>  Testing Get Schema Name with pyodbc
>> (u'dbo', )
>>  Complete 
>>  Testing SQLAlchemy Connection using pymssql
>> 
>>  Complete 
>
>
> I switched over the SQLAlchemy connection string to use pymssql.  It now
> looks like this:
>
> 'mssql+pymssql://:@CMPDSQL01:1433/CMP'
>
> The same error is produced.  I attempted to run the SELECT schema_name()
> query through the SQLAlchemy connection to see what was coming back but it
> failed in the same way because it is attempting its internal schema_name()
> query first.
>
> This is indeed quite strange since both the pyodbc and pymssql direct
> connections did return a legit row when asking for the schema name.
>
> On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote:
>>
>> can you run this query please?
>>
>> SELECT schema_name()
>>
>> the issue is, that query is returning a result, there is a row, but it
>> no columns, which is nonsensical.  Did you try running with the
>> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need
>> more info.
>>
>>
>>
>> On Tue, Jul 4, 2017 at 9:59 AM, Simon King  wrote:
>> > The key part of the stack trace is:
>> >
>> > File "c:\Program
>> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
>> > line 1773, in _get_default_schema_name default_schema_name =
>> > connection.scalar(query)
>> >
>> > ...which is in this function:
>> >
>> >
>> > https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>> >
>> > It's failing to fetch the single value that ought to come back from
>> > the query "SELECT schema_name()".
>> >
>> > I don't know anything about MSSQL or ODBC, but you could try poking
>> > around with pdb in the scalar() function:
>> >
>> >
>> > https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>> >
>> > Simon
>> >
>> >
>> > On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
>> >  wrote:
>> >> Mike,
>> >>
>> >> Sorry for the lack of information.  Please find the rest of what you
>> >> wanted
>> >> below.
>> >>
>> >> Full Stack Trace:
>> >>
>> >>>  Running Direct SQL Query
>> >>> Traceback (most recent call last):
>> >>>   File "test.py", line 45, in  result = con.execute('SELECT *
>> >>> FROM
>> >>> EPO_MODELS')
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line
>> >>> 1139, in
>> >>> execute bind, close_with_result=True).execute(clause, params or {})
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line
>> >>> 1003, in
>> >>> _connection_for_bind engine, execution_options)
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403,
>> >>> in
>> >>> _connection_for_bind conn = bind.contextual_connect()
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line
>> >>> 2112, in
>> >>> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line
>> >>> 2147, in
>> >>> _wrap_pool_connect return fn()
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 387, in connect return _ConnectionFairy._checkout(self)
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 516, in checkout rec = pool._do_get()
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 1138, in _do_get self._dec_overflow()
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py",
>> >>> line 66,
>> >>> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 1135, in _do_get return self._create_connection()
>> >>>   File "c:\Program
>> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >>> line 333, in _create_connection return 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

I ran that select statement under both the pyodbc and pymssql direct 
connections and it appears to have returned a legitimate row of data.  This 
is the excerpt from the test output:

 Testing pymssql Directly
> 
>  Complete 
>  Testing Get Schema Name with pymssql
> (u'dbo',)
>  Complete 
>  Testing pyodbc Directly
> 
>  Complete 
>  Testing Get Schema Name with pyodbc
> (u'dbo', )
>  Complete 
>  Testing SQLAlchemy Connection using pymssql
> 
>  Complete 


I switched over the SQLAlchemy connection string to use pymssql.  It now 
looks like this:

'mssql+pymssql://:@CMPDSQL01:1433/CMP'

The same error is produced.  I attempted to run the SELECT schema_name() 
query through the SQLAlchemy connection to see what was coming back but it 
failed in the same way because it is attempting its internal schema_name() 
query first.

This is indeed quite strange since both the pyodbc and pymssql direct 
connections did return a legit row when asking for the schema name.

On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote:
>
> can you run this query please? 
>
> SELECT schema_name() 
>
> the issue is, that query is returning a result, there is a row, but it 
> no columns, which is nonsensical.  Did you try running with the 
> mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need 
> more info. 
>
>
>
> On Tue, Jul 4, 2017 at 9:59 AM, Simon King  > wrote: 
> > The key part of the stack trace is: 
> > 
> > File "c:\Program 
> > Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", 
> > line 1773, in _get_default_schema_name default_schema_name = 
> > connection.scalar(query) 
> > 
> > ...which is in this function: 
> > 
> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>  
> > 
> > It's failing to fetch the single value that ought to come back from 
> > the query "SELECT schema_name()". 
> > 
> > I don't know anything about MSSQL or ODBC, but you could try poking 
> > around with pdb in the scalar() function: 
> > 
> > 
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>  
> > 
> > Simon 
> > 
> > 
> > On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel 
> >  wrote: 
> >> Mike, 
> >> 
> >> Sorry for the lack of information.  Please find the rest of what you 
> wanted 
> >> below. 
> >> 
> >> Full Stack Trace: 
> >> 
> >>>  Running Direct SQL Query 
> >>> Traceback (most recent call last): 
> >>>   File "test.py", line 45, in  result = con.execute('SELECT * 
> FROM 
> >>> EPO_MODELS') 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> 1139, in 
> >>> execute bind, close_with_result=True).execute(clause, params or {}) 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 
> 1003, in 
> >>> _connection_for_bind engine, execution_options) 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, 
> in 
> >>> _connection_for_bind conn = bind.contextual_connect() 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 
> 2112, in 
> >>> contextual_connect self._wrap_pool_connect(self.pool.connect, None), 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 
> 2147, in 
> >>> _wrap_pool_connect return fn() 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 387, in connect return _ConnectionFairy._checkout(self) 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool) 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 516, in checkout rec = pool._do_get() 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 1138, in _do_get self._dec_overflow() 
> >>>   File "c:\Program 
> >>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", 
> line 66, 
> >>> in __exit__ compat.reraise(exc_type, exc_value, exc_tb) 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 1135, in _do_get return self._create_connection() 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 333, in _create_connection return _ConnectionRecord(self) 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 461, in __init__ self.__connect(first_connect_check=True) 
> >>>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >>> line 661, in __connect 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

It's never a good idea to just blanket ignore all exceptions.   There
are a few cases where we except for dbapi.Error in order to catch
unsupported calls such as looking for sys.dm_exec_sessions.

However here, SQL Server is not complaining at all.  A result is
returned.  The result has a row.  Then the row has no columns.  That
should never happen.   Looks like a pyodbc driver bug so far.


On Tue, Jul 4, 2017 at 10:25 AM, Paul Morel
 wrote:
> I don't know the downstream implications of doing this but would it make
> sense to wrap lines 1773-1777 in a try/except with the except block
> returning self.schema_name.
>
> Like this:
>
> def _get_default_schema_name(self, connection):
> if self.server_version_info < MS_2005_VERSION:
> return self.schema_name
> else:
> query = sql.text("SELECT schema_name()")
>
> try:
>   default_schema_name = connection.scalar(query)
>   if default_schema_name is not None:
>   return util.text_type(default_schema_name)
>   else:
>   return self.schema_name
> except:
>   return self.schema_name
>
> me
>
> On Tuesday, July 4, 2017 at 9:00:22 AM UTC-5, Simon King wrote:
>>
>> The key part of the stack trace is:
>>
>> File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
>> line 1773, in _get_default_schema_name default_schema_name =
>> connection.scalar(query)
>>
>> ...which is in this function:
>>
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>>
>> It's failing to fetch the single value that ought to come back from
>> the query "SELECT schema_name()".
>>
>> I don't know anything about MSSQL or ODBC, but you could try poking
>> around with pdb in the scalar() function:
>>
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>>
>> Simon
>>
>>
>> On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
>>  wrote:
>> > Mike,
>> >
>> > Sorry for the lack of information.  Please find the rest of what you
>> > wanted
>> > below.
>> >
>> > Full Stack Trace:
>> >
>> >>  Running Direct SQL Query
>> >> Traceback (most recent call last):
>> >>   File "test.py", line 45, in  result = con.execute('SELECT *
>> >> FROM
>> >> EPO_MODELS')
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139,
>> >> in
>> >> execute bind, close_with_result=True).execute(clause, params or {})
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003,
>> >> in
>> >> _connection_for_bind engine, execution_options)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403,
>> >> in
>> >> _connection_for_bind conn = bind.contextual_connect()
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112,
>> >> in
>> >> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147,
>> >> in
>> >> _wrap_pool_connect return fn()
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 387, in connect return _ConnectionFairy._checkout(self)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 516, in checkout rec = pool._do_get()
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 1138, in _do_get self._dec_overflow()
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line
>> >> 66,
>> >> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 1135, in _do_get return self._create_connection()
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 333, in _create_connection return _ConnectionRecord(self)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 461, in __init__ self.__connect(first_connect_check=True)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> >> line 661, in __connect exec_once(self.connection, self)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246,
>> >> in
>> >> exec_once self(*args, **kw)
>> >>   File "c:\Program
>> >> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256,
>> >> in
>> >> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

can you run this query please?

SELECT schema_name()

the issue is, that query is returning a result, there is a row, but it
no columns, which is nonsensical.  Did you try running with the
mssql+pymssql:// driver?   Looks like a pyodbc bug so far but need
more info.



On Tue, Jul 4, 2017 at 9:59 AM, Simon King  wrote:
> The key part of the stack trace is:
>
> File "c:\Program
> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
> line 1773, in _get_default_schema_name default_schema_name =
> connection.scalar(query)
>
> ...which is in this function:
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>
> It's failing to fetch the single value that ought to come back from
> the query "SELECT schema_name()".
>
> I don't know anything about MSSQL or ODBC, but you could try poking
> around with pdb in the scalar() function:
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>
> Simon
>
>
> On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
>  wrote:
>> Mike,
>>
>> Sorry for the lack of information.  Please find the rest of what you wanted
>> below.
>>
>> Full Stack Trace:
>>
>>>  Running Direct SQL Query
>>> Traceback (most recent call last):
>>>   File "test.py", line 45, in  result = con.execute('SELECT * FROM
>>> EPO_MODELS')
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, in
>>> execute bind, close_with_result=True).execute(clause, params or {})
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, in
>>> _connection_for_bind engine, execution_options)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, in
>>> _connection_for_bind conn = bind.contextual_connect()
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, in
>>> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, in
>>> _wrap_pool_connect return fn()
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 387, in connect return _ConnectionFairy._checkout(self)
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 516, in checkout rec = pool._do_get()
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 1138, in _do_get self._dec_overflow()
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66,
>>> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 1135, in _do_get return self._create_connection()
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 333, in _create_connection return _ConnectionRecord(self)
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 461, in __init__ self.__connect(first_connect_check=True)
>>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>>> line 661, in __connect exec_once(self.connection, self)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, in
>>> exec_once self(*args, **kw)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, in
>>> __call__ fn(*args, **kw)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line
>>> 1331, in go return once_fn(*arg, **kw)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line
>>> 181, in first_connect dialect.initialize(c)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", line
>>> 165, in initialize super(PyODBCConnector, self).initialize(connection)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>>> 1742, in initialize super(MSDialect, self).initialize(connection)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 250,
>>> in initialize self._get_default_schema_name(connection)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>>> 1773, in _get_default_schema_name default_schema_name =
>>> connection.scalar(query)
>>>   File "c:\Program
>>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 877, in
>>> scalar return 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

I don't know the downstream implications of doing this but would it make 
sense to wrap lines 1773-1777 in a try/except with the except block 
returning self.schema_name.

Like this:

def _get_default_schema_name(self, connection):
if self.server_version_info < MS_2005_VERSION:
return self.schema_name
else:
query = sql.text("SELECT schema_name()")

try:
  default_schema_name = connection.scalar(query)
  if default_schema_name is not None:
  return util.text_type(default_schema_name)
  else:
  return self.schema_name
except:
  return self.schema_name

me

On Tuesday, July 4, 2017 at 9:00:22 AM UTC-5, Simon King wrote:
>
> The key part of the stack trace is: 
>
> File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", 
> line 1773, in _get_default_schema_name default_schema_name = 
> connection.scalar(query) 
>
> ...which is in this function: 
>
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768
>  
>
> It's failing to fetch the single value that ought to come back from 
> the query "SELECT schema_name()". 
>
> I don't know anything about MSSQL or ODBC, but you could try poking 
> around with pdb in the scalar() function: 
>
>
> https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212
>  
>
> Simon 
>
>
> On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel 
>  wrote: 
> > Mike, 
> > 
> > Sorry for the lack of information.  Please find the rest of what you 
> wanted 
> > below. 
> > 
> > Full Stack Trace: 
> > 
> >>  Running Direct SQL Query 
> >> Traceback (most recent call last): 
> >>   File "test.py", line 45, in  result = con.execute('SELECT * 
> FROM 
> >> EPO_MODELS') 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, 
> in 
> >> execute bind, close_with_result=True).execute(clause, params or {}) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, 
> in 
> >> _connection_for_bind engine, execution_options) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, 
> in 
> >> _connection_for_bind conn = bind.contextual_connect() 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, 
> in 
> >> contextual_connect self._wrap_pool_connect(self.pool.connect, None), 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, 
> in 
> >> _wrap_pool_connect return fn() 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 387, in connect return _ConnectionFairy._checkout(self) 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool) 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 516, in checkout rec = pool._do_get() 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 1138, in _do_get self._dec_overflow() 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 
> 66, 
> >> in __exit__ compat.reraise(exc_type, exc_value, exc_tb) 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 1135, in _do_get return self._create_connection() 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 333, in _create_connection return _ConnectionRecord(self) 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 461, in __init__ self.__connect(first_connect_check=True) 
> >>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> >> line 661, in __connect exec_once(self.connection, self) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, 
> in 
> >> exec_once self(*args, **kw) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, 
> in 
> >> __call__ fn(*args, **kw) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 
> >> 1331, in go return once_fn(*arg, **kw) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", 
> line 
> >> 181, in first_connect dialect.initialize(c) 
> >>   File "c:\Program 
> >> Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", 
> line 
> >> 165, in initialize super(PyODBCConnector, self).initialize(connection) 
> >>   File "c:\Program 
> >> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Simon King]

The key part of the stack trace is:

File "c:\Program
Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
line 1773, in _get_default_schema_name default_schema_name =
connection.scalar(query)

...which is in this function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11=file-view-default#base.py-1768

It's failing to fetch the single value that ought to come back from
the query "SELECT schema_name()".

I don't know anything about MSSQL or ODBC, but you could try poking
around with pdb in the scalar() function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11=file-view-default#result.py-1212

Simon


On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel
 wrote:
> Mike,
>
> Sorry for the lack of information.  Please find the rest of what you wanted
> below.
>
> Full Stack Trace:
>
>>  Running Direct SQL Query
>> Traceback (most recent call last):
>>   File "test.py", line 45, in  result = con.execute('SELECT * FROM
>> EPO_MODELS')
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, in
>> execute bind, close_with_result=True).execute(clause, params or {})
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, in
>> _connection_for_bind engine, execution_options)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, in
>> _connection_for_bind conn = bind.contextual_connect()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, in
>> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, in
>> _wrap_pool_connect return fn()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 387, in connect return _ConnectionFairy._checkout(self)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 516, in checkout rec = pool._do_get()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 1138, in _do_get self._dec_overflow()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66,
>> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 1135, in _do_get return self._create_connection()
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 333, in _create_connection return _ConnectionRecord(self)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 461, in __init__ self.__connect(first_connect_check=True)
>>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py",
>> line 661, in __connect exec_once(self.connection, self)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, in
>> exec_once self(*args, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, in
>> __call__ fn(*args, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line
>> 1331, in go return once_fn(*arg, **kw)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line
>> 181, in first_connect dialect.initialize(c)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", line
>> 165, in initialize super(PyODBCConnector, self).initialize(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>> 1742, in initialize super(MSDialect, self).initialize(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 250,
>> in initialize self._get_default_schema_name(connection)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line
>> 1773, in _get_default_schema_name default_schema_name =
>> connection.scalar(query)
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 877, in
>> scalar return self.execute(object, *multiparams, **params).scalar()
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 1223,
>> in scalar return row[0]
>>   File "c:\Program
>> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, in
>> _key_fallback expression._string_or_unprintable(key))
>>   sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for
>> column '0'"
>
>
> ODBC Driver Versions:
>>
>> 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

Mike,

Sorry for the lack of information.  Please find the rest of what you wanted 
below.

*Full Stack Trace:*

 Running Direct SQL Query
> Traceback (most recent call last):
>   File "test.py", line 45, in  result = con.execute('SELECT * FROM 
> EPO_MODELS')
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, in 
> execute bind, close_with_result=True).execute(clause, params or {})
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, in 
> _connection_for_bind engine, execution_options)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, in 
> _connection_for_bind conn = bind.contextual_connect()
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, in 
> contextual_connect self._wrap_pool_connect(self.pool.connect, None),
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, in 
> _wrap_pool_connect return fn()
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 387, in connect return _ConnectionFairy._checkout(self)
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 516, in checkout rec = pool._do_get()
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 1138, in _do_get self._dec_overflow()
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, 
> in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 1135, in _do_get return self._create_connection()
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 333, in _create_connection return _ConnectionRecord(self)
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 461, in __init__ self.__connect(first_connect_check=True)
>   File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", 
> line 661, in __connect exec_once(self.connection, self)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, in 
> exec_once self(*args, **kw)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, in 
> __call__ fn(*args, **kw)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 
> 1331, in go return once_fn(*arg, **kw)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line 
> 181, in first_connect dialect.initialize(c)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", line 
> 165, in initialize super(PyODBCConnector, self).initialize(connection)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 
> 1742, in initialize super(MSDialect, self).initialize(connection)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 250, 
> in initialize self._get_default_schema_name(connection)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 
> 1773, in _get_default_schema_name default_schema_name = 
> connection.scalar(query)
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 877, in 
> scalar return self.execute(object, *multiparams, **params).scalar()
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 1223, 
> in scalar return row[0]
>   File "c:\Program 
> Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, 
> in _key_fallback expression._string_or_unprintable(key)) 
>   sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for 
> column '0'"


*ODBC Driver Versions:*

> SQL Server (SQLSRV32.DLL) = 6.00.6002.18005
> SQL Server Native Client 10.0 (SQLNCLI10.DLL) = 2007.100.2531.00


*SQL Server Version*

> SQL Server 2008
> Microsoft SQL Server Management Studio = 10.0.6000.29
> Microsoft Analysis Services Client Tools = 10.0.6000.29
> Microsoft Data Access Components (MDAC) = 6.0.6002.18005
> Microsoft MSXML = 3.0 6.0 
> Microsoft Internet Explorer = 9.0.8112.16421
> Microsoft .NET Framework = 2.0.50727.4253
> Operating System = 6.0.6002


*Server OS*

> Windows Server 2008 Enterprise
> Service Pack 2 Installed


On Monday, July 3, 2017 at 8:33:54 PM UTC-5, Mike Bayer wrote:
>
> Can you send complete stack trace please?  That's the main thing that will 
> show which query this is occurring on (there are several upon connect).  
> Also full detail on SQL server version, odbc driver, client operating 
> system.  
>
> On Jul 3, 2017 7:26 PM, "Paul Morel" 

Re: [sqlalchemy] SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Mike Bayer]

Can you send complete stack trace please?  That's the main thing that will
show which query this is occurring on (there are several upon connect).
Also full detail on SQL server version, odbc driver, client operating
system.

On Jul 3, 2017 7:26 PM, "Paul Morel"  wrote:

Hi,

I have been trying to diagnose this issue in a Windows Python 2.7 (Anaconda
installed) environment running SQLAlchemy=1.1.11, pyodbc=4.0.17, and
pymssql=2.1.3.

Both pyodbc and pymssql connections will successfully connect and query a
table correctly.  However, when I attempt the same connection and query
through SQLAlchemy either using an ORM or direct SQL, it fails with the
following error:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for
> column '0'


The connection string I'm using is the following:

'mssql+pyodbc://:@CMPDSQL01:1433/CMP?driver=SQL+Server+
Native+Client+10.0'


The connection and simple query through pyodbc uses the following:

print " Testing pyodbc Directly"
cnxn = pyodbc.connect(
r'Trusted_Connection=yes;'
r'DRIVER={SQL Server Native Client 10.0};'
r'SERVER=CMPDSQL01;'
r'DATABASE=CMP;'
)

print cnxn
print " Complete "

print " Running Direct SQL Query on pyodbc Direct Connection"
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM EPO_MODELS')
for r in cursor:
print r
print " Complete "


The connection and simple query through pymssql uses the following:

print " Testing pymssql Directly"
cnxn = pymssql.connect(server='CMPDSQL01', port='1433', database='CMP')
print cnxn
print " Complete "

print " Running Direct SQL Query on pymssql Direct Connection"
cursor = cnxn.cursor()
cursor.execute('SELECT * FROM EPO_MODELS')
for r in cursor:
print r
print " Complete "


What is even more perplexing is that the SQLAlchemy connection used to work
but now no longer works.  Unfortunately I don't know what broke it due to a
clean start install.

I don't think the EPO_MODELS object model comes into play with this error
because even a direct SQL query fails in the same way.  However, for
completeness the EPO_MODELS object model is very simple and looks like the
following:

class EPO_MODELS(Base):
__tablename__ = 'EPO_MODELS'

ID = Column(Integer, primary_key=True, autoincrement=False)
MODELTYPE = Column(Integer, autoincrement=False)
MODELNAME = Column(NVARCHAR(255))
MEMO = Column(NVARCHAR(2000))
NEXTUNIQUEID = Column(Integer, autoincrement=False)
MODELSYNC = Column(Integer, autoincrement=False)
MODELSTATUS = Column(Integer, autoincrement=False)
AUDITUSERID = Column(Integer, autoincrement=False)
DATEALTERED = Column(DateTime)
CREATIONDATE = Column(DateTime)


The direct SQLAlchemy query looks like the following after getting the
session using the connection string above:

print " Running Direct SQL Query Through SQLAlchemy Connection"
result = con.execute('SELECT * FROM EPO_MODELS')
for r in result:
print r
print " Complete "

Very much appreciate any insight into what is going on here.  I can't seem
to find the disconnect. Thanks in advance.

Stack Overflow Post is here:  https://stackoverflow.com/q/44893049/227542

-Paul

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