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: