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()