[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
Well, I did the fake id column because SA was throwing an error on a
the reflected table since it didn't have a primary key defined. I
think I may have found a workaround though.
Thanks for the help.
Mike
On Jun 3, 5:05 pm, Michael Bayer mike...@zzzcomputing.com wrote:
i don't see what the purpose of a fake id column serves here. you can
make a Table object and place within it as many actual columns as you
want, including primary key columns. if the table name is truly all
uppercase, then you need to name it that way in the Table object.
Mike Driscoll wrote:
Hi,
Actually, that was just a placeholder name since I'm not sure that my
employer would like me sticking my tables online. Anyway, the
tablename in Microsoft's Enterprise Manager shows it as being all
lowercase although when I run queries against it in MS Query Analyzer,
it uses all uppercase.
All the column names in the table are in uppercase and there are no
foreign keys. The vendor also didn't bother setting a primary key.
So, I decided to try accessing the table without autoload. Since there
are quite a few columns, I thought I'd also try grabbing only the
columns I wanted. For a simple test, I did this:
test_tbl = Table(tablename, metadata,
Column('id', Integer, primary_key=True),
Column(CYCLE_CODE, String))
Unfortunately, MSSQL is reporting that my fake primary key column is
invalid. Is there a workaround? Do I need to recreate all the Columns
in a Table object and also in my class definition?
Sorry for all the trouble.
Mike
On Jun 3, 3:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
tableTwo is a case sensitive name so must be spelled out with that exact
case. in particular if your database is returning foreign key names
without proper case sensitivity then issues will occur with this. turn
echo='debug' to see all SQL emitted and result sets returned.
Mike Driscoll wrote:
Hi,
That fixed that issue. However, now I'm getting an error that my 2nd
table doesn't exist:
sqlalchemy.exc.NoSuchTableError: tableTwo
This is weird since it's been around since 2007 and has lots of data
in it. I tried changing the name to all lowercase and all uppercase,
but the error remains the same. I also tried removing the schema line
to no avail.
Here's the complete traceback:
File D:\pyRetention\Scripts\paster, line 5, in module
pkg_resources.run_script('pastescript==1.7.3', 'paster')
File c:\Python25\Lib\site-packages\pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File c:\Python25\Lib\site-packages\pkg_resources.py, line 1166, in
run_script
execfile(script_filename, namespace, namespace)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\EGG-
INFO\scripts\paster, line 18, in module
command.run()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 218, in run
result = self.command()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 276, in command
relative_to=base, global_conf=vars)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 313, in loadapp
**kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 204, in loadapp
return loadobj(APP, uri, name=name, **kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 225, in loadobj
return context.create()
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 625, in create
return self.object_type.invoke(self)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 110, in invoke
return fix_call(context.object, context.global_conf,
**context.local_conf)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\util\fixtypeerror.py, line 57, in fix_call
val = callable(*args, **kw)
File D:\pyRetention\pyRetention\pyretention\config\middleware.py,
line 35, in make_app
app = make_base_app(global_conf, full_stack=True, **app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 588, in make_base_app
load_environment(global_conf, app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File
[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
Hi Simon and Michael, On Jun 4, 8:03 am, King Simon-NFHD78 simon.k...@motorola.com wrote: Mike Driscoll wrote: Well, I did the fake id column because SA was throwing an error on a the reflected table since it didn't have a primary key defined. I think I may have found a workaround though. From SA's point of view, the primary key just has to be a set of columns that uniquely identify a row in the database. It doesn't explicitly have to be defined as a primary key in the database. The reason for this is that if you retrieve an object (a row) from the database and modify it, there is no way to save those changes back to the database unless you can uniquely identify that row, so you know what to put in the WHERE clause of the UPDATE statement. If there really isn't a set of columns that uniquely identifies a row in the database, and you only want read-only support, you might be able to tell SA that the primary key is made up of all the columns in the table. However, if there are any duplicate rows in the table, SA will only return a single object for those rows because it has no way of knowing that they are different. If you do have duplicate rows, you might be better working with the SQL-only layer of SA, rather than the ORM. Simon It turns out that my ultimate issue was myself. I had our db admin (i.e. my boss) look at it and he quickly found that while my username had access to the database, it did not have access to that particular table. This caused autoload to report that the table did not exist AND if I set a primary key and disabled autoload, it made SA spit out a SELECT permission error. I apologize for the noise. So much to learn...so little time. - Mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
the MSSQL has had a lot of fixes over the course of 0.5 so definitely get
on the latest 0.5 first.
Mike wrote:
Hi,
I am working on a TurboGears 2 application that uses SqlAlchemy 0.5.1.
I am using reflection and am having a lot of trouble with it. Anyway,
here's the error I am getting:
Traceback (most recent call last):
File V:\PythonPackages\Development\pyRetention\Scripts\paster-
script.py, line 8, in module
load_entry_point('pastescript==1.7.3', 'console_scripts', 'paster')
()
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\appinstall.py, line 68, in run
return super(AbstractInstallCommand, self).run(new_args)
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\command.py, line 218, in run
result = self.command()
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\appinstall.py, line 456, in command
self, config_file, section, self.sysconfig_install_vars
(installer))
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\appinstall.py, line 598, in setup_config
mod.setup_app, command, filename, section, vars)
File v:\pythonpackages\development\pyretention\lib\site-packages
\pastescript-1.7.3-py2.5.egg\past
e\script\appinstall.py, line 612, in _call_setup_app
func(command, conf, vars)
File D:\pyRetention\pyRetention\pyretention\websetup.py, line 18,
in setup_app
load_environment(conf.global_conf, conf.local_conf)
File v:\pythonpackages\development\pyretention\lib\site-packages
\turbogears2-2.0-py2.5.egg\tg\con
figuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File v:\pythonpackages\development\pyretention\lib\site-packages
\turbogears2-2.0-py2.5.egg\tg\con
figuration.py, line 393, in setup_sqlalchemy
self.package.model.init_model(engine)
File D:\pyRetention\pyRetention\pyretention\model\__init__.py,
line 64, in init_model
autoload_with=engine)
File v:\pythonpackages\development\pyretention\lib\site-packages
\sqlalchemy-0.5.1-py2.5.egg\sqlal
chemy\schema.py, line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File v:\pythonpackages\development\pyretention\lib\site-packages
\sqlalchemy-0.5.1-py2.5.egg\sqlal
chemy\schema.py, line 239, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File v:\pythonpackages\development\pyretention\lib\site-packages
\sqlalchemy-0.5.1-py2.5.egg\sqlal
chemy\engine\base.py, line 1265, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File v:\pythonpackages\development\pyretention\lib\site-packages
\sqlalchemy-0.5.1-py2.5.egg\sqlal
chemy\databases\mssql.py, line 1157, in reflecttable
coltype = coltype(*args, **kwargs)
TypeError: __init__() takes at most 2 arguments (3 given)
I'm not sure what it is talking about. Here are my sample tables
though:
tbl_checks = Table(tableOne, metadata, autoload=True,
autoload_with=engine)
mapper(Checks, tbl_checks)
tbl_test = Table(tableTwo, metadata, autoload=True,
autoload_with=engine,
schema=DBName.dbo)
mapper(Test, tbl_test)
My databases were provided by a vendor and are on Microsoft SQL Server
2000. By analyzing my code in WingWare's debugger, it looks like the
first table is getting reflected correctly in the line
self.dialect.reflecttable(conn, table, include_columns) at least.
Any pointers would be great. I am using Python 2.5 if that matters.
Thanks!
Mike
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
Hi,
Actually, that was just a placeholder name since I'm not sure that my
employer would like me sticking my tables online. Anyway, the
tablename in Microsoft's Enterprise Manager shows it as being all
lowercase although when I run queries against it in MS Query Analyzer,
it uses all uppercase.
All the column names in the table are in uppercase and there are no
foreign keys. The vendor also didn't bother setting a primary key.
So, I decided to try accessing the table without autoload. Since there
are quite a few columns, I thought I'd also try grabbing only the
columns I wanted. For a simple test, I did this:
test_tbl = Table(tablename, metadata,
Column('id', Integer, primary_key=True),
Column(CYCLE_CODE, String))
Unfortunately, MSSQL is reporting that my fake primary key column is
invalid. Is there a workaround? Do I need to recreate all the Columns
in a Table object and also in my class definition?
Sorry for all the trouble.
Mike
On Jun 3, 3:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
tableTwo is a case sensitive name so must be spelled out with that exact
case. in particular if your database is returning foreign key names
without proper case sensitivity then issues will occur with this. turn
echo='debug' to see all SQL emitted and result sets returned.
Mike Driscoll wrote:
Hi,
That fixed that issue. However, now I'm getting an error that my 2nd
table doesn't exist:
sqlalchemy.exc.NoSuchTableError: tableTwo
This is weird since it's been around since 2007 and has lots of data
in it. I tried changing the name to all lowercase and all uppercase,
but the error remains the same. I also tried removing the schema line
to no avail.
Here's the complete traceback:
File D:\pyRetention\Scripts\paster, line 5, in module
pkg_resources.run_script('pastescript==1.7.3', 'paster')
File c:\Python25\Lib\site-packages\pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File c:\Python25\Lib\site-packages\pkg_resources.py, line 1166, in
run_script
execfile(script_filename, namespace, namespace)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\EGG-
INFO\scripts\paster, line 18, in module
command.run()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 218, in run
result = self.command()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 276, in command
relative_to=base, global_conf=vars)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 313, in loadapp
**kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 204, in loadapp
return loadobj(APP, uri, name=name, **kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 225, in loadobj
return context.create()
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 625, in create
return self.object_type.invoke(self)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 110, in invoke
return fix_call(context.object, context.global_conf,
**context.local_conf)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\util\fixtypeerror.py, line 57, in fix_call
val = callable(*args, **kw)
File D:\pyRetention\pyRetention\pyretention\config\middleware.py,
line 35, in make_app
app = make_base_app(global_conf, full_stack=True, **app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 588, in make_base_app
load_environment(global_conf, app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 393, in setup_sqlalchemy
self.package.model.init_model(engine)
File d:\pyRetention\pyRetention\pyretention\model\__init__.py, line
70, in init_model
autoload=True, autoload_with=engine)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
\sqlalchemy\schema.py, line 113, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
\sqlalchemy\schema.py, line 239, in __init__
autoload_with.reflecttable(self, include_columns=include_columns)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
[sqlalchemy] Re: SqlAlchemy reflection error in TurboGears 2
i don't see what the purpose of a fake id column serves here. you can
make a Table object and place within it as many actual columns as you
want, including primary key columns. if the table name is truly all
uppercase, then you need to name it that way in the Table object.
Mike Driscoll wrote:
Hi,
Actually, that was just a placeholder name since I'm not sure that my
employer would like me sticking my tables online. Anyway, the
tablename in Microsoft's Enterprise Manager shows it as being all
lowercase although when I run queries against it in MS Query Analyzer,
it uses all uppercase.
All the column names in the table are in uppercase and there are no
foreign keys. The vendor also didn't bother setting a primary key.
So, I decided to try accessing the table without autoload. Since there
are quite a few columns, I thought I'd also try grabbing only the
columns I wanted. For a simple test, I did this:
test_tbl = Table(tablename, metadata,
Column('id', Integer, primary_key=True),
Column(CYCLE_CODE, String))
Unfortunately, MSSQL is reporting that my fake primary key column is
invalid. Is there a workaround? Do I need to recreate all the Columns
in a Table object and also in my class definition?
Sorry for all the trouble.
Mike
On Jun 3, 3:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
tableTwo is a case sensitive name so must be spelled out with that exact
case. in particular if your database is returning foreign key names
without proper case sensitivity then issues will occur with this. turn
echo='debug' to see all SQL emitted and result sets returned.
Mike Driscoll wrote:
Hi,
That fixed that issue. However, now I'm getting an error that my 2nd
table doesn't exist:
sqlalchemy.exc.NoSuchTableError: tableTwo
This is weird since it's been around since 2007 and has lots of data
in it. I tried changing the name to all lowercase and all uppercase,
but the error remains the same. I also tried removing the schema line
to no avail.
Here's the complete traceback:
File D:\pyRetention\Scripts\paster, line 5, in module
pkg_resources.run_script('pastescript==1.7.3', 'paster')
File c:\Python25\Lib\site-packages\pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File c:\Python25\Lib\site-packages\pkg_resources.py, line 1166, in
run_script
execfile(script_filename, namespace, namespace)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\EGG-
INFO\scripts\paster, line 18, in module
command.run()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 84, in run
invoke(command, command_name, options, args[1:])
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 123, in invoke
exit_code = runner.run(args)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\command.py, line 218, in run
result = self.command()
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 276, in command
relative_to=base, global_conf=vars)
File c:\Python25\Lib\site-packages\pastescript-1.7.3-py2.5.egg\paste
\script\serve.py, line 313, in loadapp
**kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 204, in loadapp
return loadobj(APP, uri, name=name, **kw)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 225, in loadobj
return context.create()
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 625, in create
return self.object_type.invoke(self)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\loadwsgi.py, line 110, in invoke
return fix_call(context.object, context.global_conf,
**context.local_conf)
File c:\Python25\Lib\site-packages\pastedeploy-1.3.3-py2.5.egg\paste
\deploy\util\fixtypeerror.py, line 57, in fix_call
val = callable(*args, **kw)
File D:\pyRetention\pyRetention\pyretention\config\middleware.py,
line 35, in make_app
app = make_base_app(global_conf, full_stack=True, **app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 588, in make_base_app
load_environment(global_conf, app_conf)
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 438, in load_environment
self.setup_sqlalchemy()
File c:\Python25\Lib\site-packages\turbogears2-2.0-py2.5.egg\tg
\configuration.py, line 393, in setup_sqlalchemy
self.package.model.init_model(engine)
File d:\pyRetention\pyRetention\pyretention\model\__init__.py, line
70, in init_model
autoload=True, autoload_with=engine)
File c:\Python25\Lib\site-packages\sqlalchemy-0.5.4p2-py2.5.egg
\sqlalchemy\schema.py, line 113, in
