SQLite
INTRODUCTION
· SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
· It is a database, which is zero-configured, which means like other databases you do not need to configure it in your system.
· SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. SQLite accesses its storage files directly.
REASONS TO CHOOSE SQLite
· SQLite does not require a separate server process or system to operate (serverless).
· SQLite comes with zero-configuration, which means no setup or administration needed.
· A complete SQLite database is stored in a single cross-platform disk file.
· SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.
· SQLite is self-contained, which means no external dependencies.
· SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.
· SQLite supports most of the query language features found in SQL92 (SQL2) standard.
· SQLite is written in ANSI-C and provides simple and easy-to-use API.
· SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT).
COMMANDS
1. Data Definition Language (DDL)
· A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database.
· These database objects include views, schemas, tables, indexes, etc.
I. CREATE
Creates a new table, a view of a table, or other object in database.
Syntax:
CREATE TABLE table_name(
Col_name1 datatype(),
Col_name2 datatype(),…
Col_namen datatype(),
);
II. ALTER
Modifies an existing database object, such as a table.
a. ADD
Adding a new column to the existing table.
Syntax:
ALTER TABLE table_name
ADD Col_name datatype()…;
b. MODIFY
Modify a datatype in an existing table.
Syntax:
ALTER TABLE table_name
MODIFY (fieldname datatype()…);
III. DROP
Deletes an entire table, a view of a table or other object in the database.
Syntax:
DROP Table name;
2. Data Manipulation Language (DML)
· DML stands for Data Manipulation Language.
· It is a language used for selecting, inserting, deleting, and updating data in a database.
· It is used to retrieve and manipulate data in a relational database.
I INSERT
Used to insert values in a record.
Syntax:
INSERT INTO Table_Name VALUES();
II UPDATE
Update command is used to update any value from any table.
Syntax:
UPDATE <table name> set to(calculation);
III DELETE
Delete query is used to delete a row from a table.
Syntax:
DELETE FROM <table_name>
IV SELECT
Select query is used to fetch the data from tables.
Syntax:
SELECT * FROM <table_name>
SQLITE IN PYTHON
· SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring.
· To use sqlite3 module, you must first create a connection object that represents the database.
Python sqlite3 module APIs
· sqlite3.connect(database)
This API opens a connection to the SQLite database file.
· connection.execute(sql)
This routine is a shortcut of the above execute method provided by the cursor object and it creates an intermediate cursor object by calling the cursor method, then calls the cursor’s execute method with the parameters given.
· connection.commit()
This method commits the current transaction.
· connection.close()
This method closes the database connection.
APPLICATION
· Embedded devices and the internet of things
· Application file format
· Websites
· Data analysis
· Server-side database
· Data transfer format
· Education and Training
LIMITATIONS
· Doesn’t support Right outer join and Full outer join.
· Doesn’t support Drop column, alter column, add constraint commands.
· Doesn’t support Each statement triggers
· Views in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
· Grant and Revoke commands are not supported.
