[sqlalchemy] Re: How can I do this relation?
On Nov 30, 3:15 pm, Alvaro Reinoso alvrein...@gmail.com wrote: One region can just contain one channel, but one channel could be in many regions. A region will never be accessed by a channel. However, Channel could be accessed by a region, so I need that relationship in region. Is that relationship OK? If not, how can I make it? It's not entirely clear what you are trying to do here. It sounds like a classic one-to-many relationship without a backref. All you have to do is get rid of the backref=regions bit and you should be good to go. BTW, you probably want to drop the idea of A is accessed by/through B. It doesn't quite fit with what's really going on in the relational world. Instead, think of how two independent objects may (or may not) be related to one another. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Odd many-to-one problem
On Nov 12, 6:42 am, Michael Bayer mike...@zzzcomputing.com wrote: But generally usage of mapper() wasn't intended to provide any tricks around import issues. You can always use mapper.add_property(...) to attach things as needed, and class_mapper(Class) to call up any mapper anywhere. This is what I am really looking for. I knew there had to be a way to modify the mappings after the declaration. This is going to solve a ton of problems I've had to code around in interesting ways and make everything much, much more clean and clear. As always, SQLAlchemy delivers. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Odd many-to-one problem
relationship() expects a class or a mapper instance, not a string. I
got this error:
ArgumentError: relationship 'available_deals' expects a class or a
mapper argument (received: type 'str')
On Nov 10, 4:46 pm, Sergey V. sergey.volob...@gmail.com wrote:
The twist is that I've spread out my tables and ORM classes across
several files. I've tried to keep it so that I don't have circular
dependencies. That means I've defined Merchant first, and then Deal
later, in separate files
To avoid problems with imports and dependencies you can pass strings
to the relationship function instead of the actual classes:
mapper(Deal, deals, properties=dict(
merchant=relationship('Merchant', backref='deals'),
))
This greatly simplifies everything if you split your classes into
separate files.
Regarding 'available_deals', 'deleted_deals' etc. - the approach with
properties is sub-optimal. Consider a merchant having thousands of
deals, only a few of which are available - the method would have to
fetch all those deals only to discard most of them. Also, that won't
work with eager loading. The optimal way would be to make SA to
generate a query like SELECT ... FROM Deals WHERE ... AND deleted=1
which would return only the records we're interested in. I'm sure it's
possible but I'll leave it to you to find it in SA docs :) When you
find it please post it here :)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Odd many-to-one problem
That is useful for mapping single or combined columns to an attribute.
Here, I want to map entire objects.
On Nov 10, 10:20 pm, Eric Ongerth ericonge...@gmail.com wrote:
Good point, Sergey.
Here is the relevant documentation regarding mapping attributes to
selects:http://www.sqlalchemy.org/docs/orm/mapper_config.html?highlight=arbit...
On Nov 10, 4:46 pm, Sergey V. sergey.volob...@gmail.com wrote:
The twist is that I've spread out my tables and ORM classes across
several files. I've tried to keep it so that I don't have circular
dependencies. That means I've defined Merchant first, and then Deal
later, in separate files
To avoid problems with imports and dependencies you can pass strings
to the relationship function instead of the actual classes:
mapper(Deal, deals, properties=dict(
merchant=relationship('Merchant', backref='deals'),
))
This greatly simplifies everything if you split your classes into
separate files.
Regarding 'available_deals', 'deleted_deals' etc. - the approach with
properties is sub-optimal. Consider a merchant having thousands of
deals, only a few of which are available - the method would have to
fetch all those deals only to discard most of them. Also, that won't
work with eager loading. The optimal way would be to make SA to
generate a query like SELECT ... FROM Deals WHERE ... AND deleted=1
which would return only the records we're interested in. I'm sure it's
possible but I'll leave it to you to find it in SA docs :) When you
find it please post it here :)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Odd many-to-one problem
This is what I need to do, except the Merchant object is defined before the Deal object. In the example in the documentation, I have mapped User before I have mapped Address. On Nov 11, 10:25 am, Mike Conley mconl...@gmail.com wrote: For cases like this I have found something like this to be useful http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relati... using lazy loading and viewonly=True as needed I found this to be clearer than column property because it fits cleanly with the rest of the relationship configuration. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Odd many-to-one problem
I have two tables, merchants and deals. The merchants table is represented by Merchant and deals table by Deal. Each merchant can have 0, 1, or many deals. Some of those deals will be available, while others will be expired or coming soon or deleted. Each deal belongs to exactly one merchant. I'd like to setup Merchant to have attributes deals, available_deals, expired_deals, upcoming_deals, and deleted_deals. These would return, obviously, deals from those groups. The twist is that I've spread out my tables and ORM classes across several files. I've tried to keep it so that I don't have circular dependencies. That means I've defined Merchant first, and then Deal later, in separate files It looks like this: in model/merchant.py: merchants = Table(...) class Merchant(object): ... mapper(Merchant, merchants) in model/deal.py: deals = Table(...) class Deal(object): ... mapper(Deal, deals, properties=dict( merchant=relationship(Merchant, backref='deals'), )) What can I sprinkle in model/deal.py's mapper call to add backrefs to 'available_deals', 'deleted_deals', etc...? Or am I going about this all wrong? Thanks in advance. BTW, SQLAlchemy is, by far, the most superior ORM in the history of the world, bar none, IMHO. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Odd many-to-one problem
On Nov 10, 12:19 pm, Eric Ongerth ericonge...@gmail.com wrote: Rather than create a specific backref for each subtype of deal, why not just continue with your basic 'deals' backref, then attach regular python properties to your Merchant class which return just the desired sub-deals. Something like: class Merchant(object): ... @property def available_deals(self): return [deal for deal in self.deals if deal.available] @property def expired_deals(self): return [deal for deal in self.deals if deal.expired] ... and so on. That's an interesting idea. If I changed the list comprehensions to generators ( deal for deal in self.deals if deal...) then I might have a winner. You could also reverse your order of definition, define Deal first with no reference to Merchant, then define Merchant second, with mapper properties for each of your type of deal (probably mapped to select statements). But I don't know if it would work to have each of those different mapper properties all use 'merchant' (with, of course, the uselist=False option to make it 1:1) as the backref identifier. Yeah, I was thinking something similar. It'd require a lot of work to reorder everything, though. On Nov 10, 11:19 am, Jonathan Gardner jgard...@jonathangardner.net wrote: I have two tables, merchants and deals. The merchants table is represented by Merchant and deals table by Deal. Each merchant can have 0, 1, or many deals. Some of those deals will be available, while others will be expired or coming soon or deleted. Each deal belongs to exactly one merchant. I'd like to setup Merchant to have attributes deals, available_deals, expired_deals, upcoming_deals, and deleted_deals. These would return, obviously, deals from those groups. The twist is that I've spread out my tables and ORM classes across several files. I've tried to keep it so that I don't have circular dependencies. That means I've defined Merchant first, and then Deal later, in separate files It looks like this: in model/merchant.py: merchants = Table(...) class Merchant(object): ... mapper(Merchant, merchants) in model/deal.py: deals = Table(...) class Deal(object): ... mapper(Deal, deals, properties=dict( merchant=relationship(Merchant, backref='deals'), )) What can I sprinkle in model/deal.py's mapper call to add backrefs to 'available_deals', 'deleted_deals', etc...? Or am I going about this all wrong? Thanks in advance. BTW, SQLAlchemy is, by far, the most superior ORM in the history of the world, bar none, IMHO. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] patch: PGUuid type not compatible with Python uuid module
The PGUuid doesn't accept Python's uuid.UUID type. Here's a simple patch to fix that: diff --git a/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/databases/po index 038a9e8..394c293 100755 --- a/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/databases/postgres. +++ b/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/databases/postgres. @@ -200,6 +200,17 @@ class PGBit(sqltypes.TypeEngine): class PGUuid(sqltypes.TypeEngine): def get_col_spec(self): return UUID + +def bind_processor(self, dialect): +def process(value): +return str(value) +return process + +def result_processor(self, dialect): +import uuid +def process(value): +return uuid.UUID(value) +return process class PGArray(sqltypes.MutableType, sqltypes.Concatenable, sqltypes.TypeEngine): def __init__(self, item_type, mutable=True): Also, I wonder if we should make UUID type a generic type. Other databases can either store it as an integer or as a string of hex digits. There are db designers out there (like myself) who prefer UUIDs over numbers for IDs. --~--~-~--~~~---~--~~ 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: in operator and literal list
Eoghan Murray wrote: Ideally this would be expressed as: MyTable.filter(MyTable.c.MyColumn.in(ls)) Try: MyTable.filter(MyTable.c.MyColumn.in_(ls)) Just a guess. No time to see if this is right. --~--~-~--~~~---~--~~ 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] Error with primaryjoin in relation() in 0.4.1
I'm getting a strange error when I specify primaryjoin in relation().
Here's a short script to reproduce the error:
#!/usr/bin/env python
from sqlalchemy import *
from sqlalchemy.types import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
table1 = Table('table1', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode()),
)
table2 = Table('table2', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode()),
Column('left', Integer, ForeignKey('table1.id')),
Column('right', Integer, ForeignKey('table1.id')),
)
class Table1(object):
def __init__(self, name):
self.name = name
class Table2(object):
def __init__(self, name, left, right):
self.name = name
self.left = left
self.right = right
mapper(Table1, table1, properties={
'left_of':relation(Table2,
primaryjoin=table1.c.id==table2.c.left),
'right_of':relation(Table2,
primaryjoin=table1.c.id==table2.c.right),
})
mapper(Table2, table2, properties={
'left':relation(Table1,
primaryjoin=table1.c.id==table2.c.left),
'right':relation(Table1,
primaryjoin=table1.c.id==table2.c.right),
}, allow_column_override=True)
metadata.drop_all(engine)
metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
session = Session()
a = Table1('a')
session.save(a)
b = Table1('b')
session.save(b)
c = Table2('c', a, b)
session.save(c)
session.commit()
Here's the error I get:
2007-12-28 21:43:36,070 INFO sqlalchemy.engine.base.Engine.0x..cL
PRAGMA table_info(table2)
2007-12-28 21:43:36,071 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,072 INFO sqlalchemy.engine.base.Engine.0x..cL
PRAGMA table_info(table1)
2007-12-28 21:43:36,072 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,074 INFO sqlalchemy.engine.base.Engine.0x..cL
PRAGMA table_info(table1)
2007-12-28 21:43:36,075 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,076 INFO sqlalchemy.engine.base.Engine.0x..cL
PRAGMA table_info(table2)
2007-12-28 21:43:36,076 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,078 INFO sqlalchemy.engine.base.Engine.0x..cL
CREATE TABLE table1 (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY (id)
)
2007-12-28 21:43:36,078 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,079 INFO sqlalchemy.engine.base.Engine.0x..cL
COMMIT
2007-12-28 21:43:36,081 INFO sqlalchemy.engine.base.Engine.0x..cL
CREATE TABLE table2 (
id INTEGER NOT NULL,
name TEXT,
left INTEGER,
right INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(left) REFERENCES table1 (id),
FOREIGN KEY(right) REFERENCES table1 (id)
)
2007-12-28 21:43:36,082 INFO sqlalchemy.engine.base.Engine.0x..cL {}
2007-12-28 21:43:36,083 INFO sqlalchemy.engine.base.Engine.0x..cL
COMMIT
2007-12-28 21:43:36,117 INFO sqlalchemy.engine.base.Engine.0x..cL
BEGIN
2007-12-28 21:43:36,119 INFO sqlalchemy.engine.base.Engine.0x..cL
INSERT INTO table1 (name) VALUES (?)
2007-12-28 21:43:36,120 INFO sqlalchemy.engine.base.Engine.0x..cL
['a']
2007-12-28 21:43:36,131 INFO sqlalchemy.engine.base.Engine.0x..cL
INSERT INTO table1 (name) VALUES (?)
2007-12-28 21:43:36,132 INFO sqlalchemy.engine.base.Engine.0x..cL
['b']
2007-12-28 21:43:36,133 INFO sqlalchemy.engine.base.Engine.0x..cL
ROLLBACK
Traceback (most recent call last):
File /home/jgardner/tmp/sqlalchemy-error.py, line 57, in module
session.commit()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/session.py, line 483, in commit
self.transaction = self.transaction.commit()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/session.py, line 210, in commit
self.session.flush()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/session.py, line 683, in flush
self.uow.flush(self, objects)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 209, in flush
flush_context.execute()
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 436, in execute
UOWExecutor().execute(self, head)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 1055, in execute
self.execute_save_steps(trans, task)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 1072, in execute_save_steps
self.execute_dependencies(trans, task, False)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 1085, in execute_dependencies
self.execute_dependency(trans, dep, False)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.1-py2.5.egg/
sqlalchemy/orm/unitofwork.py, line 1066, in
[sqlalchemy] Re: Error with primaryjoin in relation() in 0.4.1
I found the problem goes away if I don't try to overload the names, Still, it would be nice if this wasn't cause a problem. The solution is to rename the columns in table2 to right_id and left_id, and to not use the allow_column_override option in the mapper() function. If that isn't an option, then I would have to rename the attributes to right_obj and left_obj in the mapper(). --~--~-~--~~~---~--~~ 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: Error with primaryjoin in relation() in 0.4.1
Michael Bayer wrote:
the error is because of the allow_column_override, combined with the
fact that you haven't reassigned the column attributes for left and
right. the left and right columns on table2 need to be
available as scalar attributes on your Table2 class since thats how
foreign key attributes are tracked.
so get rid of the allow_column_override (any error message that says
specify X to ignore this condition is pretty much a red flag) and
set up the foreign key columns as something:
mapper(Table2, table2, properties={
'_left':table2.c.left,
'_right':table2.c.right,
'left':relation(Table1,
primaryjoin=table1.c.id==table2.c.left),
'right':relation(Table1,
primaryjoin=table1.c.id==table2.c.right),
})
Thanks! This works as well and is much more elegant.
--~--~-~--~~~---~--~~
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] Missing Sequences
I don't know what the design decisions were regarding the Sequence
object, so bear with my ignorance for a moment here.
I played with SQLAlchemy in conjunction with Pylons. I had code that
looked like this:
links_table = Table('links', metadata,
Column('id', types.Numeric(), schema.Sequence('link_id_seq'),
primary_key=True),
Column('title', types.Unicode(), ),
Column('url', types.Unicode(), )
)
Of course, using SQLite, this leads to missing 'id' when inserting a
new row since, as far as I can tell, SQLite doesn't support sequences.
The interesting part is the following. If I take the sequence out and
treat it as something separate:
id_seq = schema.Sequence('id_seq', 1000)
And then leave it out of the table defined above, then I can do
something like this:
log.info(Adding test link...)
link = model.Link()
link.id = engine.execute(model.id_seq)
log.info(link.id = %r % link.id)
Well, except for the bit about the link.id being None in SQLite.
The following questions are more about design than implementation.
That is, there may be something about the assumptions SQLAlchemy makes
and how it handles different things.
Shouldn't SQLAlchemy throw an exception when you try to grab the next
value of a non-existant sequence?
Should SQLAlchemy implement a crude version of sequences in Python to
cover up deficient SQL implemetnations? I imagine it is technically
possible. We can have a table that stores the last value of the
sequence and its parameters, and then in those databases just go look
up the last state of the sequence there. But is this in the spirit of
the SQLAlchemy design?
Should sequences be sequences first-class citizens of the schema like
tables are? That is, why do I have to create them and drop them
separately? Why can't we have a next() method for sequences and then
bind sequences to the metadata the same way tables are?
If you are looking for development help, I have some spare time here
and there and this is actually interesting. If someone can point the
way to go, I can take it from there.
Otherwise, I am incredibly surprised at how well SQLAlchemy works in
allowing me to write truly database independent applications. You have
done something I don't think anyone else has ever done in the history
of computing, and for that you should be proud.
--~--~-~--~~~---~--~~
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: Missing Sequences
On Dec 18, 10:39 am, Michael Bayer [EMAIL PROTECTED] wrote: if you can produce decent code, and almost more importantly good unit tests that ensure the decent code always works (else it might as well be broken), commit access is there for the taking. Start with trac tickets, i.e. create some and/or fix some, hang out on irc and sqlalchemy-devel, come up with some patches and we go from there. We're quite ready for many more developers. I'll be lurking as best I can. When I get the courage I'll give it a go. thanks for the compliments ! see you at Pycon. Wish I could go... enjoy yourselves and let us know what happens! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
