Skip to content

Commit b050e33

Browse files
author
Kepka
committed
rolling back
1 parent fa99235 commit b050e33

File tree

7 files changed

+277
-0
lines changed

7 files changed

+277
-0
lines changed

‎RollingBack/accounts.sqlite‎

20 KB
Binary file not shown.

‎RollingBack/checkdb.py‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
import sqlite3
2+
import pytz
3+
4+
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
5+
6+
for row in db.execute("SELECT * FROM history"):
7+
utc_time = row[0]
8+
local_time = pytz.utc.localize(utc_time).astimezone()
9+
print(f"{utc_time},\t {local_time}")
10+
11+
db.close()

‎RollingBack/checkdb1.py‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
import sqlite3
2+
3+
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
4+
5+
# for row in db.execute("SELECT strftime('%Y-%m-%d %H:%M:%f', history.time, 'localtime') as localtime, history.account,"
6+
# "history.amount FROM history ORDER BY history.time"):
7+
for row in db.execute("SELECT * FROM localhistory"):
8+
print(row)
9+
10+
db.close()

‎RollingBack/rollback2.py‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
from decimal import *
2+
3+
4+
class Account(object):
5+
_qb = Decimal('0.00') # class constant, accessible without creating an instance.
6+
7+
def __init__(self, name: str, opening_balance: float = 0.0):
8+
self.name = name
9+
self._balance = Decimal(opening_balance).quantize(Account._qb)
10+
print("Account created for {}. ".format(self.name), end='')
11+
self.show_balance()
12+
13+
def deposit(self, amount: float) -> Decimal:
14+
decimal_amount = Decimal(amount).quantize(Account._qb)
15+
if decimal_amount > Account._qb:
16+
self._balance = self._balance + decimal_amount
17+
print("{} deposited".format(decimal_amount))
18+
return self._balance
19+
20+
def withdraw(self, amount: float) -> Decimal:
21+
decimal_amount = Decimal(amount).quantize(Account._qb)
22+
if Account._qb < decimal_amount <= self._balance:
23+
self._balance = self._balance - decimal_amount
24+
print("{} withdrawn".format(decimal_amount))
25+
return decimal_amount
26+
else:
27+
print("The amount must be greater than zero and no more than your account balance")
28+
return Account._qb
29+
30+
def show_balance(self):
31+
print("Balance on account {} is {}".format(self.name, self._balance))
32+
33+
34+
if __name__ == '__main__':
35+
tim = Account("Tim")
36+
tim.deposit(10.1)
37+
tim.deposit(0.1)
38+
tim.deposit(0.1)
39+
tim.withdraw(0.3)
40+
tim.withdraw(0)
41+
tim.show_balance()
42+
43+
print("=" * 80)
44+
x = tim.withdraw(900)
45+
print(x)

‎RollingBack/rollingback.py‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
import sqlite3
2+
import datetime
3+
import pytz
4+
5+
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
6+
db.execute("CREATE TABLE IF NOT EXISTS accounts (name TEXT PRIMARY KEY NOT NULL, balance INTEGER NOT NULL)")
7+
db.execute("CREATE TABLE IF NOT EXISTS history (time TIMESTAMP NOT NULL,"
8+
" account TEXT NOT NULL, amount INTEGER NOT NULL, PRIMARY KEY (time, account))")
9+
db.execute("CREATE VIEW IF NOT EXISTS localhistory AS"
10+
" SELECT strftime('%Y-%m-%d %H:%M:%f', history.time, 'localtime') as localtime, history.account,"
11+
" history.amount FROM history ORDER BY history.time")
12+
13+
14+
class Account(object):
15+
16+
@staticmethod
17+
def _current_time():
18+
return pytz.utc.localize(datetime.datetime.utcnow())
19+
# local_time = pytz.utc.localize(datetime.datetime.utcnow())
20+
# return local_time.astimezone()
21+
# return 1
22+
23+
def __init__(self, name: str, opening_balance: int = 0):
24+
cursor = db.execute("SELECT name, balance FROM accounts WHERE (name = ?)", (name,))
25+
row = cursor.fetchone()
26+
27+
if row:
28+
self.name, self._balance = row
29+
print(f"Retrieved record for {self.name}", end='')
30+
else:
31+
self.name = name
32+
self._balance = opening_balance
33+
cursor.execute("INSERT INTO accounts VALUES(?, ?)", (name, opening_balance))
34+
cursor.connection.commit()
35+
print(f"Account created for {name}", end='')
36+
self.show_balance()
37+
38+
def _save_update(self, amount):
39+
new_balance = self._balance + amount
40+
deposit_time = Account._current_time()
41+
try:
42+
db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
43+
db.execute("INSERT INTO history VALUES(?, ?, ?)", (deposit_time, self.name, amount))
44+
except sqlite3.Error:
45+
db.rollback()
46+
else:
47+
db.commit()
48+
self._balance = new_balance
49+
50+
def deposit(self, amount: int) -> float:
51+
if amount > 0.0:
52+
# new_balance = self._balance + amount
53+
# deposit_time = Account._current_time()
54+
# db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
55+
# db.execute("INSERT INTO history VALUES(?, ?, ?)", (deposit_time, self.name, amount))
56+
# db.commit()
57+
# self._balance = new_balance
58+
self._save_update(amount)
59+
print(f"{amount / 100:.2f} deposited")
60+
return self._balance / 100
61+
62+
def withdraw(self, amount: int) -> float:
63+
if 0 < amount <= self._balance:
64+
# new_balance = self._balance - amount
65+
# withdrawal_time = Account._current_time()
66+
# db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
67+
# db.execute("INSERT INTO history VALUES(?, ?, ?)", (withdrawal_time, self.name, -amount))
68+
# db.commit()
69+
# self._balance = new_balance
70+
self._save_update(-amount)
71+
print(f"{amount / 100:.2f} withdrawn")
72+
return amount / 100
73+
else:
74+
print("The amount must be greater than zero and no more than your account balance")
75+
return 0.0
76+
77+
def show_balance(self):
78+
print(f"Balance on account {self.name} is {self._balance / 100:.2f}")
79+
80+
81+
if __name__ == '__main__':
82+
john = Account("John")
83+
john.deposit(1010)
84+
john.deposit(10)
85+
john.deposit(10)
86+
john.withdraw(30)
87+
john.withdraw(0)
88+
john.show_balance()
89+
90+
terry = Account("TerryJ")
91+
graham = Account("Graham", 9000)
92+
eric = Account("Eric", 7000)
93+
michael = Account("Michael")
94+
terryG = Account("TerryG")
95+
# db.execute("DROP TABLE accounts")
96+
# db.execute("DROP TABLE history")
97+
db.close()

‎RollingBack/tzcheck.py‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
import sqlite3
2+
import pytz
3+
import pickle
4+
5+
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
6+
7+
for row in db.execute("SELECT * FROM history"):
8+
utc_time = row[0]
9+
picked_zone = row[3]
10+
zone = pickle.loads(picked_zone)
11+
local_time = pytz.utc.localize(utc_time).astimezone(zone)
12+
print(f"{utc_time}\t{local_time}\t{local_time.tzinfo}")
13+
14+
db.close()

‎RollingBack/tztest.py‎

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
import sqlite3
2+
import datetime
3+
import pytz
4+
import pickle
5+
6+
db = sqlite3.connect("accounts.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)
7+
db.execute("CREATE TABLE IF NOT EXISTS accounts (name TEXT PRIMARY KEY NOT NULL, balance INTEGER NOT NULL)")
8+
db.execute("CREATE TABLE IF NOT EXISTS history (time TIMESTAMP NOT NULL,"
9+
" account TEXT NOT NULL, amount INTEGER NOT NULL,"
10+
" zone INTEGER NOT NULL, PRIMARY KEY (time, account))")
11+
db.execute("CREATE VIEW IF NOT EXISTS localhistory AS"
12+
" SELECT strftime('%Y-%m-%d %H:%M:%f', history.time, 'localtime') as localtime, history.account,"
13+
" history.amount FROM history ORDER BY history.time")
14+
15+
16+
class Account(object):
17+
18+
@staticmethod
19+
def _current_time():
20+
# return pytz.utc.localize(datetime.datetime.utcnow())
21+
# local_time = pytz.utc.localize(datetime.datetime.utcnow())
22+
# return local_time.astimezone()
23+
utc_time = pytz.utc.localize(datetime.datetime.utcnow())
24+
local_time = utc_time.astimezone()
25+
zone = local_time.tzinfo
26+
return utc_time, zone
27+
28+
def __init__(self, name: str, opening_balance: int = 0):
29+
cursor = db.execute("SELECT name, balance FROM accounts WHERE (name = ?)", (name,))
30+
row = cursor.fetchone()
31+
32+
if row:
33+
self.name, self._balance = row
34+
print(f"Retrieved record for {self.name}", end='')
35+
else:
36+
self.name = name
37+
self._balance = opening_balance
38+
cursor.execute("INSERT INTO accounts VALUES(?, ?)", (name, opening_balance))
39+
cursor.connection.commit()
40+
print(f"Account created for {name}", end='')
41+
self.show_balance()
42+
43+
def _save_update(self, amount):
44+
new_balance = self._balance + amount
45+
deposit_time, zone = Account._current_time() # <-- unpacking the return tuple
46+
picked_zone = pickle.dumps(zone)
47+
48+
db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
49+
db.execute("INSERT INTO history VALUES(?, ?, ?, ?)", (deposit_time, self.name, amount, picked_zone))
50+
db.commit()
51+
self._balance = new_balance
52+
53+
def deposit(self, amount: int) -> float:
54+
if amount > 0.0:
55+
# new_balance = self._balance + amount
56+
# deposit_time = Account._current_time()
57+
# db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
58+
# db.execute("INSERT INTO history VALUES(?, ?, ?)", (deposit_time, self.name, amount))
59+
# db.commit()
60+
# self._balance = new_balance
61+
self._save_update(amount)
62+
print(f"{amount / 100:.2f} deposited")
63+
return self._balance / 100
64+
65+
def withdraw(self, amount: int) -> float:
66+
if 0 < amount <= self._balance:
67+
# new_balance = self._balance - amount
68+
# withdrawal_time = Account._current_time()
69+
# db.execute("UPDATE accounts SET balance = ? WHERE (name = ?)", (new_balance, self.name))
70+
# db.execute("INSERT INTO history VALUES(?, ?, ?)", (withdrawal_time, self.name, -amount))
71+
# db.commit()
72+
# self._balance = new_balance
73+
self._save_update(-amount)
74+
print(f"{amount / 100:.2f} withdrawn")
75+
return amount / 100
76+
else:
77+
print("The amount must be greater than zero and no more than your account balance")
78+
return 0.0
79+
80+
def show_balance(self):
81+
print(f"Balance on account {self.name} is {self._balance / 100:.2f}")
82+
83+
84+
if __name__ == '__main__':
85+
john = Account("John")
86+
john.deposit(1010)
87+
john.deposit(10)
88+
john.deposit(10)
89+
john.withdraw(30)
90+
john.withdraw(0)
91+
john.show_balance()
92+
93+
terry = Account("TerryJ")
94+
graham = Account("Graham", 9000)
95+
eric = Account("Eric", 7000)
96+
michael = Account("Michael")
97+
terryG = Account("TerryG")
98+
# db.execute("DROP TABLE accounts")
99+
# db.execute("DROP TABLE history")
100+
db.close()

0 commit comments

Comments
 (0)