Add register from Excel in SQL Server

create_pedido.py

import pandas as pd
from pandas import to_datetime

from datetime import datetime
import pytz

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

import locale

from models import Pessoa, Fornecedor, PedidoDeCompra

# Definir a localização como "Português do Brasil"
locale.setlocale(locale.LC_ALL, 'pt_BR.utf8')

data = datetime.now()
data_formatada = data.strftime('%Y-%m-%d 00:00:00.000')

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

id_pessoa_endereco_contato = 'id_pessoa_endereco_contato'

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


# Conecta a máquina.
connection = engine.connect()

# criar uma sessão para executar as consultas
# session = Session(engine)

# criar uma sessão
Session = sessionmaker(bind=engine)
session = Session()

# ----------------------------------------------------------------------------------------------------------------------------------------------------
# Crio um dataframe com todos os fornecedores
stmt_query_fornecedores = select(Pessoa.IdPessoa, Pessoa.CdChamada, Pessoa.NmPessoa, Pessoa.NmCurto, 
                                 Fornecedor.IdOperacao, Fornecedor.IdPrazo, Fornecedor.IdNaturezaLancamento) \
                                .join(Fornecedor, Fornecedor.IdPessoaFornecedor == Pessoa.IdPessoa)

# Cria um dataframe para percorrer os dados linha a linha.
df_fornecedores = pd.read_sql(stmt_query_fornecedores, engine)

# Remove os espaços em branco das colunas
df_fornecedores['CdChamada'] = df_fornecedores['CdChamada'].apply(lambda x: x.strip())
df_fornecedores['IdPessoa'] = df_fornecedores['IdPessoa'].apply(lambda x: x.strip())

# ----------------------------------------------------------------------------------------------------------------------------------------------------
# Gero um id para o campo IdPedidoDeCompra
query_get_multi_code = text(
    "EXEC stp_GetMultiCode 'PedidoDeCompra', 'IdPedidoDeCompra', 1")
campo_id_pedido_de_compra = connection.execute(query_get_multi_code)

# Percorro campo_id_pedido_de_compra para coletar o id.
for row in campo_id_pedido_de_compra:
    id_pedido_de_compra = row[0]
# ----------------------------------------------------------------------------------------------------------------------------------------------------

# Gero um código para o campo CdChamada
codigo_chamada_pedido = None

with engine.connect() as conn:
    result = conn.execute(text("DECLARE @Codigo INT; EXEC stp_RetornarProximoCodigo 'PedidoDeCompra', 'CdChamada', @Codigo OUT; SELECT @Codigo as VlUltimoCodigo"))
    codigo_chamada_pedido = result.scalar()
    # codigo_chamada_pedido = codigo_chamada_pedido +1

print(int(codigo_chamada_pedido) + 1)
# ----------------------------------------------------------------------------------------------------------------------------------------------------
#Ler a planilha
df_itens_planilha = pd.read_excel("COTACAO.xlsx")

# Criando a capa do pedido de compra.
df_pedido = df_itens_planilha.loc[:, ['CodigoFornecedor', 'Fornecedor']]
df_pedido['IdPedidoDeCompra'] = id_pedido_de_compra
df_pedido['CdChamadaPedido'] = int(codigo_chamada_pedido) + 1
df_pedido['CdEmpresa'] = 4
df_pedido['CdEmpresaFinanceiro'] = 4
df_pedido['DtEmissao'] = data_formatada
df_pedido['DtEntrega'] = data_formatada
df_pedido['DtLiberacao'] = data_formatada

# Preenchendo com Zeros a esquerda.
df_pedido['CodigoFornecedor'] = df_pedido['CodigoFornecedor'].astype(str).str.zfill(6)
df_pedido['CdChamadaPedido'] = df_pedido['CdChamadaPedido'].astype(str).str.zfill(6)

# Removendo duplicados
df_pedido = df_pedido.drop_duplicates(subset=['IdPedidoDeCompra', 'CdChamadaPedido'], keep='first')

# Unifico os dataframes df_pedido e df_fornecedores pelas colunas 'CodigoFornecedor' e 'CdChamada' respectivamente.
# Agora posso usar todas as informações de df_pedido_fornecedores para gerar a capa do pedido.
df_pedido_fornecedores = pd.merge(df_pedido, df_fornecedores[['CdChamada', 'IdPessoa', 'IdOperacao', 'IdPrazo', 'IdNaturezaLancamento']], left_on='CodigoFornecedor', right_on='CdChamada', how='left')

# print(df_pedido_fornecedores['CdChamadaPedido'], '<<<========')
df_pedido_fornecedores.to_excel('pedido_de_compra.xlsx', index=False)

#print(df_pedido_fornecedores.dtypes)

try:
    stmt_insere_capa_pedido = (
        insert(PedidoDeCompra).
        values(IdPedidoDeCompra=df_pedido_fornecedores['IdPedidoDeCompra'],
               CdChamada=codigo_chamada_pedido,
               CdEmpresa=4,
               DsPedidoDeCompra='descricao',
               CdEndereco=None,
               IdPessoaTransportador=None,
               IdUsuario='00A0000001',
               TpFretePorConta='E',
               IdPessoaFornecedor=df_pedido_fornecedores['IdPessoa'],
               DtEmissao=data_formatada,
               IdCotacao=None ,
               StPedidoDeCompra='A',
               DtEntrega=data_formatada,
               DsObservacao=None,
               IdPrazo='00A000000O',
               VlAjusteFinanceiro=0,
               IdTipoContato=None,
               VlAcrescimo=0,
               VlDesconto=0,
               VlFrete=0,
               VlSeguro=0,
               VlOutrasDespesas=0,
               IdNaturezaLancamento='00A0000030',
               IdCentroDeCusto=None,
               StEntregaParcial='N',
               NrOrcamento=None ,
               IdIndexador=None,
               IdUsuarioLiberacao='00A0000001',
               DtLiberacao=data_formatada,
               IdUsuarioCancelamento=None,
               DtCancelamento=None,
               CdCEP=None,
               NmLogradouro=None,
               DsComplemento=None,
               NrLogradouro=None,
               TpLogradouro=None,
               IdBairro=None,
               IdCidade=None,
               CdEmpresaFinanceiro=4,
               DsAplicacao=None,
               IdPrazoTransportador=None,
               VlACT=0,
               DtEmissaoACT=None,
               IdEntidadeOrigem=None,
               NmEntidadeOrigem=None,
               IdUsuarioResponsavelLiberacao=None,
               IdOperacao=None,
               VlICMSFrete=0,
               VlICMSSTFrete=0,
               AlICMSFrete=0,
               AlICMSSTFrete=0,
               VlBCICMSFrete=0,
               VlBCICMSSTFrete=0,
               IdControleAlcadaRegra=None)
    )

    with engine.connect() as conn:
        conn.execute(stmt_insere_capa_pedido)

except Exception as e:
    print(e)



# Percorre os pedidos de venda do dataframe df_pedidos.
for index, row in df_itens_planilha.iterrows():
    cod_chamada_fornecedor = str(row['CodigoFornecedor']).zfill(6)
    nome_fornecedor =  row['Fornecedor']
    cod_produto =  row['Código']
    quantidade_item = row['Sug Compras']
    preco_unitario = row['P. Comp Novo']
    al_icms = row['Icms']
    al_ipi = row['IPI']

    df_fornecedores_filtrado = df_fornecedores.loc[df_fornecedores['CdChamada'] == cod_chamada_fornecedor]
    id_pessoa = df_fornecedores_filtrado['IdPessoa'].iloc[0]

    #print(id_pessoa)
# ----------------------------------------------------------------------------------------------------------------------------------------------------

'''
select * from PedidoDeCompra where CdChamada='080402'
select * from PedidoDeCompraItem where IdPedidoDeCompra='00A00027IZ'
'''