def LendBook(st, titleC, ID):
with connection.cursor() as cursor:
query4 = "SELECT COUNT(*) FROM books WHERE title=%s AND statut='dispo' "
cursor.execute(query4,(titleC,))
result = cursor.fetchall()
count=result[0][0]
if count== 0:
return st.warning("Error! Either the given title does not exist or the wanted book is not available now !")
else :
with connection.cursor() as cursor:
UpdateStatut = "UPDATE books SET statut ='non disponible' , emprunté_par=%s , lend_date= date(now()) WHERE title=%s "
cursor.execute(UpdateStatut,(ID,titleC))
connection.commit()
return st.success("Book Lended SUCCESSFULLY !")
def viewbooks(st, connection) :
with connection.cursor() as cursor:
choix = st.radio("choose an option : SHOW : ", ("ALL details", "authors" , "titles"))
if choix == "ALL details":
sql = 'SELECT * FROM books'
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df.rename(columns = {0: 'title', 1: 'author' , 2: 'statut' , 3:'déposé_par' , 4: 'emprunté_par' ,5 :'category' ,6:'lend_date' , 7: 'add_date'})
# options supplémentaires :
elif choix == "authors" :
rq1 = 'SELECT DISTINCT author FROM books'
cursor.execute(rq1)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df.rename(columns = {0: 'author'})
elif choix == "titles" :
rq2 = 'SELECT DISTINCT title FROM books'
cursor.execute(rq2)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df.rename(columns = {0: 'category'})
return st.table(df)
def generate_link(st,title):
query = ("SELECT COUNT(*) FROM pdfs WHERE title=%s")
with connection.cursor() as cursor:
cursor.execute(query,(title,))
result = cursor.fetchall()
count=result[0][0]
if count== 0:
return st.warning("Error! Please check the title of the book")
else :
with connection.cursor() as cursor:
query = ("SELECT uploaded_file FROM pdfs WHERE title=%s")
cursor.execute(query,(title,))
result = cursor.fetchall()
g_link=result[0][0]
st.write("check this link to find you book: ")
st.info(g_link)
def save(self): # TODO: Check that the thing actually exists in the database.
with connection:
with connection.cursor() as cursor:
cursor.execute(database.UPDATE_GAME_ON_GAME_TABLE, (self.computer.id, self.player.id,
self.turn_indicator,
self.deck.id, self.user.id,
self.id))
def load_latest_game(cls, email):
"""Return latest game by a user or a new game if one didn't exist."""
with connection:
with connection.cursor() as cursor:
user = User.get_user_by_email(email)
cursor.execute(database.GET_LATEST_GAME_TABLE_BY_EMAIL, (email,))
bulk = cursor.fetchone()
print (bulk)
if bulk:
(game_table_id, timestamp_created, computer_hand_id,
player_hand_id, deck_id, user_id, turn_indicator) = bulk
else:
return cls.new_game(user)
# Get Hands
cursor.execute(database.GET_HAND_BY_ID, (player_hand_id, ))
p_s1, p_v1, p_s2, p_v2, p_s3, p_v3, p_s4, p_v4, p_s5, p_v5,\
uid, mtime = cursor.fetchone()
player = User.get_user_by_id(uid)
player_hand = Hand(player, Card(p_s1, p_v1),
Card(p_s2, p_v2),
Card(p_s3, p_v3),
Card(p_s4, p_v4),
Card(p_s5, p_v5))
cursor.execute(database.GET_HAND_BY_ID, (computer_hand_id, ))
c_s1, c_v1, c_s2, c_v2, c_s3, c_v3, c_s4, c_v4, c_s5, c_v5,\
cuid, cmtime = cursor.fetchone()
computer = User.get_user_by_id(COMPUTER_UID)
computer_hand = Hand(computer, Card(c_s1, c_v1),
Card(c_s2, c_v2),
Card(c_s3, c_v3),
Card(c_s4, c_v4),
Card(c_s5, c_v5))
deck = Deck(deck_id)
return cls(deck, user, player_hand, computer_hand, game_table_id, turn_indicator)
def create_card(self):
# todo
name = input('please input your card name:')
type = input(
'''please choose your card type : Quantity/Courage/Patience/
Knowledge/Health/Charm/Labour/Skill：''')
content = input('''Content:''')
effect = input('''Effect:''')
reward = input('''Reward:''')
with connection.cursor() as cursor:
try:
sql_create_card = f"""Insert into table {constants.table_name_cards}
(
{constants.cards_field_name},
{constants.cards_field_type},
{constants.cards_field_content},
{constants.cards_field_effect},
{constants.cards_field_reward}
)
Values
({name}, {type}, {content}, {effect}, {reward})"""
cursor.execute(sql_create_card)
connection.commit()
except:
connection.rollback()
card = Card()
card.name = name
card.type = type
card.content = content
card.effect = effect
card.reward = reward
return card
def ReturnBook(st,titleD,rating,ID):
with connection.cursor() as cursor:
query5 = "SELECT COUNT(*) FROM books WHERE title=%s AND statut ='non disponible' AND emprunté_par=%s"
cursor.execute(query5,(titleD,ID))
result = cursor.fetchall()
count=result[0][0]
if count==0:
return st.warning("ERROR ! Either the given title does not exist or the username of the one who lended the book does not match the given one !")
else :
with connection.cursor() as cursor:
UpdateStatus = "UPDATE books SET statut='dispo' , lend_date=%s ,emprunté_par=%s WHERE title =%s"
cursor.execute(UpdateStatus,(None, None ,titleD))
connection.commit()
query6= "UPDATE infos SET rating=%s WHERE title=%s"
cursor.execute(query6,(rating,titleD))
connection.commit()
return st.success("Book returned SUCCESSFULLY !")
def AddBook(st,titleA,writer,ID,Email,description,Category):
query1 = ("""INSERT INTO Books(title , Author, statut , déposé_par , emprunté_par, category,add_date) VALUES (%s, %s, %s, %s, %s, %s,date(now()))""")
query2 = ("""INSERT INTO Infos(title , Email, b_description , Rating) VALUES (%s, %s, %s, %s)""")
with connection.cursor() as cursor:
cursor.execute(query1,(titleA, writer, 'dispo' , ID , None ,Category))
cursor.execute(query2,(titleA, Email, description, 0 ))
connection.commit()
st.success("Book added SUCCESSFULLY !")
def expload(st):
query = ("SELECT title FROM pdfs")
st.write("list of books")
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()
df = pd.DataFrame(result)
df = df.rename(columns = {0: 'title'})
return st.table(df)
def draw(self):
with connection:
with connection.cursor() as cursor:
cursor.execute(database.DRAW_A_CARD, (self.id, ))
_id, suit, value, deck = cursor.fetchone()
cursor.execute(database.MARK_A_CARD_DRAWN,
(datetime.datetime.timestamp(
datetime.datetime.now()), _id))
return Card(suit, value)
def RemoveBook(st, titleB,ID):
with connection.cursor() as cursor:
queryA='SELECT COUNT(*) from books WHERE title=%s AND déposé_par=%s '
cursor.execute(queryA,(titleB,ID))
result = cursor.fetchall()
count=result[0][0]
if count== 0:
return st.warning("Either the given name does not exist Or the ID of the person who added this book doesn't match the given one, note that only the one who added something can delete it")
else:
with connection.cursor() as cursor:
queryB ='DELETE FROM books WHERE title=%s '
cursor.execute(queryB,(titleB,))
queryC='DELETE FROM infos WHERE title=%s '
cursor.execute(queryC,(titleB,))
connection.commit()
return st.success("Book removed SUCCESSFULLY !")
def LendBook(st, titleC, ID):
with connection.cursor() as cursor:
query4 = 'SELECT COUNT(*) FROM books WHERE title = "{}" AND statut = "dispo"'.format(
titleC)
cursor.execute(query4)
result = cursor.fetchall()
count = result[0]["COUNT(*)"]
if count == 0:
return st.warning(
"Error! Either the given title does not exist or the wanted book is not available now !"
)
else:
with connection.cursor() as cursor:
UpdateStatut = ' UPDATE books SET statut = "non disponible" , emprunté_par="{}" WHERE title ="{}" '.format(
ID, titleC)
cursor.execute(UpdateStatut)
connection.commit()
return st.success("Book Lended SUCCESSFULLY !")
def viewFeedbacks(st, titleF):
with connection.cursor() as cursor:
query = 'SELECT * FROM infos where title ="{}"'.format(titleF)
cursor.execute(query)
result = cursor.fetchall()
if result:
df = pd.DataFrame(result)
return st.table(df)
else:
return st.warning('this title does not exist')
def viewFeedbacks(st,titleF):
with connection.cursor() as cursor:
query = 'SELECT * FROM infos where title =%s'
cursor.execute(query,(titleF,))
result = cursor.fetchall()
if result :
df = pd.DataFrame(result)
df = df.rename(columns = {0: 'title', 1: 'email' , 2: 'b_Description' , 3:'rating'})
return st.table(df)
else:
return st.warning('this title does not exist')
def save(self):
"""Raises EmailAlreadyRegistered exception if it is so."""
if not self.id: # Insert a new one in to the database.
with connection:
with connection.cursor() as cursor:
try:
cursor.execute(database.CREATE_NEW_USER,
(self.email, self.password))
self.id = cursor.fetchone()[0]
except psycopg2.errors.UniqueViolation: # TODO: UPDATE instead. Or is it better?
raise EmailAlreadyRegistered(
"Try another email address.")
def new_game(cls, user: User):
deck = Deck()
player_hand = Hand(user)
computer_hand = Hand(User.get_user_by_id(1)) # 1 is the ID of the computer player.
timestamp = datetime.timestamp(datetime.now())
with connection:
with connection.cursor() as cursor:
# 1 here and at the return statement is turn_indicator. 1 Means player's turn.
cursor.execute(database.INSERT_NEW_GAME_TABLE, (timestamp, computer_hand.id, player_hand.id,
constants.PLAYER_TURN_INDICATOR, deck.id, user.id))
id_ = cursor.fetchone()[0]
return GameTable(deck, user, player_hand, computer_hand, id_, constants.PLAYER_TURN_INDICATOR)
def get_user_by_id(cls, id_):
with connection:
with connection.cursor() as cursor:
cursor.execute(database.GET_USER_BY_ID, (id_, ))
try:
id_, email, pw_from_db = cursor.fetchone()
except TypeError:
id_ = None
if id_:
return cls(email, pw_from_db, id_)
else:
raise UserNotFound("User not found.")
def setPost(self, id, post):
with connection.cursor() as cursor:
try:
cursor.execute(
'INSERT INTO post (user_id,post) VALUES (%s,%s)',
(id, post))
connection.commit()
except Exception as e:
print(str(e))
else:
print('Posted Successfully')
def ReturnBook(st, titleD, rating, ID):
with connection.cursor() as cursor:
query5 = 'SELECT COUNT(*) FROM books WHERE title ="{}" AND statut = "non disponible" AND emprunté_par="{}"'.format(
titleD, ID)
cursor.execute(query5)
result = cursor.fetchall()
count = result[0]["COUNT(*)"]
if count == 0:
return st.warning(
"ERROR ! Either the given title does not exist or the username of the one who lended the book does not match the given one !"
)
else:
with connection.cursor() as cursor:
UpdateStatus = ' UPDATE books SET statut = "dispo" , emprunté_par="NULL" WHERE title = "{}"'.format(
titleD)
cursor.execute(UpdateStatus)
connection.commit()
query6 = 'UPDATE infos SET rating={} WHERE title="{}"'.format(
rating, titleD)
cursor.execute(query6)
connection.commit()
return st.success("Book returned SUCCESSFULLY !")
def RemoveBook(st, titleB, ID):
with connection.cursor() as cursor:
queryA = 'SELECT COUNT(*) from books where (`books`.`title`="{}" AND `books`.`déposé_par`="{}")'.format(
titleB, ID)
cursor.execute(queryA)
result = cursor.fetchall()
count = result[0]["COUNT(*)"]
if count == 0:
return st.warning(
"Either the given name does not exist Or the ID of the person who added this book doesn't match the given one, note that only the one who added something can delete it"
)
else:
with connection.cursor() as cursor:
queryB = 'DELETE FROM `books` WHERE `books`.`title`="{}" '.format(
titleB)
cursor.execute(queryB)
queryC = 'DELETE FROM `infos` WHERE `infos`.`title` ="{}" '.format(
titleB)
cursor.execute(queryC)
connection.commit()
return st.success("Book removed SUCCESSFULLY !")
def load(cls, id):
"""id in the hands table."""
with connection:
with connection.cursor() as cursor:
cursor.execute(database.GET_HAND_BY_ID, (id, ))
(suit1, value1, suit2, value2, suit3, value3, suit4, value4,
suit5, value5, user_id, time_modified) = cursor.fetchone()
cards = []
for suit, value in Hand.pairwise(
(suit1, value1, suit2, value2, suit3, value3, suit4,
value4, suit5, value5)):
if suit and value:
cards.append(Card(suit, value))
return cls(User.get_user_by_id(user_id), *cards)
def read_drinksdb():
connect()
from database import connection
global drinks
drinks = []
cursor_drinks = connection.cursor()
cursor_drinks.execute("SELECT name, category, volume, price FROM drink")
rowsd = cursor_drinks.fetchall()
for row in rowsd:
drink = Drinks(row[0], row[1], row[2], row[3])
drinks.append(drink)
cursor_drinks.close()
connection.close()
def read_peopledb():
connect()
from database import connection
global rowsp, people
people = []
cursor_people = connection.cursor()
cursor_people.execute("SELECT first_name, last_name, age FROM person")
rowsp = cursor_people.fetchall()
for row in rowsp:
person = Person(row[0], row[1], row[2])
people.append(person)
cursor_people.close()
connection.close()
def save(self):
hand = tuple([
element for tpl in self.cards for element in (tpl.suit, tpl.value)
])
hand += (None, ) * (10 - len(hand))
hand += (datetime.datetime.timestamp(datetime.datetime.now()), )
with connection:
with connection.cursor() as cursor:
if not self.id:
# Omits self.id. Inserts link to the owner of the hand.
cursor.execute(database.INSERT_INTO_HANDS,
hand + (self.user.id, ))
self.id = cursor.fetchone()[0]
else:
# Owner already exists on the table.
cursor.execute(database.UPDATE_HAND_TO_HANDS,
hand + (self.id, ))
def enterval():
connect()
from database import connection
value_first_name = new_first_name.get()
value_surname = new_surname.get()
value_age = new_age.get()
cursor_people = connection.cursor()
sql_person_command = "INSERT INTO person (first_name, last_name, age) VALUES (%s, %s, %s)"
val_person = (value_first_name, value_surname, value_age)
cursor_people.execute(sql_person_command, val_person)
connection.commit()
cursor_people.close()
connection.close()
new.destroy()
def getUser(self, email):
id = -1
try:
with connection.cursor() as cursor:
cursor.execute("Select id from users where email = '" + email +
"'")
result = cursor.fetchall()
for row in result:
id = row[0]
print(str(id))
except Exception as e:
print("User Not Found " + str(e))
return id
else:
return id
def setUser(self, email, first_name, last_name):
self.email = email
self.first_name = first_name
self.last_name = last_name
try:
with connection.cursor() as cursor:
cursor.execute(
'INSERT INTO users (email,first_name,last_name) VALUES (%s, %s, %s )',
(self.email, self.first_name, self.last_name))
connection.commit()
except Exception as e:
print(
"This User Is Already Exist\nEnter Again\n--------------------"
)
return True
else:
print('User Created Successfully')
return False
def enterval():
connect()
from database import connection
value_drink = new_drink.get()
value_container = new_category.get()
value_volume = new_volume.get()
value_price = new_price.get()
cursor_drinks = connection.cursor()
sql_drink = "INSERT INTO drink (name, category, volume, price) VALUES (%s, %s, %s, %s)"
val_drink = (value_drink, value_container, value_volume, value_price)
cursor_drinks.execute(sql_drink, val_drink)
connection.commit()
cursor_drinks.close()
connection.close()
new.destroy()
def getAnotherUserPost(self, id):
with connection.cursor() as cursor:
cursor.execute(
"SELECT u.first_name, u.last_name, p.post FROM users u INNER JOIN post p "
"ON u.id = p.user_id WHERE u.id != " + str(id))
result = cursor.fetchall()
data = list()
for row in result:
user_name = row[0]
user_last_name = row[1]
user_post = row[2]
data.append({
'first_name': user_name,
'last_name': user_last_name,
'post': user_post
})
return data
def viewbooks(st, connection):
with connection.cursor() as cursor:
choix = st.radio("choose an option : SHOW : ",
("ALL details", "authors", "titles"))
if choix == "ALL details":
sql = 'SELECT * FROM books'
cursor.execute(sql)
result = cursor.fetchall()
df = pd.DataFrame(result)
# options supplémentaires :
elif choix == "authors":
rq1 = 'SELECT DISTINCT author FROM books'
cursor.execute(rq1)
result = cursor.fetchall()
df = pd.DataFrame(result)
elif choix == "titles":
rq2 = 'SELECT DISTINCT title FROM books'
cursor.execute(rq2)
result = cursor.fetchall()
df = pd.DataFrame(result)
return st.table(df)
from database import connection
from database import user
from database import word
from sqlite3 import OperationalError
cursor = connection.cursor()
# Hash passwords
try:
cursor.execute('''ALTER TABLE users ADD COLUMN salt TEXT NULL''')
connection.commit()
except OperationalError as e:
if e.args != ('duplicate column name: salt',):
raise
else:
results = cursor.execute('''SELECT username,password,salt FROM users''').fetchall()
for u in results:
if u[2] is None:
uo = user.User.from_username(u[0])
print(uo.username + '\'s password is not hashed (detected NULL salt). Performing hash on password...')
uo.update(u[1])
print('Successfully hashed ' + uo.username + '\'s password.')
try:
cursor.execute('''ALTER TABLE votes ADD COLUMN storyID INTEGER NULL''')
connection.commit()
except OperationalError as e:
if e.args != ('duplicate column name: storyID',):
raise
else:
cursor.execute('''ALTER TABLE votes RENAME TO votes_old''')
