I am using SqlSoup to read from an MS SQL database and I'm having some
issues building a many-to-many relation on the mapping. The database
doesn't have any foreign keys, so I'm manually specifying the join
conditions and keys.
Specifying the relation returns without exceptions, but as soon as I
query the primary object, even with something as simple as .first(),
SQLAlchemy tries to insert a row into the association table. This
seems like incorrect behavior.
db = SqlSoup(url)
# A Product has many Categories, via the ProductCategory association
table.
db.Products.relate('categories', db.Categories,
secondary=db.ProductCategory,
primaryjoin=db.Products.c.ManProdCode ==
db.ProductCategory.c.ManProdCode,
secondaryjoin=db.ProductCategory.c.Category1 ==
db.Categories.c.CatCode,
foreign_keys=[db.ProductCategory.c.ManProdCode,
db.ProductCategory.c.Category1])
db.Products.first() # Fails!
I am using SQLAlchemy 0.5.2 with pymssql 0.8.0 connecting through
FreeTDS 1.12 to MS SQL Server 9.0.2047. I also checked it with the svn
trunk (r5823) and the behavior is the same.
The association table is nothing special, basically:
(
ManProdCode CHAR(5) NOT NULL,
Category1 CHAR(2),
Category2 CHAR(2)
)
The user I'm connecting with does not have INSERT privileges, so a
DatabaseError is raised when db.Products.first() is called and SA
tries to do the INSERT. Any idea why SA is trying to do this?
Thanks!
Scott
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "sqlalchemy/orm/query.py", line 1219, in first
ret = list(self[0:1])
File "sqlalchemy/orm/query.py", line 1140, in __getitem__
return list(res)
File "sqlalchemy/orm/query.py", line 1279, in __iter__
self.session._autoflush()
File "sqlalchemy/orm/session.py", line 902, in _autoflush
self.flush()
File "sqlalchemy/orm/session.py", line 1347, in flush
self._flush(objects)
File "sqlalchemy/orm/session.py", line 1417, in _flush
flush_context.execute()
File "sqlalchemy/orm/unitofwork.py", line 244, in execute
UOWExecutor().execute(self, tasks)
File "sqlalchemy/orm/unitofwork.py", line 707, in execute
self.execute_save_steps(trans, task)
File "sqlalchemy/orm/unitofwork.py", line 722, in execute_save_steps
self.save_objects(trans, task)
File "sqlalchemy/orm/unitofwork.py", line 713, in save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "sqlalchemy/orm/mapper.py", line 1352, in _save_obj
c = connection.execute(statement.values(value_params), params)
File "sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "sqlalchemy/engine/base.py", line 874, in
_execute_clauseelement
return self.__execute_context(context)
File "sqlalchemy/engine/base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "sqlalchemy/engine/base.py", line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.DatabaseError: (DatabaseError) internal error: SQL
Server message 229, severity 14, state 5, line 1:
INSERT permission denied on object 'ProductCategory', database
'SomeDB', schema 'dbo'.
DB-Lib error message 229, severity 14:
General SQL Server error: Check messages from the SQL Server
'INSERT INTO [ProductCategory] ([Category1], [Category2]) VALUES (%
(Category1)s, %(Category2)s)' {'Category1': None, 'Category2': None}
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
