def aggiornaDatabase(lista):
# Credentials to access your database
mydb = inserisciCredenzialiDatabase()
# Initialize cursor
mycursor = mydb.cursor()
sqlFormula = 'INSERT INTO immobili2 (ID, Ora, Sito, Localita,' \
'Headline, URL, Prezzo, Locali, Superficie, ' \
'Bagni, Ratio, BoxPostoAuto, Piano, ' \
'AnnodiCostruzione, Stato, Riscaldamento, ClasseEnergetica, ' \
'Via, Lat, Lon, CodiceCasa) ' \
'VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
# Database update cicle
for elem in lista:
# Vector to insert in the database
vet = [
elem.ID, elem.Ora, elem.Sito, elem.Localita, elem.Headline,
elem.URL, elem.Prezzo, elem.Locali, elem.Superficie, elem.Bagni,
elem.Ratio, elem.BoxPostoAuto, elem.Piano, elem.AnnodiCostruzione,
elem.Stato, elem.Riscaldamento, elem.ClasseEnergetica, elem.Via,
elem.Latitudine, elem.Longitudine, elem.CodiceCasa
]
# Execute and commit
mycursor.execute(sqlFormula, vet)
mydb.commit()
def cancellaDatabase():
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
mycursor.execute('DROP TABLE immobili2')
# Stampa Resoconto a Terminale
oraBreveAttuale = f'{datetime.datetime.now().hour}:{datetime.datetime.now().minute}'
logger.info("Database Azzerato")
def tabellaDuplicati():
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
try:
mycursor.execute('DROP TABLE duplicato_immobili')
except:
pass
mycursor.execute('CREATE TABLE duplicato_immobili ' + STDDATABASE)
def locate():
# It connects to the database
mydb = inserisciCredenzialiDatabase()
# It selects all the different locations available on the database
data = pd.read_sql(f'SELECT distinct localita FROM immobili2 ', con=mydb)
# Compulsory for the Geo data representation
lowercase = lambda x: str(x).lower()
data.rename(lowercase, axis="columns", inplace=True)
return data
def creaDatabase():
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
mycursor.execute('CREATE TABLE immobili2' + STDDATABASE)
# It prints into the logging file
oraBreveAttuale = f'{datetime.datetime.now().hour}:{datetime.datetime.now().minute}'
logger.info("Database Creato")
def SearchingInDB(location):
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
location = f'"{location}"'
mycursor.execute(f'select URL from Immobili2 where URL is not Null and localita =' + location)
Lista_aux = mycursor.fetchall()
Lista = list(itertools.chain(*Lista_aux))
print(Lista)
return Lista
# It prints into the logging file
def resetDatabase():
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
try:
mycursor.execute('DROP TABLE immobili2')
except:
logger.info("Nessun Database Cancellato")
mycursor.execute('CREATE TABLE immobili2' + STDDATABASE)
# It prints into the logging file
oraBreveAttuale = f'{datetime.datetime.now().hour}:{datetime.datetime.now().minute}'
logger.info("Database Azzerato")
def house(location, stato):
# It connects to the database
mydb = inserisciCredenzialiDatabase()
location = f'"{location}"'
# It selects the best house associated to the location and status given by the user
# To be improved
data = pd.read_sql(
'SELECT URL, Prezzo '
'FROM immobili2 '
f'where Localita = {location} '
'and Ratio is not Null '
f'and Stato = {stato} '
'and Ratio <= all(select Ratio from immobili2 '
f'where Localita={location} and Ratio is not Null and Stato={stato})',
con=mydb)
return data
def rimozioneDuplicati():
mydb = inserisciCredenzialiDatabase()
mycursor = mydb.cursor()
# Put all the duplicates in the duplicate_table
mycursor.execute(
'INSERT INTO duplicato_immobili select '
'* from immobili2 as I '
'where I.sito = "Immobiliare.it" '
'and (I.Prezzo, I.Superficie, I.Locali) in '
'(select Prezzo, Superficie, Locali from immobili2 as C where C.sito = "Casa.it")'
)
mydb.commit()
# Remove all the duplicates from the original table
mycursor.execute(
'DELETE immobili2 from immobili2 '
'WHERE (Prezzo, Superficie, Locali) IN '
'(SELECT Prezzo, Superficie, Locali FROM duplicato_immobili)')
mydb.commit()
# Update the original table
mycursor.execute('INSERT INTO immobili2 '
'select * '
'from duplicato_immobili')
mydb.commit()
# Drop the duplicate_table
mycursor.execute('drop table duplicato_immobili')
tabellaDuplicati()
mycursor.execute(
'INSERT INTO duplicato_immobili select '
'* from immobili2 as I '
'where I.sito = "Immobiliare.it" '
'and (I.CodiceCasa) in '
'(select CodiceCasa from immobili2 as C where C.sito = "Casa.it")')
mydb.commit()
# Remove all the duplicates from the original table
mycursor.execute('DELETE immobili2 from immobili2 '
'WHERE CodiceCasa IN '
'(SELECT CodiceCasa FROM duplicato_immobili)')
mydb.commit()
# Update the original table
mycursor.execute('INSERT INTO immobili2 '
'select * '
'from duplicato_immobili')
mydb.commit()
# Drop the duplicate_table
mycursor.execute('drop table duplicato_immobili')
def load_data(location, columns, columns_aux):
# It connects to the database
mydb = inserisciCredenzialiDatabase()
location = f'"{location}"'
# Columns for the Raw data
# Creation of the total columns (the standard ones plus the ones chosen by the user
tot = ''
for elem in columns:
tot = tot + ',' + elem
for elem in columns_aux:
tot = tot + ',' + elem
tot = tot[1:]
# It exctracts from the database only the infos requested by the users
data = pd.read_sql(
f'SELECT {tot} FROM immobili2 Where lat is not Null and lon is not Null'
f' and Localita =' + location,
con=mydb)
data = data.replace('latitudine', 'lat')
# Compulsory for the Geo data representation
lowercase = lambda x: str(x).lower()
data.rename(lowercase, axis="columns", inplace=True)
return data