Home Blog

All about the SELECT command in SQLite3 With Python

By
ankushraj8757377106@gmail.com
-
0
SELECT command in SQLIte3

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:

Q1. Which SQLite3 keyword is used to retrive data from a database?

Ans: ‘SELECT’

Q2. Which keyword is used to specify conditions in a SELRCT query?

Ans: WHERE

Q3. What operator is used to compare value in a select query?

Ans: =(equal)

Q4. What operators is used to include specific value in select query?

Ans: IN

Q5. What operators is used to exclude specific value in select query?

Ans: NOT IN

Q6. Which operator check for a range of values in a select query?

Ans: BETWEEN

click here “Introduction of SQLite3 in Python”
click here “All about SLite3 commands in Python”

All about SQLite3 command in Python.

By
ankushraj8757377106@gmail.com
-
1
All about SQLite3 command in Python.

SQLite3 Command in Python:

SQLite3 command in Python module is used to interact with SQLite databases. Below are the definitions and purposes of various SQLite3 command in Python.

SQLite3 Commands in Python can be broadly categorized into the following types:

  1. Connection Management Commands:
  2. Data Definition Language (DDL) commands:
  3. Data Manipulation Language (DML) commands:
  4. Transaction Management Language (TCL) commands:
  5. Data Query Language (DQL) commands:
  6. Miscellaneous Commands:

Connection Management Commands:

Connection Management SQLite3 commands in Python is:

Commands SQLite3.connect(‘database_name’)

import sqlite3
con=sqlite3.connect('database_name')
  • This command connects to the specified SQLite3 database.
  • If data base does not exist, it will be created.
  • To connect to an SQLite3 database in Python, you use the ‘sqlite3.connect()’ function.

Understanding the connection object:

  • The connection object represents the database connection.
  • It is used to create cursor objects,manage transactions, and execute SQLite3 commands in Python.
  • Purpose: To initiate a session with the SQLite database, enabling subsequent database operations.

Data Definition Language (DDL) commands:

Data Definition Language (DDL) SQLite3 command in Python are used to define and manage database schema structures, such as tables, indexes, and views.

In Python using the ‘SQLite3’ module you can execute Data Definition Language (DDL) commands to create table, modify table structure and content, and delete these structures and content.

There are manly three types of Data Definition Language (DDL) SQLite3 command in Python:

  1. CREATE TABLE
  2. ALTER TABLE
  3. DROP TABLE

CREATE TABLE:-

‘CREATE TABLE’ SQLite3 command in Python is used to create a new table in the database.

Purpose of CREATE TABLE: To define the structure of a table, including columns and there data types.

Syntax: SQLite Query to create table:-

import sqlite3
con=sqlite3.connect('database_name.db')
query='''CREATE TABLE Table_Name(
                                    column_name_1 data_type constraint,
                                    column_name_2 data_type(data_type_length),
                                    column_name_3 data_type(data_type_length),
                                    column_name_4 data_type(data_type_length) constraint,
                                    column_name_5  data_type,
                                    column_name_6 data_type(data_type_length)
                                    .....................
                                    ....................
                                    column_name_n data_type(data_type_length)
)'''
con.execute(query)
con.close()

In above query con and query is user defined constant.

Example: Create Student table with St_id as a primary key constraint.

Example: Create Student_3 table with multiple field as UNIQUE constraint.

In this example St_id as a primary key, St_email and state field is as a unique key.

Example: Create student_4 table with NOT NULL Constraint.

In this example st_id is primary key constraint, st_email and state is unique constraint, st_name is NOT NULL constraint.

Example: Create student_5 table with check constraint.

In this above example st_id as a primary key and as a check constraint, st_name as NOT NULL constraint, st_email,Mo_no and state as an unique constraint.

Example: Create Student_6 table with DEFAULT constraint.

In this above example st_id as a primary key and check constraint, St_name as a NOT NULL constraint, st_email,Mo_no and state as a unique constraint, st_class as a DEFAULT constraint.

ALTER TABLE:-

This ALTER TABLE SQLite3 command in python is used to modify an existing table structure in a database.

Purpose: To add or modify columns in a table.

Syntax of add column in table:-

import sqlite3

con=sqlite3.connect('database_name')
query='''ALTER TABLE table_name
ADD COLUMN column_name datatype [constraint]
'''
con.execute(query)
con.close()

Example: Add a new column in existing table in SQLite3 database.

CODE: You can directly copy and execute this code.

import sqlite3

#Connect to a database
con=sqlite3.connect('abcde.db')

#Query of add a new column to the table
query=''' ALTER TABLE student_6
ADD COLUMN Admission_year YEAR
'''
#Execute above query command
con.execute(query)

#Close the connection
con.close()

In this above example the new column Admission_year has been added to the student_6 table.

NOTE:- Modifying a column in SQLite3 command in Python is not as straightforward as in some other database systems because SQLite3 command in Python does not support direct column modification(e.g., changing data type, renaming column)using the ‘ALTER TABLE’ command.

DROP TABLE:-

SQLite3 command in python used to delete a table from the database.

Purpose: To remove an entire table and its data from the database.

Syntax for DROP TABLE:-

DROP TABLE table_name;

Example: DROP TABLE from database.

CODE: You can directly copy and execute this code.

import sqlite3

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

#sqlite query for drop table to database.
query='''DROP TABLE student_2'''

#execute above query command.
con.execute(query)

#close database command.
con.close()

Data Manipulation Language (DML) commands:

  • Data Manipulation Language (DML) commands in sqlite3 command in python are used to manipulate data stored in the database.
  • These commands allow you to insert new data, update existing data, and delete data from the database.
  • In python using the ‘SQLite3’ module you can execute DML commands to intrect with the database.

There are manly three types of Data Manipulation Language (DML) sQLite3 command in Python:

  1. INSERT INTO
  2. UPDATE
  3. DELETE FROM

INSERT INTO Command:-

SQLite3 command in Python used to insert new rows into a table.

We can at a time one new row or more than one row insert into a table.

Purpose: To add new records to a table.

Syntax: For insert one new rows/records of data in table:

INSERT INTO table_name(column_1, column_2,column_3,........,column_n)
VALUES(value_1,value_2,value_3,.........,value_n);

Example: For insert single new rows of data in table.

Code: for insert single records in table.

insert sqlite3

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

#query for insert single row
query='''INSERT INTO student(st_id,st_name,st_email,st_class,Mo_no,state)
VALUES(104,'Ankush Raj','ankushraj106@gmail.com','B-TECH',8757377104,'Delhi')
'''
#Query execute syntax.
con.execute(query)

#Save(commit) the all transaction.
con.commit()

#close the database.
con.close()

Syntax for insert more than one row/records of data in table: 

INSERT INTO table_name(column_1, column_2,column_3,........,column_n)
VALUES(value_1,value_2,value_3,.........,value_n),
(value_1,value_2,value_3,.........,value_n),
(value_1,value_2,value_3,.........,value_n),.....................;

Example: To insert multiple rows/records of data in table:

Code: To insert a multiple records in table:

import sqlite3

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

#query for insert multiple row
query='''INSERT INTO student_3(st_id,st_name,st_email,st_class,Mo_no,state)
VALUES(105,'Ankush singh','ankushsingh106@gmail.com','B-COM',8757377105,'UP'),
(106,'Raushan singh','raushansingh106@gmail.com','B-COME',8757377103,'MP'),
(107,'Rakesh singh','rakeshingh106@gmail.com','M-COM',8757377102,'UK')
'''
#Query execute syntax.
con.execute(query)

#Save(commit) the all transaction.
con.commit()

#close the database.
con.close()

UPDATE Command:-

SQLite3 command in Python is a used to modify existing rows in a table.

Purpose:- To update the data of existing records.

Syntax: For UPDATE command:-

UPDATE table_name
SET column_1=value_1,column_2=value_2,........
WHERE condition;

Example: To update a records from a table:

Code: To UPDATE a records in table:

import sqlite3

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

#Query to update data in the table.
query='''UPDATE student_3
            SET st_name='Ritesh Jha'
            WHERE st_id=104
'''

#Execute Query
con.execute(query)

#commit the changes and close the connection
con.commit()
con.close()

DELETE FROM Command:-

SQLite3 command in Python used to delete rows from a table.

Purpose: To remove specific records from a table.

Syntax: For Deleting data from a table:

DELETE FROM table_name
WHERE condition;

Example: For Deleting data from a Table:

Code: For Deleting data from a Table:

import sqlite3

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

#Query for deleting data.
query='''DELETE FROM student_3
             WHERE st_id=104
'''
#Execute query
con.execute(query)

#Commit the changes and close the connection
con.commit()
con.close()

Transaction Control Language (TCL) commands:

  • Transaction Control Language (TCL) SQLite3 command in Python are used to manage transactions within the database.
  • Transactions are sequences of SQLite commands that are executed as a single unit of work.
  • TCL(Transaction Control Language) command ensure the integrity and consistency of the database by following you to commit or roll back changes.
  • In Python using the ‘SQLIte3’ module, you can execute TCL commands to manage transaction.

There are manly three type of Transaction Control Language(TCL) SQLite3 command in Python.

  1. BEGIN TRANSACTION
  2. COMMIT
  3. ROLLBACK

BEGIN TRANSACTION:

This command begins a new transaction.

Syntax:

BEGIN TRANSACTION;

Purpose: To start a new transaction so that subsequent SQLite3 commands are execute within this transaction.

Example in Python:

COMMIT:

TCL command commits the current transaction,making all changes permanent.

Syntax:

COMMIT;

Purpose: To save the changes made during the transaction to the database.

Example In Python:

ROLLBACK:

Roll back the current transaction, undoing all changes made during the transaction.

Syntax:

ROLLBACK;

Purpose: To revert the database to its previous state in case of an error.

Example in Python:

Data Query Language (DQL) commands:

Data Query Language(DQL) commands in SQLite3 are used to query and retrieve data from the database.

The primary DQL command is ‘SELECT’ which allows you to specify the columns,tables, and conditions for retrieving the desired data.

In python using the SQLite3 module you can execute this command to fetch data from the database.

There are only one command in DQL.

  1. SELECT

SELECT Command:-

The SELECT command is the most important command in the entire database SQLit3 command.

This command is used to retrieve data from one or more tables.

Syntax:

SELECT column_1,column_2,...........
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number;

Purpose: To fetch data based on specified criteria from the database.

Example in Python:

Above code output:

Miscellaneous Commands:

  • connection.close(): Close the connection to the database.
  • connection.execute(): Execute a single SQLite3 statement.
  • connection.executemany(): Execute a SQLite3 command against all parameter sequences or mappings found in the sequence.
  • connection.fetchall(): Fetch all(remaining) rows of a query result.
  • connection.fetchmany(size): Fetches the next set of rows of a query result, returning a list.

MCQ related all about SQLite3 command in Python:

  1. Q 1: Which command is used to connect to an SQLite3 database in Python?
    A. sqlite.connect(‘database_name.db’)
    B. database.connect(‘database_name.db’)
    C. sqlite3.connect(‘database_name.db’)
    D. connect(‘database_name.db’)

    Ans:C. sqlite3.connect(‘database_name.db’)

  2. Q 2: Which of the following is correct option to create a table in SQLite?
    A. create database table_name
    B. create index table_name
    C. create schema table_name
    D. create table table_name

    Ans:D. create table table_name

  3. Q 3: What is the correct syntax to insert data into an SQLite3 table?
    A. INSERT INTO table_name (column_1, column_2) SET (value_1,value_2)
    B. INSERT table_name(column_1,column_2)VALUES(column_1,column_2)
    C. INSERT INTO
    table_name(column_1,column_2)VALUES(value_1,value_2)
    D. INSERT INTO table_name SET column_1=value_1,column_2=value_2

    Ans:C. INSERT INTO table_name(column_1,column_2)VALUES(value_1,value_2)

  4. Q 4: Which command is used to fetch all rows from the result of a query in SQLite3?
    A. connection.getrow()
    B. connection.fetchall()
    C. connection.fetchrow()
    D. connection.fetchallrows()

    Ans:B. connection.fetchall()

  5. Q 5: How do you delete row from an SQLite3 database table?
    A. DELETE FROM table_name WHERE condition
    B. DELETE ROW FROM table_name WHERE condition
    C. REMOVE FROM table_name WHERE condition
    D. DROP FROM table_name WHERE condition

    Ans:A. DELETE FROM table_name WHERE Condition

  6. Q 6: What is the correct SQLite3 command to update a value in an SQLite3 database table?
    A. MODIFY table_name SET column_1=value_1 WHERE condition
    B. CHANGE table_name SET column_1=value_1 WHERE condition
    C. UPDATE table_name SET column_1=value_1 WHERE condition
    D. SET table_name UPDATE column_1=value_1 WHERE condition

    Ans:C. UPDATE table_name SET column_1=value_1 WHERE condition

  7. Q 7: How do you commit a transaction in SQLite3 after executing SQLite3 commands in Python?
    A. cursor.commit()
    B. connection.commit()
    C. database.commit()
    D. sqlite3.commit()

    Ans:B. connection.commit()

  8. Q 8: Which command is used to delete a table stricture and his data from an SQLite database?
    A. DELETE TABLE table_name
    B. REMOVE TABLE table_name
    C. DESTROY TABLE table_name
    D. DROP TABLE table_name

    Ans:D. DROP TABLE table_name

  9. Q 9: What is the proper way to close an SQLite3 database connection in Python?
    A. connection.close()
    B. cursor.close()
    C. db.close()
    D. database.close()

    Ans:A. connection.close()

  10. Q 10: Which command allows you to execute multiple SQLite3 statements at once in SQLite3?
    A. connection.executesql()
    B. connection.executescript()
    C. connection.executemany()
    D. connection.executeall()

    Ans:B. connection.executescript()

Click here for “all about Introduction of SQLite3 in Python.”

All about SQLite3 in Python

By
ankushraj8757377106@gmail.com
-
2
All about SQLite3 in Python

What is SQLite3 in Python:

Introduction of SQLIte3 In Python:-

  • SQLite3 in Python is popular, open source, Relational Database Management System(RDBMS).
  • SQLite3 library is written in c Programming language.
  • They provide a lightweight disk-based database.
  • That does not required a separate server process and allows accessing the database using a non-standers variant of the the SQL query language.
  • SQL stands for structure Query Language.
  • This is built-ed module of Python.
  • Some application can use SQLite for internal data store.

Key feature of SQLite3 in Python:

  1. Self contained:– This means that the entire database system is contained in a single library file.
  2. Server less:- Sqlite3 in Python does not require a server process, making it easy to integrate into application. This data base is store directly in file system.
  3. Zero Configuration:-No setup or administration is required. It does not required any special configuration to set it up. It run without any administration.
  4. Cross Platform:- SQLite3 runs on various operating system(OS) such as Windows, Linux,mac-OS,etc.
  5. Small and Lightweight:- The code base of SQLite3 is very small,making it ideal for embedded systems and mobile applications.

Data Base(DB) Create and connection in SQLite3 in Python:

Steps of data base creation and connection:

step 1:- Import sqlite3 library in python file

import sqlite3

step 2:- Create connection with database abcde.db

con=sqlite3.connect(“abcde.db”)

where:-

‘con’ is a variable,

‘abcde’ is data base name and

‘.db’ is extension of data base.

Types of data type in database SQLite3 in Python:

Number Data Type

  • Tiny Int(1-3 digits)
  • INT(1-12 digits)
  • Big INT(1-20 digits)

String Data Type

  • VAR CHAR(0-255 char)
  • Text(0-6000 char)
  • Long Text(above 6000)

Date and time data type

  • Date Time(yyyy-mm-dd HH:MM:SS)
  • Date(yyyy-mm-dd)
  • Time Stame(Take the current time.)
  • Time(HH:MM:SS)
  • Year(yyyy)

Type of keys in SQLite3 database in Python:

There are manly three types of keys in SQLite3 in Python

  1. Primary Key
  2. Unique Key
  3. Foreign Key

Primary Key:-

  • The primary key constraint uniquely identifies each row in table.
  • It must contain UNIQUE value and has an implicit NOT NULL constraint.

Unique Key:-

  • A unique key constraint uniquely identified each records in the database.
  • This is provides uniqueness for the column or set of column.
  • A unique constraint ensure that all value in a column are different.
  • This provide uniqueness for the column(s) and helps identify each row uniquely.
  • Unique key, there can be multiple unique constraint defined per table.

Foreign Key:-

  • A foreign key comprise of single or collection of field in a table that essentially refer to the primary key in another table.
  • Foreign key constraint ensure referential integrity in the relation between two table.
  • The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the refereed and parent table.

Difference between Primary key and Unique key:

PRIMARY KEYUNIQUE KEY
Primary key cannot accept NULL value.Unique key accept NULL value.
A table contain only one primary key.More than one unique present at a time.
A cluster index automatically created when a primary key is defined.Unique key generates the non-cluster index.

How to create Table in Database SQLite3 in Python:

Steps of create table in database SQLite3 in Python:-

Step 1:-Create connection with data base

import sqlite3

con=sqlite3.connect(“abcde.db”)

Step 2:- Create table in data base.

con.execute(”’

create table table_name( column_name_1 Data type,

column_name_2 Data type,

column_name_3 Data type)

”’)

step 3:- Close the connection.

con.close()

Create table with single field/column as a primary key:

In this table one field/column is primary key.

Create table with single field/column as a unique key:

In this table single field/column as a unique key

In this above table student_1 Mo_no as a unique key.

Create table with multiple field/column as unique key:

In this table more than one field as a unique key

In this above table st_email,Mo_no,State all three field has unique key.

How to insert Data into a table in a SQLite3 Database in a Python:

Step to insert data into a table in a SQLite3 database in a Python.

step 1:- Create connection with database

import sqlite3

con=sqlite3.connect(“database_name.db”)

step 2:-write insert Data query

ins=”’insert into table_name(column_1, column_2,column_3,……..,column_n)VALUES(value_1,value_2,value_3,……..,value_n)”’

step 3:- Execute insert data query

con.execute(ins)

step 4:- commit all transaction/connection(Save all Transaction/connection)

con.commit()

step 5:- Close connection to database

con.close()

SQLite3 constraint in Python:

Introduction of SQLite3 constraint in Python:

Sqlite3 in Python constraint are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of data inside table.

constraint are used to make sure that the integrity of data is maintained in the database.

Types of constraint:

Following are the most used constraint that can be applied to a table of SQLite3 in Python:

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT

NOT NULL:-

  • By default a column can hold NULL value.
  • If you do not want a column to have a NULL value, use the NOT NULL constraint.
  • It is used when you want to restricts a column having a NULL value.

Example:- Table with NOT NULL constraint:-

In this above table student_4 St_name field/column has NOT NULL constraint.

NOTE:-More than one field/column in the table can have a NOT NULL constraint defined.

CHECK:-

It is used to restrict the value of a column between a range.

It perform check on the value, before storing them into the database.

Its like condition checking before saving data into a column.

Example:- Table with CHECK constraint:-

DEFAULT Constraint:

The DEFAULT constraint provides a default value for a column when no value is specified.

UNIQUE:-

It ensure that a column will only have unique value.

A unique constraint field can not have any duplicate data.

Example:- Table with UNIQUE constraint:-

MCQ related all about SQLite3 in Python:-

Question 1. What is SQLite in Python?
A. A type of operating system
B. A database engine
C. A programming language
D. A web server

Answer: A database engine

Question 2. Which Python module is used to interact with SQLite databases?
A. sqlite
B. sqlite3
C. sqlmodule
D. sqliteconnector

Answer: B. sqlite3

Question 3. Which method is used to create table in sqlite3 in Python?
A.cursor.create_table()
B. cursore.table()
C.cursore.execute()
D.cursore.create()

Answer:c. cursore.execute()

Question 4. Which method is used to execute SQL queries in SQLite3 in Python?
A. run()
B. exec()
C. executequery()
D. execute()

Answer:D. execute()

Question 5. What is the correct way to close the database connection in SQLite3 in Python?
A. db.close()
B. db.stop()
C. db.shutdown()
D. db.exit()

Answer:A. db.close()

Question 6. How do you create a new SQLite3 database in Python?
A. db=sqlite3.new(‘database.db’)
B. db=sqlite3.connect(‘database.db’)
C. db=sqlite3.open(‘database.db’)
D. db=sqlite3.create(‘database.db’)

Answer:B sqlite3.connect(‘database.db’)

Question 7. Which of the following is not a constraint?
A. UNIQUE
B. Primary Key
C. OVER
D. CHECK

Answer:C OVER

Question 8. Which is correct about primary key?
A. primary key contain duplicate value.
B. Primary key contain Null Value.
C. Primary key is a unique identification of records/row.
D. Primary key is a unique identification of field/column.

Answer:C. Primary key is a unique identification of records/rows.

Question 9. Which is the correct syntax of insert data into table in SQLite3 in Python.
A. Con.execute(”insert into table_name()”)
B. Con.execute(insert from table_name())
C. Con.execute(inset into table_name())
D. Con.execute(‘append into table_name()’)

Answer:A. Con.execute(“insert into table_name()”)

Question 10. Which option is correct for difference between primary key and unique key?
A. Primary key accept null value but unique key not accept null value.
B. Primary key not accept null value but unique key accept null value.
C. A table contain more than one primary key as well as unique.
D. A table contain one unique key but more than one primary key.

Answer:B. Primary key not accept null value but unique key accept null value.

ALL About for me

Copyright © studymoral.com, All Rights Reserved.