Database Queries Tutorial

Introduction to Database Queries

Database queries are commands used to interact with a database. They allow you to retrieve, insert, update, and delete data from a database. In this tutorial, we will focus on how to perform database queries using Django's ORM (Object-Relational Mapping) system.

Setting Up Django

Before we dive into database queries, ensure you have Django installed and a project set up. You can install Django using pip:

pip install django

After installation, create a new Django project:

django-admin startproject myproject

Navigate to your project directory:

cd myproject

Finally, create a new app within your project:

python manage.py startapp myapp

Defining Your Models

In Django, models are used to define the structure of your database tables. Here is an example of a simple model:

from django.db import models

class Person(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    age = models.IntegerField()
                

After defining your models, run the following commands to create the necessary database tables:

python manage.py makemigrations

python manage.py migrate

Performing Queries

Now that we have our models and database setup, let's perform some basic queries.

Inserting Data

To insert data into your database, use the save() method:

from myapp.models import Person

person = Person(first_name="John", last_name="Doe", age=30)
person.save()
                

Retrieving Data

To retrieve all records from a table, use the all() method:

people = Person.objects.all()
for person in people:
    print(person.first_name, person.last_name)
                

To retrieve specific records, use the filter() method:

adults = Person.objects.filter(age__gte=18)
for adult in adults:
    print(adult.first_name, adult.last_name)
                

Updating Data

To update a record, retrieve it and modify its attributes, then call the save() method:

person = Person.objects.get(id=1)
person.age = 31
person.save()
                

Deleting Data

To delete a record, retrieve it and call the delete() method:

person = Person.objects.get(id=1)
person.delete()
                

Advanced Queries

Django provides a variety of advanced query capabilities.

Aggregation

To perform aggregation, use the aggregate() method:

from django.db.models import Avg

average_age = Person.objects.aggregate(Avg('age'))
print(average_age)
                

Annotations

To add calculated fields to your querysets, use the annotate() method:

from django.db.models import Count

people_with_same_last_name = Person.objects.values('last_name').annotate(name_count=Count('last_name'))
for entry in people_with_same_last_name:
    print(entry['last_name'], entry['name_count'])
                

Conclusion

In this tutorial, we covered the basics of database queries using Django's ORM. We learned how to set up a Django project, define models, and perform basic and advanced queries. With this knowledge, you can now effectively interact with your database in a Django application.