[sqlalchemy] Re: trunk is now on 0.5
hi all my tests run ok on this (as well 0.4), except the concrete-inh case reported in http://groups.google.com/group/sqlalchemy/browse_thread/thread/92417a6f215fa8d8/c72eb562a3070bd9 - attached again. it's about concrete inh B of A, where polymunion contains only B, and there are instances of B only. in older versions (pre v4371) query(A) would produce Bs; in newer query(A) produces nothing. which seems wrong to me.. Any howto pointers about the new UDS? my hacks dont work anymore .. Also i have some idea of reworking the sql.visitors, when i finish i'll post it. What to use for speed test of that? did u migrate there toward functions because of speed or what? my opinion on the controversies: - Python 2.3. drop it - Unicode. Consideration of going further into being unicode everywhere, i'm against unicode being 'the one and the only'. it's like ascii was long time ago. i dont think becoming unicode everywhere inside SA will reduce the number of 'conversion issues' as they always happen on the boundaries - in/out - and there u'll have same stuff.. ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- _test_ABC_all.py Description: application/python
[sqlalchemy] Re: trunk is now on 0.5
one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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] Reconnection to PostgreSQL
Hi, I am using SQLALchemy 0.4.5 and PostgreSQL 8.1 db with psycopg2. If I restart the db I need to also restart my python server that is using the SQLAlchemy for interfacing the PostgreSQL db. Is there any means in the SQLAlchemy to automatically reconnect to the db without needing to restart the server? I do not mean pool_recyle-attribute, because that does not do the reconnection when needed but just in specified intervals. --~--~-~--~~~---~--~~ 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: Reconnection to PostgreSQL
On May 12, 2008, at 7:02 AM, nymaol wrote: Hi, I am using SQLALchemy 0.4.5 and PostgreSQL 8.1 db with psycopg2. If I restart the db I need to also restart my python server that is using the SQLAlchemy for interfacing the PostgreSQL db. Is there any means in the SQLAlchemy to automatically reconnect to the db without needing to restart the server? I do not mean pool_recyle-attribute, because that does not do the reconnection when needed but just in specified intervals. the Connection will hit a connection closed error when first used; when that's detected, the entire connection pool is recycled. so the error should happen exactly once and then resolve itself after that. psycopg2 has been known to have a lot of quirks in this area, such as not throwing the exception in a way that it can be caught in all cases, so feel free to document situations where this does not work as expected. (use recent versions of psycopg2/sqla for best results too). --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... the resetjoinpoint is one possibility, and some left_bracket()/right_bracket() is another. another way is to be able to do boolean arithmetics over whole queries, maybe thats even better? query.or_( query.filter(this).join(that), query.filter(that).join(this) ) On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6 798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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] 2 Many to many relations with extra-columns - How to for a newb
Hello All,
I am trying to understand how to use SA and need some help.
I have several tables with 2 many-to-many relations with extra columns
and 1 only with foreign keys.
See below for the definitions of tables and mappers. I also created
classes for all tables (entities and associations).
1) For the association without extra-column (self.correspond), no
problem.
I can add questions and categories. For instance:
q=Question(question='blabla')
c=Category('cat1')
q.categories.append(c)
session.save(q)
session.commit()
2) For the 2 other which have extra-columns, I don't understand how to
manage.
For info, these 2 associations relate to both the users and the
questions tables.
For instance, how can I add a question related to a user, ie go
through the ask relation ?
I went through the excellent documentation but I have to admit that I
don't understand...
Can somebody :
- check my mappers are well defined (those with extra columns:
askMapper and answerMapper and also questMapper)
- briefly explain me how to handle operations between users and
questions tables through these mappers
I'm hoping it is clear enough
Thanks a lot in advance for your help
Dominique
Tables and relations are as follows:
#Entities
self.users = Table('users',self.metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', Unicode(25), unique = True))
self.categories = Table('categories',self.metadata,
Column('categ_id',Integer, primary_key = True),
Column('categ_name',Unicode(250), unique = True))#
rajouter unique
self.questions = Table('questions', self.metadata,
Column('quest_id', Integer, primary_key = True),
Column('question', Unicode(300)))
# Associations
self.correspond = Table('categories_questions', self.metadata,
Column('quest_id', Integer,
ForeignKey('questions.quest_id'), primary_key = True),
Column('categ_id', Integer,
ForeignKey('categories.categ_id'), primary_key = True))
self.ask = Table('ask', self.metadata,
Column('user_id',Integer,
ForeignKey('users.user_id'), primary_key = True),
Column('quest_id',Integer,
ForeignKey('questions.quest_id'), primary_key = True),
Column('data1',Integer, nullable = False, default
= 50))
self.answer = Table('answer',self.metadata,
Column('user_id',Integer,
ForeignKey('users.user_id'), primary_key=True),
Column('quest_id',Integer,
ForeignKey('questions.quest_id'), primary_key=True),
Column('data2',Integer),
ForeignKeyConstraint(['user_id','quest_id'],
['ask.user_id','ask.quest_id']))
# mappers
self.userMapper = mapper(User, self.users)
self.categMapper = mapper(Category, self.categories)
self.questMapper = mapper(Question, self.questions, properties ={
# ManyToMany CorrespondAssociation between
questions and categories
'categories': relation(Category, secondary =
self.correspond, backref='questions'),
# ManyToMany AskAssociation between questions and
users
'users': relation(AskAss, backref='questions'),
# ManyToMany AnswerAssociation between questions
and users
'users': relation(AnswerAss, backref='questions')
})
self.askMapper = mapper(AskAss, self.poser, properties = {
# Ask Association between questions and users
'users': relation(User, backref = 'ask')
})
self.answerMapper = mapper(AnswerAss, self.answer, properties = {
# ManyToMany AnswerAssociation between questions
and users
'users': relation(User, backref = 'answer')
})
--~--~-~--~~~---~--~~
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: trunk is now on 0.5
-1. It's confusing, and there's already an extant or_ function that's documented and not confusing. The proposal is no more cooked than it was five months ago. On Mon, May 12, 2008 at 11:58 AM, [EMAIL PROTECTED] wrote: On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... the resetjoinpoint is one possibility, and some left_bracket()/right_bracket() is another. another way is to be able to do boolean arithmetics over whole queries, maybe thats even better? query.or_( query.filter(this).join(that), query.filter(that).join(this) ) On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6 798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
I dont see how this: cls.query.left_bracket().filter_or(cls.y == 17).filter_or(cls.x==27).right_bracket() is clearer than this: cls.query.filter(or_(cls.y == 17, cls.x==27)) Also, another vote for cutting off python 2.3. Seriously, it's 2008. On Mon, May 12, 2008 at 11:58 AM, [EMAIL PROTECTED] wrote: On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... the resetjoinpoint is one possibility, and some left_bracket()/right_bracket() is another. another way is to be able to do boolean arithmetics over whole queries, maybe thats even better? query.or_( query.filter(this).join(that), query.filter(that).join(this) ) On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thread/f6 798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
On May 12, 2008, at 11:58 AM, [EMAIL PROTECTED] wrote: On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... how about or_() ? --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
On Monday 12 May 2008 18:12:39 Bobby Impollonia wrote: I dont see how this: cls.query.left_bracket().filter_or(cls.y == 17).filter_or(cls.x==27).right_bracket() is clearer than this: cls.query.filter(or_(cls.y == 17, cls.x==27)) it's not. it's not about replacing the or_. noone should use it that way... u have horses and owners with houses and hats. give me horses (whose owners have a hat of size 10 and color green) or (whose owners have a house of color red) or (have no tail) On Mon, May 12, 2008 at 11:58 AM, [EMAIL PROTECTED] wrote: On Monday 12 May 2008 17:01:23 Michael Bayer wrote: what does q.filter(x==5).filter_or(x==9).filter(y=17).filter_or(x==27) do ? (x=5 or x=9) and (y=17 or x=27) ? ((x=5 or x=9) and y=17) or x=27 ? etc .. what pythons/C x==5 or x==9 and y==17 or x==27 does? i know... the parenthesises. cant we invent something? it's not for tomorrow... the resetjoinpoint is one possibility, and some left_bracket()/right_bracket() is another. another way is to be able to do boolean arithmetics over whole queries, maybe thats even better? query.or_( query.filter(this).join(that), query.filter(that).join(this) ) On May 12, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: one thing that might go in a wishlist - query.filter_or() http://groups.google.com/group/sqlalchemy/browse_thread/thre ad/f6 798eb5ef2c0bfe should i make it into a ticket? as you might have noticed we've merged 0.5 into the trunk. --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
cls.query.left_bracket().filter_or(cls.y == 17).filter_or(cls.x==27).right_bracket() is clearer than this: cls.query.filter(or_(cls.y == 17, cls.x==27)) it's not. it's not about replacing the or_. noone should use it that way... u have horses and owners with houses and hats. give me horses (whose owners have a hat of size 10 and color green) or (whose owners have a house of color red) or (have no tail) filter(or_( horses.owners.any(hatsize=10, color='green'), horses.owners.any(and_(owners.house_id==houses.house_id, houses.color=='red')), horses.tail == None )) ahha, so thats how joins can go inside or_(). isn't this (i guess it has subselects) more expensive than a single plain or/and clause? --~--~-~--~~~---~--~~ 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] sessionExtension
hi i have some pre_save automatic operations and decided MVC-like to split them into 2 phases - a per-object validation-only that goes at save(), and a per-flush set-up 2nd phase for putting timestamps etc. The 2nd one is via SessionExtension.before_flush(). i'm looking at the session state at that time: - what is the list of all instances going to be changed? something like all = ses.new() + [ i for i in ses.dirty() if ses.modified(i) ] - would this include all relation-cascaded items or not? - as flush hasn't happened yet, i guess dependencies are not available - or are they? ciao svil --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
On May 12, 2008, at 1:48 PM, [EMAIL PROTECTED] wrote:
cls.query.left_bracket().filter_or(cls.y ==
17).filter_or(cls.x==27).right_bracket()
is clearer than this:
cls.query.filter(or_(cls.y == 17, cls.x==27))
it's not. it's not about replacing the or_. noone should use it
that way...
u have horses and owners with houses and hats.
give me horses (whose owners have a hat of size 10 and color
green) or (whose owners have a house of color red) or (have no
tail)
filter(or_(
horses.owners.any(hatsize=10, color='green'),
horses.owners.any(and_(owners.house_id==houses.house_id,
houses.color=='red')),
horses.tail == None
))
ahha, so thats how joins can go inside or_().
isn't this (i guess it has subselects) more expensive than a single
plain or/and clause?
if you wanted to spell it out with JOIN you can do that too -
house_owners = aliased(Owner)
hat_owners = aliased(Owner)
query(Horse).outerjoin((house_owners, 'owners'), 'houses').\
outerjoin((hat_owners, 'owners'), 'hats').\
filter(or_(
and_(Hat.size==10, Hat.color=='green'),
House.color=='red',
Horse.tail == None
))
im not sure but you might be also able to disable aliasing on House/
Hat if you said query.outerjoin('owners', (House, 'houses'),
aliased=True)...thats a little more experimental tho.
--~--~-~--~~~---~--~~
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: sessionExtension
On May 12, 2008, at 2:08 PM, [EMAIL PROTECTED] wrote: hi i have some pre_save automatic operations and decided MVC-like to split them into 2 phases - a per-object validation-only that goes at save(), and a per-flush set-up 2nd phase for putting timestamps etc. The 2nd one is via SessionExtension.before_flush(). i'm looking at the session state at that time: - what is the list of all instances going to be changed? something like all = ses.new() + [ i for i in ses.dirty() if ses.modified(i) ] theres a method that tells you is this *really* modified called session.is_modified(x). - would this include all relation-cascaded items or not? it wont include changes on foreign key columns nor every kind of orphaned object. - as flush hasn't happened yet, i guess dependencies are not available - or are they? some of them, but not all. --~--~-~--~~~---~--~~ 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: sessionExtension
i have some pre_save automatic operations and decided MVC-like to split them into 2 phases - a per-object validation-only that goes at save(), and a per-flush set-up 2nd phase for putting timestamps etc. The 2nd one is via SessionExtension.before_flush(). i'm looking at the session state at that time: - what is the list of all instances going to be changed? something like all = ses.new() + [ i for i in ses.dirty() if ses.modified(i) ] theres a method that tells you is this *really* modified called session.is_modified(x). - would this include all relation-cascaded items or not? it wont include changes on foreign key columns nor every kind of orphaned object. orphans i dont care, but the rest... So at what time these are available - after_flush? or before_commit? there isn't going to be deletion... and there isnt going to be much update either... both mostly mean insert new version, with all the many2many links copied anew. i need to iterate over *all* would-be written-to-DB objects, and fix their timestamps. But maybe i need just the *new* objects - explicitly sess.added() or implicitly via relation. would before_flush suffice for them? --~--~-~--~~~---~--~~ 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: sessionExtension
On May 12, 2008, at 2:37 PM, [EMAIL PROTECTED] wrote: orphans i dont care, but the rest... So at what time these are available - after_flush? or before_commit? for foreign key attributes written by a dependency, before_insert(). i need to iterate over *all* would-be written-to-DB objects, and fix their timestamps. im gathering theres a great reason that triggers or regular column defaults cant do this (or just before_insert()). But maybe i need just the *new* objects - explicitly sess.added() or implicitly via relation. would before_flush suffice for them? sure new objects are all defined at flush time. --~--~-~--~~~---~--~~ 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: 2 Many to many relations with extra-columns - How to for a newb
lets all repeat the mantraassociation tables with any columns
beyond the two foreign keys use the association object pattern. I
guess its a little too wordy to be catchy. Documented at
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
.
On May 12, 2008, at 10:45 AM, [EMAIL PROTECTED] wrote:
Hello All,
I am trying to understand how to use SA and need some help.
I have several tables with 2 many-to-many relations with extra columns
and 1 only with foreign keys.
See below for the definitions of tables and mappers. I also created
classes for all tables (entities and associations).
1) For the association without extra-column (self.correspond), no
problem.
I can add questions and categories. For instance:
q=Question(question='blabla')
c=Category('cat1')
q.categories.append(c)
session.save(q)
session.commit()
2) For the 2 other which have extra-columns, I don't understand how to
manage.
For info, these 2 associations relate to both the users and the
questions tables.
For instance, how can I add a question related to a user, ie go
through the ask relation ?
I went through the excellent documentation but I have to admit that I
don't understand...
Can somebody :
- check my mappers are well defined (those with extra columns:
askMapper and answerMapper and also questMapper)
- briefly explain me how to handle operations between users and
questions tables through these mappers
I'm hoping it is clear enough
Thanks a lot in advance for your help
Dominique
Tables and relations are as follows:
#Entities
self.users = Table('users',self.metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', Unicode(25), unique = True))
self.categories = Table('categories',self.metadata,
Column('categ_id',Integer, primary_key = True),
Column('categ_name',Unicode(250), unique = True))#
rajouter unique
self.questions = Table('questions', self.metadata,
Column('quest_id', Integer, primary_key = True),
Column('question', Unicode(300)))
# Associations
self.correspond = Table('categories_questions', self.metadata,
Column('quest_id', Integer,
ForeignKey('questions.quest_id'), primary_key = True),
Column('categ_id', Integer,
ForeignKey('categories.categ_id'), primary_key = True))
self.ask = Table('ask', self.metadata,
Column('user_id',Integer,
ForeignKey('users.user_id'), primary_key = True),
Column('quest_id',Integer,
ForeignKey('questions.quest_id'), primary_key = True),
Column('data1',Integer, nullable = False, default
= 50))
self.answer = Table('answer',self.metadata,
Column('user_id',Integer,
ForeignKey('users.user_id'), primary_key=True),
Column('quest_id',Integer,
ForeignKey('questions.quest_id'), primary_key=True),
Column('data2',Integer),
ForeignKeyConstraint(['user_id','quest_id'],
['ask.user_id','ask.quest_id']))
# mappers
self.userMapper = mapper(User, self.users)
self.categMapper = mapper(Category, self.categories)
self.questMapper = mapper(Question, self.questions, properties ={
# ManyToMany CorrespondAssociation between
questions and categories
'categories': relation(Category, secondary =
self.correspond, backref='questions'),
# ManyToMany AskAssociation between questions and
users
'users': relation(AskAss, backref='questions'),
# ManyToMany AnswerAssociation between questions
and users
'users': relation(AnswerAss, backref='questions')
})
self.askMapper = mapper(AskAss, self.poser, properties = {
# Ask Association between questions and users
'users': relation(User, backref = 'ask')
})
self.answerMapper = mapper(AnswerAss, self.answer, properties = {
# ManyToMany AnswerAssociation between questions
and users
'users': relation(User, backref = 'answer')
})
--~--~-~--~~~---~--~~
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: sessionExtension
orphans i dont care, but the rest... So at what time these are available - after_flush? or before_commit? for foreign key attributes written by a dependency, before_insert(). it might be useful if there is a simple sequence diagram - textual is ok - for which kind of extension and which method of it is called at what time within session's lifetime. Same as the metadata-mappers-session-transaction lifespan in overall SA-usage lifetime - if u remember we did talk about this once. lifespans and hook-points in them do matter a lot. maybe for the book? i need to iterate over *all* would-be written-to-DB objects, and fix their timestamps. im gathering theres a great reason that triggers or regular column defaults cant do this (or just before_insert()). now thinking about it... can column_defaults be my functions? pythonside? how about their context? i dont know, sessExt seemed like just one call to do everything. svil --~--~-~--~~~---~--~~ 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: sessionExtension
On May 12, 2008, at 3:16 PM, [EMAIL PROTECTED] wrote: orphans i dont care, but the rest... So at what time these are available - after_flush? or before_commit? for foreign key attributes written by a dependency, before_insert(). it might be useful if there is a simple sequence diagram - textual is ok - for which kind of extension and which method of it is called at what time within session's lifetime. Same as the metadata-mappers-session-transaction lifespan in overall SA-usage lifetime - if u remember we did talk about this once. lifespans and hook-points in them do matter a lot. maybe for the book? i dont think SA will ever be carved in stone to the degree that we can publish line-by-line code flow diagrams in books (or if thats even usefulits just a Python script after all...). seems like something better generated by as-yet-nonexistent tools, also. now thinking about it... can column_defaults be my functions? pythonside? how about their context? i dont know, sessExt seemed like just one call to do everything. sure --~--~-~--~~~---~--~~ 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] Retrieving id (primary key) from newly inserted record when id generated by pre-insert trigger
I saw the posts about SQLAlchemy updating an id when this is generated by SQLAlchemy by setting Sequence() on the table's primary key Column, but being an old Oracle hacker, I am generating the primary keys via pre-insert triggers on the tables. As SQLAlchemy is not selecting seq_name.nextval itself the id of the object is not being updated as is the case above. Is there nonetheless any SQLAlchemy magic to retrieve the value of the newly inserted record? Thanks for your help, Tim --~--~-~--~~~---~--~~ 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] composite primary key/postgres
hi. i have a sort-of multicolumn m2m association table, where the primary key is composed of all the links. At least 1 link (actualy, 2) is always present, but never all. so i am defining all of those columns with primary_key=True, nullable=True. which is fine in sqlite, but doesnot work in postgres - it autoincrements those columns without value. how can i fix this? would a default_value=0 - or something - work? (now as i look at it, at least as declaration, the whole primary key seems nullable - is this wrong?) ciao svil --~--~-~--~~~---~--~~ 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: Retrieving id (primary key) from newly inserted record when id generated by pre-insert trigger
On May 12, 2008, at 2:54 PM, Dr.T wrote: I saw the posts about SQLAlchemy updating an id when this is generated by SQLAlchemy by setting Sequence() on the table's primary key Column, but being an old Oracle hacker, I am generating the primary keys via pre-insert triggers on the tables. As SQLAlchemy is not selecting seq_name.nextval itself the id of the object is not being updated as is the case above. Is there nonetheless any SQLAlchemy magic to retrieve the value of the newly inserted record? there is not in that case since cx_oracle nor OCI provides any way of getting at that value (i.e. cursor.lastrowid is not supported and im not familiar with a reliable select last_inserted_id technique for oracle). With Oracle, SQLA needs to be given a SQL expression which it can execute in order to get at the new ID, *before* its inserted. Using triggers on your table is not entirely incompatible with this, as long as you give SQLA a default generator representing the expression which the trigger calls (using the default keyword argument on Column); SQLA then calls this expression directly and presents the new ID to the INSERT statement, thus bypassing the trigger. If there is in fact some kind of select last_inserted_id function available that im not aware of, the oracle dialect could conceivably be enhanced to support this model as well. --~--~-~--~~~---~--~~ 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: 2 Many to many relations with extra-columns - How to for a newb
Hey -
I wrote you a reasonable test application using your tables. So I
apologize, you werent mapping secondary to an association table, I
thought I saw that but it was an email formatting issue. You were
creating overlapping names to relations though, so the attached script
resolves that and illustrates some sample usage.
I also found a small bug in Query related to your extra
ForeignKeyConstraint (at least in 0.5) which you normally would not
come across, that will be fixed soon.
hope this helps.
- 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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo='debug')
metadata = MetaData()
Session = sessionmaker(bind=engine, transactional=True, autoflush=True)
Base = declarative_base(metadata=metadata)
users = Table('users',metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', Unicode(25), unique = True))
categories = Table('categories',metadata,
Column('categ_id',Integer, primary_key = True),
Column('categ_name',Unicode(250), unique = True))
questions = Table('questions', metadata,
Column('quest_id', Integer, primary_key = True),
Column('question', Unicode(300)))
correspond = Table('categories_questions', metadata,
Column('quest_id', Integer,ForeignKey('questions.quest_id'), primary_key = True),
Column('categ_id', Integer,ForeignKey('categories.categ_id'), primary_key = True))
ask = Table('ask', metadata,
Column('user_id',Integer,ForeignKey('users.user_id'), primary_key = True),
Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key = True),
Column('data1',Integer, nullable = False, default= 50))
answer = Table('answer',metadata,
Column('user_id',Integer,ForeignKey('users.user_id'), primary_key=True),
Column('quest_id',Integer,ForeignKey('questions.quest_id'), primary_key=True),
Column('data2',Integer),
# the double FK constraint here is not invalid, but has revealed a bug
# in current SA when using join() with aliased=True [ticket:1041]
#ForeignKeyConstraint(['user_id','quest_id'],['ask.user_id','ask.quest_id'])
)
class PrettyRepr(object):
def __repr__(self):
return %s(%s) % (
(self.__class__.__name__),
','.join([%s=%s % (key, repr(getattr(self, key))) for key in self.__dict__ if not key.startswith('_')])
)
class User(Base, PrettyRepr):
__table__ = users
def ask_question(self, question, data):
self.ask.append(AskAss(question=question, data1=data))
def answer_question(self, question, data):
self.answer.append(AnswerAss(question=question, data2=data))
class Category(Base, PrettyRepr):
__table__ = categories
class AskAss(Base):
__table__ = ask
user = relation(User, backref = 'ask')
class AnswerAss(Base):
__table__ = answer
user = relation(User, backref = 'answer')
class Question(Base):
__table__ = questions
categories = relation(Category, secondary=correspond, backref=questions)
ask_ass_users = relation(AskAss, backref='question')
answer_ass_users = relation(AnswerAss, backref='question')
metadata.create_all(engine)
sess = Session()
jack = User(user_name='jack')
ed = User(user_name='ed')
wendy = User(user_name='wendy')
[sess.save(x) for x in [jack, ed, wendy]]
colors = Category(categ_name='colors')
shapes = Category(categ_name='shapes')
cars = Category(categ_name='cars')
[sess.save(x) for x in [colors, shapes, cars]]
favorite_color = Question(question=uWhat's your favorite color? 1. blue 2. green 3. red, categories=[colors])
favorite_shape = Question(question=uWhat's your favorite shape? 1. square 2. circle 3. trapezoid, categories=[shapes])
blue_cars = Question(question=uWhat's your favorite blue car? 1. pinto 2. nova 3. duster, categories=[colors, cars])
[sess.save(x) for x in [favorite_color, favorite_shape, blue_cars]]
jack.ask_question(favorite_shape, 2)
jack.ask_question(favorite_color, 1)
wendy.answer_question(favorite_color, 2)
ed.answer_question(blue_cars, 3)
ed.ask_question(favorite_shape, 3)
jack.answer_question(favorite_shape, 2)
sess.commit()
# all users who asked about colors
assert sess.query(User).join('ask', 'question',
