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 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 book_update(id):
#Update a book
sql = "UPDATE books SET title = %s WHERE id = %s;"
cursor.execute(sql, (request.json["title"], id))
connection.commit()
sql = "SELECT * FROM books WHERE id = %s"
cursor.execute(sql, (id,))
book = cursor.fetchone()
return jsonify(book)
def book_create():
#Create a new book
sql = "INSERT INTO books (title) VALUES (%s);"
cursor.execute(sql, (request.json["title"],))
connection.commit()
sql = "SELECT * FROM books ORDER BY ID DESC LIMIT 1"
cursor.execute(sql)
book = cursor.fetchone()
return jsonify(book)
def book_delete(id):
sql = "SELECT * FROM books WHERE id = %s;"
cursor.execute(sql, (id,))
book = cursor.fetchone()
if book:
sql = "DELETE FROM books WHERE id = %s;"
cursor.execute(sql, (id,))
connection.commit()
return jsonify(book)
def insert(self):
try:
cursor.execute(
"INSERT INTO users (name, email, password) VALUES(%s, %s, %s) RETURNING id",
(self.name, self.email, self.__password))
connection.commit()
self.id = cursor.fetchone()[0]
return True
except (Exception, psycopg2.Error) as error:
connection.rollback()
print("Error while connecting to PostgreSQL", error)
return False
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 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 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 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 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 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 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 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 getPlayer(mlb_id):
db.execute("SELECT * FROM players WHERE mlb_id = ?", (mlb_id,))
row = db.fetchone()
if row is None:
# Let's go find this player! First we need to find out what team they're on, and what that team's last game_id was
try:
file = urllib2.urlopen("http://gd2.mlb.com/components/game/mlb/year_2012/batters/%s.xml" % mlb_id).read()
except urllib2.HTTPError:
return False
dom = minidom.parseString(file)
game_id = dom.getElementsByTagName("batting")[0].getAttribute("game_id")
year, month, day, meh = game_id.split("/")
game_id = game_id.replace("-", "_").replace("/", "_")
try:
file = urllib2.urlopen(
"http://gd2.mlb.com/components/game/mlb/year_%s/month_%s/day_%s/gid_%s/batters/%s.xml"
% (year, month, day, game_id, mlb_id)
).read()
except urllib2.HTTPError:
return False
player = minidom.parseString(file).getElementsByTagName("Player")[0]
db.execute(
"INSERT OR IGNORE INTO players (mlb_id, first, last, num, team) VALUES (?, ?, ?, ?, ?)",
(
mlb_id,
player.getAttribute("first_name"),
player.getAttribute("last_name"),
player.getAttribute("jersey_number"),
getTeam(player.getAttribute("team")).code,
),
)
connection.commit()
return getPlayer(mlb_id)
return Player(*row)
def SignUp(st):
st.sidebar.subheader(" Create A New Account ")
new_user = st.sidebar.text_input('Username')
ID = st.sidebar.number_input(label="Enter Student ID", step=1, value=0)
new_passwd = st.sidebar.text_input('Password', type='password')
if st.sidebar.button('SignUp'):
query = "SELECT COUNT(*) FROM Users WHERE ID =%s "
c.execute(query, (ID, ))
result = c.fetchall()
count = result[0][0]
if count == 0:
query = 'INSERT INTO Users(user_name ,ID, passwd) VALUES (%s, %s, %s)'
c.execute(query, (new_user, ID, make_hashes(new_passwd)))
connection.commit()
st.success("You have successfully created an account.")
st.info("Go to the SignIn Menu to login")
else:
return st.error("This ID is already used !")
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 create_obj(self, **kwargs):
for key in self._columns.keys():
if isinstance(self._columns[key], PrimaryKey):
if key in kwargs:
raise Exception('You cannot pass a primary key to the table!')
continue
if key not in kwargs:
raise MissingColumnError(f'Your {key} column is missing!')
if len(kwargs.keys()) >= len(self._columns.keys()):
raise ExtraColumnsError()
# save the given obj values
for key, value in kwargs.items():
self._columns[key].save_value(value)
valid_keys = ', '.join(key for key, val in self._columns.items() if not isinstance(val, PrimaryKey))
valid_values = ', '.join(convert_to_sql_string(val.value) for val in self._columns.values() if not isinstance(val, PrimaryKey))
insert_sql = f"""INSERT INTO {self.__tablename__}\t({valid_keys}) VALUES ({valid_values});"""
cursor.execute(insert_sql)
connection.commit()
def SignUp(st):
st.sidebar.subheader(" Create A New Account ")
new_user = st.sidebar.text_input('Username')
ID = st.sidebar.number_input(label="Enter Student ID", step=1, value=0)
new_passwd = st.sidebar.text_input('Password', type='password')
if st.sidebar.button('SignUp'):
query = 'SELECT COUNT(*) FROM Users WHERE ID ="{}"'.format(ID)
c.execute(query)
result = c.fetchall()
count = result[0]["COUNT(*)"]
if count == 0:
# encrypt or hash the passwd to make it more secure
# + store the hashed/encrypted password to the database not the plaintext password
query = 'INSERT INTO Users(user ,ID, passwd) VALUES (%s, %s, %s)'
c.execute(query, (new_user, ID, make_hashes(new_passwd)))
connection.commit()
st.success("You have successfully created an account.")
st.info("Go to the SignIn Menu to login")
else:
return st.error("This ID is already used !")
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 rate(self, q, user):
if self.rating == q:
return False
dt = datetime.datetime.now()
try:
if self.id:
cursor.execute(
"UPDATE calificaciones SET calificacion = %s, updated_at = %s WHERE id = %s",
(q, dt, self.id))
connection.commit()
else:
cursor.execute(
"INSERT INTO calificaciones (user_id, impresora_id, calificacion, created_at, updated_at) VALUES(%s, %s, %s, %s, %s) RETURNING id",
(user.id, self.printer_id, q, dt, dt))
connection.commit()
self.id = cursor.fetchone()[0]
return True
except (Exception, psycopg2.Error) as error:
connection.rollback()
print("Error while connecting to PostgreSQL", error)
return False
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''')
def upload(st,title,link):
query = ("""INSERT INTO pdfs (title , uploaded_file) VALUES (%s, %s)""")
with connection.cursor() as cursor:
cursor.execute(query,(title,link))
connection.commit()
st.success("Book added SUCCESSFULLY !")
def create_tables(cls):
for table in cls.__tables__:
table.create_table(table)
connection.commit()
