Sorry for the trouble, after much stepping through source I figured it
out.
`secondary` needs to be a Table object, not a SqlSoup Entity. This
fixes it:
db.Products.relate('categories', db.Categories,
secondary=db.ProductCategory._table, #### _table is your friend!
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])
Cheers,
Scott
On Mar 6, 2:49 pm, Scott Torborg <storb...@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
