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.
