On 12/24/2015 12:12 AM, Sohail Khan wrote:
> 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."Alphacode" as orig_alpha_code,
> orig."latitude" as orig_lat,
> orig."longitude" as orig_long,
> dest."Name" as dest_name,
> dest."Alphacode" 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."Alphacode"=lonlats.state
> ) orig
> ON orig."Numericcode" = transactions."ORIG_STATE"
> JOIN (
> SELECT states.*, lonlats.*
> FROM "FIPS_States" states
> JOIN state_latlon lonlats
> on states."Alphacode"=lonlats.state
> ) dest
> ON dest."Numericcode" = transactions."DEST_STATE"
> WHERE
> dest."Alphacode"=:dest_code AND orig."Alphacode" =:orig_code
> LIMIT 100
>
> """)
>
>
>
> # My closest way of approximating this with sqlalchemy, seems too cumbersome
"orig" and "dest" here are both what I assume is your
"state_latlon_joined" select, so
orig = state_latlon_joined.alias("orig")
dest = state_latlon_joined.alias("dest")
stmt = select([
transactions,
orig.c.name.label('orig_name'),
orig.c.alphacode.label("orig_alpha_code"),
# ...
]).select_from(transactions.\
join(orig, transactions.c.orig_state == orig.c.numericcode).\
join(dest, transactions.c.dest_state == dest.c.numericcode).\
where(dest.c.alphacode == 'some_code').\
where(orig.c.alphacode == 'some_code').\
limit(100)
When using Core SQL you should always be able to compose the structure
in Python as succinctly, or moreso, than the original SQL, for standard
SQL like this.
Though your database might optimize better if you instead aliased states
and lonlats directly, removed the subqueries, and made it a long string
of joins without any parenthesization.
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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.
