[sqlalchemy] Re: pymssql delete problem
Hi Rick, Thanks for your continuing interest in my silly problem Rick Morrison wrote: How are you deleting the rows? Is this via Session.flush(), or an SQL expression statement? Via a session commit/flush Please post some code as to how you're trying this... The code goes something like this (not actual code!) for x in listofproducts: contlist=sess.query(Container).filter_by(Content=x).all() for y in contlist: quantity[x]-=y.Take(quantity[x]) if y.quantity==0: sess.delete(y) if quantity[x]==0: break sess.begin() try: sess.commit() print OK except: sess.rollback() print Ooops! If only one container needs to be deleted, it works. More than one and it fails. It all works when using MySQL. Cheers, François --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: some error at v4070
fixed in r4156 On Feb 11, 2008, at 4:20 AM, svilen wrote: hi. running the dbcook tests, from v4070 onwards i get the following error: Traceback (most recent call last): File rr.py, line 68, in module for q in session.query(A).all(): print q File sqlalchemy/orm/query.py, line 746, in all return list(self) File sqlalchemy/orm/query.py, line 887, in iterate_instances rows.append(main(context, row)) File sqlalchemy/orm/query.py, line 831, in main extension=context.extension, only_load_props=context.only_load_props, refresh_instance=context.refresh_instance File sqlalchemy/orm/mapper.py, line 1291, in _instance row = self.translate_row(mapper, row) File sqlalchemy/orm/mapper.py, line 1388, in translate_row translator = create_row_adapter(self.mapped_table, tomapper.mapped_table, equivalent_columns=self._equivalent_columns) File sqlalchemy/orm/util.py, line 202, in create_row_adapter corr = from_.corresponding_column(c) File sqlalchemy/sql/expression.py, line 1628, in corresponding_column i = c.proxy_set.intersection(target_set) AttributeError: 'NoneType' object has no attribute 'proxy_set' The case is somewhat strange but is working before that - a polymorphic union of concrete inheritances where the root-table is not included in the union (i.e. leafs only). If it gets included, the error goes away. i dont know, if it's the case that is too weird, i could workaround it possibly. svilen rr.py --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Sprint
That sounds great -- count me in. It's going to have to be after the 28th for me; yet another deadline in progress. BTW I've got a working LIMIT/OFFSET implementation in a local patch here if you're interested - MSSQL 2005-only: uses row_number() like the Oracle implementation. Rick --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL Sprint
Hi, Would anyone like to join me in doing a one day sprint on MSSQL support in SQLAlchemy? (Rick - hope you can find some time) I feel we're at the point now where the last few niggles could be ironed out quite quickly. I've just lost steam a bit doing this on my own. The main points would be: 1) Add support for limit with offset 2) Fix the last few failing unit tests for pyodbc on Windows against SQL 2005 and 2000 3) Close off the remaining MSSQL tickets 4) Take a look at pyodbc on Unix Let me know if you're interested, we can figure out a date that suits everyone. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Hi, ...and what happens if these methods are called and the tables already exist? With metadata.create_all, it only creates ones that don't exist. table.create() will error, or if you use the checkfirst option, will do nothing. What if they exist but don't match the spec that SA has created? SA doesn't know, so it continues until you hit a problem. We should really pull the code in tg-admin sql out into a standalone script. I think having a model vs database diff function would help you out a lot. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: building mappers for an existing database
svilen wrote: probably something like it. Reverse engineering the db, and mime self accordingly, IF possible. I don't know what and mime self accordingly means... See the autoload=true flag to metadata and tables, it does most of the job. Not sure what you're referring to here... cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Association objects in 0.4.2p3
On Feb 12, 2008, at 9:41 AM, Donovan Kolbly wrote:
I am trying to go through the mapping tutorial in 0.4.2p3 for
association objects. I get an error about Could not assemble any
primary key columns for mapped table 'association' when attempting to
map the association table itself. This is straight out of the Mapping
Configuration docs that comes with the distribution...
Any thoughts on where things are going awry?
Here's my complete code:
from sqlalchemy import create_engine, \
Table, Column, Integer, String, \
MetaData, ForeignKey
from sqlalchemy.orm import relation, sessionmaker, mapper
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData();
left_table = Table('left', metadata,
Column('id', Integer, primary_key=True))
right_table = Table('right', metadata,
Column('id', Integer, primary_key=True))
association_table = Table('association', metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id')),
Column('data', String(50))
)
class Parent(object): pass
class Association(object): pass
class Child(object): pass
mapper( Parent, left_table, properties={
'children':relation(Association)
})
mapper( Association, association_table, properties={
'child':relation(Child)# --- chokes here
})
mapper( Child, right_table)
metadata.create_all( engine )
the association_table itself has no primary key columns, so you have
to tell the mapper which columns it should consider to be the primary
key:
mapper(Association, association_table, properties={...},
primary_key=[association_table.c.left_id, association_table.c.right_id])
Ill update the docs right now.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
Paul Johnston wrote: SA will only try to create table when you tell it - either table.create() or metadata.create_all() ...and what happens if these methods are called and the tables already exist? What if they exist but don't match the spec that SA has created? cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Polymorphic and inheritance with missing children
On Feb 11, 2008, at 10:25 PM, Richard Levasseur wrote:
Ok, so I tried this. It works fine, the only catch is that yeah,
the pid and cid are hardcoded in there. I couldn't figure out any
way to determine them programatically. I can get the join condition
clause, but its just a binary expression object (essentially saying
pid = cid), and I don't know how to extract the portions reliably
(from the sounds of things, it doesn't sound possible at all). It
looks like cid is always on the right, and pid is always on the
left, but I'm guessing thats just deterministic chance, and wouldn't
know what to do when its a more complicated expression. This
worries me because I know there are other tables that will require
more complicated join conditions (where deleted/archived/hidden == 0
or IS NULL, or some such thing)
to programmatically determine columns on a table, use the table.c
collection. the direction of the join clause is not really
important here (dont see how thats related?) it should be easy enough
to just say:
for c in childtable.c:
setattr(instance, c.name, None)
i.e. when the row does not contain columns from childtable.
A little bit of experimentation with the Table construct should reveal
that pretty much anything is possible there.
It looks like the nested post_execute def has a reference to
`statement` that it uses to figure out the join condition and issue
the subquery. If I could simply call that inside my extension's
populate_instance and handle the exception, that'd probably work.
sure, try calling that. But then, you could also adapt the source
code of that to do more specifically what you need.
Ok, let me give a more practical example:
Lets say we have the following schema:
Persons(pid, etype, name, is_active)
Managers(pid, mid, level, full_team)
Engineers(pid, eid, language)
Lets say there's a single search on the webpage and users can enter
in queries like:
1) engineer.language:Java OR manager.level:5
2) name:john
3) engineer.language: python
the psuedo-sql for those queries should be something like
1) select * from persons left join engineers on pid=pid left join
managers on pid=pid and full_team=0 where engineers.language=Java
or managers.level=5
2) select * from persons where name ='john''
3) select * from persons left join engineers using pid where
engineers.language='python'
If in the above example the mapper were configured with select_table,
mapper(Person, people,
select_table=people.outerjoin(engineers).outerjoin(managers))
you can filter on those columns directly:
sess.query(Person).filter(or_(Engineer.language=='java',
Manager.level==5))
if you don't want to use select_table, then you can set up the joins
on a per-query basis:
sess
.query
(Person
).select_from
(people
.outerjoin
(engineers).outerjoin(managers)).filter(or_(Engineer.language=='java',
Manager.level==5))
theres a query.join() call but that currently is used for joining
along relations between classes, which is not quite what we have here.
As far as the mappers seeing Engineer.language and magically knowing
to add engineers to the base table of people on its own that seems
a little magical to me (im not sure how it could guess the desired
action herelike how would it know to outerjoin and not join to
engineers ? how would it know that you didnt join to engineers in
some other way already ?). For reference, Hibernate inheritance could
never do that; it would require that you query specifically for
Engineer before specifying Engineer-specific criterion.
Note that for (1), it has the additional full_team=0 condition as
part of the join itself.
So, depending on the user's query, we need to join to different
tables.
yeah in any case, you'd need to specify that. Even with joins along
relations, we still require that you say query.join('relationname'),
which is less verbose than using the full join condition but still
requires explicitness. We dont guess joins at the query level. The
best we can do is some eventual feature like
query(Person).polymorphic_with(Engineer).filter(), something like
that.
For the api, it'd be nice to do something like:
session
.query(Person).filter(Manager.level=5).filter(Person.is_active==1)
And the orm uses the join conditions we defined elsewhere (probably
on the mapper?).
if you said
session
.query
(Person
).filter(Manager.person_id==Person.person_id).filter(Manager.level ==
5), then you've already joined to Managers. Thats what I mean by we
can't guess.
Right now, it'll join to the tables it needs, but it won't put in
the join conditions. I know its in there somewhere, otherwise it
couldn't do the subquery (...right?). Another catch I'm seeing is
how to define those additional join conditions (use select_table
with a custom condition?). It looks like its
[sqlalchemy] Re: Joining a table to itself.
Hello Nick, Nick Murdoch wrote: I'm having trouble setting up a relationship between one table and itself. Check out the Adjacency List Relationships chapter from the docs. It explains how to deal with self-referential relations. http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Entity name None - solved
Michael, Michael Bayer wrote: On Feb 11, 2008, at 12:02 PM, Werner F. Bruhin wrote: Thanks again for you quick reply. I had a case problem, my string was containing the mapper name instead of the object name. not sure if you're referring to your emailthe error basically means no mapper() is set up for the object in question. Your first reply pointed me in the right direction, i.e. I found out that I was not working with the object I thought I was working as I used the incorrect name (see below). container_lm = sao.mapper(. class Container_Lm(Object): Thanks again for your help and best regards Werner --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Association objects in 0.4.2p3
I am trying to go through the mapping tutorial in 0.4.2p3 for
association objects. I get an error about Could not assemble any
primary key columns for mapped table 'association' when attempting to
map the association table itself. This is straight out of the Mapping
Configuration docs that comes with the distribution...
Any thoughts on where things are going awry?
Here's my complete code:
from sqlalchemy import create_engine, \
Table, Column, Integer, String, \
MetaData, ForeignKey
from sqlalchemy.orm import relation, sessionmaker, mapper
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData();
left_table = Table('left', metadata,
Column('id', Integer, primary_key=True))
right_table = Table('right', metadata,
Column('id', Integer, primary_key=True))
association_table = Table('association', metadata,
Column('left_id', Integer, ForeignKey('left.id')),
Column('right_id', Integer, ForeignKey('right.id')),
Column('data', String(50))
)
class Parent(object): pass
class Association(object): pass
class Child(object): pass
mapper( Parent, left_table, properties={
'children':relation(Association)
})
mapper( Association, association_table, properties={
'child':relation(Child)# --- chokes here
})
mapper( Child, right_table)
metadata.create_all( engine )
And here is what I get:
$ python -i posting.py
Traceback (most recent call last):
File posting.py, line 31, in module
'child':relation(Child)# --- chokes here
File /tmp/SQLAlchemy-0.4.2p3/lib/sqlalchemy/orm/__init__.py, line
544, in mapper
return Mapper(class_, local_table, *args, **params)
File /tmp/SQLAlchemy-0.4.2p3/lib/sqlalchemy/orm/mapper.py, line
160, in __init__
self._compile_pks()
File /tmp/SQLAlchemy-0.4.2p3/lib/sqlalchemy/orm/mapper.py, line
428, in _compile_pks
raise exceptions.ArgumentError(Could not assemble any primary key
columns for mapped table '%s' % (self.mapped_table.name))
sqlalchemy.exceptions.ArgumentError: Could not assemble any primary
key columns for mapped table 'association'
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: schema changes
On Feb 12, 2008, at 12:34 PM, Chris Withers wrote: Paul Johnston wrote: SA will only try to create table when you tell it - either table.create() or metadata.create_all() ...and what happens if these methods are called and the tables already exist? What if they exist but don't match the spec that SA has created? just try it out...create_all() by default checks the system tables for the presence of a table first before attempting to create it (same with dropping). this is controlled by a flag called checkfirst. it the table exists, nothing is created. theres no comparison which takes place between the table in the DB and whats defined in your application. if you're concerned about people running your application against databases created from a different version and then failing, I would suggest adding a version table to your database which contains data corresponding against the version of your application in some way. There has been interest among some SA users over building a generic schema comparison system and I think even some prototypes are available, though I think thats a fairly complicated and unreliable approach to take for this particular issue. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
