I’m trying to write an ATM bank machine web app. I’m learning how to use SQL databases with Django and Python which use primary keys and Foreign Keys to establish two “Many-to-One” relationships between different tables.
I’m refactoring my web app by dividing the data points across three different class models (and therefore three different tables in my SQL database). I’ve got a User class, a BankAccount class, and a Transaction class.
For my User class, it is the access_card attribute which will be singular and unique to all the other users in the database. So I’ve designated this as the primary key. Other data points in this table which are not necessarily unique to every entry in the database include the access card’s PIN, when a client opened their account, along with a client’s first and last name.
For the next class that I’ve called BankAccount, it is the account_number that is unique to each instance, so I have designated this one as the primary key. Other attributes in this table which won’t necessarily be unique when data is entered in production include interest amount, account opening date, and transit number.
For the Transaction class, the first two attributes that I declare are the Foreign Keys because they are supposed to refer to the other two classes which have primary keys.
Here is my models.py:
from django.db import models
from datetime import datetime
import decimal
from decimal import Decimal
from random import randint
from django.forms import ModelForm
from telagents.forms import AmountForm
class User(models.Model):
access_card = models.IntegerField(max_length=15, primary_key=True)
PIN = models.IntegerField(max_length=5 )
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=30)
client_since = models.DateTimeField('Client since')
def creation_date(self):
return self.client_since.strftime("%A %d %B %Y @ %I:%M:%S %p")
def __str__(self):
return f"{self.first_name} {self.last_name}'s bank account."
class BankAccount(models.Model):
account_number = models.IntegerField(max_length=6, primary_key=True)
interest = models.DecimalField(max_digits=6, decimal_places=3) # Percent
account_opened = models.DateTimeField('Inception date')
transit_number = models.IntegerField(max_length=5)
def __str__(self):
return f"{self.transit_number} / {self.account_number}"
class Transaction(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
account = models.ForeignKey(BankAccount, on_delete=models.CASCADE)
debit = models.DecimalField(max_digits=12, decimal_places=2)
credit = models.DecimalField(max_digits=12, decimal_places=2)
balance = models.DecimalField(max_digits=12, decimal_places=2)
amount = models.DecimalField(max_digits=12, decimal_places=2)
trans_timestamp = models.DateTimeField(auto_now=False, auto_now_add=False)
trans_id = models.BigIntegerField()
What I am trying to do next is perform a lookup to grab all the data from Transactions where account_id == 1 and display those in the table. Here is a screenshot of an sqlite browser showing this table:
As you can see, the transaction app’s table has an account_id column as well as a user_id column which I presume was created by the two Foreign Key attributes declared inside the Transaction class. So does this mean it is working?
Resources I’ve leveraged so far in my exploration of sql, relational databases, and Django docs include:
- Relational Database Concepts - YouTube
- SQL FOREIGN KEY Constraint
- Build query to join data from two models without primary or foreign key
- (Official Django doc:) Many-to-one relationships | Django documentation | Django
I’m not sure what changes I need to make inside my views.py to fully extract the data properly and then what changes I need to make in my template to present the data to the web visitor.
Here is my current views.py:
from django.shortcuts import render
from django.http import HttpResponseRedirect,HttpResponse
from .models import BankAccount, User, Transaction
from .forms import AmountForm
def index(request):
balance = 0
context = {'balance': balance}
user_data = User.objects.all()
account_data = BankAccount.objects.all()
my_transaction_data = Transaction.objects.get(user=1)
print(request.POST)
if request.method == 'POST':
form = AmountForm(request.POST)
if form.is_valid():
amount = form.cleaned_data['amount']
if request.POST['transaction'] == 'Deposit':
balance = balance + amount
context.update({'balance': balance,})
if request.POST['transaction'] == 'Withdraw':
balance = balance - amount
context.update({'balance': balance,})
my_transaction_data.balance = balance
my_transaction_data.save()
return render(request, 'telagents/home.html', {'form': form, 'user_data':user_data, 'account_data':account_data, 'context': context,})
else:
form = AmountForm()
return render(request, 'telagents/home.html', {'form': form, 'data':data, })
Here is my templates/home.html:
<!DOCTYPE html>
<html lang="en">
<head>
</head>
<body>
{% block content %}
<br><br>
{% for data_obj in user_data %}
<center>
Client Name : {{data_obj.first_name}} {{data_obj.last_name}}
<br>
Client Since : {{ data_obj.creation_date }}
<br>
{% endfor%}
<br>
<hr style="width:5%">
{% for data_obj in account_data %}
<br>
Transit Account Number : {{data_obj.transit_number}}
<br>
Bank Account Number : {{data_obj.account_number}}
<br>
Interest Rate : {{data_obj.interest}} %
<br>
Opening Date : {{data_obj.account_opened}}
</center>
{% endfor%}
<br><br>
<center>
<form action="{% url 'index' %}" method="post">
{% csrf_token %}
{{ form }}
<input type="submit" value="Deposit" name="transaction" >
<input type="submit" value="Withdraw" name="transaction">
</form>
</center>
<center>
<table class="GeneratedTable">
<thead>
<tr>
<th>Type</th>
<th>Timestamp</th>
<th>Trans ID #</th>
<th>Merchant</th>
<th>Debits</th>
<th>Credits</th>
<th>Running Balance</th>
</tr>
</thead>
<tbody>
<tr>
{% for trans_objs in account_data %}
<td>(null)</td>
<td>{{ trans_objs.trans_timestamp }}</td>
<td>{{ trans_objs.trans_id }}</td>
<td>Big Bank ATM</td>
<td>{{ trans_objs.debit }}</td>
<td>{{ trans_objs.credit }}</td>
<td>{{ trans_objs.balance }} </td>
{% endfor %}
</tr>
</tbody>
<tbody>
<tr>
{% for trans_objs in context %}
<td>(null)</td>
<td>(null)</td>
<td>(null)</td>
<td>Big Bank ATM</td>
<td>{{trans_objs.debit}} </td>
<td>{{trans_objs.credit}} </td>
<td>{{ trans_objs.balance }} </td>
{% endfor %}
</tr>
</tbody>
</table>
</center>
{% endblock %}
</body>
</html>