It ended up being a unicode issue. I had to set this:
supports_unicode_binds=False (see Unicde Binds here
http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc),
in order to get it to work. Annoying, to say the least!
python test_connect.py
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-08-18 16:15:06,611 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,613 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-08-18 16:15:06,614 DEBUG sqlalchemy.engine.base.Engine Row (u'dbo', )
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-18 16:15:06,616 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine SELECT
CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2014-08-18 16:15:06,619 INFO sqlalchemy.engine.base.Engine ()
<sqlalchemy.engine.base.Connection object at 0x101877ed0>
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine select 6 * 7 as
[Result];
2014-08-18 16:15:06,639 INFO sqlalchemy.engine.base.Engine ()
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Col ('Result',)
2014-08-18 16:15:06,641 DEBUG sqlalchemy.engine.base.Engine Row (42, )
42
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine SELECT
[TABLES_1].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
[TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-08-18 16:15:06,647 INFO sqlalchemy.engine.base.Engine ('dbo', 'BASE
TABLE')
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_NAME',)
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'irb_desc', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row (u'irbd', )
2014-08-18 16:15:06,663 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_irb', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_status_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'study_subject', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_desc', )
2014-08-18 16:15:06,664 DEBUG sqlalchemy.engine.base.Engine Row
(u'subj_status_desc', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'subject_status', )
2014-08-18 16:15:06,665 DEBUG sqlalchemy.engine.base.Engine Row
(u'sysdiagrams', )
Thanks all!
Greg--
On Monday, August 18, 2014 1:08:55 PM UTC-5, Michael Bayer wrote:
>
> try the query as stated along with a pyodbc connection (e.g. conn =
> pyodbc.connect(…); cursor = conn.cursor(); cursor.execute(<the statement>);
> cursor.fetchall()). the way pyodbc is connecting might be changing things.
>
>
>
> On Aug 18, 2014, at 12:59 PM, Horcle <g...@umn.edu <javascript:>> wrote:
>
> Thanks for the heads up. Unfortunately, it did not help. In any case, the
> issue appears to be that while the last query DOES return a record set when
> run as straight up SQL on the server, it does not work as desirecd through
> SQLAlchemy.
>
> More digging to be done.
>
> Greg--
>
> On Monday, August 18, 2014 11:30:39 AM UTC-5, Simon King wrote:
>>
>> It looks like the code that runs the "SELECT default_schema_name"
>> query has changed since the version you are running:
>>
>> https://bitbucket.org/zzzeek/sqlalchemy/commits/1fb4ad75a38c
>>
>> It might be worth upgrading to the latest release.
>>
>> Simon
>>
>> On Mon, Aug 18, 2014 at 5:22 PM, Horcle <g...@umn.edu> wrote:
>> > Thanks, this does help. I was wondering why the return results had no
>> values
>> > given.
>> >
>> > Greg--
>> >
>> >
>> > On Monday, August 18, 2014 11:17:48 AM UTC-5, Simon King wrote:
>> >>
>> >> I think you are seeing, for each query:
>> >>
>> >> 1. The query itself
>> >> 2. The parameters being passed in to the query
>> >> 3. The names of the columns being returned
>> >> 4. The returned rows, if any.
>> >>
>> >> So for example, the first thing that happens is:
>> >>
>> >> SELECT user_name()
>> >>
>> >> with no parameters
>> >>
>> >> ()
>> >>
>> >> returning a result set containing a single column with no name:
>> >>
>> >> ('',)
>> >>
>> >> followed by a single row containing a single value, the unicode string
>> >> "dbo":
>> >>
>> >> (u'dbo', )
>> >>
>> >> The second query goes like this:
>> >>
>> >> SELECT default_schema_name FROM
>> >> sys.database_principals
>> >> WHERE name = ?
>> >> AND type = 'S'
>> >>
>> >> Note the bound parameter ("name = ?"). The next line tells us that SA
>> >> is sending the unicode string "dbo" for that parameter:
>> >>
>> >> (u'dbo',)
>> >>
>> >> The result set has a single column called 'default_schema_name':
>> >>
>> >> ('default_schema_name',)
>> >>
>> >> ...but there are no matching rows.
>> >>
>> >> Then a bit further along, SA is running this query:
>> >>
>> >> SELECT [TABLES_1].[TABLE_NAME]
>> >> FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
>> >> WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
>> >> [TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
>> >>
>> >> ...passing the parameters "dbo" and "BASE TABLE", but not getting any
>> >> results back.
>> >>
>> >> I don't know anything about MS-SQL so can't tell you why that is, but
>> >> perhaps you've got enough information to carry on digging?
>> >>
>> >> Hope that helps,
>> >>
>> >> Simon
>> >>
>> >>
>> >> On Mon, Aug 18, 2014 at 4:27 PM, Horcle <g...@umn.edu> wrote:
>> >> > Indeed!
>> >> >
>> >> > Here is the output:
>> >> >
>> >> >
>> >> > gms$ python test_connect.py
>> >> > 2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine SELECT
>> >> > user_name()
>> >> > 2014-08-18 10:17:28,095 INFO sqlalchemy.engine.base.Engine ()
>> >> > 2014-08-18 10:17:28,097 DEBUG sqlalchemy.engine.base.Engine Col
>> ('',)
>> >> > 2014-08-18 10:17:28,098 DEBUG sqlalchemy.engine.base.Engine Row
>> (u'dbo',
>> >> > )
>> >> > 2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine
>> >> > SELECT default_schema_name FROM
>> >> > sys.database_principals
>> >> > WHERE name = ?
>> >> > AND type = 'S'
>> >> >
>> >> > 2014-08-18 10:17:28,099 INFO sqlalchemy.engine.base.Engine (u'dbo',)
>> >> > 2014-08-18 10:17:28,101 DEBUG sqlalchemy.engine.base.Engine Col
>> >> > ('default_schema_name',)
>> >> > 2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine SELECT
>> >> > CAST('test
>> >> > plain returns' AS VARCHAR(60)) AS anon_1
>> >> > 2014-08-18 10:17:28,103 INFO sqlalchemy.engine.base.Engine ()
>> >> > 2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine SELECT
>> >> > CAST('test
>> >> > unicode returns' AS NVARCHAR(60)) AS anon_1
>> >> > 2014-08-18 10:17:28,106 INFO sqlalchemy.engine.base.Engine ()
>> >> > <sqlalchemy.engine.base.Connection object at 0x101839490>
>> >> > 2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine select 6
>> * 7
>> >> > as
>> >> > [Result];
>> >> > 2014-08-18 10:17:28,382 INFO sqlalchemy.engine.base.Engine ()
>> >> > 2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Col
>> >> > ('Result',)
>> >> > 2014-08-18 10:17:28,384 DEBUG sqlalchemy.engine.base.Engine Row (42,
>> )
>> >> > 42
>> >> > 2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine SELECT
>> >> > [TABLES_1].[TABLE_NAME]
>> >> > FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1]
>> >> > WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND
>> >> > [TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
>> >> > 2014-08-18 10:17:28,389 INFO sqlalchemy.engine.base.Engine (u'dbo',
>> >> > 'BASE
>> >> > TABLE')
>> >> > 2014-08-18 10:17:28,394 DEBUG sqlalchemy.engine.base.Engine Col
>> >> > ('TABLE_NAME',)
>> >> > A
>> >> >
>> >> > Not quite sure how to parse this?
>> >> >
>> >> > For example, is "u'dbo'" what is being used as the schema?
>> >> >
>> >> > Greg--
>> >> >
>> >> > On Monday, August 18, 2014 10:05:09 AM UTC-5, Simon King wrote:
>> >> >>
>> >> >> On Mon, Aug 18, 2014 at 3:47 PM, Horcle <g...@umn.edu> wrote:
>> >> >> > On Friday, August 15, 2014 8:28:41 PM UTC-5, Michael Bayer wrote:
>> >> >> >>
>> >> >> >>
>> >> >> >> On Aug 15, 2014, at 5:03 PM, Greg Silverman <g...@umn.edu>
>> wrote:
>> >> >> >>
>> >> >> >>
>> >> >> >> Then, I thought, what if this is an SQLAlchemy issue. Looks to
>> be. I
>> >> >> >> ran
>> >> >> >> the following script as a test:
>> >> >> >>
>> >> >> >> import pyodbc
>> >> >> >> import sqlalchemy
>> >> >> >> from sqlalchemy.engine import reflection
>> >> >> >> from sqlalchemy.engine.reflection import Inspector
>> >> >> >>
>> >> >> >> def connect():
>> >> >> >> return pyodbc.connect(
>> >> >> >> 'DRIVER={FreeTDS};SERVER=<ip_address>;'
>> >> >> >> 'DATABASE=<db_name>;UID=test;PWD=test;port=1433;'
>> >> >> >> 'TDS_Version=9.1;')
>> >> >> >> engine = sqlalchemy.create_engine('mssql://', creator=connect)
>> >> >> >> conn = engine.connect()
>> >> >> >> print conn
>> >> >> >>
>> >> >> >> for row in engine.execute('select 6 * 7 as [Result];'):
>> >> >> >> print row.Result
>> >> >> >>
>> >> >> >> insp = reflection.Inspector.from_engine(engine)
>> >> >> >> table_name = 'irb_desc'
>> >> >> >> table_names = insp.get_table_names()
>> >> >> >> if table_name not in table_names:
>> >> >> >> print 'A'
>> >> >> >>
>> >> >> >> Again, I am connecting fine with the database create.engine
>> method
>> >> >> >> (that
>> >> >> >> is '42' is printing as expected), but when I run the
>> >> >> >> inspector.get_table_names method with the given conditional it
>> is
>> >> >> >> printing
>> >> >> >> the 'A' (I have tried other table names in the same database to
>> >> >> >> which I
>> >> >> >> added 'irbd_balance,' all with the same result.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> what is the SQL output if you set echo=‘debug’; then, take the
>> SQL
>> >> >> >> you
>> >> >> >> see and take a look at what it’s SELECTing so you can see what
>> might
>> >> >> >> be
>> >> >> >> wrong. Probably some schema name setting or something like
>> that.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > Thanks, I did not realize this was an option (actually, it is
>> >> >> > echo=True,
>> >> >> > but
>> >> >> > at least I can see the SQL being sent). Hopefully this will lead
>> me
>> >> >> > to
>> >> >> > an
>> >> >> > answer.
>> >> >> >
>> >> >>
>> >> >> "echo='debug'" will show you more information than "echo=True" (it
>> >> >> shows the rows coming back as well as the query that is sent)
>> >> >>
>> >> >> Simon
>> >> >
>> >> > --
>> >> > 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+...@googlegroups.com.
>> >> > To post to this group, send email to sqlal...@googlegroups.com.
>> >> > Visit this group at http://groups.google.com/group/sqlalchemy.
>> >> > For more options, visit https://groups.google.com/d/optout.
>> >
>> > --
>> > 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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > Visit this group at http://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
