Upload planilha excel no Python

[Elcio_Sanoli]

Olá a todos.

Estou tentando escrever um codigo em python que seguirá os seguintes passos:

1 Tenho uma base acumulada em excel (.XLSX) que quero migra-la para um banco de dados. Ou sejam será a carga inicial dos dados.

2 Diariamente recebo uma outra planilha (origem do sistema) em excel (.XLSX) que tenho que importar para esse novo banco que criei mas antes disso tenho que:

    2.1 consultar na planilha qual a maior e menor data da coluna de data.

    2.2 entrar no banco de dados consultar o intervalor correspondente às datas extraidas do excel (coluna_data >= menor AND coluna_data <= maior) (Meu problema está aqui)

    2.3 upload da planilha de excel para o banco de dados.

Como as informações são de origem de um sistema, a regra que preciso usar para exclusão de "duplicados" é somente a data, como demonstrado acima. Caso utilize outro criterio como ID, isso me traz outro tipo de erro, então preciso da solução utilizando a data como criterio.

Por enquanto estou tendo problemas na etapa de deletar o intervalo de data

o codigo que estou usando é

=======================================================================================

# Criar banco de dados

import sqlite3

# conectando...
con = sqlite3.connect(r'C:\Users\Meu_Computador\Desktop\Anaconda\db_teste.db')

# definindo um cursor
cur = con.cursor()

# criando a tabela (schema)
sql = """
CREATE TABLE WWS_Latam (
    Value_Date DATE,
    Opening_Date DATE,
    Maturity_Date DATE,
    Continuation_Number INTEGER,
    Transaction_Number INTEGER,
    Counterparty TEXT,
    Counterparty_Name TEXT,
    Counterparty_Portfolio TEXT,
    Portfolio_Name TEXT,
    Instrument TEXT,
    Transaction_Sign TEXT,
    Settlement_Amount DOBLE,
    Settlement_Date DATE,
    Nominal_Amount DOUBLE,
    FX_Netting_Currency TEXT,
    FX_Base_Amount DOUBLE,
    FX_Forward_Points DOUBLE,
    Trader TEXT,
    Instrument_Group TEXT,
    Deal_Rate DOUBLE,
    Deal_Price DOUBLE,
    Units DOUBLE,
    Owner TEXT,
    Portfolio TEXT,
    Transaction_State TEXT,
    Currency TEXT,
    Currency_2nd TEXT,
    FX_Spot_Rate DOUBLE,
    Fixing_Offset DOUBLE,
    Date_Basis TEXT,
    Transaction_Kind TEXT,
    Transaction_Type TEXT,
    FX_Premium_Type TEXT,
    Premium_Amount TEXT,
    Premium_Currency TEXT,
    Premium_Date DATE,
    Premium_Price TEXT,
    Premium_Type TEXT,
    Reference_Number TEXT,
    Comment TEXT)
                    """
cur.execute(sql)
con.commit()

# desconectando...
con.close()

 

=======================================================================================

#Carga Inicial Banco de dados
import xlrd
import sqlite3
import datetime

# Open the workbook and define the worksheet
book = xlrd.open_workbook(r'C:\Users\Meu_Computador\Desktop\Anaconda\Base_bruta_excel.xlsx')
sheet = book.sheet_by_name('T_Base_BD')

# Establish a MySQL connection
database = sqlite3.connect(r'C:\Users\Meu_Computador\Desktop\Anaconda\db_teste.db')
#database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython")

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create the INSERT INTO sql query
query = """INSERT INTO WWS_Latam(Value_Date, Opening_Date, Maturity_Date, Continuation_Number, Transaction_Number,
Counterparty, Counterparty_Name, Counterparty_Portfolio, Portfolio_Name, Instrument, Transaction_Sign,
Settlement_Amount, Settlement_Date, Nominal_Amount, FX_Netting_Currency, FX_Base_Amount, FX_Forward_Points,
Trader, Instrument_Group, Deal_Rate, Deal_Price, Units, Owner, Portfolio, Transaction_State, Currency,
Currency_2nd, FX_Spot_Rate, Fixing_Offset, Date_Basis, Transaction_Kind, Transaction_Type, FX_Premium_Type,
Premium_Amount, Premium_Currency, Premium_Date, Premium_Price, Premium_Type, Reference_Number, Comment) 
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?)"""

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r in range(1, sheet.nrows):

        Value_Date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,0).value, book.datemode))
        Opening_Date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,1).value, book.datemode))
        Maturity_Date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,2).value, book.datemode))
        Continuation_Number = sheet.cell(r,3).value
        Transaction_Number = sheet.cell(r,4).value
        Counterparty = sheet.cell(r,5).value
        Counterparty_Name = sheet.cell(r,6).value
        Counterparty_Portfolio = sheet.cell(r,7).value
        Portfolio_Name = sheet.cell(r,8).value
        Instrument = sheet.cell(r,9).value
        Transaction_Sign = sheet.cell(r,10).value
        Settlement_Amount = sheet.cell(r,11).value
        Settlement_Date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,12).value, book.datemode))
        Nominal_Amount = sheet.cell(r,13).value
        FX_Netting_Currency = sheet.cell(r,14).value
        FX_Base_Amount = sheet.cell(r,15).value
        FX_Forward_Points = sheet.cell(r,16).value
        Trader = sheet.cell(r,17).value
        Instrument_Group = sheet.cell(r,18).value
        Deal_Rate = sheet.cell(r,19).value
        Deal_Price = sheet.cell(r,20).value
        Units = sheet.cell(r,21).value
        Owner = sheet.cell(r,22).value
        Portfolio = sheet.cell(r,23).value
        Transaction_State = sheet.cell(r,24).value
        Currency = sheet.cell(r,25).value
        Currency_2nd = sheet.cell(r,26).value
        FX_Spot_Rate = sheet.cell(r,27).value
        Fixing_Offset = sheet.cell(r,28).value
        Date_Basis = sheet.cell(r,29).value
        Transaction_Kind = sheet.cell(r,30).value
        Transaction_Type = sheet.cell(r,31).value
        FX_Premium_Type = sheet.cell(r,32).value
        Premium_Amount = sheet.cell(r,33).value
        Premium_Currency = sheet.cell(r,34).value
        Premium_Date = sheet.cell(r,35).value
        #Premium_Date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell(r,35).value, book.datemode))
        Premium_Price = sheet.cell(r,36).value
        Premium_Type = sheet.cell(r,37).value
        Reference_Number = sheet.cell(r,38).value
        Comment = sheet.cell(r,39).value

              
        # Assign values from each row
        values = (Value_Date, Opening_Date, Maturity_Date, Continuation_Number, Transaction_Number,
Counterparty, Counterparty_Name, Counterparty_Portfolio, Portfolio_Name, Instrument, Transaction_Sign,
Settlement_Amount, Settlement_Date, Nominal_Amount, FX_Netting_Currency, FX_Base_Amount, FX_Forward_Points,
Trader, Instrument_Group, Deal_Rate, Deal_Price, Units, Owner, Portfolio, Transaction_State, Currency,
Currency_2nd, FX_Spot_Rate, Fixing_Offset, Date_Basis, Transaction_Kind, Transaction_Type, FX_Premium_Type,
Premium_Amount, Premium_Currency, Premium_Date, Premium_Price, Premium_Type, Reference_Number, Comment)

        # Execute sql Query
        cursor.execute(query, values)
# Close the cursor
cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

 

=======================================================================================

#Exclusão de datas do relatorio do Banco de dados

import sqlite3

import pandas as pd
wb = pd.read_excel(r'C:\Users\Meu_Computador\Desktop\Anaconda\Base_bruta_excel.xlsx',sheet_name = 'T_Base_BD')

menor = wb['Opening Date'].min()
maior = wb['Opening Date'].max()
 

# conectando...
con = sqlite3.connect(r'C:\Users\Meu_Computador\Desktop\Anaconda\db_teste.db')

# definindo um cursor
cur = con.cursor()

# criando a tabela (schema)
sql = f"DELETE FROM WWS_Latam WHERE Opening_Date >= menor AND Opening_Date <= maior"
print(sql)
cur.execute(sql)
con.commit()

# desconectando...
con.close()