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 = '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.
>
*
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e0345071-902f-454d-bd4c-1e4262d03b84n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/e0345071-902f-454d-bd4c-1e4262d03b84n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/fc187fa4-d018-456e-90a4-c8f9e290e141%40app.fastmail.com.
