Bank Accounts and Transaction Processing

A MySQL-Python Integrated Implementation

An improved implementation of a project assignment in "CSE 4701: Principles of Databases" offered at the University of Connecticut.

Goal

Create a simplified banking software to practice concepts of embedded SQL and database transactions. Information on bank accounts will be persistently stored in a MySQL database on a local computer. The bank account database will have only one table named account with the following details.

Implementation

The banking software is implemented with a Python program and MySQL databse integration, utilizing the mysql-connector Python package, which provides the communication between Python and MySQL. The Python program provides a graphical user-interface (GUI) implemented using the wxpython GUI toolkit package. Instructions to install the packages on a Windows and macOS machine are provided below.

Package Installation

Install mysql-connector

$ pip install mysql-connector-python

Install wxpython

$ pip install wxPython

MySQL Specifications

Download the open source MySQL Community Server here: https://dev.mysql.com/downloads/mysql/

To ensure the program executes with no errors, make sure that the MySQL user specifications (user name, password, host, database name) in the Python program are correct. These specifications can be found at the top of the program, and look as such:

cnx = mysql.connector.connect(user='root', password='pwd', host='localhost', database='db_name')

Program Specifications

• Create Account - When a new customer comes to the bank, the bank teller is able to open a new account using the program. The bank teller enters the name_on_account and opening balance in order to create a new account. Other attributes account_no and account_open_date obtain auto-generated values.

• Check Balance - The banke teller is able to make balance inquiries on existing bank accounts. They enter account_no and the program provides the details (number, name, balance, and account open date). An error is displayed if an invalid account number is supplied.

• Deposit - The bank teller is asked for account_no and shows the account details (number, name, balance, and account open date), and the account is locked for update. The teller then is able to enter the deposit amount. The balance on the account is updated, and the lock on the account is released. The updated account information is displayed.

• Withdraw -The bank teller is asked for account_no and shows the account details (number, name, balance, and account open date), and the account is locked for update. The teller then is able to enter the withdrawal amount. The balance on the account is updated, and the lock on the account is released. The updated account information is displayed. An error is displayed if the balance is insufficient for withdrawal.

• Transfer - The bank teller is asked for source and target account_no and shows the account details and locks both accounts. The teller is then able to enter the transfer amount. In order to simulate network congestion, the program sleeps for 10 seconds after withdrawing from the source account, and again for 10 seconds after depositing into the target account. The transaction is committed and the updated account details are shown.