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
