CODEWITHSUNDEEP
pythonpython-3.xsqlalchemy
kalaLokia
kalaLokia

Reputation: 131

SQL Alchemy select columns from joined tables only

I want to run a working sql raw query in orm way. Where I need to pick columns and count of columns from tables joined not any columns from main. How do I implement this in ORM way.

My Raw query (I usually uses in sql server):

RAW SQL:

 SELECT [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName]
       ,[barcode].[dbo].[Tbl_barcode].[Size]
       ,COUNT([barcode].[dbo].[Tbl_barcode].[Size])
   FROM [barcode].[dbo].[tbl_ProductionScan]
   INNER JOIN [barcode].[dbo].[Tbl_barcode] ON [barcode].[dbo].[Tbl_barcode].[Serial_no] = [barcode].[dbo].[tbl_ProductionScan].[serial_no]
   INNER JOIN [barcode].[dbo].[Tbl_ProductionMaster] ON [barcode].[dbo].[Tbl_ProductionMaster].[ProductionCode] = [barcode].[dbo].[Tbl_barcode].[Product_code]
   WHERE [barcode].[dbo].[tbl_ProductionScan].[prod_date] BETWEEN '2021-08-01 08:00:00' AND '2021-08-25 08:00:00' AND [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName] Like '%3780%black%'
   GROUP BY [barcode].[dbo].[Tbl_ProductionMaster].[ProductionName], [barcode].[dbo].[Tbl_barcode].[Size]

What I managed to do in SQL Alchemy :

 result = (
 session.query(ProductionMaster.article, Barcode.size, sa.func.count(Barcode.size))
 .join(Barcode, Barcode.serial_no == ProductionScan.serial_no)
 .join(ProductionMaster, ProductionMaster.prod_code == Barcode.prod_code)
 .filter(
     sa.and_(
         ProductionScan.date >= "2021-08-01 08:00:00",
         ProductionScan.date <= "2021-08-25 08:00:00",
         ProductionMaster.article.like("%3780%black%"),
     )
 )
 .group_by(ProductionMaster.article, Barcode.size)
 .all()

This run to an error as the raw query returned is not correct.

Error:

 The multi-part identifier "tbl_ProductionScan.serial_no" could not be bound. (4104) (SQLExecDirectW)')

Raw sql returned from sqlalchemy error:

 [SQL: SELECT [Tbl_ProductionMaster].[ProductionName] AS [Tbl_ProductionMaster_ProductionName], [Tbl_barcode].[Size] AS [Tbl_barcode_Size], count([Tbl_barcode].[Size]) AS count_1
 FROM [tbl_ProductionScan], [Tbl_ProductionMaster] JOIN [Tbl_barcode] ON [Tbl_barcode].serial_no = [tbl_ProductionScan].serial_no JOIN [Tbl_ProductionMaster] ON [Tbl_ProductionMaster].[ProductionCode] = [Tbl_barcode].[Product_Code]
 WHERE [tbl_ProductionScan].prod_date >= ? AND [tbl_ProductionScan].prod_date <= ? AND [Tbl_ProductionMaster].[ProductionName] LIKE ? GROUP BY [Tbl_ProductionMaster].[ProductionName], [Tbl_barcode].[Size]]

In this sqlalchemy query, How do I get ride of Tbl_ProductionScan in the FROM keyword. I only need Tbl_ProductionScan in there, all the rest tables Tbl_ProductionMaster, Tbl_Barcode in the JOIN keyword only. That way sqlalchemy orm matches my actuall raw query given at the top.

Upvotes: 0

Views: 1096

Answers (1)

alim91
alim91

Reputation: 546

I am aware of the filtering, make sure you convert your string to date and are you try to filter on two string values (use or) or one

start_date = datetime.strptime("2021-08-01 08:00:00", "%Y-%m-%d %H:%M:%S")
end_date = datetime.strptime("2021-08-25 08:00:00", "%Y-%m-%d %H:%M:%S")
search = "%{}%{}%".format("3780", "black")

and then try to run this

result = session.query(ProductionScan, ProductionMaster, Barcode, )
    .join(Barcode, Barcode.serial_no == ProductionScan.serial_no)
    .join(ProductionMaster, ProductionMaster.prod_code == Barcode.prod_code)
    .filter(
        sa.and_(
            ProductionScan.date >= start_date,
            ProductionScan.date <= end_date,
            ProductionMaster.article.like(search),
            # sa.or_(ProductionMaster.article.like(search1), ProductionMaster.article.like(search2))
        )
    )
    .filter(Barcode.serial_no.isnot(None))
    .with_entities(ProductionMaster.article.label('article'), Barcode.size.label('size'), sa.func.count(Barcode.size).label('count'))
    .group_by(ProductionMaster.article, Barcode.size)
    .all()

with_entities

Return a new Query replacing the SELECT list with the given entities.

Upvotes: 2

Related Questions