Re: [sqlalchemy] Using joins+max with sql server
*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)
Em sex., 14 de abr. de 2023 às 15:21, Mike Bayer <
mike_not_on_goo...@zzzcomputing.com> escreveu:
> 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.
>
> On Fri, Apr 14, 2023, at 1:30 PM, Elias Coutinho wrote:
>
> 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 = '00A002'
> 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.ex
[sqlalchemy] Using joins+max with sql server
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 = '00A002'
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')\
