cannot access tables

[Greg Silverman]

I am able to access our MS SQL Server 2012 instance and create tables to my heart's content.

I'm using the FreeTDS driver to connect to our server with the following string:

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

And am using the Cubes create_table_from_csv method to create the table from a CSV file as follows:

create_table_from_csv(engine,

                      "data.csv",

                      table_name=FACT_TABLE,

                      fields=[

                            ("category", "string"),

                            ("category_label", "string"),

                            ("subcategory", "string"),

                            ("subcategory_label", "string"),

                            ("line_item", "string"),

                            ("year", "integer"),

                            ("amount", "integer")],

                      create_id=True

                  )

The table is created, the data are loaded, and the permissions for my UID are all good to go.

However, when I try to actually see the tables, they are not accessible. 

I had been trying to get the Cubes "hello_world" example (http://pythonhosted.org/cubes/install.html#quick-start-or-hello-world) working on our remote server, and when I tried running the aggregate.py script, I kept getting the error that "cubes.errors.WorkspaceError: No such fact table 'irbd_balance'." 

I know the table is there, I have checked the permissions a zillion times, etc., and kept trying to tweak the cubes model, all to no avail.

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.

Any clue why I am not able to see my tables?

Thanks in advance!

Greg--

[Michael Bayer]

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.

[Horcle]

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.

Greg-- 

[Simon King]

"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

[Horcle]

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

[Simon King]

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

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

[Horcle]

Thanks, this does help. I was wondering why the return results had no values given.

Greg--

[Simon King]

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

[Horcle]

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

[Michael Bayer]

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.

[Horcle]

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