I couldn't figure out a better description or explanation on what I'm
trying to do so I instead wrote a sql query that I want to perform and
tried getting as close as possible to a result that I wanted. I wanted to
know if there was a better way of doing this.
t = text("""
SELECT transactions.*,
orig."Name" as orig_name,
orig."Alpha code" as orig_alpha_code,
orig."latitude" as orig_lat,
orig."longitude" as orig_long,
dest."Name" as dest_name,
dest."Alpha code" as dest_alpha_code,
dest."latitude" as dest_lat,
dest."longitude" as dest_lon
FROM transactions
JOIN (
SELECT states.*, lonlats.*
FROM "FIPS_States" states
JOIN state_latlon lonlats
on states."Alpha code"=lonlats.state
) orig
ON orig."Numeric code" = transactions."ORIG_STATE"
JOIN (
SELECT states.*, lonlats.*
FROM "FIPS_States" states
JOIN state_latlon lonlats
on states."Alpha code"=lonlats.state
) dest
ON dest."Numeric code" = transactions."DEST_STATE"
WHERE
dest."Alpha code"=:dest_code AND orig."Alpha code" =:orig_code
LIMIT 100
""")
# My closest way of approximating this with sqlalchemy, seems too cumbersome
orig = transactions.join(
state_latlon_joined,
onclause=transactions.c.ORIG_STATE == FIPS_States.c.get("Numeric code"))
s = select([transactions,
FIPS_States.c.get("Alpha code").label("orig_state_alpha_code"),
FIPS_States.c.Name.label("orig_state_name"),
state_latlon.c.latitude.label('orig_lat'),
state_latlon.c.longitude.label('orig_lon')
]).select_from(orig).limit(10)
dest = transactions.join(
state_latlon_joined,
onclause=transactions.c.DEST_STATE == FIPS_States.c.get("Numeric code"))
# use the previous select statement as a basis for the new select statement
new_table = (select([s.alias(),
FIPS_States.c.get("Alpha code").label("dest_state_alpha_code"),
FIPS_States.c.Name.label("dest_state_name"),
state_latlon.c.latitude.label('dest_lat'),
state_latlon.c.longitude.label('dest_lon')])
.select_from(dest).limit(10))
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
