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
-~----------~----~----~----~------~----~------~--~---
