-
Notifications
You must be signed in to change notification settings - Fork 0
/
sqlhelpers.py
176 lines (146 loc) · 6.19 KB
/
sqlhelpers.py
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
from app import mysql, session
from blockchain import Block, Blockchain
# custom exceptions for transaction errors
class InvalidTransactionException(Exception): pass
class InsufficientFundsException(Exception): pass
# what a mysql table looks like. Simplifies access to the database 'crypto'
class Table():
# specify the table name and columns
# EXAMPLE table:
# blockchain
# number hash previous data nonce
# -data- -data- -data- -data- -data-
#
# EXAMPLE initialization: ...Table("blockchain", "number", "hash", "previous", "data", "nonce")
def __init__(self, table_name, *args):
self.table = table_name
self.columns = "(%s)" %",".join(args)
self.columnsList = args
# if table does not already exist, create it.
if isnewtable(table_name):
create_data = ""
for column in self.columnsList:
create_data += "%s varchar(100)," %column
cur = mysql.connection.cursor() #create the table
cur.execute("CREATE TABLE %s(%s)" %(self.table, create_data[:len(create_data)-1]))
cur.close()
# get all the values from the table
def getall(self):
cur = mysql.connection.cursor()
result = cur.execute("SELECT * FROM %s" %self.table)
data = cur.fetchall(); return data
# get one value from the table based on a column's data
# EXAMPLE using blockchain: ...getone("hash","00003f73gh93...")
def getone(self, search, value):
data = {}; cur = mysql.connection.cursor()
result = cur.execute("SELECT * FROM %s WHERE %s = \"%s\"" %(self.table, search, value))
if result > 0: data = cur.fetchone()
cur.close(); return data
# delete a value from the table based on column's data
def deleteone(self, search, value):
cur = mysql.connection.cursor()
cur.execute("DELETE from %s where %s = \"%s\"" %(self.table, search, value))
mysql.connection.commit(); cur.close()
# delete all values from the table.
def deleteall(self):
self.drop() # remove table and recreate
self.__init__(self.table, *self.columnsList)
# remove table from mysql
def drop(self):
cur = mysql.connection.cursor()
cur.execute("DROP TABLE %s" %self.table)
cur.close()
# insert values into the table
def insert(self, *args):
data = ""
for arg in args: # convert data into string mysql format
data += "\"%s\"," %(arg)
cur = mysql.connection.cursor()
cur.execute("INSERT INTO %s%s VALUES(%s)" %(self.table, self.columns, data[:len(data)-1]))
mysql.connection.commit()
cur.close()
# execute mysql code from python
def sql_raw(execution):
cur = mysql.connection.cursor()
cur.execute(execution)
mysql.connection.commit()
cur.close()
# check if table already exists
def isnewtable(tableName):
cur = mysql.connection.cursor()
try: # attempt to get data from table
result = cur.execute("SELECT * from %s" %tableName)
cur.close()
except:
return True
else:
return False
# check if user already exists
def isnewuser(username):
# access the users table and get all values from column "username"
users = Table("users", "name", "email", "username", "password")
data = users.getall()
usernames = [user.get('username') for user in data]
return False if username in usernames else True
# send money from one user to another
def send_money(sender, recipient, amount):
# verify that the amount is an integer or floating value
try: amount = float(amount)
except ValueError:
raise InvalidTransactionException("Invalid Transaction.")
# verify that the user has enough money to send (exception if it is the BANK)
if amount > get_balance(sender) and sender != "BANK":
raise InsufficientFundsException("Insufficient Funds.")
# verify that the user is not sending money to themselves or amount is less than or 0
elif sender == recipient or amount <= 0.00:
raise InvalidTransactionException("Invalid Transaction.")
# verify that the recipient exists
elif isnewuser(recipient):
raise InvalidTransactionException("User Does Not Exist.")
# update the blockchain and sync to mysql
blockchain = get_blockchain()
number = len(blockchain.chain) + 1
data = "%s-->%s-->%s" %(sender, recipient, amount)
blockchain.mine(Block(number, data=data))
sync_blockchain(blockchain)
# get the balance of a user
def get_balance(username):
balance = 0.00
blockchain = get_blockchain()
# loop through the blockchain and update balance
for block in blockchain.chain:
data = block.data.split("-->")
if username == data[0]:
balance -= float(data[2])
elif username == data[1]:
balance += float(data[2])
return balance
# get the blockchain from mysql and convert to Blockchain object
def get_blockchain():
blockchain = Blockchain()
blockchain_sql = Table("blockchain", "number", "hash", "previous", "data", "nonce")
for b in blockchain_sql.getall():
blockchain.add(Block(int(b.get('number')), b.get('previous'), b.get('data'), int(b.get('nonce'))))
return blockchain
# update blockchain in mysql table
def sync_blockchain(blockchain):
blockchain_sql = Table("blockchain", "number", "hash", "previous", "data", "nonce")
blockchain_sql.deleteall()
for block in blockchain.chain:
blockchain_sql.insert(str(block.number), block.hash(), block.previous_hash, block.data, block.nonce)
# Function to empty an account and create a transaction
def empty_account(username):
try:
balance = get_balance(username) # Get the account balance
if balance > 0.00:
# Create a transaction to empty the account
send_money(username, "BANK", balance)
print(f"Account {username} emptied. {balance} tokens transferred to BANK.")
else:
print(f"Account {username} already empty.")
except InvalidTransactionException as e:
print("Error:", e)
except InsufficientFundsException as e:
print("Error:", e)
except Exception as e:
print("Error:", e)