Python SQLite

SQLite

It is an RDBMS (Relational Database Management System). It is an embedded, serverless, transactional SQL database engine. It is an open-source application. It is named SQLite because of its lightweight. It is less than 500kb, unlike many other database systems such as SQL and Oracle. It was designed in August 2000.

Why do we use SQLite?

• It is serverless. It does not need different servers to operate.
• It is very flexible. It allows you to work on multiple databases at the same time.
• It is free. It does not require any license to work on it.
• It doesn’t require configuration. We can start working on it just by installing it.
• It is a cross-platform DBMS.
• We can easily store data.
• It also provides a lot of APIs.

Advantages of SQLite

• It is a very lightweight database.
• It has better performance. Operations are very fast and easy to perform for SQLite databases.
• It is very easy to use. We don’t have to install it. We can download the SQLite library.
• It is very reliable. Since it updates your content regularly, you don’t have to keep track of losing data.
• It can access through any third-party tool.

Disadvantages of SQLite

• It cannot handle high-traffic HTTP requests.
• Its database size is very restricted.

SQLite Command

1. DDL: Data Definition Language
   • Create
   • Alter
   • Drop
2. DML: Data Manipulation Language
   • Insert
   • Update
   • Delete
3. DQL: Data Query Language
   • Select

SQLite Datatype

SQLite data types are used to represent the type of data of any object. In the database, each column, variable and expression are the type of database. These data types are used when we create a table. In SQLite, the data type is associated with the value itself but not the container in which it is stored.

Types of SQLite Data Types

1. SQLite Storage Class
   • NULL: It is used to represent a null value.
   • INTEGER: It is used to represent the integer value of 1, 2, 3, 4, 6 and 8 bytes.
   • REAL: It represents a floating point value, stored as an 8-byte IEEE value.
   • text: It is used to represent a string value.
   • BLOB: It is used to represent data in a blob value, storing it as the data itself.
2. SQLite Afinity Class
   • TEXT: This column is used to store data using the storage class data type( TEXT, NULL, BLOB).
   • NUMERIC: This column contain all five storage class data type.
   • INTEGER: This behaves similarly to the numeric affinity class.
   • REAL: It behaves the same as the numeric affinity class. In this, there is an exception that the integer type is forcefully converted into a floating type.
   • NONE: In this column class, we don’t prefer one storage class with another one.

Connecting SQLite with Python

To install the SQLite library, we use the following command.

sudo apt-get install sqlite3 libsqlite3-dev  

After installing SQLite, we can use the command to start prompting the library is:

sqlite3

After that, we can create a folder, and we can use the command

sqlite3 database_name.db

To check if the database is created, we can use the command

.databases

Example of using SQLite

# file location
  
import sqlite3  
 
conn = sqlite3.databs('tutorial.db')  
  
print "Opened database successfully";  
conn.execute('''''CREATE TABLE Employees 
       (ID INT PRIMARY KEY NOT NULL, 
       NAME  TEXT    NOT NULL, 
       AGE INT NOT NULL, 
       ADDRESS CHAR(50), 
       SALARY REAL);''')  
print "Table is created";  
  
conn.close()  

Explanation

In this example, we will create a python file databs.py and use the following code. After that, we created a database name as tutorial.db, and we can create a table name as an employee after connecting this database.

Inserting data into the database

import sqlite3  
  
conn = sqlite3.connect('tutorial.db')  
print "Opened database successfully";  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (1, 'Aryan', 22, 'Delhi', 40000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (2, 'Ben', 22, 'London', 25500.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (3, 'Alan', 25, 'CA', 500000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (4, 'Krish', 27, 'Gujrat ', 45000.00 )");  
  
conn.commit()  
print "Records inserted successfully";  
conn.close()  

Selecting the data from the database

import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
  
data = conn.execute("select * from Employees");    
for row in data:  
   print "ID = ", row[0]  
   print "NAME = ", row[1]  
   print "ADDRESS = ", row[2]  
   print "SALARY = ", row[3], "\n"  
  
conn.close();