[sqlalchemy] Re: How to map a Selectable such that objects can be created/inserted, updated, and deleted?
mapper() has a selectable= argument, go read about it. it can do just about anything u fancy. Is there a way to create object mappers with bean managed persistence, as the Java folks would call it? What I would like to do is to map a class to a Selectable such as a join. Now when an object is created, its attributes should be inserted into several tables. Likewise, updates or deletes of objects should spread over several tables. For this to happen, I think I would need to hook into the ORM. Any ideas? I'm afraid that this is connected with the VIEWs on the sqlalchemy todo list. Best regards, Klaus --~--~-~--~~~---~--~~ 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: How to map a Selectable such that objects can be created/inserted, updated, and deleted?
Sorry but I don't see how the select_table parameter (used in inheritance hierarchies) relates to my question. Selecting things from a Selectable is not much of a problem, for example, and I need more than one table. On 18 Jun., 13:00, svilen [EMAIL PROTECTED] wrote: mapper() has a selectable= argument, go read about it. it can do just about anything u fancy. Is there a way to create object mappers with bean managed persistence, as the Java folks would call it? What I would like to do is to map a class to a Selectable such as a join. Now when an object is created, its attributes should be inserted into several tables. Likewise, updates or deletes of objects should spread over several tables. For this to happen, I think I would need to hook into the ORM. Any ideas? I'm afraid that this is connected with the VIEWs on the sqlalchemy todo list. Best regards, Klaus --~--~-~--~~~---~--~~ 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: How to map a Selectable such that objects can be created/inserted, updated, and deleted?
The MapperExtension might be what I need, however. On 18 Jun., 13:48, [EMAIL PROTECTED] wrote: Sorry but I don't see how the select_table parameter (used in inheritance hierarchies) relates to my question. Selecting things from a Selectable is not much of a problem, for example, and I need more than one table. On 18 Jun., 13:00, svilen [EMAIL PROTECTED] wrote: mapper() has a selectable= argument, go read about it. it can do just about anything u fancy. Is there a way to create object mappers with bean managed persistence, as the Java folks would call it? What I would like to do is to map a class to a Selectable such as a join. Now when an object is created, its attributes should be inserted into several tables. Likewise, updates or deletes of objects should spread over several tables. For this to happen, I think I would need to hook into the ORM. Any ideas? I'm afraid that this is connected with the VIEWs on the sqlalchemy todo list. Best regards, Klaus --~--~-~--~~~---~--~~ 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] in_ operator as kwarg to filter_by
I know that you can:
my_query.select_by(my_source.c.code.in_('1', '2'))
Is there a way to use kwargs with in_ as you can with equality
settings, as in:
my_query.select_by(code='1')
Since SA is NOT a framework, I imagine I could wrap the filter_by
method to work out Django style kwargs like code__in=(...), but if
there is an easier way...
Thanks,
David S.
--~--~-~--~~~---~--~~
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] Problem when applying max_identifier_length
Since upgrading to 0.3.8 (from 0.3.6), I've run in to a problem with long
identifiers. It seems that _truncate_bindparam is being applied to both
parts of the ClauseElements (that is, the parts both before and after the
AS), and is testing = (instead of ) max_identifier_length, so that when I
have a column identifier that is the maximum length, it ends up creating a
no such column error.
An example test-case follows:
from sqlalchemy import *
from sqlalchemy.engine.url import URL
class Underwriter(object):
def __init__(self, source, explan):
self.primaryHeatSource = source
self.primaryHeatSourceExplan = explan
def init_database(db):
metadata = BoundMetaData(db)
underwriting = Table('und_underwriting_tbl', metadata,
Column('und_underwriting_id', Integer, primary_key=True,
key='id'),
Column('und_primary_heat_source', String(20),
key='primaryHeatSource'),
Column('und_primary_heat_source_explan', String(100),
key='primaryHeatSourceExplan'),
)
mapper(Underwriter, underwriting)
# Add a test record to the DB
session = create_session()
underwriting.create()
uw = Underwriter('bonfire', 'Maybe she lives in the woods?')
session.save(uw)
session.flush()
session.close()
db = create_engine(URL(drivername='sqlite', database=':memory:'))
# simulate running under Oracle
db.dialect.max_identifier_length = lambda: 30
db.echo = True
# insert for test record seems to work OK
init_database(db)
session = create_session()
# select, however, fails
query = session.query(Underwriter)
underwriters = query.select()
session.close()
Throws the following error (note the
und_underwriting_tbl.und_primary_heat_source__1 AS
und_underwriting_tbl_und_2):
...
File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 602,
in _execute
sqlalchemy.exceptions.SQLError: (OperationalError) no such column:
und_underwriting_tbl.und_primary_heat_source__1 u'SELECT
und_underwriting_tbl.und_primary_heat_source__1 AS
und_underwriting_tbl_und_2, und_underwriting_tbl.und_underwriting_id AS
und_underwriting_tbl_und_3, und_underwriting_tbl.und_primary_heat_source AS
und_underwriting_tbl_und_4 \nFROM und_underwriting_tbl ORDER BY
und_underwriting_tbl.oid' []
Thank you,
Cory Johns
Systems
Tower Hill Insurance Group, Inc.
CONFIDENTIAL NOTICE: This email including any attachments, contains
confidential information belonging to the sender. It may also be
privileged or otherwise protected by work product immunity or other
legal rules. This information is intended only for the use of the
individual or entity named above. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or the taking of any action in reliance on the contents
of this emailed information is strictly prohibited. If you have
received this email in error, please immediately notify us by
reply email of the error and then delete this email immediately.
--~--~-~--~~~---~--~~
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: dictionaries collection_class
Ok, I applied that change to the version I'm using (0.3.8). That fixed it. But I think I stumbled on another bug/inconcistancy. If I tried to str/repr obj.attr it failed. I looked around the code and this fix seemed to work, but I'm not sure if it fits in with how the class is intended to function. In /sqlalchemy/ext/associationproxy.py in class class _AssociationDict(object) change def items(self): return [(k, self._get(self.col[k])) for k in self] to def items(self): return [(k, self._get(self.col[k])) for k in self.keys()] Does that make sense? Or is the problem deeper in the code? It seems to go along with what you said earlier about how the iteration was made to work like an instrumentedlist. -Ron On Jun 16, 3:07 pm, jason kirtland [EMAIL PROTECTED] wrote: Ron wrote: Now I'm having trouble with updating values in AttributeDict: so obj.attrs['key'] = 'somevalue' (works) obj.attrs['key'] = 'newvalue'(second one doesn't work) I get this error: File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.8- py2.4.egg/sqlalchemy/orm/mapper.py, line 679, in init raise e TypeError: lambda() takes exactly 2 arguments (3 given) Any more ideas? -Ron Fixed in 2739. (a silly typo) -jek --~--~-~--~~~---~--~~ 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: dictionaries collection_class
Ron wrote: Ok, I applied that change to the version I'm using (0.3.8). That fixed it. But I think I stumbled on another bug/inconcistancy. If I tried to str/repr obj.attr it failed. [...] Does that make sense? Or is the problem deeper in the code? It seems to go along with what you said earlier about how the iteration was made to work like an instrumentedlist. Yeah, that's the funky iteration in effect. __iter__ on the proxy was changed to be fully dict-like in the trunk and for 0.3.9. The matching change to the underlying InstrumentedDict iterator won't be in until 0.4. -jek --~--~-~--~~~---~--~~ 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: How to map a Selectable such that objects can be created/inserted, updated, and deleted?
On Jun 18, 2007, at 4:13 AM, [EMAIL PROTECTED] wrote: Is there a way to create object mappers with bean managed persistence, as the Java folks would call it? What I would like to do is to map a class to a Selectable such as a join. Now when an object is created, its attributes should be inserted into several tables. Likewise, updates or deletes of objects should spread over several tables. For this to happen, I think I would need to hook into the ORM. Any ideas? I'm afraid that this is connected with the VIEWs on the sqlalchemy todo list. map directly to the select() (or join()) of your choice. SQLAlchemy identifies the Table objects which comprise the select when it does insert/update/delete operations, and operates upon those for which it can formulate a primary key. http://www.sqlalchemy.org/docs/ adv_datamapping.html#advdatamapping_selects --~--~-~--~~~---~--~~ 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: How to map a Selectable such that objects can be created/inserted, updated, and deleted?
On Jun 18, 2007, at 7:00 AM, svilen wrote: mapper() has a selectable= argument, go read about it. it can do just about anything u fancy. select_table is only used for polymorphic inheritance situations such that a mapper's select operations occur via a different selectable than that which the base mapper is mapped against. --~--~-~--~~~---~--~~ 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: in_ operator as kwarg to filter_by
On Jun 18, 2007, at 11:47 AM, David S. wrote:
I know that you can:
my_query.select_by(my_source.c.code.in_('1', '2'))
Is there a way to use kwargs with in_ as you can with equality
settings, as in:
my_query.select_by(code='1')
Since SA is NOT a framework, I imagine I could wrap the filter_by
method to work out Django style kwargs like code__in=(...), but if
there is an easier way...
django's method of shoving SQL operators into the names of keyword
arguments seems horribly ugly to me. SQLAlchemy's approach is to
use query.filter(sometable.c.col.in_(x, y)). it uses the same
operators as everything else without the need to memorize magic codes
to embed in keyword names. select_by() is deprecated in the
upcoming 0.4 series.
--~--~-~--~~~---~--~~
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: Problem when applying max_identifier_length
On Jun 18, 2007, at 11:57 AM, Cory Johns wrote: Since upgrading to 0.3.8 (from 0.3.6), I've run in to a problem with long identifiers. It seems that _truncate_bindparam is being applied to both parts of the ClauseElements (that is, the parts both before and after the AS), and is testing = (instead of ) max_identifier_length, so that when I have a column identifier that is the maximum length, it ends up creating a no such column error. this has been fixed in the current 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: documentation unsearchable
On Jun 18, 2007, at 2:30 PM, gardsted wrote: If I were to suggest something it would be a two-toc approach - a toc for python-programmers and one for sql-programmers. SQLAlchemy would like you to assume both roles. I see a lot of people blogging about ORMs are good because you dont have to learn SQL...SQLAlcehmy is not at all that kind of tool. SA is the ORM for people who like SQL. I find myself asking myself questions like: 'how do i do this and that sql using the good features in sqlalchemy' but a lot of the time i would actually want to skip right to some q+d sql + a resultset and then come back later and 'clean up' when I have learned some more. but I was having a hard time even finding out how to do that. Maybe now i am better helped with the above mentioned I cant tell you either since you arent exactly explaining very clearly what it is. --~--~-~--~~~---~--~~ 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] Mapper with relation/custom join condition fails
Dear list...
I'm having trouble with two assign-mappers with a custom JOIN condition.
(And I admit that I'm not yet good at that in SQLAlchemy. So be gentle.)
powerdns_records_table = Table(
'records', meta,
Column('id', Integer, primary_key=True),
Column('name', Unicode(80)),
Column('type', Unicode(10)),
Column('content', Unicode(200)),
)
dhcp_hosts_table = Table(
'dhcp_hosts', meta,
Column('id', Integer, primary_key=True),
Column('ip_address', PGInet, nullable=False),
)
assign_mapper(session_context, DhcpHost, dhcp_hosts_table)
assign_mapper(session_context, DnsRecord, powerdns_records_table,
properties={
'dhcphost': relation(DhcpHost,
primaryjoin=(cast(powerdns_records_table.c.content,PGInet)==dhcp_hosts_table.c.ip_address),
foreign_keys=[powerdns_records_table.c.content]),
}
)
Yes, this may look a bit dirty. The JOIN condition is really that and I
have no chance to alter the table schemas because that's what another
application demands. The powerdns_records_table uses a string and
dhcp_hosts_table uses a PostgreSQL inet object (defined in SQLAlchemy
as types.PGInet) and I am trying to match those. I added the
foreign_keys parameter when SQLAlchemy complained:
ArgumentError: Can't locate any foreign key columns in primary join
condition 'CAST(records.content AS INET) = dhcp_hosts.ip_address'
for relationship 'DnsRecord.dhcphost (DhcpHost)'. Specify
'foreign_keys' argument to indicate which columns in the join
condition are foreign.
Now when I try DnsRecords.get_by(content='10.0.0.1') I get:
ArgumentError: No syncrules generated for join criterion
CAST(records.content AS INET) = dhcp_hosts.ip_address
When I try that again (or use
DnsRecords.select(DnsRecords.c.content='10.0.0.1') then I get:
AttributeError: 'PropertyLoader' object has no attribute 'strategy'
The actual SQL query I'd expect would be:
SELECT * FROM records JOIN dhcp_hosts ON CAST(records.content AS
inet)=dhcp_hosts.ip_address WHERE records.content='10.0.0.1';
My SQLAlchemy version is 0.3.7. Any idea what might be the problem? If
that join condition is generally not supposed to work in SA I'll have to
do that matching manually in my Python code (which I expect to be way
slower than pure SQL). Are primaryjoins with less than trivial integer
unique IDs unsupported?
Christoph
--~--~-~--~~~---~--~~
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: postgresql: need schema-qualified foreign keys
On Jun 18, 2007, at 4:25 AM, [EMAIL PROTECTED] wrote: In my first experiments with elixir I noticed that sqlalchemy doesn't handle foreign keys correctly on autoloaded tables. This has to to with schema handling in the postgresql driver. A foreign key referencing a table in the public schema from outside gets the table name without the schema part and thus locates the table in its own schema. I've appended a trivial fix below. im afraid this patch breaks several postgres reflection unit tests, since the schema attribute of Table is assumed to be of the default schema if not present. by making it present here, it fails to locate tables within its own metadata. i found when trying to create the test case for this issue it required explicitly stating the default schema name as the schema of the inside table. that is also a bug. the patch that fixes the all issues for the test I was able to create is: Index: lib/sqlalchemy/schema.py === --- lib/sqlalchemy/schema.py(revision 2742) +++ lib/sqlalchemy/schema.py(working copy) @@ -701,7 +701,7 @@ raise exceptions.ArgumentError(Invalid foreign key column specification: + self._colspec) if m.group(3) is None: (tname, colname) = m.group(1, 2) -schema = parenttable.schema +schema = None else: (schema,tname,colname) = m.group(1,2,3) table = Table(tname, parenttable.metadata, mustexist=True, schema=schema) meaning, if you say ForeignKey(sometable.somecolumn) for a particular Column, the schema is assumed to be the default schema, not the schema specified for the Table which contains the ForeignKey object. this means creating a ForeignKey between two tables A and B, both with schema=myschema, would have to look like ForeignKey(myschema.a.somecol), even though both tables are in the same myschema schema. Im OK with that but not sure how disruptive this change would be. if you can provide a test case illustrating your scenario (using Table/MetaData/Engine objects only; no elixir classes please), that would help greatly. --~--~-~--~~~---~--~~ 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: Mapper with relation/custom join condition fails
On Jun 18, 2007, at 3:34 PM, Christoph Haas wrote:
Yes, this may look a bit dirty. The JOIN condition is really that
and I
have no chance to alter the table schemas because that's what another
application demands. The powerdns_records_table uses a string and
dhcp_hosts_table uses a PostgreSQL inet object (defined in
SQLAlchemy
as types.PGInet) and I am trying to match those. I added the
foreign_keys parameter when SQLAlchemy complained:
ArgumentError: Can't locate any foreign key columns in primary
join
condition 'CAST(records.content AS INET) = dhcp_hosts.ip_address'
for relationship 'DnsRecord.dhcphost (DhcpHost)'. Specify
'foreign_keys' argument to indicate which columns in the join
condition are foreign.
Now when I try DnsRecords.get_by(content='10.0.0.1') I get:
ArgumentError: No syncrules generated for join criterion
CAST(records.content AS INET) = dhcp_hosts.ip_address
When I try that again (or use
DnsRecords.select(DnsRecords.c.content='10.0.0.1') then I get:
AttributeError: 'PropertyLoader' object has no attribute
'strategy'
The actual SQL query I'd expect would be:
SELECT * FROM records JOIN dhcp_hosts ON CAST(records.content AS
inet)=dhcp_hosts.ip_address WHERE records.content='10.0.0.1';
My SQLAlchemy version is 0.3.7. Any idea what might be the problem? If
that join condition is generally not supposed to work in SA I'll
have to
do that matching manually in my Python code (which I expect to be way
slower than pure SQL). Are primaryjoins with less than trivial integer
unique IDs unsupported?
primary joins of all kinds are supported. however the target column
of the primary join, i.e. the one SA needs to be able to set values
within, has always been expected to be a Column object, not a CAST.
this is just something that nobody has tried to do before. my only
guess of what to try right now, i dont think will work. its:
mycast = cast(powerdns_records_table.c.content,PGInet)
assign_mapper(session_context, DnsRecord, powerdns_records_table,
properties={
'dhcphost': relation(DhcpHost,
primaryjoin=(mycast==dhcp_hosts_table.c.ip_address),
foreign_keys=[mycast]),
}
)
the other is to place the CAST on the other side, i.e. cast
dhcp_hosts_table.c.ip_address as a string, since thats not the
foreign key side of things. but this also might create problems
with lazy loaders.
ticket 610 is added for this, and it may be a 5 minute fix or it
might be an involved refactoring. for now you might try using a
property-based loader for this relationship.
--~--~-~--~~~---~--~~
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: in_ operator as kwarg to filter_by
django's method of shoving SQL operators into the names of keyword arguments seems horribly ugly to me. SQLAlchemy's approach is to use query.filter(sometable.c.col.in_(x, y)). it uses the same operators as everything else without the need to memorize magic codes to embed in keyword names. select_by() is deprecated in the upcoming 0.4 series. I appreciate the appeal to aesthetics. But now a user of the query needs to know about the underlying selectable itself and not just the field names. It seems to lessens the utility of the filter_by method. Anyhow, thanks for the help. SA is remarkable. Peace, David S. --~--~-~--~~~---~--~~ 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] a couple of newbie questions: 'not exists' with reference to selected row
Dear List.
How do I do this more ormish?
The statement is supposed to find the latest messages which havent yet
been answered
by 123456, assuming a later message is an answer;-)
def play4():
print ### play4 #
engine=create_engine(dburi)
result=engine.execute(
select m.fromid, s.name, m.sent from person s,message m
where toid=123456 and
s.personid = m.fromid and not exists (
select 1 from message k where
k.toid=m.fromid and k.fromid=m.toid
and k.sent m.sent
)
order by m.sent desc
)
...
I completely fail to grasp how I get from the straight sql-
representation to the part where i
can actually benefit from the orm
Here am I: I have created a mapper where each message knows it's
sender and receiver by foreign key and attribute like this (and this I
like very much):
message_mapper=mapper(
Message,
message_table,
properties={
sender: relation(Person,
primaryjoin=(message_table.c.fromid==Person.c.personid)),
receiver: relation(Person,
primaryjoin=(message_table.c.toid==Person.c.personid))
}
)
kind regards
jorgen
--~--~-~--~~~---~--~~
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: Mapper with relation/custom join condition fails
On Jun 18, 2007, at 3:34 PM, Christoph Haas wrote:
assign_mapper(session_context, DhcpHost, dhcp_hosts_table)
assign_mapper(session_context, DnsRecord, powerdns_records_table,
properties={
'dhcphost': relation(DhcpHost,
primaryjoin=(cast
(powerdns_records_table.c.content,PGInet)
==dhcp_hosts_table.c.ip_address),
foreign_keys=[powerdns_records_table.c.content]),
}
)
also, this works:
mapper(DhcpHost, dhcp_hosts_table)
mapper(DnsRecord, powerdns_records_table,
properties={
'dhcphost': relation(DhcpHost,
primaryjoin=(cast
(powerdns_records_table.c.content,PGInet)
==dhcp_hosts_table.c.ip_address),
foreign_keys=[powerdns_records_table.c.content],
remote_side=[dhcp_hosts_table.c.ip_address],
viewonly=True,
),
}
)
now, the viewonly eliminates your syncrule issue. remote_side
currently gives the lazyloader more information on which to build a
lazy clause (works for eager too), and i can make it check for CAST
to work around this issue (though id like it to work for any SQL
function too).
but since viewonly, this wont help you with a flush(). if i made
syncrule accept a manual argument, such as a callable which you
would define to say destination.content = str(source.ip_address),
that would be a generalized way for there to be any kind of
functional stuff indicated within a primaryjoin condition.
in the case of CAST specifically, we do have some awareness that we
need to go from PGInet - string when setting DhcpHost's content
attribute...but i dont like to redefine DB functionality on the
Python side since we cant easily generalize it.
ive added some comments to this effect to the ticket. this feature
would be better in the 0.4 series, which has several weeks before
release though is available via SVN. does that work for you ?
--~--~-~--~~~---~--~~
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: a couple of newbie questions: 'not exists' with reference to selected row
On Jun 18, 2007, at 4:52 PM, gardsted wrote:
Dear List.
How do I do this more ormish?
The statement is supposed to find the latest messages which havent yet
been answered
by 123456, assuming a later message is an answer;-)
def play4():
print ### play4 #
engine=create_engine(dburi)
result=engine.execute(
select m.fromid, s.name, m.sent from person s,message m
where toid=123456 and
s.personid = m.fromid and not exists (
select 1 from message k where
k.toid=m.fromid and k.fromid=m.toid
and k.sent m.sent
)
order by m.sent desc
)
...
I completely fail to grasp how I get from the straight sql-
representation to the part where i
can actually benefit from the orm
Here am I: I have created a mapper where each message knows it's
sender and receiver by foreign key and attribute like this (and this I
like very much):
message_mapper=mapper(
Message,
message_table,
properties={
sender: relation(Person,
primaryjoin=(message_table.c.fromid==Person.c.personid)),
receiver: relation(Person,
primaryjoin=(message_table.c.toid==Person.c.personid))
}
)
here are some rough approaches.
text:
session.query(Message).select_text(select m.* from person s,
message m
where toid=123456 and
s.personid = m.fromid and not exists (
select 1 from message k where
k.toid=m.fromid and k.fromid=m.toid
and k.sent m.sent
)
order by m.sent desc)
hybrid:
session.query(Message).select_from(messages.join(person)).filter
(toid=123456 and not exists (
select 1 from message k where
k.toid=messages.fromid and k.fromid=messages.toid
and k.sent messages.sent
)).order_by(desc(Message.c.sent)).list()
fully constructed:
k = messages.alias('k')
session.query(Message).select_from(messages.join(person)).filter
(messages.c.toid==123456).\
filter(~exists([1], and_(k.c.toid==messages.c.fromid,
k.c.fromid==messages.c.toid, k.c.sendmessages.c.sent))).\
order_by(desc(messages.c.sent)).list()
--~--~-~--~~~---~--~~
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: a couple of newbie questions: 'not exists' with reference to selected row
Fantastic: and now i don't need the join to the persons table anymore
- it stays hidden inside the message object, so i can just ask for
sender.name or whatever i want - that's nice - If I have the
objects, i can delegate the use of them to others, maybe leading to
higher level of reuse (= less bugs) of the 'sql'.
Me on sqlalchemy:
One of the things, I like about sql, is it's language
independency -
it can be discussed with almost all programmers and
powerusers and even to some extent with laymen.
With this set of tools I can (i hope;-) accomodate two things -
take some of the tedious things away from expressing
myself in sql but still have sql (kindly provided by sqlalchemy),
that i can discuss with powerusers
k = model.message_table.alias('k')
m = model.message_table
return dbsession.query(model.Message).\
filter(m.c.toid==79487281).\
filter(~exists([1], and_(
k.c.toid==m.c.fromid,
k.c.fromid==m.c.toid,
k.c.sentm.c.sent))
).\
order_by(desc(m.c.sent)).list()
On Jun 19, 12:03 am, gardsted [EMAIL PROTECTED] wrote:
Thank You very much
Worked like a charm - only better - now I get objects.
I will now see, if I can understand what goes on ;-)
The results were the same rows.
def play5():
print ### play5 #
k = model.message_table.alias('k')
s = model.person_table
m = model.message_table
result=dbsession.query(model.Message).select_from(
m.join(s,m.c.fromid==s.c.personid)).\
filter(m.c.toid==123456).\
filter(~exists([1], and_(
k.c.toid==m.c.fromid,
k.c.fromid==m.c.toid,
k.c.sentm.c.sent))
).\
order_by(desc(m.c.sent)).list()
for i in result:
print i
On Jun 18, 11:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 18, 2007, at 4:52 PM, gardsted wrote:
Dear List.
How do I do this more ormish?
The statement is supposed to find the latest messages which havent yet
been answered
by 123456, assuming a later message is an answer;-)
def play4():
print ### play4 #
engine=create_engine(dburi)
result=engine.execute(
select m.fromid, s.name, m.sent from person s,message m
where toid=123456 and
s.personid = m.fromid and not exists (
select 1 from message k where
k.toid=m.fromid and k.fromid=m.toid
and k.sent m.sent
)
order by m.sent desc
)
...
I completely fail to grasp how I get from the straight sql-
representation to the part where i
can actually benefit from the orm
Here am I: I have created a mapper where each message knows it's
sender and receiver by foreign key and attribute like this (and this I
like very much):
message_mapper=mapper(
Message,
message_table,
properties={
sender: relation(Person,
primaryjoin=(message_table.c.fromid==Person.c.personid)),
receiver: relation(Person,
primaryjoin=(message_table.c.toid==Person.c.personid))
}
)
here are some rough approaches.
text:
session.query(Message).select_text(select m.* from person s,
message m
where toid=123456 and
s.personid = m.fromid and not exists (
select 1 from message k where
k.toid=m.fromid and k.fromid=m.toid
and k.sent m.sent
)
order by m.sent desc)
hybrid:
session.query(Message).select_from(messages.join(person)).filter
(toid=123456 and not exists (
select 1 from message k where
k.toid=messages.fromid and k.fromid=messages.toid
and k.sent messages.sent
)).order_by(desc(Message.c.sent)).list()
fully constructed:
k = messages.alias('k')
session.query(Message).select_from(messages.join(person)).filter
(messages.c.toid==123456).\
filter(~exists([1], and_(k.c.toid==messages.c.fromid,
k.c.fromid==messages.c.toid, k.c.sendmessages.c.sent))).\
order_by(desc(messages.c.sent)).list()
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
