import sqlite3
def createDB():
conn = None
try:
conn = sqlite3.connect(‘bank.db’)
cur = conn.cursor()
# Create tables
sqlStatement = ”’ CREATE TABLE IF NOT EXISTS Client(
client_id INTEGER PRIMARY KEY NOT NULL,
name TEXT,
address TEXT,
type TEXT);
CREATE TABLE IF NOT EXISTS Branch(
branch_id INTEGER PRIMARY KEY NOT NULL,
name TEXT,
address TEXT,
manager_id INTEGER);
CREATE TABLE IF NOT EXISTS Account(
account_id INTEGER PRIMARY KEY NOT NULL,
balance REAL,
type TEXT,
branch_id INTEGER,
FOREIGN KEY(branch_id) REFERENCES Branch(branch_id));
CREATE TABLE IF NOT EXISTS ClientAccount(
client_id INTEGER NOT NULL,
account_id INTEGER NOT NULL,
PRIMARY KEY(client_id, account_id),
FOREIGN KEY(account_id) REFERENCES Account(account_id),
FOREIGN KEY(client_id) REFERENCES Client(client_id))
”’
cur.executescript(sqlStatement)
# Commit changes
conn.commit()
except sqlite3.Error:
# Respond to the database exception
print(‘Database error’)
except:
# Respond to the general exception
print(‘Runtime error’)
finally:
if conn is not None:
conn.close()
def insertClient(cid, name, addr, t):
conn = None
try:
conn = sqlite3.connect(‘bank.db’)
cur = conn.cursor()
cur.execute(‘PRAGMA foreign_keys=ON’)
sqlStatement = ”’INSERT INTO Client(client_id, name, address, type)
VALUES(?,?,?,?)
”’
cur.execute(sqlStatement, (cid, name, addr, t))
conn.commit()
except sqlite3.Error:
print(‘Database error’)
raise
except Exception:
print(‘Runtime error’)
finally:
if conn is not None:
conn.close()
def displayClient():
conn = None
try:
conn = sqlite3.connect(‘bank.db’)
cur = conn.cursor()
sqlStatement = ”’SELECT *
FROM Client
”’
cur.execute(sqlStatement)
result = cur.fetchall()
for row in result:
print(f'{row[0]:5} | {row[1]:15} | {row[2]:20} | {row[3]:5}’)
conn.commit()
except sqlite3.Error:
print(‘Database error’)
except Exception:
print(‘Runtime error’)
raise
finally:
if conn is not None:
conn.close()
if __name__ == ‘__main__’:
createDB()