CRUD with QuerySet API in Python and Django

Hi peeps!

I’ve been working on a web app “a time tracker for workers” using Python and Django, it’s super fun!

There is an API called QuerySet that you can manipulate the database in Django.

Implementing CRUD is super easy thanks to this.

I’ll write it down as my memo, or hope this helps somebody… :)

0. How to use

Connect with database

The default database of Django is SQLite.

I used Heroku Postgres, it’s super handy and easy to create/connect.

You can just add Heroku Postgres as an add-on to your Heroku Project.

https://elements.heroku.com/addons/heroku-postgresql

Create models.py

There are some ways to create models.py, in my case I used the command that Django automatically makes classes for you.

Don’t forget to run “manage.py migrate”.

Call QuerySet API at views! That’s it!

It doesn’t have to be at views. Any class is fine.

This time assume we have a table “Work” something like below;

Let’s get started!

1. Get ALL records from a table

First, let’s get all of the records. You must import your model class before running.

from app.models import Work

So, the code is like just this one line;

user = Work.objects.all()

This should return a QuerySet.

If you want to change the value of the result, you can simply do like this;

user.id = 5

When I was googling about this QuerySet, I kinda want to know the set of Query API and actual SQL query since I am a kind of SQL person.

So if you write SQL query, it’s like this;

SELECT * FROM Work;

2. Get a single record

user = Work.objects.get(id=10)

This method is for getting a single record, therefore it will raise an exception if there are more than 2, or if there’s no record.

In SQL query, it is like;

SELECT * FROM Work WHERE id=10;

3. Filter your result with param

Work.objects.filter(id=10)

The difference between get() is you can get results more than 1 if you use filter().

Also you might want to filter with a specific month, you can do it like this;

Work.objects.filter(starttime__year=2021, starttime__month=4)

Column name __ year or month or day can do that for you.

4. Create a new record

You simply use create() method and set each value;

Work.objects.create(id=11, start=datetime.now()

In SQL query, needless to say but;

INSERT INTO Work VALUES (id=11, start='2022-01-24 10:00');

5. Update a record

Get a record, overwrite (set a new value), and save.

record = Work.objects.get(id=11)
record.end = datetime.now()
record.save()

So simple!

If it’s SQL queries, it is something like;

UPDATE Work
SET end = '2022-01-24 18:00'
WHERE id=11;

6. Delete a record

Now I think you can guess how it goes — delete() method.

record = Work.objects.get(id=11)
record.delete()

SQL:

DELETE FROM Work WHERE id=11;

Wrapping up

I implemented a web app using Java and Spring Boot before, but I used My Batis as an OR mapper in which you write SQL queries.

It was great too, but in-built method like QuerySet is super powerful.

It’s been just months since I started using Python and Django, and I’m really into it!

Will post about those more soon.

👋