INTRODUCTION
In this article I will show you how to set up a one-to-many database relationship, how you would know when you would need to use one, as well as some example queries.
We will use:
- Python 3.9+
- PostgreSQL (database)
- Flask (framework)
- Flask-SQLAlchemy (object-relation mapper)
- Pipenv (virtual environment)
Once your C.R.U.D. ( Create Read Update Delete ) applications start getting more complex, you will find that you need to link tables together within the database. In databases it is very common task to create relationships between tables. A one-to-many relationship is a method that will allow you to link a parent to a child table in a database. A one-to-many relationship is created by adding a foreign key to the child table.
Diagram created on diagrams.net
PK = primary key
FK = foreign key
WHEN WOULD I USE A ONE-TO-MANY RELATIONSHIP?
Let me present to you a real-world scenario, let’s say you have an online store and you need to keep track of your customers and the orders they make. In your database you would create a table for customers and separate table for the orders. How would you know which order belongs to which customer? The way to do that is to set up a one-to-many relationship. This will allow you to take one customer and query all the information connected to the orders the customer made.
I will show you an easy way to know when you need to set up a one-to-many database relationship as opposed to a one-to-one or a many-to-many database relationship. Going back to the example of the online store, you have two tables, a customer table and an order table. You would ask yourself two questions about the tables.
Example 1:
Q: Does a customer have many orders?
A: Yes, a customer can have many orders.
Q: Does an order have many customers?
A: No, only one person can create an order.
We have one customer and many orders but it does not work the other way around. One order does not have many customers. We can clearly see that example 1 is a great candidate for a one-to-many database relationship.
Let’s look at another example.
Example 2:
In another imaginary database we have a table for students and a table for classes. So we form the questions like this.
Q: Does a student have many classes?
A: Yes, a student can have many classes.
Q: Does a class have many students?
A: Yes, a class can have many students.
Here we have many students and many classes because of that, example 2 is not suitable for a one-to-many relationship.
How To Set Up The Project
Install PostgreSQL and Pip. We use Pip to install Pipenv.
$ sudo apt install postgresql libpq-dev python3-pip
Enable and start PostgreSQL.
$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql
Check to see if PostgreSQL is running.
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2021-02-01 10:09:46 EST; 2h 56min ago
Process: 920 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 920 (code=exited, status=0/SUCCESS)
[...]
Log in as the postgres user and then the interactive PostgreSQL terminal 'psql'.
$ sudo su – postgres
postgres@laptop:~$ psql
postgres=#
Check the PostgreSQL version.
postgres=# SELECT version();
version
------------------------------------------------------------------
PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu [...]
Set the postgres user’s password. Please use something stronger than 123456.
postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE
Create a database called mystoredb.
postgres=# CREATE DATABASE mystoredb;
CREATE DATABASE
List the databases. You will see mystoredb on the list.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mystoredb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
[...]
Exit psql interactive terminal and logout as the postgres user.
postgres=# \q
postgres@laptop:~$ exit
Create a directory for your project.
$ mkdir my_project/
$ cd my_project/
Install pipenv using pip3.
$ pip3 install pipenv
Start the Python virtual environment.
$ pipenv shell
Install the required libraries.
$ pipenv install flask flask-sqlalchemy psycopg2-binary
Here is a simple example of a one-to-many relationship with Flask-SQLAlchemy.
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
comment_id = db.relationship('Comment', cascade='all, delete', backref='author' lazy=True)
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
db.relationship()
This function looks at the child table and pulls up multiple instances of the child table.
backref
Backref creates a kind of invisible column within the child table.
lazy
Lazy defines when the data will be pulled from the database.
lazy=select
or
lazy=True
This is the default setting. SQLAlchemy will load the all data necessary at once.
db.ForiegnKey()
This function specifies the parent class (written in lowercase) and relates items back to the parent table.
Create file called application.py and add the following content.
$ vim application.py
# application.py
from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['DEBUG'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:123456@127.0.0.1:5432/mystoredb'
db = SQLAlchemy(app)
class Customer(db.Model):
'''Customer table'''
__tablename__ = 'customer'
id = db.Column(db.Integer, primary_key=True)
customer_name = db.Column(db.String(25))
customer_email = db.Column(db.String(100), nullable=True)
order_id = db.relationship('Order', cascade='all, delete', backref='customer', lazy=True)
def __init__(self, customer_name, customer_email):
self.customer_name = customer_name
self. customer_email = customer_email
def __repr__(self):
return f'<Customer: {self.id} {self.customer_name}>'
class Order(db.Model):
'''Order table'''
__tablename__ = 'order'
id = db.Column(db.Integer, primary_key=True)
order_date = db.Column(db.DateTime, default=datetime.now)
# Use datetime to generate a random 6 digit number from milliseconds.
order_number = db.Column(db.Integer, default=datetime.now().strftime("%f"))
customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'))
def __repr__(self):
return f'<OrderID: {self.id}>'
if __name__ == '__main__':
app.run(debug=True)
*NOTE:
The syntax for the SQLALCHEMY_DATABASE_URI variable is
'postgresql://<db_username>:<password>@<ip_address>:<port>/<database_name>'
Example of what I am using in this tutorial:
'postgresql://postgres:123456@127.0.0.1:5432/mystoredb'
Change the values of the database URI to reflect your set up.
Start a Python interpreter shell on the command line.
$ python3
Import the database + models and create the tables.
>>> from application import db, Customer, Order
>>> db.create_all()
Add a few people to the customer table.
>>> brandon = Customer(customer_name='Brandon', customer_email='brandon@example.com')
>>> maria = Customer(customer_name='Maria', customer_email='maria@example.com')
>>> ahmed = Customer(customer_name='Ahmed', customer_email='ahmed@example.com')
>>> cheuk = Customer(customer_name='Cheuk', customer_email='cheuk@example.com')
>>> nikolaus = Customer(customer_name='Nikolaus', customer_email='nikolaus@example.com')
Add all people to the database session.
>>> db.session.add_all([brandon, maria, ahmed, cheuk, nikolaus])
Commit all changes to the database to save the users.
>>> db.session.commit()
Create orders and link them with customers in the database like this. Remember 'customer' is the backref value in the parent table.
>>> order1 = Order(customer=brandon)
>>> order2 = Order(customer=brandon)
>>> order3 = Order(customer=brandon)
>>> order4 = Order(customer=ahmed)
>>> order5 = Order(customer=ahmed)
>>> order6 = Order(customer=cheuk)
>>> order7 = Order(customer=cheuk)
>>> order8 = Order(customer=maria)
>>> order9 = Order(customer=nikolaus)
Add all orders to the session.
>>> db.session.add_all([order1, order3, order3, order4, order5, order6, order7, order8, order9])
Commit all changes to the database to save the orders.
>>> db.session.commit()
How To Perform Queries
At this point we can query the database.
Pull up all the customer records in the database and loop through the results.
>>> customers = Customer.query.all()
>>> for person in customers:
... f'ID: {person.id} Name: {person.customer_name} Email: {person.customer_email}'
...
'ID: 1 Name: Brandon Email: brandon@example.com'
'ID: 2 Name: Maria Email: maria@example.com'
'ID: 3 Name: Ahmed Email: ahmed@example.com'
'ID: 4 Name: Cheuk Email: cheuk@example.com'
'ID: 5 Name: Nikolaus Email: nikolaus@example.com'
Extract the name like this.
>>> for customer in customers:
... customer.customer_name
...
'Brandon'
'Maria'
'Ahmed'
'Cheuk'
'Nikolaus'
Get the total order count for Brandon.
>>> brandon_order_count = Order.query.filter_by(customer_id=1).count()
>>> f'Total orders for Brandon: {brandon_order_count}'
Total orders for Brandon: 3
Get the order numbers for Brandon.
>>> brandon_all_orders = Order.query.filter(Order.customer_id == 1).all()
>>> for order in brandon_all_orders:
... f'Order number: {order.order_number}'
...
'Order number: 680018'
'Order number: 193492'
'Order number: 662450'
Get the orders from Brandon sorted by most recent.
>>> latest_orders = Order.query.order_by(Order.order_date.desc()).all()
>>> for order in latest_orders:
... order.order_date.strftime("%Y-%m-%d %H:%M")
...
'2022-04-03 18:09'
'2022-02-02 20:22'
'2022-01-01 10:59'
Get the orders from Brandon sorted by oldest first.
>>> oldest_orders = Order.query.order_by(Order.order_date.desc()).all()
>>> for order in oldest_orders:
... order.order_date.strftime("%Y-%m-%d %H:%M")
...
'2022-01-01 10:59'
'2022-02-02 20:22'
'2022-04-03 18:09'
Get orders filtering by a before a specific date.
>>> from datetime import datetime
>>> from_date = datetime(year=2022, month=2, day=22)
>>> befor_feb_22 = Order.query.filter(Order.order_date <= from_date).all()
>>> for order in before_feb_22:
... f'ID: {order.id} Date: {order.order_date} OrderNum: {order.order_number} CustID: {order.customer_id}'
...
'ID: 2 Date: 2022-01-01 10:29:43.380762 OrderNum: 27227 CustID: 1'
'ID: 1 Date: 2022-02-02 10:29:43.380762 OrderNum: 27227 CustID: 1'
Get orders filtering by a after a specific date.
>>> after_date = datetime(year=2022, month=2, day=22)
>>> after_feb_22 = Order.query.filter(Order.order_date >= after_date).all()
>>> for order in after_feb_22:
... f'ID: {order.id} Date: {order.order_date} OrderNum: {order.order_number} CustID: {order.customer_id}'
...
'ID: 3 Date: 2022-04-03 10:29:43.380762 OrderNum: 27227 CustID: 1'
Get the date and time Cheuk placed the orders.
>>> cheuk_all_orders = Order.query.filter(Order.customer_id == 4).all()
>>> for order in cheuk_all_orders:
... f'Order date: {order.order_date.strftime("%Y-%m-%d %H:%M")}'
...
'Order date: 2022-02-07 19:52'
'Order date: 2022-02-07 21:38'
>>> exit()
How To Troubleshoot If You Have Issues
Check carefully for syntax errors.
If you ran db.create_all() with errors in the application.py file you might have to delete the database and recreate it.
$ sudo su – postgres
postgres=# DROP DATABASE mystoredb;
DROP DATABASE
postgres=# CREATE DATABASE mystoredb;
CREATE DATABASE
Then run db.create_all() again to create the tables.
>>> python3
>>> from application import db, Customer, Order
>>> db.create_all()
Feel free to leave comments, questions, and suggestions.
Oldest comments (4)
hey , am getting this error TypeError: init() missing 1 required positional argument: 'customer_email' when i try to enter customer name
Hello Emmanuel, please try to go over your code and look for any typos. I have tested the code a few times. It works.
You can add the customer_email when you add the customer to the database.
Thank you Brandon, it worked out