[DB-SIG] Table Oriented Programming
Ian Bicking
ianb at colorstudy.com
Fri Jan 28 22:11:43 CET 2005
Brad Clements wrote:
> On 28 Jan 2005 at 12:35, Robert Brewer wrote:
>
>
>>Why is it interesting to you? What benefits do you see over:
>>
>>john = employees.findfirst(name='john')
>>print john.Department.name
>>
>
>
> If only my life was so easy, vs:
>
> select
> first 5000
> s.shipmentid, s.ownerid, s.deliverydate, s.shipmentstate, s.shipdate, s.etadatetime,
> s.billing, p.buyerspurchaseordernumber, p.salesordernumber, p.pieces,
> p.publictrackingnumber, p.trackingnumber, ro.party, ro.customerrole, ato.company as
> shiptocompany, ato.postal_code as shiptozip, ato.city as shiptocity, ato.state_province
> as shiptostate, afrom.company as shipfromcompany, afrom.postal_code as
> shipfromzip, afrom.city as shipfromcity, afrom.state_province as shipfromstate,
> ce.documentid, ce.entryid, ce.shipmentid as entryshipmentid, dc.pagecount,
> cs.carrierid, cs.servicetype
> from
> Shipment s inner join Package p on (s.shipmentid = p.shipmentid)
> inner join PartyShipRelation pss on (s.id = pss.shipment_id)
> inner join Roles ro on (pss.role_id = ro.id)
> left join address ato on (s.shipto_id = ato.id)
> left join address afrom on (s.shipfrom_id = afrom.id)
> left join CustomsEntryShipLink cesl on (s.shipmentid = cesl.shipmentid)
> left join CustomsEntries ce on (cesl.entryid = ce.entryid)
> left join DocumentCatalog dc on (ce.documentid = dc.documentid)
> inner join CarrierServices cs on (s.carrierserviceid = cs.serviceid)
Let's say this above part of the query something else entirely.
> where
> ( (upper(s.shipmentid) not like 'SFIEXP%') and (s.shipdate >= ?) and (s.shipdate
> < ?) ) and
> ( (pss.party_id = ?) )
> order by s.shipdate desc, s.shipmentid, p.trackingnumber
With sqlbuilder you might do something like:
s = sqlbuilder.Table('s')
clause = (
(~sqlbuilder.func(s.shipmentid).startswith('SFIEXP'))
& (s.shipdate >= min_shipment_date)
& (s.shipdate <= max_shipment_date)
& (pss.party_id = party_id))
# to get the SQL:
sqlbuilder.sqlrepr(clause, db_name)
It doesn't use bound parameters like ?, instead it does all the SQL
generation itself including quotation. So it has to know what database
you are using to actually create the SQL, like "mysql", "postgresql", etc.
--
Ian Bicking / ianb at colorstudy.com / http://blog.ianbicking.org
More information about the DB-SIG
mailing list