Now run the following command in the shell. It will return the first record that exists in the Destination table (eg. <Destination: Paris, France> ) :

Destination.objects.all() [0]

You can also run other queries to get specific details about a record in the database (eg. the name, or description, or slug for a specific destination).

Try out each of the following commands in the shell:

Destination.objects.all()[0].name

…this will return 'Paris, France'

Destination.objects.all()[0].description

…this will return 'The capital city of France'

Destination.objects.all()[0].slug

…this will return 'paris-france'

Retrieving a specific object

If you wish the retrieve a specific object eg. by searching for a specific value for a field, you can use the get() method. For example let’s say we want to find an object in our Destination table with a specific slug value (such as “paris-france”). We could use the following code:

Destination.objects.get(slug="paris-france")

If we want to store the retrieved object for further use, we can store it in a variable, for example:

obj = Destination.objects.get(slug="paris-france")

Updating data for an object

Let’s say we want to update the description field for the object we just retrieved (and that is stored in the obj variable). We can use the following code:

obj.description = "The city of light"

obj.save()

For more examples of queries you can run, this Django ORM cheatsheet is a great resource.

Querying the database using raw SQL queries

Now we will look at how to query the database using raw SQL queries instead. To query a database using raw SQL queries, open the VS Code terminal and run the following command:

python3 manage.py dbshell

Note, if using Windows you might just need python instead of python3 at the start of the command.

Now you are ready to use SQL queries on your database tables. Make sure any query you run on the database ends with a semi-colon character ( ; ).

Here are some common SQL queries you may use on a database:

The SELECT query

The SELECT query is used to retrieve data from one or more tables. You can choose whether you want to just get all the records from a table, or only records matching certain criteria. You can also choose whether you want to retrieve specific columns or all columns for one or more records matching your query. The * operator represents “all” (meaning to select all columns from the record(s) in the table(s) that match your query).

Here are some examples:

SELECT * FROM myapp_destination;

…would return all the columns (id, name, description, and slug) for all records that exist in the myapp_destination table.

SELECT * FROM myapp_destination WHERE slug = 'paris-france';

…would return all the columns (id, name, description, and slug) for any record with the value ‘paris-france’ for the slug field in the myapp_destination table.

SELECT name FROM myapp_destination WHERE id = 1;

…would return the name of a destination that has an id of 1.

ORDER BY

You can also order the results from a query by field name(s) and in ascending or descending order. Here are some examples:

SELECT * FROM myapp_destination ORDER BY name;

…will return all records from the myapp_destination table in order by name (ascending order by default eg. A to Z).

SELECT * FROM myapp_destination ORDER BY name ASC;

…will return all records from the myapp_destination table in order by name (ascending order eg. A-Z).

SELECT * FROM myapp_destination ORDER BY name DESC;

…will return all records from the myapp_destination table in order by name (descending order eg. Z-A).

SELECT * FROM myapp_destination ORDER BY name, id ASC;

…will return all records from the myapp_destination table in order by name and then by id (ascending order eg. A-Z). This is useful if you might have several results with the same name (eg. you query a database of users and want to order them by last name, and then by first name if they have the same last name).

The INSERT query

The INSERT query is used to insert new data into the database (create a new record in a table). When using the INSERT query, you specify the name of the table you want to create a new record in, the columns/fields you are inserting values into, and then the values for those fields. For example:

INSERT INTO myapp_destination (name, description, slug) VALUES ("Sydney, Australia", "The harbour city", "sydney-australia");

…would insert a new destination record into the myapp_destination table. It will have the value “Sydney, Australia” for the name field, “The harbour city” for the description, and “sydney-australia” for the slug. It will also automatically be given an id.

The UPDATE query

The UPDATE query is used to modify existing records in a table. To use the UPDATE query, you first specify the table you wish to update, which field you want to update and the new value, and the criteria for the record that is to be updated (eg. id = 1) For example:

UPDATE myapp_destination SET description = "The capital city of France" WHERE id = 1;

…would change the description of the destination that has the id of 1.

The DELETE query

The DELETE query is used to remove data from the database. To use the DELETE query, you specify the table you wish to delete a record from (eg. myapp_destination) and the criteria the record you wish to delete must meet (eg. id = 3). For example:

DELETE FROM myapp_destination WHERE id = 3;

…would delete a record from the myapp_destination table that has the id of 1.

Other SQL queries

There are many other SQL queries you can use. This SQL cheat sheet from DataCamp provides several helpful examples.

Using SQLITE Explorer

Remember, you can use the SQLITE EXLORER extension to explore your database tables. To do this, open the command palette in VS Code (click View > Command Palette), run the SQLite: Open database command. Then select your database and table. Click the Refresh button if needed and the triangle icon next to each table to open it and view the contents.

A new panel will open displaying the data in each column in the selected table. If you click the ‘SQL’ button, you will see the “SELECT * FROM…” command used to query the database table and display all records in it.

Next lesson: Exploring the Django Admin