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(
