backend/db_handler.py at master · hack2022team/backend · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
import sqlite3
import pandas as pd

def write_to_database(info, escrow_wallet, appID):
    conn = sqlite3.connect('loans.sqlite')
    # conn.execute('''
    # create table if not exists loans
    # (
    #     id            INTEGER
    #         primary key autoincrement
    #         unique,
    #     name          TEXT    not null,
    #     email         TEXT    not null,
    #     story         TEXT    not null,
    #     location      TEXT    not null,
    #     wallet        TEXT    not null,
    #     escrow_wallet TEXT    not null,
    #     max_fees      INTEGER not null,
    #     sum           INTEGER not null,
    #     photo         BLOB    not null,
    #     risk          TEXT    not null,
    #     loan_duration INTEGER not null
    # )''')
    cursor = conn.cursor()
    sqlite_insert_with_param = """INSERT INTO loans
                      (name, email, story, location, wallet, escrow_wallet, max_fees, sum, photo, risk, loan_duration, status, appid)
                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"""

    data_tuple = (info['inputName'], info['inputEmail'], info['inputStory'], info['inputLocation'], info['inputWallet'], escrow_wallet,
                  info['inputMaxFees'], info['inputSum'], "", info['inputRisk'], info['inputDuration'], "OPEN", appID)
    cursor.execute(sqlite_insert_with_param, data_tuple)
    conn.commit()
    conn.close()

def get_open_loans():
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT * from loans where status='OPEN'", conn)
    return open_loans.to_dict(orient="records")


def get_giver_accounts():
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT * from givers", conn)
    return open_loans.to_dict(orient="records")


def get_receiver_accounts():
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT * from receivers", conn)
    return open_loans.to_dict(orient="records")


def get_giver_key(key):
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT private_key from givers WHERE public_key='" + key +"'", conn)
    return open_loans.to_dict(orient="records")[0]['private_key']

def get_receiver_key(key):
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT private_key from receivers WHERE public_key='" + key +"'", conn)
    return open_loans.to_dict(orient="records")[0]['private_key']

def get_appid(key):
    conn = sqlite3.connect('loans.sqlite')
    open_loans = pd.read_sql_query("SELECT appid from loans WHERE escrow_wallet='" + key+"'", conn)
    return open_loans.to_dict(orient="records")[0]['appid']