All about the SELECT command in SQLite3 With Python

SELECT command in SQLite3 with Python:-

Introduction to SELECT command in SQLite3 with Python:

The SELECT command in SQLite3 is a command of the SQLite3 database, that is used to extract data from the database.

The SELECT command in SQLite3 allows you to retrieve data from one or more tables based on specified criteria.

The SELECT command in SQLite3 is also known as the Data Query Language(DQL) commands.

In Python you can use the ‘sqlite3’ module to execute ‘SELECT’ commands and fetch data from an SQLite3 database.

Connection of SQLite3 in Python:

before diving into SELECT command in SQLite3, it essential to set up SQLite3 in Python.

SQLite3 module provides an easy way to interact with SQLite3 databases.

Syntax: Connection of SQLite3 in Python.

import sqlite3

#connect to an SQLite3 database
con=sqlite3.connect('abcde.db')

Example:

SELECT command in SQLite3:

SELECT command in SQLite3 is used to fetch/retrieve data from a database.

Syntax: Basic syntax of SELECT command in SQLite3.

SELECT column_1,column_2,..........
FROM table_name

Syntax: To select all field from a table:

#import module sqlite3
import sqlite3

#connect to database
con=sqlite3.connect('database_name.db')

#SELECT all column query
query='SELECT * FROM table_name'

#Execute query
con.execute(query)

#close connection
con.close() 

Example: To select all field from a table:

Where: ‘data’ is user-defined variable(In this variable store executed data).

‘*’ this symbol mins select all column/fields from student_3 table.

In this above example they can not give output because data is store in ‘data’ variable, but data is not printed.

Syntax: To select specific column from a table:

#import module sqlite3
import sqlite3

#connect to database
con=sqlite3.connect('database_name.db')

#SELECT some specific column query
query='''SELECT column_1,column_2,colum_3,...... 
             FROM table_name'''

#Execute query
con.execute(query)

#close connection
con.close() 

Example: To select specific column from a table:

In this above example select only three column st_id,st_name and st_email.

In this above example they can not give output because data is store in ‘data’ variable, but data is not printed.

For printing data required data iteration in Python:

Data iteration mins:

In Python data iteration is a perform with the help of for loop.

Syntax: Printing all column data from the database:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query
query='SELECT * FROM table_name'

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example: Printing all column data from the database:

Output: Output of above query:

Syntax: Printing specific column data from database:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query
query='''SELECT column_1,column_2,column_3,... FROM table_name'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example: Printing specific column data from database:

Output:

In this above example only three column (st_id,st_name,st_email) are selected, so only three fields, records are printed.

Searching or Filtering data from a database using the SELECT command in Sqlite3:

To Filter or searching the data, we use the ‘WHERE’ clouse in the SELECT command in SQLite3 query.

Syntax:

SELECT <column_list>
FROM table_name
WHERE condition;

Here the condition can be combined with one or more than one logical(and,or,…) expression.

The predicates or logical expression can be evaluated as TRUE or FALSE.

When the WHERE clause does not return any row/records then the predicated is evaluated as FALSE.

If it returns the row/records, it will be included in the result-set and the predicated is evaluated as TRUE.

WHERE clause in SELECT command in SQLite3 and the following use cases:

1. The WHERE clause with an equal(=) operator.
2. The WHERE clause with an AND operator.
3. The WHERE clause with an OR operator.
4. The WHERE clause with an BETWEEN operator.
5. The WHERE clause with an IN operator.
6. The WHERE clause with an NOT IN operator.
7. The WHERE clause with an Comparison operator.

SELECT command in SQLite3 and WHERE clause with an equal(=) operator:

The equal(=) operator in the ‘WHERE’ clause is used to match a column’s value exactly with the given value.

Syntax:For WHERE clause with an equal operator:

SELECT column_1,column_2,.....
FROM table_name
WHERE column_name=value;

Example:

Suppose you have a table named ‘student_3’ with columns ‘st_id’, ‘st_name’, and ‘st_email’. If you want to select all the students whose st_name is equal to Ankush Raj, you would use the following SQLite3 query:

SELECT st_id,st_name,st_email 
FROM student_3
WHERE st_name='Ankush Raj'

This query select the ‘st_id’, ‘st_name’, and ‘st_email’ columns from the ‘Student_3’ table where the ‘st_name’ is ‘Ankush Raj’.

Syntax: Overall code in python :

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and equal operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name=Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

EXample: code in Python:

Output:

SELECT command in SQLite3 and WHERE clause with an AND operator:

The SELECT command in SQLite3 is used to query data from a database table, and you can use the ‘WHERE‘ clause with an ‘AND‘ operator to specify multiple conditions that must all be meet for the records to be selected.

NOTE:- IN this SELECT command in SQLite3 with AND operators minimum two condition is required and we can use more thane two condition.

Syntax: WHERE clause with AND operators:

SELECT column_1,column_2,.......
FROM table_name
WHERE condition_1 AND condition_2 AND..........;

Example:

Consider a table named ‘student_3’ with columns ‘st_id’, ‘st_name’ and ‘st_email’.To select all student from the B-TECH class who have a st_id greater than equal to 102.

You would use the following SQLite3 query:

SELECT st_id,st_name,st_email
FROM student_3
WHERE st_class='B-TECH' AND st_id>=102;

This query select the ‘st_id’,‘st_name’, and ‘st_email’ columns from the ‘student_3’ table where the ‘st_class’ is ‘B-TECH’ and the ‘st_id’ is greater than equal to 102.

Syntax: Code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and AND operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE cndition_1 AND condition_2 AND.........'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example:

Output:

SELECT command in SQLite3 and WHERE clause with an OR operator:

The SELECT command in SQLite3 is used to query data from a database.

The ‘WHERE’ clause is used to filter records based on specific conditions.

When using the ‘OR’ operator the query will return records if at lesat one of the conditions separated by the ‘OR’ is true.

NOTE:- IN this SELECT command in SQLite3 with OR operators minimum two condition is required and we can use more thane two condition.

Syntax: Query with SELECT and WHERE using OR:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE condition_1 OR condition_2 OR.......;

Example: Query with SELECT and WHERE using OR:

Consider a table named ‘student_3’ with columns ‘st_id’, ‘st_name’ and ‘st_email’.To select all student who are either in the B-TECH class or st_id greater than equal to 102.

You would use the following query:

SELECT st_id,st_name,st_email
FROM student_3
WHERE st_class = 'B-TECH' OR st_id >= 102;

Explanation:

• SELECT st_id,st_name,st_email FROM student_3: This part of the query select st_id,st_name and st_email column from the student_3 table.
• WHERE st_class = ‘B-TECH’ OR st_id >= 102: This filters the results to include only those student who are either in the B-TECH class or st_id grater thane equal to 120.

Syntax: code in python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and OR operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE cndition_1 OR condition_2 OR.........'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example:

Output:

SELECT command in SQLite3 and WHERE clause with an BETWEEN operator:

The SELECT command in SQLite3 is used to query data from a database, and the ‘WHERE’ clause is used to filter records based on specific conditions.

The ‘BETWEEN’ operator is used to filter the result set within a certain range.

Syntax: Qyery with SELECT and WHERE using BETWEEN:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

Example: Query with SELECT and WHERE using BETWEEN:

To select student whose ID is between 105 and 115(inclusive).

You would use the following query:

SELECT * FROM student_3
WHERE st_id BETWEEN 105 AND 115;

Explanation:

• SELECT * FROM studen_3: This part of the query selects all columns from the student_3 table.
• WHERE age BETWEEN 105 AND 115: This filters the results to include only those students whose st_id is between 105 and 115, inclusive.

Syntax: code in python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and BETWEEN operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE cndition_name BETWEEN value_1 AND value_2'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example: Full code in python

Output:

SELECT command in SQLite3 and WHERE clause with an IN operator:

The SELECT command in SQLite3 is used to query data from a database, and the WHERE clause is used to filter records based on specifics conditions.

The IN operators allows you to specify multiple values in a WHERE clause, and it will return records that match any of the value in the specified list.

Syntax: Query with SELECT and WHERE using IN:

SELECT column_1,column_2,column_3.......
FROM table_nmae
WHERE column_name IN (value_1,value_2........);

Example: Query with SELECT and WHERE using IN:

To select student who are either in the B-TECH or B-COM class.

You would use the following query:

SELECT * FROM student_3
WHERE st_class IN ('B-TECH',B-COM)

Explanation:

• SELECT * FROM student_3: This part of the query selects all columns from the student_3 table.
• WHERE st_class IN(‘B-TECH’,’B_COM’): This filters the results to include only those student wh0 are in either the B-TECH OR B-COM class.

Syntax: Code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and IN operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name IN (value_1,value_2,.........)'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example:

Output:

SELECT command in SQLite3 and WHERE clause with a NOT IN operator:

The SELECT command in SQLite3, when used with the ‘WHERE’ clause and the ‘NOT IN’ operator, is used to filter records that di not match any of the values in a specified list.

Syntax: Query with SELECT and WHERE using NOT IN:

SELECT column_1,column_2,column_3,........
FROM table_name
WHERE column_name NOT IN(value_1,value_2,.....);

Example: Query with SELECT and WHERE using NOT IN:

To select student who are not in the B-TECH or B-COM class.

You would use the following query:

SELECT * FROM student_3
WHERE st_class NOT IN('B-TECH','B-COM');

Explanation:

• SELECT * FROM student_3: This part of the query selects all columns from the student_3 table.
• WHERE st_class NOT IN(‘B-TECH’,’B-COM’): This filters the results to include only those student who are not in either the B-TECH or B-COM class.

Syntax: code in python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and NOT IN operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name NOT IN (value_1,value_2,.........)'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Example:

Output:

SELECT command in SQLite3 and WHERE clause with a comparision operators:

The SELECT command in SQLite3, combined with the WHERE clause and comparision opertors, allows you to filter records based on specific conditions.

Types of Comparision Operators:

Operators Name	Operators
Equal to	=
Not equal to	<> or !=
Greater than	>
Less than	<
Greater than equal to	>=
Less than equal to	<=

Equal to (=) comparision operators:

In SELECT command in SQLite3, the equal operator ‘=’ is used in the ‘WHERE’ clauseto filter records based on a condition that checks for equility.

When you use the equal operator, the query returns only those records wher the specified column value matches the given value exactly.

Use case scenarious:

1. Find records that match a specific value.
2. Filter data based on exact criteria.
3. Perform simple comparisions in query conditions.

syntax: Query with SELECT and WHERE using equal to comparision operators:

SELECT column_1,column_2,column_3,........
FROM table_name 
WHERE column_name = value;

Example: Query with SELECT and WHERE using equal to comparision operator:

To select student who class is an equal to B-TECH, you would use the following query:

SELECT * FROM student_3
WHERE st_class='B-TECH';

Explanation:

• SELECT * FROM student_3: This part of query is select all column from student_3 table.
• WHERE st_class=’B-TECH’:This filters the results to include only those student who are class equal to B-TECH.

Syntax: code in python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and equal to comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name=Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Not equal to(<> or !=) comparision operators:

Filter records based on a condition that checks for inequaility.

These operators return records where the specified column’s value does not match the given value.

Use case scenarios:

• Find records that do not match a specic values.
• Exclude certai data based on criteria.
• Perform exclusionary comparisions in query conditions.

Syntax: Query with SELECT and WHERE using using not equal to comparision operator:

SELECT column_1,column_2,........
FROM table_name
WHERE column_name <> value;

OR

SELECT column_1,column_2,........
FROM table_name
WHERE column_name != value;

Explanation:

‘<>’ and ‘!=’ are the not equal operators in SQLit3.

These operators are used to filter records that do not match a specific value in a given columns.

Example: SELECT command in SQLIte3 with not equal to operators:

To select all student whose class not equal to B-TECH.

SELECT * FROM student_3
WHERE st_class <> 'B-TECH';

OR:

SELECT * FROM student_3
WHERE st_class != 'B-TECH';

SELECT * FROM student_3: This part of query is selecte all columns from student_3 table.

WHERE st_class <> ‘B-TECH’; or WHERE st_class != ‘B-TECH’; :This filters the result is include only those student who are st_class not equal to B-TECH.

Syntax: code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and not equal to comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name<>Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Greater than(>) comparision operators:

Filter records based on a condition that check if a value is greater than.

These operators return records where the specified column value is greater than the given value.

Use Case:

• Filtering Records: Useful for filtering records where the value exceed a specific limit.
• Range Queries: Helps to retrieve records within a certain range by combining with other operators.
• Data Analysis: Facilitates analysis by focusing on records that meet a minimum criterion.

Syntax:Query with SELECT and WHERE greater than comparision operator:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE column_name>value;

Explanation:

‘>’ are the greater than operators in SQLit3.

These operators are used to filter records that have more than a specific value in a given column.

Example: SELECT command in SQLIte3 with greater than operators:

To select all student whose st_id greater than to 106.

SELECT * FROM student_3
WHERE st_id > 106;

SELECT * FROM student_3: This part of query is selecte all columns from student_3 table.

WHERE st_id > 106;:This filters the result is include only those student who are st_id greater than to 106.

Syntax: code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and greater than comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name>Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Less than(<) comparision operators:

Filter records based on a condition that check if a value is less than.

These operators return records where the specified column value is less than the given value.

Syntax:Query with SELECT and WHERE less than comparision operator:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE column_name<value;

Explanation:

‘<‘ are the less than operators in SQLit3.

These operators are used to filter records that have less than a specific value in a given column.

Example: SELECT command in SQLIte3 with less than operators:

To select all student whose st_id less than to 106.

SELECT * FROM student_3
WHERE st_id < 106;

SELECT * FROM student_3: This part of query is selecte all columns from student_3 table.

WHERE st_id > 106;:This filters the result is include only those student who are st_id less than to 106.

Syntax: code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and less than comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name<Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Greater than equla to(>=) comparision operators:

Filter records based on a condition that check if a value is greater than equal to.

These operators return records where the specified column value is greater than equal to the given value.

Syntax:Query with SELECT and WHERE greater than equla to comparision operator:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE column_name>=value;

Explanation:

‘>=’ are the greater than equal to operators in SQLit3.

These operators are used to filter records that have more than equal to a specific value in a given column.

Example: SELECT command in SQLIte3 with greater than equal to operators:

To select all student whose st_id greater than equal to 106.

SELECT * FROM student_3
WHERE st_id >= 106;

SELECT * FROM student_3: This part of query is selecte all columns from student_3 table.

WHERE st_id > 106;:This filters the result is include only those student who are st_id greater than equal to 106.

Syntax: code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and greater than equal to comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name>=Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Less than equal to(<=) comparision operators:

Filter records based on a condition that check if a value is less than equal to.

These operators return records where the specified column value is less than equla to the given value.

Syntax:Query with SELECT and WHERE less than equlal to comparision operator:

SELECT column_1,column_2,column_3.........
FROM table_name
WHERE column_name<=value;

Explanation:

‘<=’ are the less than equal to operators in SQLit3.

These operators are used to filter records that have less than equal to a specific value in a given column.

Example: SELECT command in SQLIte3 with less than equal to operators:

To select all student whose st_id less than equal to 106.

SELECT * FROM student_3
WHERE st_id <= 106;

SELECT * FROM student_3: This part of query is selecte all columns from student_3 table.

WHERE st_id > 106;:This filters the result is include only those student who are st_id less than equal to 106.

Syntax: code in Python:

#Import sqlite3 module 
import sqlite3

#connect from database
con=sqlite3.connect('database_name.db')

#SELECT query with where condition and less than equal to comparision operators
query='''SELECT column_1,column_2,column_3,... FROM table_name WHERE column_name<=Value'''

#Execute SELECT query
data=con.execute(query)

#Iterate Execute data
for n in data:
       #Print Iterated Execute data
       print(n)

#close connection
con.close()

Question related SELECT command in SQLite3: