Python Tutorial

Different types of Python SQLite Clauses

Where Clause in Python SQLite

The WHERE clause in SQLite is used to provide a criterion while obtaining data from one or more tables.

It returns the specified value from the database if the provided condition is fulfilled, which indicates true. You'll need to utilize the WHERE clause to filter the data and retrieve only the ones you need.

The WHERE clause is utilized not just in SELECT statements, but also in UPDATE, DELETE statements and other statements that will be discussed in later chapters.

Syntax:

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition]

Video : https://youtu.be/z66E6QHeQBs

And & OR Clause in Python SQLite

Multiple criteria are compiled using SQLite AND & OR operators to limit down the selected data in an SQLite statement. Conjunctive operators refer to these two operators.

These operators allow you to do several comparisons in the same SQLite statement using various operators.

  • AND Operator

In an SQLite statement's WHERE clause, the AND operator permits many conditions to appear. When utilizing the AND operator, the entire condition is deemed to be true if all of the conditions are true. For example, only when both condition1 and condition2 are true will [condition1] AND [condition2] be true.

Syntax:

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] AND [condition2]...AND [conditionN];

  • OR Operator

In an SQLite statement's WHERE clause, the OR operator is also used to combine numerous criteria. When the OR operator is used, the entire condition is deemed to be true if at least one of the conditions is true. If either condition1 or condition2 is true, [condition1] OR [condition2] will be true.

Syntax:

SELECT column1, column2, columnN 

FROM table_name

WHERE [condition1] OR [condition2]...OR [conditionN]

Like Clause in Python SQLite

The LIKE operator in SQLite is used to compare text values to a pattern utilizing wildcards. The LIKE operator returns true, which is 1 if the search expression matches the pattern expression. When using the LIKE operator, there are two wildcards that can be utilized.

  • The percent sign (%)

  • The underscore (_)

The % sign can be used to signify zero, one, or several integers or characters. A single number or letter is represented by the underscore. These symbols can be mixed and matched.

Syntax:

Following is the basic syntax of % and _.

SELECT FROM table_name

WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name

WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name

WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX_'

Limit Clause in Python SQLite

The LIMIT clause in SQLite is used to limit the amount of data returned by a SELECT query.

Syntax:

With LIMIT clause.

SELECT column1, column2, columnN 

FROM table_name

LIMIT [no of rows]

With OFFSET clause.

SELECT column1, column2, columnN 

FROM table_name

LIMIT [no of rows] OFFSET [row num]

Order By Clause in Python SQLite

The ORDER BY clause in SQLite is used to sort data in ascending or descending order by one or more columns.

Syntax:

SELECT column-list 

FROM table_name 

[WHERE condition] 

[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Group By Clause in Python SQLite

The ORDER BY clause in SQLite is used to sort data by one or more columns in ascending or descending order.

Syntax:

SELECT column-list

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2....columnN

ORDER BY column1, column2....columnN

Python SQLite Having Clause

You can use the HAVING clause to establish conditions that determine which group results appear in the final results.

The WHERE clause applies conditions to the columns that have been chosen, but the HAVING clause applies conditions to the groups that have been generated by the GROUP BY clause.

Syntax:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Python SQLite Distinct Clause

The DISTINCT keyword in SQLite is used in combination with the SELECT command to remove all duplicate records and get only unique records.

There can be times when a table has several duplicate records. It makes more sense to fetch just unique records rather than duplicate records when retrieving such records.

Syntax:

SELECT DISTINCT column1, column2,.....columnN 

FROM table_name

WHERE [condition]

QUIZ!

quiz-img
Did you find this article helpful?