brandon_wallace

Posted on Feb 18, 2021 Edited on Jan 4, 2022

One-To-Many Database Relationships Complete How To

#database #sql #python #tutorial

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)
[...]

$ 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.

Top comments (4)

Emmanuel Kiprono • Jun 26 '21

hey , am getting this error TypeError: init() missing 1 required positional argument: 'customer_email' when i try to enter customer name

brandon_wallace • Jan 3 '22 • Edited

You can add the customer_email when you add the customer to the database.

brandon = Customer(customer_name='Brandon', customer_email='brandon@example.com')

Emmanuel Kiprono • Jan 26 '22

Thank you Brandon, it worked out

brandon_wallace • Jun 28 '21

Hello Emmanuel, please try to go over your code and look for any typos. I have tested the code a few times. It works.