Using joins+max with sql server

6 views
Skip to first unread message

Elias Coutinho

unread,
Apr 14, 2023, 1:30:19 PM4/14/23
to sqlalchemy
Good afternoon.
I am having trouble transforming a SQL Server query to SQL Alchemy.

The SQL Server query is this

SELECT CP.CdChamada, P.NmProduto, PE.VlPrecoCusto, PE.VlPrecoSugerido, EE.QtEstoque, EE.DtReferencia
FROM Produto P
 INNER JOIN Produto_Empresa PE ON  P.IdProduto = PE.IdProduto
 INNER JOIN CodigoProduto CP ON  P.IdProduto = CP.IdProduto
 INNER JOIN (SELECT IdProduto, CdEmpresa, MAX(DtReferencia) AS MaxDtReferencia
             FROM EstoqueEmpresa
             GROUP BY IdProduto, CdEmpresa) AS EE2 ON EE2.IdProduto = P.IdProduto AND EE2.CdEmpresa = PE.CdEmpresa
 INNER JOIN EstoqueEmpresa EE ON EE.IdProduto = EE2.IdProduto AND EE.DtReferencia = EE2.MaxDtReferencia AND EE.CdEmpresa = EE2.CdEmpresa
WHERE PE.StAtivoVenda = 'S' AND
      PE.CdEmpresa = 4 AND
      CP.IdTipoCodigoProduto = '00A0000002'
ORDER BY CP.CdChamada

My code is as follows:
My Model
class EstoqueEmpresa(Base):
    __tablename__ = 'EstoqueEmpresa'

    IdProduto = Column(CHAR(10, 'SQL_Latin1_General_CP850_CI_AI'), primary_key=True, nullable=False)
    CdEmpresa = Column(Integer, primary_key=True, nullable=False)
    DtReferencia = Column(DateTime, primary_key=True, nullable=False, index=True)
    VersaoRegistro = Column(TIMESTAMP, nullable=False)
    QtCompra = Column(Float(53))
    VlCompra = Column(Float(53))
    QtVenda = Column(Float(53))
    VlVenda = Column(Float(53))
    VlCustoMercadoriaVendida = Column(Float(53))
    QtEntrada = Column(Float(53))
    VlEntrada = Column(Float(53))
    QtSaida = Column(Float(53))
    VlSaida = Column(Float(53))
    VlSaidaAcerto = Column(Float(53))
    QtSaidaAcerto = Column(Float(53))
    QtEstoque = Column(Float(53))
    VlEstoque = Column(Float(53))
    VlUltimoCustoMedio = Column(Float(53))
    DtInicialAlinhamentoEstoque = Column(DateTime)
    QtCompraNaoAtualizaCustoMedio = Column(Float(53))
    VlCompraNaoAtualizaCustoMedio = Column(Float(53))
    QtEntradaNaoAtualizaCustoMedio = Column(Float(53))
    VlEntradaNaoAtualizaCustoMedio = Column(Float(53))

My code
from sqlalchemy import create_engine, text, Column, update, insert, select
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.orm import Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func, and_

import unidecode
import pandas as pd
from datetime import datetime
import re
import itertools

from itertools import islice

# Importo somente as tabelas que vou usar
from models import CodigoProduto, ProdutoEmpresa, EstoqueEmpresa, Produto

#-------------------------------------------------------------------------------------------------------------------------------------
# Configuração da conexão com o banco de dados
USER = 'sa'
PASSWORD = 'Abc*123'
HOST = 'SERVER-02\MSSQLSERVERB'
DATABASE = 'ALTERDATA_TESTE'

engine = create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOST}/{DATABASE}?driver=ODBC+Driver+17+for+SQL+Server')

# Create a Session object
Session = sessionmaker(bind=engine)
session = Session()


#-------------------------------------------------------------------------------------------------------------------------------------
# Subquery para buscar o maior registro de estoqueempresa para cada produto
estoqueAtual = session.query(
        EstoqueEmpresa.IdProduto,
        EstoqueEmpresa.QtEstoque,
        func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
    ).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque, EstoqueEmpresa.DtReferencia).subquery()

#print(estoqueAtual)

# Realiza a consulta com SQLAlchemy
##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
##               .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.IdProduto)\
##               .join(CodigoProduto, Produto.IdProduto == CodigoProduto.IdProduto)\
##               .join(estoqueAtual, and_(Produto.IdProduto == estoqueAtual.c.IdProduto))\
##               .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == Produto.IdProduto))\
##               .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
##               .filter(ProdutoEmpresa.CdEmpresa == 4)\
##               .order_by(Produto.NmProduto)

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, EstoqueEmpresa.DtReferencia, estoqueAtual.c.QtEstoque)\
               .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.IdProduto)\
               .join(CodigoProduto, Produto.IdProduto == CodigoProduto.IdProduto)\
               .join(estoqueAtual, and_(Produto.IdProduto == estoqueAtual.c.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
               .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == Produto.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
               .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
               .filter(ProdutoEmpresa.CdEmpresa == 4)\
               .order_by(Produto.NmProduto)


print(query)

# Execute the query and get the result as a list of dicts
result = query.all()
print(result)

# Pass the result to pd.DataFrame() to create the DataFrame
df_produtos = pd.DataFrame(result)

# exporta o dataframe para um arquivo Excel
df_produtos.to_excel('teste_join.xlsx')

# Close the Session
session.close()

print('***** FIM *****')

My Traceback
SELECT [CodigoProduto].[CdChamada] AS [CodigoProduto_CdChamada], [Produto].[IdProduto] AS [Produto_IdProduto], [Produto].[NmProduto] AS [Produto_NmProduto], [Produto_Empresa].[VlPrecoCusto] AS [Produto_Empresa_VlPrecoCusto], [Produto_Empresa].[VlPrecoSugerido] AS [Produto_Empresa_VlPrecoSugerido], [EstoqueEmpresa].[DtReferencia] AS [EstoqueEmpresa_DtReferencia], anon_1.[QtEstoque] AS [anon_1_QtEstoque]
FROM [Produto] JOIN [Produto_Empresa] ON [Produto].[IdProduto] = [Produto_Empresa].[IdProduto] JOIN [CodigoProduto] ON [Produto].[IdProduto] = [CodigoProduto].[IdProduto] JOIN (SELECT [EstoqueEmpresa].[IdProduto] AS [IdProduto], [EstoqueEmpresa].[QtEstoque] AS [QtEstoque], max([EstoqueEmpresa].[DtReferencia]) AS [MaxDtReferencia]
FROM [EstoqueEmpresa] GROUP BY [EstoqueEmpresa].[IdProduto], [EstoqueEmpresa].[QtEstoque], [EstoqueEmpresa].[DtReferencia]) AS anon_1 ON [Produto].[IdProduto] = anon_1.[IdProduto] AND [EstoqueEmpresa].[DtReferencia] = anon_1.[MaxDtReferencia] JOIN [EstoqueEmpresa] ON [EstoqueEmpresa].[IdProduto] = [Produto].[IdProduto] AND [EstoqueEmpresa].[DtReferencia] = anon_1.[MaxDtReferencia]
WHERE [Produto_Empresa].[StAtivoVenda] = ? AND [Produto_Empresa].[CdEmpresa] = ? ORDER BY [Produto].[NmProduto]
Traceback (most recent call last):
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\default.py", line 748, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The multi-part identifier "EstoqueEmpresa.DtReferencia" could not be bound. (4104) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Users\SV\Desktop\codes_sv\fone.py", line 65, in <module>
    result = query.all()
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\orm\query.py", line 2697, in all
    return self._iter().all()  # type: ignore
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\orm\query.py", line 2855, in _iter
    result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\orm\session.py", line 2229, in execute
    return self._execute_internal(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\orm\session.py", line 2124, in _execute_internal
    result: Result[Any] = compile_state_cls.orm_execute_statement(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\orm\context.py", line 253, in orm_execute_statement
    result = conn.execute(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1414, in execute
    return meth(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\sql\elements.py", line 486, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 2326, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
  File "C:\Users\SV\kivy_venv\lib\site-packages\sqlalchemy\engine\default.py", line 748, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The multi-part identifier "EstoqueEmpresa.DtReferencia" could not be bound. (4104) (SQLExecDirectW);
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')
[SQL: SELECT [CodigoProduto].[CdChamada] AS [CodigoProduto_CdChamada], [Produto].[IdProduto] AS [Produto_IdProduto], [Produto].[NmProduto] AS [Produto_NmProduto], [Produto_Empresa].[VlPrecoCusto] AS [Produto_Empresa_VlPrecoCusto], [Produto_Empresa].[VlPrecoSugerido] AS [Produto_Empresa_VlPrecoSugerido], [EstoqueEmpresa].[DtReferencia] AS [EstoqueEmpresa_DtReferencia], anon_1.[QtEstoque] AS [anon_1_QtEstoque]
FROM [Produto] JOIN [Produto_Empresa] ON [Produto].[IdProduto] = [Produto_Empresa].[IdProduto] JOIN [CodigoProduto] ON [Produto].[IdProduto] = [CodigoProduto].[IdProduto] JOIN (SELECT [EstoqueEmpresa].[IdProduto] AS [IdProduto], [EstoqueEmpresa].[QtEstoque] AS [QtEstoque], max([EstoqueEmpresa].[DtReferencia]) AS [MaxDtReferencia]
FROM [EstoqueEmpresa] GROUP BY [EstoqueEmpresa].[IdProduto], [EstoqueEmpresa].[QtEstoque], [EstoqueEmpresa].[DtReferencia]) AS anon_1 ON [Produto].[IdProduto] = anon_1.[IdProduto] AND [EstoqueEmpresa].[DtReferencia] = anon_1.[MaxDtReferencia] JOIN [EstoqueEmpresa] ON [EstoqueEmpresa].[IdProduto] = [Produto].[IdProduto] AND [EstoqueEmpresa].[DtReferencia] = anon_1.[MaxDtReferencia]
WHERE [Produto_Empresa].[StAtivoVenda] = ? AND [Produto_Empresa].[CdEmpresa] = ? ORDER BY [Produto].[NmProduto]]
[parameters: ('S', 4)]
(Background on this error at: https://sqlalche.me/e/20/f405)

I don't know where to reference this field "CompanyStock.DtReference".

I just wanted the largest of each idproduct.


Mike Bayer

unread,
Apr 14, 2023, 2:21:56 PM4/14/23
to noreply-spamdigest via sqlalchemy
the initial issue is that you want DtReferencia from the subquery on the outside:

session.query(..., estoqueAtual.c.DtReferencia, ...)

and not "EstoqueEmpresa.DtReferencia", that's not available in the FROM list, it's inside a subquery.

also I dont think you'd want to "group by" the same column that you are feeing into max().that would defeat the purpose of using an aggregate.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 sqlalchemy+...@googlegroups.com.

Elias Coutinho

unread,
Apr 14, 2023, 3:21:44 PM4/14/23
to sqlal...@googlegroups.com
I am suffering!

It showed the same message.



# Subquery para buscar o maior registro de estoqueempresa para cada produto
estoqueAtual = session.query(
        EstoqueEmpresa.IdProduto,
        EstoqueEmpresa.QtEstoque,
        func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
    ).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque).subquery()

#print(estoqueAtual)

# Realiza a consulta com SQLAlchemy
##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
##               .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.IdProduto)\
##               .join(CodigoProduto, Produto.IdProduto == CodigoProduto.IdProduto)\
##               .join(estoqueAtual, and_(Produto.IdProduto == estoqueAtual.c.IdProduto))\
##               .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == Produto.IdProduto))\
##               .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
##               .filter(ProdutoEmpresa.CdEmpresa == 4)\
##               .order_by(Produto.NmProduto)

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, EstoqueEmpresa.DtReferencia, EstoqueEmpresa.QtEstoque)\
               .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.IdProduto)\
               .join(CodigoProduto, Produto.IdProduto == CodigoProduto.IdProduto)\
               .join(estoqueAtual, and_(Produto.IdProduto == estoqueAtual.c.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
               .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == Produto.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
               .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
               .filter(ProdutoEmpresa.CdEmpresa == 4)\
               .order_by(Produto.NmProduto)


--
Elias Coutinho.
Aprender sobre alguns assuntos é fundamental.
Aprender sobre Deus é indiscutivelmente o melhor conteúdo.

Mike Bayer

unread,
Apr 14, 2023, 4:13:47 PM4/14/23
to noreply-spamdigest via sqlalchemy
this line of code:

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, EstoqueEmpresa.DtReferencia, EstoqueEmpresa.QtEstoque)
    

should look like this:

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.DtReferencia, estoqueAtual.QtEstoque)
Reply all
Reply to author
Forward
0 new messages