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()