Selecting records in a SQL query to a database in Python

The test SQL query code contains the SELECT command, which selects data from the database. Let's now take a closer look at its syntax. Here it is:

query = "SELECT * FROM table WHERE condition"

After the table name, you can also add the WHERE command, which specifies a condition for the records to be selected. The following comparison operations are allowed in it: =, !=, <>, <, >, <=, >=.

Let's look at their application using examples.

Example

Let's select a user with id equal to 2:

query = "SELECT * FROM users WHERE id=2"

The result of the executed code:

{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}

Example

Let's select users with id greater than 2:

query = "SELECT * FROM users WHERE id>2"

The result of the executed code:

{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}

Example

Let's select users with id greater than or equal to 2:

query = "SELECT * FROM users WHERE id>=2"

The result of the executed code:

{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}

Example

Let's select users with id not equal to 2:

query = "SELECT * FROM users WHERE id!=2"

The result of the executed code:

{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}

Example

Instead of the command != you can write the command <>:

query = "SELECT * FROM users WHERE id<>2"

Example

Let's select a user named 'user1'. Here we have an important nuance: since the name is a string, it must be enclosed in quotation marks:

query = "SELECT * FROM users WHERE name='user1'"

The result of the executed code:

{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}

Example

If the WHERE command is missing, then all records from the table will be selected. Let's select all employees:

query = "SELECT * FROM users"

The result of the executed code:

{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}

Practical tasks

Select a user with id equal to 3.

Select users with a salary of 900.

Select users aged 23 years.

Select users with a salary greater than 400.

Select users with a salary equal to or greater than 500.

Select users with a salary NOT equal to 500.

Select users with a salary equal to or less than 500.