mssql reflection NoSuchTableError

[one.person]

Hello all

I am experimenting with SA on MSSQL and am having difficulty with
table reflection as outlined in the tutorial, for instance:

from sqlalchemy import *
db = create_engine('mssql://login:password@sqlserver/db')
metadata = BoundMetaData(db)

users_table = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String(40)),
Column('password', String(10))
)

metadata.engine.echo = True
users_table.create()

All of this works fine, it creates the 'users' table no problem, but
if I come back and try this it returns a NoSuchTableError:

users = Table('users', metadata, autoload=True)

Traceback (most recent call last):
File "<pyshell#3>", line 1, in -toplevel-
users = Table('users', metadata, autoload=True)
File "build\bdist.win32\egg\sqlalchemy\schema.py", line 166, in
__call__
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 809, in
reflecttable
File "build\bdist.win32\egg\sqlalchemy\databases\mssql.py", line
506, in reflecttable
NoSuchTableError: users

With a little searching around I also tried the schema='' argument and
the owner='' argument on Table() with the same error. I tried the
exact same thing on mysql and it worked perfectly.

There must be something obvious that I am doing wrong here, or
something specific to mssql, any tips? (This is MSSQL2000 server +
pymssql on a windows client, python 2.4.2 btw)

Thanks in advance

[Rick Morrison]

Just tried it here on a Linux + pymssql box and it worked fine.

The 'NoSuchTable' error would indicate that the table is not found, as you surmised. Check to make sure the table is really persisting after your first session with the table create.  In the meantime, I'll see if I can get pymssql running on Windows to see if I can replicate the problem here.

Rick

On 6/12/07, one.person <one.p...@gmail.com> wrote:

Hello all

I am experimenting with SA on MSSQL and am having difficulty with
table reflection as outlined in the tutorial, for instance:

from sqlalchemy import *

db = create_engine('mssql://login:password@sqlserver /db')

[one.person]

one.person
View profile
More options Jun 12, 5:12 pm
From: "one.person" <one.per...@gmail.com>
Date: Wed, 13 Jun 2007 00:12:50 -0000
Local: Tues, Jun 12 2007 5:12 pm
Subject: Re: mssql reflection NoSuchTableError
Reply | Reply to author | Forward | Print | Individual message | Show
original | Remove | Report this message | Find messages by this author
Thanks for the quick reply, I have additional info after more
experimentation. This works fine in a single python session as shown:

>>> from sqlalchemy import *
>>> db = create_engine('mssql://login:pw@mssqlserver/dbname')
>>> metadata = BoundMetaData(db)
>>> z_table = Table('z_table', metadata,

Column('col1', Integer),
Column('col2', String(20))
)

>>> z_table.create()
>>> z_table_test = Table('z_table', metadata, autoload=True)
>>> [c.name for c in z_table_test.columns]
['col1', 'col2']

However, if I shut down python and start a new session (I am using
IDLE btw), it will not pick up the table it just created:

>>> from sqlalchemy import *
>>> db = create_engine('mssql://login:pw@mssqlserver/dbname')
>>> metadata = BoundMetaData(db)
>>> z_table_test = Table('z_table', metadata, autoload=True)

Traceback (most recent call last):
File "<pyshell#3>", line 1, in -toplevel-

z_table_test = Table('z_table', metadata, autoload=True)

File "build\bdist.win32\egg\sqlalchemy\schema.py", line 166, in
__call__
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 809, in
reflecttable
File "build\bdist.win32\egg\sqlalchemy\databases\mssql.py", line
506, in reflecttable

NoSuchTableError: z_table

Consequently when I do the above I cannot reflect any of the pre-
existing tables in the database.

When I check the database itself 'z_table' really does exist and I can
query it in Query Analyzer and modify it in Enterprise Manager.

Thanks again for your help!

[Rick Morrison]

Works in Windows, too, sqlalchemy 0.3.8, Python 2.5, pymssql 0.8.0, MSSQL 2005

Try upgrading your pymssql if you're not at 0.8.0. If that won't work, then I would suggest a switch to pyodbc

Rick

On 6/12/07, one.person <one.p...@gmail.com> wrote:

[one.person]

So all of the above was done with Windows XP, Python 2.4.3, pymssql
0.7.4, and MSSQL 2000. I upgraded to pymssql 0.8.0 with the same
results. I uninstalled that and installed pyodbc 2.0.3.6, same
results.

The obvious differences between my installation and yours is that I am
using MSSQL 2000 and Python 2.4.3.

Not exactly sure what else to do here other than experiment with the
'schema' and 'owner' arguments on Table(). Any other suggestions?

Thanks again

[Rick Morrison]

I don't think you'll have a lot of luck with the schema/owner arguments; it's most likely an environmental issue with your MSSQL install, there are a lot of settings for MSSQL that can play havoc with table reflection.

Try logging the SQL that's being generated, that might point out the issue.

Or, try not using table reflection and writing out explicit table definitions.

Rick

On 6/12/07, one.person <one.p...@gmail.com> wrote:

So all of the above was done with Windows XP, Python 2.4.3, pymssql
0.7.4, and MSSQL 2000.  I upgraded to pymssql 0.8.0 with the same

results.  I uninstalled that and installed pyodbc 2.0.3.6 , same

[Michael Bayer]

turn on query logging and see whats wrong with the queries themselves,
when it goes to load data about the tables. try them manually, etc.

[one.person]

Thanks for all the quick replies. Currently on pyodbc 2.0.3.6.

Anyway, I tried this (the table 'zones' most definitely exists):

>>> metadata.engine.echo = True
>>> zones_table = Table('zones', metadata, autoload=True)

2007-06-12 18:20:40,924 INFO sqlalchemy.engine.base.Engine.0x..b0 SET
nocount ON
2007-06-12 18:20:40,924 INFO sqlalchemy.engine.base.Engine.0x..b0 None
2007-06-12 18:20:40,924 INFO sqlalchemy.engine.base.Engine.0x..b0
SELECT [COLUMNS_eba4].[TABLE_SCHEMA], [COLUMNS_eba4].[TABLE_NAME],
[COLUMNS_eba4].[COLUMN_NAME], [COLUMNS_eba4].[IS_NULLABLE],
[COLUMNS_eba4].[DATA_TYPE], [COLUMNS_eba4].[ORDINAL_POSITION],
[COLUMNS_eba4].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_eba4].
[NUMERIC_PRECISION], [COLUMNS_eba4].[NUMERIC_SCALE], [COLUMNS_eba4].
[COLUMN_DEFAULT]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_eba4]
WHERE [COLUMNS_eba4].[TABLE_NAME] = ? AND [COLUMNS_eba4].
[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_eba4].[ORDINAL_POSITION]
2007-06-12 18:20:40,940 INFO sqlalchemy.engine.base.Engine.0x..b0
['zones', 'dbo']

Traceback (most recent call last):

File "<pyshell#6>", line 1, in -toplevel-
zones_table = Table('zones', metadata, autoload=True)

File "build\bdist.win32\egg\sqlalchemy\schema.py", line 166, in
__call__
File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 809, in
reflecttable
File "build\bdist.win32\egg\sqlalchemy\databases\mssql.py", line
506, in reflecttable

NoSuchTableError: zones
>>>

I think that was what you guys meant by query logging....

[Rick Morrison]

That looks OK to me.

Try pasting that query (cleaned-up) into a query window on Enterprise Manager and see what kind of results you get. The "?" arguments are positional, so the first would be the table 'zone'; the second the schema 'dbo'.

On 6/12/07, one.person <one.p...@gmail.com> wrote:

[one.person]

Thanks a lot for the help guys. I got this to work by specifying the
schema="" argument properly. It turns out I had to specify the
'schema' argument to Table() as what MSSQL refers to as the 'Owner' of
the table in enterprise manager.

Still confused over the difference between schema and owner in MSSQL,
but now at least it works!

Thanks again

On Jun 12, 7:05 pm, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> That looks OK to me.
>
> Try pasting that query (cleaned-up) into a query window on Enterprise
> Manager and see what kind of results you get. The "?" arguments are
> positional, so the first would be the table 'zone'; the second the schema
> 'dbo'.
>