Is there a way to join results from two queries in Python using sqlalchemy engine?

Question

I am trying to run two sql queries using sqlalchemy engine in python 3.7. However, I am having trouble joining results columns from two queries. Is there an efficient way to perform this for MSSQL?

Following is the table that is being queried

timestamp           startX  startY  Number
2019-05-13-10:31    695     384     0
2019-05-13-10:32    3914    256     25ZLH3300MEPACC16x25
2019-05-13-10:32    3911    442     25ZLH3300MEPACC16x25
2019-05-13-10:32    3904    2109    25ZLH3300MEPACC16x25
2019-05-13-10:32    3910    627     25ZLH3300MEPACC16x25
2019-05-13-10:32    3904    1445    25ZLH3300MEPACC16x25

and I need to get this as an output

timestamp           startX  startY  Number                 Quantity
2019-05-13-10:31    695     384     0                      1
2019-05-13-10:32    3914    256     25ZLH3300MEPACC16x25   5

First query returns unique records based on Number as follows

SELECT * FROM 
    (SELECT 
       [timestamp]
      ,[startX]
      ,[startY]
      ,[Number]
  ,ROW_NUMBER() OVER(Partition by [Table].Number, 
                                    [Table].Number,  
                                    type order by [timestamp] DESC) rownumber 
                                    FROM [Table]) a WHERE rownumber = 1

Second query returns count of duplicate records as Quantity column with a Number column.

SELECT [Table].Number, count(*) AS 'Quantity'
FROM   [Table]
GROUP  BY [TABLE].Number
HAVING count(*) >= 1

I would like to join results from query #1 and column quantity from query #2 based on Number as primary key.

connection = engine.connect()
            connection.execute(""" Query """)

See Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Raymond Nijland
– Raymond Nijland, Commented May 14, 2019 at 14:40
@RaymondNijland I just added the table and the output required to satisfy that requirement — Jenny
– Jenny, Commented May 14, 2019 at 14:49
Why not just use SELECT * FROM (query1) AS q1 INNER JOIN (query2) AS q2 ON q1.number = q2.number? — MatBailie
– MatBailie, Commented May 14, 2019 at 14:53
you can't get the same results always on every run with this example data as SQL tables/resultsets are by ANSI/ISO SQL standard definition orderless.. ORDER BY timestamp DESC would still give non deterministic (random) results because the timestamps are not unique.. Do you have a column with IDENTITY in your table we need to that to get pure deterministic (fixed) results by adding that also in the ORDER BY — Raymond Nijland
– Raymond Nijland, Commented May 14, 2019 at 14:56
@Far - That's not caused by the INNER JOIN, that's caused by having type in your PARTITION BY; in other words, the first query already includes the duplicates that you don't want, so fix the first query. — MatBailie
– MatBailie, Commented May 14, 2019 at 15:05

MatBailie · Accepted Answer · 2019-05-14 14:58:34Z

3

You could just do it in a single query, one example would be:

SELECT
  *
FROM
(
    SELECT 
        [timestamp]
       ,[startX]
       ,[startY]
       ,[Number]
       ,ROW_NUMBER()
            OVER (PARTITION BY [Table].Number, 
                               [Table].type
                      ORDER BY [timestamp] DESC
                 )
                   AS rownumber 
       ,COUNT(*)
            OVER (PARTITION BY [Table].Number
                 )
                   AS Quantity
    FROM
      [Table]
)
    a
WHERE
        rownumber = 1
    AND quantity  > 1

answered May 14, 2019 at 14:58

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

@RaymondNijland - Indeed, but the op wanted to know how to join the queries, not how to fix or validate either query ;)

Raymond Nijland Over a year ago

very true i removed the comment under this answer because the topicsstarter said the order wasn't important.. +1 by the way the query is valid..

WolfgangK · Accepted Answer · 2019-05-14 14:46:07Z

0

You could try using pandas and join on DataFrames:

import pandas as pd
import sqlalchemy

engine = slqalchemy.create_enging(my_sql_settings)

df_unique_records = pd.read_sql(sql=my_query_1, conn=engine, index_col=Number)
df_duplicate_counts = pd.read_sql(sql=my_query_2, conn=engine, index_col=Number)

df = df_unique_records.merge(df_duplicate_counts, left_index=True, right_index=True)

answered May 14, 2019 at 14:46

WolfgangK

98312 silver badges19 bronze badges

1 Comment

Jenny Over a year ago

Thanks, but I wanted to know if I could have sql server handle this.

Collectives™ on Stack Overflow

Is there a way to join results from two queries in Python using sqlalchemy engine?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related