def restituisci(user, titolo_film): active_booking = check_if_active(user, titolo_film) user_id = get_data.get_user_id(user) movie_id = get_data.get_movie_id(titolo_film) data_corrente = get_data.get_current_date() sql = "update bookings set Actual_Return_date = '%s' where user_id = %d and\ movie_id = %d and Actual_Return_Date is null"%(data_corrente, user_id, movie_id) db = dbutils.connect() cursor = db.cursor() #Se il Film effettivamente non è stata ancora restituito if active_booking is True: try: cursor.execute(sql) #Con commit invio i dati alla Tabella db.commit() print("Hai restituito il film %s in data: %s"%(titolo_film, data_corrente)) db.close() except(MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() print("Errore Database") db.close() #Se invece è già stato restituito else: print("Impossibile restituire il Film specificato... Forse risulta già restituito?") return
def check_if_active(user, movie): ritorno = False user_id = get_data.get_user_id(user) movie_id = get_data.get_movie_id(movie) sql = "select booking_id from bookings where user_id = %d and movie_id = %d\ and Actual_Return_Date is null"%(user_id, movie_id) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) #Memorizzo il campo booking_id data = cursor.fetchone() #Se la query non restituisce nulla significa che il Film è già stato restituito if data is None: check = False #Altrimenti la Prenotazione è ancora attiva else: check = True #Chiudo la Connessione al DB db.close() #Se venissero lanciate Eccezioni except(MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.close() print("Errore Database") return check
def print_active_bookings(username): user_id = get_data.get_user_id(username) sql = "select movies.movie_id, title, director, booking_date, return_date from users,\ bookings, movies where movies.movie_id = bookings.movie_id and bookings.user_id = %d \ and users.user_id = %d and Actual_Return_Date is null" % (user_id, user_id) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) data = cursor.fetchall() for row in data: movie_id = row[0] title = row[1] director = row[2] booking_date = row[3] return_date = row[4] print( "ID Film: %d Titolo: %s Regista: %s Data Prestito: %s Data Restituzione: %s\ " % (movie_id, title, director, booking_date, return_date)) db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on displaying data...") db.close() return
def login(username, password): check = False user1 = "" pass1 = "" db = dbutils.connect() cursor = db.cursor() sql = "select name, surname from users where user = '******' and pass = '******'" %(username, password) try: cursor.execute(sql) data = cursor.fetchall() for row in data: user1 = row[0] pass1 = row[1] if(user1 != "" and pass1 != ""): check = True db.close() else: check = False db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Login attempt failed") db.rollback() db.close() return check
def create_users_table(): #Connetto al Database db = dbutils.connect() cursor = db.cursor() sql = "create table users(user_id integer primary key,\ name varchar(25), surname varchar(25), user varchar(13), pass varchar(13))" sql_drop = "drop table if exists users" try: #Elimino la tabella se esiste già cursor.execute(sql_drop) #Ora creo la Tabella cursor.execute(sql) print("Table Users succesfully created...") #Chiudo la Connessione db.close() #Se viene lanciata un'eccezione except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Creation of Users table failed...") #Annullo le modifiche db.rollback() #Chiudo la connessione db.close() return
def create_bookings_table(): db = dbutils.connect() cursor = db.cursor() sql_drop = "drop table if exists bookings" sql = "create table bookings (booking_id integer primary key,\ user_id integer, movie_id integer, booking_date varchar(25), return_date varchar(25),\ actual_return_date varchar(25))" try: #Elimino la tabella se c'è già cursor.execute(sql_drop) #Ora creo la tabella cursor.execute(sql) print("Table Bookings created succesfully") #E chiudo la Connessione al DB db.close() #Se venisse lanciata un'eccezione except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Creation of Bookings table failed...") #Annullo le modifiche db.rollback() #Chiudo la connessione db.close() return
def login(): logged_user = "" db = dbutils.connect() cursor = db.cursor() print("Si prega di eseguire il Login:"******"Username: "******"Password: "******"select user_id from users where user = '******' and pass = '******'"%(user, passw) try: cursor.execute(sql) data = cursor.fetchone() if data is None: logged_user = None else: logged_user = user db.close() except(MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.close() print("Errore Database") if logged_user is None: print("Login Fallito, riprova...") else: print("Benvenuto %s"%(logged_user)) return logged_user
def create_relationships(): sql_rel1 = "ALTER TABLE `bookings` ADD CONSTRAINT `bookings_users` FOREIGN KEY (`user_id`)\ REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;" sql_rel2 = "alter table `bookings` add constraint `bookings_movies` foreign key\ (`movie_id`) references `movies` (`movie_id`) on delete cascade on update cascade" #Mi connetto al DB db = dbutils.connect() cursor = db.cursor() try: #Creo la relazione tra la tabella Bookings e la tabella Users cursor.execute(sql_rel1) #Creo la relazione tra la tabella Bookings e Movies cursor.execute(sql_rel2) #Chiudo la Connessione db.close() print("Created Relationship between Movies Table and Bookings Table") print("Created Relationship between Users table and Bookings table") #Se venisse lanciata un'eccezione except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Relationship creation between Users and Bookings failed...") #Annullo le modifiche db.rollback() #Chiudo la connessione db.close() return
def insert_to_db(list): #print [x.guid for x in list] conn = dbutils.connect() c = conn.cursor() for t in list: c.execute("insert into data(title, date, hash_guid, content, link) values(?, ?, ?, ?, ?)", (t.title, t.date, t.guid, t.content, t.link)); conn.commit() c.close()
def contains_in_db(guid): conn = dbutils.connect() c = conn.cursor() c.execute("select * from data where hash_guid = '%s'" % guid); if c.fetchall() == []: return False else: return True
def print_data(): conn = dbutils.connect() c = conn.cursor() c.execute("select * from data") conn.commit() data = c.fetchone() while data != None: print data data = c.fetchone() c.close()
def get_movie_id(titolo_libro): sql = "select movie_id from movies where title = '%s'" % (titolo_libro) db = dbutils.connect() cursor = db.cursor() movie_id = 0 try: cursor.execute(sql) data = cursor.fetchall() for row in data: movie_id = row[0] db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on retrieving movie_id....") db.close() return movie_id
def get_user_id(username): sql = "select user_id from users where user = '******'" % (username) db = dbutils.connect() user_id = 0 cursor = db.cursor() try: cursor.execute(sql) data = cursor.fetchall() for row in data: user_id = row[0] db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on getting user_id") db.close() return user_id
def insert_into_movies(movie_id, title, director, year, income): sql = "insert into movies(movie_id, title, director, year, income) values (%d, '%s', '%s', %d, %d)"\ % (movie_id, title, director, year, income) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) #Con commit invio i dati alla Tabella db.commit() print("Data inserted into Movies table") db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() print("Error: Data not inserted...") db.close() return
def insert_into_users(user_id, name, surname, user, passw): sql = "insert into users(user_id, name, surname, user, pass) values (%d,'%s', '%s', '%s', '%s')"\ %(user_id, name, surname, user, passw) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) #Con commit invio i dati alla Tabella db.commit() print("New data succesfully added to Users table") db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() print("Error: Data not inserted...") db.close() return
def print_users(): sql = "select * from users" db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) data = cursor.fetchall() for row in data: user_id = row[0] name = row[1] surname = row[2] print("ID Utente %d Nome %s Cognome %s" % (user_id, name, surname)) db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on printing table") db.close() return
def insert_into_bookings(booking_id, user_id, movie_id, booking_date, return_date): sql = "insert into bookings (booking_id, user_id, movie_id, booking_date, return_date) values "\ "(%d, %d, %d, '%s', '%s')" %(booking_id, user_id, movie_id, booking_date, return_date) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) #Con commit invio i dati alla Tabella db.commit() print("New data succesfully added to Bookings table") db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) db.rollback() print("Error, Data not inserted...") db.close() return
def print_bookings(): sql = "select * from bookings" db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) data = cursor.fetchall() for row in data: booking_id = row[0] user_id = row[1] movie_id = row[2] booking_date = row[3] return_date = row[4] print("ID Pren: %d ID Utente: %d ID Film: %d Data Prenotazione: %s\ Data Prevista Restituzione: %s" % (booking_id, user_id, movie_id, booking_date, return_date)) db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on printing table") db.close() return
def print_movies(): sql = "select * from movies" db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) data = cursor.fetchall() for row in data: movie_id = row[0] title = row[1] director = row[2] year = row[3] income = row[4] print("ID: %d Titolo: %s Regista: %s Anno: %d Incasso: %d" %(movie_id, title, director,\ year, income)) db.close() except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Error on printing Movies table") db.rollback() db.close() return
def borrow(user, titolo): user_id = get_data.get_user_id(user) movie_id = get_data.get_movie_id(titolo) booking_id = randint(2, 50) data_corrente = get_data.get_current_date() data_futura = get_data.get_future_date() sql = "insert into bookings (booking_id, user_id, movie_id, booking_date, return_date) values\ (%d, %d, %d, '%s', '%s')"%(booking_id, user_id, movie_id, data_corrente, data_futura) db = dbutils.connect() cursor = db.cursor() try: cursor.execute(sql) #Con commit invio i dati alla Tabella db.commit() print("Hai preso in prestito il film %s"%(titolo)) db.close() except(MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Errore nell'aggiungere la prenotazione al DB") db.rollback() db.close() return
def create_movie_table(): sql_drop = "drop table if exists movies" sql = "create table movies (movie_id integer primary key, title varchar(25), director varchar(25), year integer, income decimal(15,2))" db = dbutils.connect() cursor = db.cursor() try: #Se la tabella esiste già, la elimino cursor.execute(sql_drop) #Ora creo la tabella Movies cursor.execute(sql) #Chiudo la connessione db.close() print("Table Movies succesfully created") #Se un'Eccezione viene lanciata except (MySQLdb.Error, MySQLdb.Warning) as e: print(e) print("Creation of Movies table failed...") #Annullo le modifiche db.rollback() #Chiudo la connessione db.close() return
def test_cursor(self): self.assertIsNotNone(dbutils.connect())
def clear_data(self): c = dbutils.connect() c.execute("delete from data")
def test_date(self): conn = dbutils.connect() c = conn.cursor() c.execute("select * from data") conn.commit()
def insert_data(self): c = dbutils.connect() c.execute("insert into data values('1', '1', '1')")
def clear_data(): conn = dbutils.connect() c = conn.cursor() c.execute("delete from data") conn.commit()
def get_name(username): sql = "select name from users where user = '******'" % (username) db = dbutils.connect() cursor = db.cursor()
def query(date): conn = dbutils.connect() c = conn.cursor() c.execute("select * from data where date >= ?", (date,)) return c
import dbutils if __name__ == '__main__': conn = dbutils.connect() c = conn.cursor() ids = [] for row in c.execute("select _id from data"): id = row[0] ids.append(id) date = 0 for id in ids: c.execute("update data set date = ? where _id = ?", (date, id)) date += 1 conn.commit() conn.close()
import dbutils conn = dbutils.connect() cursor = conn.cursor() def count_tables(): query = """ SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES; """ count = cursor.execute(query).fetchval() return count # def table_exists(table_name): # query = """ # SELECT COUNT(*) # FROM INFORMATION_SCHEMA.TABLES # WHERE TABLE_NAME = '%s' # """ % table_name # count = cursor.execute(query).fetchval() # return count != 0 # def delete_table(table_name): # query = "DROP TABLE %s" % table_name # cursor.execute(query) # conn.commit() print("Num tables: %s" % count_tables()) cursor.execute(""" CREATE TABLE GamesNew(