SQLAlchemy Core Join with USING

Viewed 60

Overview

I am trying to run a query to join some data in 3 tables in BigQuery (should apply to most relation databases like PostgreSQL as well) using SQLAlchemy Core.

Input Data

Table 1 (t1)

c1 c2 x
0 0 0
0 1 1
1 0 2

Table 2 (t2)

c1 c2 y
0 0 3
1 1 4
1 2 5

Table 3 (t3)

c1 c2 z
0 0 6
1 1 7
2 2 8

Desired Query

The query I am trying to run is

SELECT
  *
FROM
  t1
FULL OUTER JOIN
  t2
USING (c1, c2)
FULL OUTER JOIN
  t3
USING (c1, c2)

which yields (- indicates null):

c1 c2 x y z
0 0 0 3 6
0 1 1 - -
1 0 2 - -
1 1 - 4 7
1 2 - 5 -
2 2 - - 8

Problem

The problem I am running into is that it does not seem possible to perform joins with USING in SQLAlchemy Core. In particular, the join method only supports an onclause and not USING.

The two solutions I am attempting are:

  • Try to get USING to work with a TextClause. This is proving difficult as I need to consume the resulting table, referring to the columns by name.
  • Try to do the joins with the provided SQLAlchemy join method. I am unable to replicate this query using JOINs with ON without making subqueries.

Any help would be appreciated. Thanks!

0 Answers
Related