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 KEY	UNIQUE 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:-

ALL About for me