Good afternoon.
I am having trouble transforming a SQL Server query to SQL Alchemy.
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
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))
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.