def seed_database(db_name): con, cur = conn.open_connection(db_name) cur.execute("""CREATE TABLE accounts ( acct_id INTEGER PRIMARY KEY AUTOINCREMENT, initial_limit NUMERIC(18,6), current_limit NUMERIC(18,6), created_on REAL DEFAULT (datetime('now', 'localtime')) ); """) cur.execute("""CREATE TABLE trans ( trans_id INTEGER PRIMARY KEY AUTOINCREMENT, acct_id INTEGER, trans_type VARCHAR(50), trans_amt NUMERIC(18,6), created_on REAL DEFAULT (datetime('now', 'localtime')) ); """) cur.execute("""CREATE TABLE holds ( hold_id INTEGER PRIMARY KEY AUTOINCREMENT, acct_id INTEGER, vendor_id INTEGER, hold_amt NUMERIC(18,6), created_on REAL DEFAULT (datetime('now', 'localtime')) ); """) # cur_time = time.time() sql = "INSERT INTO accounts ( initial_limit, current_limit ) " \ + "VALUES ( 100000, 100000 );" cur.execute(sql) con.commit() conn.close_connection(con, cur)
def create_account(initial_balance, db_name): """ :param initial_balance: Initial balance for this account :param db_name: Database name (path) :return: -1 on failure or new account number """ # # For a given initial balance create a new account # try: # Get connection and cursor to database con, cur = conn.open_connection(db_name) # Build sql to insert this user sql = 'INSERT INTO accounts ( initial_limit, current_limit ) VALUES ( {0}, {0} );'.format( initial_balance) # Insert into database and commit cur.execute(sql) # New account number = MAX(accounts.acct_id) new_acct_id = get_last_account_id(cur) insert_trans(cur, new_acct_id, 'create', initial_balance) # Commit all database transactions and close database connection con.commit() conn.close_connection(con, cur) except Exception as e: print(e) new_acct_id = -1 return new_acct_id
def new(name): cursor = connection.get_cursor() query = """ INSERT INTO tag(name) VALUES ('{name}') """.format( name=name ) cursor.execute(query) connection.close_connection(cursor)
def clear(): try: connection = con.get_connection() cursor = connection.cursor() query = """DELETE FROM nevera_temp""" cursor.execute(query) connection.commit() con.close_connection(connection) except (Exception, psycopg2.Error) as error: print("Error while clearing", error)
def delete(id: int): cursor = connection.get_cursor() query = """ DELETE FROM question WHERE id={id} """.format( id=id ) cursor.execute(query) connection.close_connection(cursor)
def remove(question_id: int, tag_id: int): cursor = connection.get_cursor() query = """ DELETE FROM question_tag WHERE question_id={question} AND tag_id={tag} """.format( question=question_id, tag=tag_id ) cursor.execute(query) connection.close_connection(cursor)
def update(id: int, message=None, edited_count: int = None): cursor = connection.get_cursor() query = """ UPDATE comment SET {message}{edited} WHERE id={id} """.format( id=id, message=f"message='{message}'{',' if edited_count != None else ''}" if message else '', edited=f"edited_count={edited_count}" if edited_count is not None else '' ) cursor.execute(query) connection.close_connection(cursor)
def update(id: int, vote=None, message=None, image=None): cursor = connection.get_cursor() query = """ UPDATE answer SET {vote}{message}{image} WHERE id={id} """.format( id=id, vote=f"vote_number={vote}{',' if message or image else ''}" if vote is not None else '', message=f"message='{message}'{',' if image else ''}" if message else '', image=f"image='{image}'" if image else '' ) cursor.execute(query) connection.close_connection(cursor)
def get(question_id: int = None): cursor = connection.get_cursor() query = """ SELECT {elements} FROM tag {condition} """.format( elements=f"{'id, name' if question_id != None else '*'}", condition=f"WHERE id IN (SELECT tag_id FROM question_tag WHERE question_id={question_id})" if question_id is not None else '' ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data
def get_last(): try: connection = con.get_connection() cursor = connection.cursor() query = """SELECT minute FROM people_count ORDER BY ID DESC LIMIT 1;""" cursor.execute(query) minuto = cursor.fetchone() elmin = 0 con.close_connection(connection) print("MINUTO ", minuto[0]) return minuto[0] except (Exception, psycopg2.Error) as error: print("Error while clearing", error)
def insert(value): try: connection = con.get_connection() cursor = connection.cursor() insert_query = """INSERT INTO olla_temp (temp) VALUES (%s)""" cursor.execute(insert_query, (value, )) connection.commit() cursor.execute("SELECT * FROM olla_temp ORDER BY ID DESC LIMIT 1") res = cursor.fetchone() print('se inserto: ', res) con.close_connection(connection) except (Exception, psycopg2.Error) as error: print("Error while getting data", error)
def update(id: int, view=None, vote=None, title=None, message=None, image=None): cursor = connection.get_cursor() query = """ UPDATE question SET {view}{vote}{title}{message}{image} WHERE id={id} """.format( id=id, view=f"view_number={view}{',' if vote != None or title or message or image else ''}" if view is not None else '', vote=f"vote_number={vote}{',' if title or message or image else ''}" if vote is not None else '', title=f"title='{title}'{',' if message or image else ''}" if title else '', message=f"message='{message}'{',' if image else ''}" if message else '', image=f"image='{image}'" if image else '' ) cursor.execute(query) connection.close_connection(cursor)
def get(key=None, value=None): if key in ('id', 'question_id', 'answer_id', 'edited_count'): condition = f"{key}={value}" else: condition = f"UPPER({key}) LIKE UPPER('%{value}%')" cursor = connection.get_cursor() query = """ SELECT * FROM comment {condition} """.format( condition=f"WHERE {condition}" if key else "" ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data[0] if key == 'id' else data
def add(title, message, image): cursor = connection.get_cursor() cursor.execute("SELECT nextval('question_id_seq')") id = cursor.fetchall()[0]['nextval'] query = """ INSERT INTO question(id, submission_time, view_number, vote_number, title, message, image) VALUES ({id}, '{time}', 0, 0, '{title}', '{message}', '{image}') """.format( id=id, time=datetime.now().strftime("%Y-%m-%d %H:%M:%S"), title=title, message=message, image=f"question_{id}.{image}" if image else '' ) cursor.execute(query) connection.close_connection(cursor) return id
def insert(value): try: minute = get_last() connection = con.get_connection() cursor = connection.cursor() insert_query = """INSERT INTO people_count (count, minute) VALUES (%s, %s)""" cursor.execute(insert_query, ( value, minute + 1, )) connection.commit() cursor.execute("SELECT * FROM people_count ORDER BY ID DESC LIMIT 1") res = cursor.fetchone() print('se inserto: ', res) con.close_connection(connection) except (Exception, psycopg2.Error) as error: print("Error while getting data", error)
def add(message, question_id: int = None, answer_id: int = None): cursor = connection.get_cursor() cursor.execute("SELECT nextval('comment_id_seq')") id = cursor.fetchall()[0]['nextval'] query = """ INSERT INTO comment(id{id_for}, message, submission_time, edited_count) VALUES ({id}, {question}{answer}, '{message}', '{time}', 0) """.format( id=id, id_for=f", {'question_id' if question_id != None else 'answer_id' if answer_id != None else ''}", question=question_id if question_id is not None else '', answer=answer_id if answer_id is not None else '', message=message, time=datetime.now().strftime("%Y-%m-%d %H:%M:%S") ) cursor.execute(query) connection.close_connection(cursor) return id
def insert(value): try: time = datetime.now() connection = con.get_connection() cursor = connection.cursor() insert_query = """INSERT INTO tank (water, time) VALUES (%s, %s)""" cursor.execute(insert_query, ( value, time + timedelta(minutes=10), )) connection.commit() cursor.execute("SELECT * FROM tank ORDER BY ID DESC LIMIT 1") res = cursor.fetchone() print('se inserto: ', res) con.close_connection(connection) except (Exception, psycopg2.Error) as error: print("Error while getting data", error)
def get(key=None, value=None): # if key is not None return the answers with key == value if key in ('id', 'vote_number', 'question_id'): condition = f"{key}={value}" else: condition = f"UPPER({key}) LIKE UPPER('%{value}%')" cursor = connection.get_cursor() query = """ SELECT * FROM answer {condition} """.format( condition=f"WHERE {condition}" if key else "" ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data[0] if key == 'id' else data
def add(question_id: int, tag_id: int): try: cursor = connection.get_cursor() query = """ INSERT INTO question_tag(question_id, tag_id) VALUES ({question}, {tag}) """.format( question=question_id, tag=tag_id ) cursor.execute(query) connection.close_connection(cursor) except IndexError: pass except KeyError: pass except ValueError: pass except IndentationError: pass
def hold(acct_id, vendor_id, amt, db_name): """ :param acct_id: Account id :param vendor_id: Vendor id :param amt: Hold amount :param db_name: Database name (path) :return: True on success """ try: # Get connection and cursor to database con, cur = conn.open_connection(db_name) # Only 1 hold allowed per vendor id per account id sql = 'SELECT COUNT(*) FROM holds WHERE acct_id = {0} AND vendor_id = {1}'.\ format(acct_id, vendor_id) cur.execute(sql) rows = cur.fetchall() num_holds = rows[0][0] if num_holds > 0: return -3 sql = 'INSERT INTO holds (acct_id, vendor_id, hold_amt) VALUES'\ '( {0}, {1}, {2} );'.format(acct_id, vendor_id, amt) # Insert hold into database cur.execute(sql) # Place a hold on this account for the amount specified ret_sts = debit_acct(cur, acct_id, amt, 'hold') if ret_sts < 0: return ret_sts # Commit all database transactions and close database connection con.commit() conn.close_connection(con, cur) return ret_sts except Exception as e: print(e) return -1
def charge(acct_id, amt, db_name): """ :param acct_id: Account number to charge :param amt: Amount to charge :param db_name: Database name (path) :return: 0 = Success -1 = Charge failed (database error) -2 = Insufficient funds """ try: # Get connection and cursor to database con, cur = conn.open_connection(db_name) ret_sts = debit_acct(cur, acct_id, amt, 'charge') # Commit all database transactions and close database connection con.commit() conn.close_connection(con, cur) return ret_sts except Exception as e: print(e) return -1