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