Sqlite3
Terminology:
IT - In Terminal
IP - IN sqlite3 prompt terminal
Create Database:
IT - sqlite3 [Link]
To dump data from db to text file:
(IT) sqlite3 [Link] .dump > [Link]
To read the data from dump:
(IT) sqlite3 [Link] < [Link]
Attach & Detach Database:
When we connect to the db, we will connect to the main db metnioned (sqlite3
db_name.db). Inorder to connect to multiple databases, we use the attach keyword
ATTACH database db_name as Alias_name; -> (alias must not be main or temp)
DETACH database alias_name;
DDL(Data Definition Language):
1. CREATE: Creating a table in the database
Example:
CREATE table alias.table_name (
columns datatype constraints;
)
Example2:
CREATE TABLE table_name IF NOT EXISTS (
username varchar(25) primary key,
password varchar(25) not null
);
We use alias.table_name to create tables in other databases(secondary),
but can use table_name if intended in the same db
.schema -> To see the schema of the tables in the database
2. DROP: Used to drop the table from the database
Example:
DROP table alias.table_name;
3. ALTER: This is used to modify the attributes of a table (COLUMNS &
RENAME). It doesn;t support modifying the existing columns.
Syntax:
ALTER TABLE table_name action;
Example:
1. ALTER TABLE users rename to auth_users;
2. ALTER TABLE users add column age int;
4. Views: They are replica of a subset of the database table. They are used
to avoid locking of the main table.
* CREATE:
CREATE VIEW view_name as SELECT col1, col2 from table_name where
condition;
* DROP:
DROP VIEW view_name;
DML(Data Manipulation Language):
1. Insert: Inserts data into the table
For table: (username, password)
Example:
1. INSERT INTO users (user, password) values ('admin',
'admin@123'), ('user1', 'user1@123');
2. INSERT INTO users values ('user2', 'user2@123')
3. INSERT INTO usernames from SELECT username from users where
password not null; -> Here we can't use the values.
2. UPDATE: Updates rows in the tables based on any defined conditions
Example:
UPDATE table_name SET column_name=(column_1 + column_2) where
conditions;
3. DELETE: Deletes the row from the table
Example:
DELETE from table_name where conditions;
OPERATORS: These are used in expressions for evaluating conditions.
1. LIKE: This operator is used to match if the text matches the given regex.
Here there are only 2 values (% & _).
% -> 0 or more any characters
_ -> 1 any character
This is case-insensitive
Example:
SELECT * from users where username like 'user*'; -> Fetches
details of users whose username starts with 'user'
2. GLOB: This is also a pattern matching operator which is 'Case-Sensitive'.
Here also there are only 2 values (* & ?).
* -> 0 or more of any character
? -> 1 anny character
Example:
SELECT * from users where username glob 'user*';
DQL(Data Query Language):
1. SELECT: This is used to select the columns from the table in the database
Example:
select * from users; -> Select all the columns from the user table
To view the tables in the database:
select tbl_name from sqlite_master where type="table";
To query sql_schema or any other information, query the sqlite_master
table
2. WHERE: This is used with SELECT to apply conditions or filters while
fetching data
Example:
SELECT * from users where username like 'user%' -> Fetches
username & password where username starts with 'user'
3. LIMIT & OFFSET: Limit is used to limit the number of rows that are
displayed or returned. Offset is used to determine the starting row position in the
returned values
Example:
SELECT column_1, column_2 from table_name limit number_of_rows
OFFSET row_pos;
4. ORDER BY: This operator is used to sort the rows based on the values in
the columns.
Example:
SELECT column_1, column_2 from table_name order by username asc;
-> Fetches column1 & column2 from table in the ascending order of username
5. GROUP BY: This is used to group the rows based on the columns.
Example:
select sum(salary_paid) as "Amount Withdrawn" from users group by
username -> Obtains the unique users and adds the salary_paid column for each user
returning as 'Amount Withdrawn'
6. HAVING: This operator is used to apply condition on the results of 'group
by' clause.
Example:
SELECT * from users group by username where count(username) > 2;
-> Selecting those users who are mentioned more than twice
7. DISTINCT: This operator fetches the unique values of the column.
Example: select distinct name, age from users; -> Fetches the distinct
pairs of (name, age).
CONSTRAINTS: They are the conditions set on the columns, when defining the table.
1. NOT NULL: Makes sure that the column value is not an empty or undefined
value.
2. UNIQUE: Makes sure that the values of the particular column are unique.
3. PRIMARY KEY: A column that is used for the purpose of identification and
indexing by the engine. There can be only 1 primary key.
If there are more than 1 columns for primary key then it is composite
key.
4. DEFAULT: This constraint determines the default value of the column if no
data is provided.
Example:
CREATE TABLE users (
username varchar(25) primary key,
age int default 18
)
5. CHECK: This is used to set certain conditions on the column data.
Example:
CREATE table table_name (
username varchar(25) primary key,
age int not null default 18,
aadhar_number uniq not null,
salary real chack (salary > 0.0)
)
6. AUTOINCREMENT: This is used only with integers. It automatically
increments the value of the corresponding row in the column.
Syntax:
CREATE TABLE table_name (
id int autoincrement;
)
(NOTE): We can add constraints after defining column names while creating the
table.
Example:
CREATE TABLE table_name (
name char(30);
id varchar(15),
aadhar_number int,
primary key(id),
unique (aadhar_number)
)
JOINS: They are used to obtain the rows in multiple tables that follow a certain
condition.
1. CROSS JOIN: This JOIN obtains m * n rows (m, n -> number of rows in table1
and table2). It fetches all the possible combinations from the tables involved.
It doesn't require the keyword 'ON'
Example:
SELECT * from users cross join orders;
2. INNER JOIN: This join creates a new result table by combining values of 2
tables based on the condition. If the condition is satisfied the common values for
each matched pair of rows of A and B are combined into a result row.
Syntax:
SELECT columns from table1 inner join table2 on condition;
Example:
select * from users as u inner join orders as o on [Link] = [Link];
3. OUTER JOIN: This is used to fetch all the rows in left table, and the
corresponding common rows in right table. If the right table has no common row for
a given row in right table it will result in NULL.
Syntax:
SELECT columns from left_table as lt left outer join right_table
as rt on lt.col_val = rt.col_val
Example:
SELECT col1, col2 from users as u left outer join orders as o on
[Link] = [Link];
UNION: The operator is used to combine the results of 2 or more sub-queries. There
are certain conditions to be met:
Conditions:
1. The number of rows from result in each sub-query must be the same.
2. The column names of the first sub-query are teh resultant column
names.
3. The datatype must belong to the same type.
4. Duplicate results are removed unless 'UNION ALL' is specified.
5. 'ORDER BY' is not used in immediate queries only in final query.
Syntax:
SELECT col1, col2, col3 from users union col4, col5, col6 from orders
union col7, col8, col9 from shipment;
Example:
SELECT username, name from users union orderId, orderName from orders
union shipId, shipName from shipment;
TRIGGERS: Triggers are used to perform a set of commands, after a
transaction(INSERT/UPDATE/DELETE). The trigger consists of timing & set of commands
to be run.
The old data can be accessed in the triggers via (old) & new data can be
accessed via (new) in the triggers.
Syntax: CREATE TRIGGER trigger_name [BEFORE|AFTER] action on table_name BEGIN
trigger commands
END;
Example:
CREATE TRIGGER audit_log after INSERT on company BEGIN
insert into audit(empid, date) values ([Link], datetime('now'))
END;
Dropping a trigger: DROP TRIGGER trigger_name
INDEX: This is a table or data structure that is used to improve the engine's
efficiency in data lookups.
(NOTE): Indexes are not advised when table is small or R/W is high.
It indexes the column with the row address improving the efficiency.
Syntax:
CREATE INDEX index_name on table_name (column1, column2);
For creating UNIQUE index:
CREATE UNIQUE INDEX index_name on table_name (c1, c2);
IMPLICIT INDEX: This type of indexing is done for the complete table. It uses
primary key and unique constraints.
Dropping Index: DROP INDEX index_name;
INDEXED BY: This is used to query the specified index instead of the one that the
engine automatically selects.
Syntax:
SELECT * from table_name indexed by index_name;
(Note): If you don't want the index to be used, then declare not indexed.
SELECT * from table_name not indexed where condition;
TRANSACTIONS: They are a list of commands that are cluubed together. They start
with a begin and complete with an COMMIT.
Example:
BEGIN;
COMMAND-1;
COMMAND-2;
COMMIT;
Transaction follows the ACID properties (Atomicity, Consistency, Isolation,
Durability)
A transaction can be rolled back using the `ROLLBACK;`
EXPLAIN & EXPLAIN QUERY PLAN:
The EXPLAIN query will compile the statement and provide a series of low
level instructions that are performed while executing the command.
In case of EXPLAIN QUERY PLAN it compiles the statement and provides a series
of high-level understandable instructions along with strategy of solving that
command.
Syntax:
EXPLAIN (Query);
EXPLAIN QUERY PLAN (Query);
VACCUM: This is used to clear any free pages and clear the main database. It aligns
the table data to be contigous and cleans file structures.
It builds the database again from scratch, so no operations are permitted.
Syntax:
sqlite3 database_name "VACCUM;"
VACCUM;
VACCUM table_name;
FORIEGN KEY: This is used to link 2 or more tables. The foreign keys must be
primary keys in the parent table.
Syntax:
CREATE TABLE users (
userId varchar(25) primary key,
name char(25) not null,
age int not null
)
CREATE TABLE orders(
orderId varchar(15) primary key,
user_id references users(userId),
order_details varchar(50) default "None"
)
ORM (OBJECT RELATION MAPPING):
The library used is `sqlalchemy` as sql
Creating the Engine:
1. create_engine: We use the create_engine function with url and optional
params to connect with the database.
Syntax:
engine = sql.create_engine(url, params)
URL -> type_of_db+library+path_of_file
path_of_file: If :memory then temporary one created else file is
selected
Example:
engine = sql.create_engine("sqlite+pysqlite:////[Link]", echo=True)
(or)
engine = sql.create_engine("sqlite+pysqlite:////:memory")
CORE (Not ORM):
Here, we use the concept of Column, Table and MetaData object
** NOTE: This is not ORM as it is not pythonic way rather this is the
database way
Example:
from sqlalchemy import MetaData, CheckConstraint, text
from sqlalchemy import create_engine, Table, Column, Integer, Text,
VARCHAR, ForeignKey, FLOAT
metadata_obj = MetaData()
userTable = Table("UsersTable", metadata_obj,
Column(name="userId", type_=VARCHAR(30), primary_key=True,
nullable=False),
Column(name="email", type_=VARCHAR(30), unique=True,
nullable=False, index=True),
Column(name="password" ,type_=VARCHAR(30), nullable=False),
Column(name="name", type_=Text(35), nullable=False),
Column(name="age", type_=Integer, nullable=False)
)
orderTable = Table("orders", metadata_obj,
Column(name="orderId", type_=VARCHAR, primary_key=True),
Column("customerId", ForeignKey("[Link]"),
nullable=True),
Column("price", type_=FLOAT, nullable=False, default=0.0)
)
engine = create_engine("sqlite:///databases/[Link]", echo=True)
metadata_obj.create_all(engine)
with [Link]() as connection:
[Link](text("PRAGMA foreign_keys = ON;"))
ORM:
This is the pythonic way, where we use classes and methods along with the
database. Here instead of the MetaData class we use DeclarativeClass which also
acts in a similar way
Example:
from sqlalchemy import ForeignKey, Integer, create_engine
from [Link] import DeclarativeBase, relationship
from [Link] import mapped_column, Mapped
class Base(DeclarativeBase):
pass
class Order(Base):
__tablename__ = "Orders"
orderId: Mapped[str] = mapped_column(Integer, primary_key=True,
nullable=False)
class User(Base):
__tablename__ = "UserTable"
userId: Mapped[str] = mapped_column(primary_key=True, index=True,
nullable=False)
orderId: Mapped[List["Orders"]] = relationship("Orders",
back_populates=None)
age: Mapped[int | None] = mapped_column()
name = Column("name", VARCHAR(30), nullable=False)
engine = create_engine("sqlite:///databases/[Link]", echo=True)
[Link].create_all(engine)
Table Reflection:
Instead of declaring the created table everytime for any inserts or other, we
can re-initialize using this simple approach.
from sqlalchemy import MetaData
metadata = MetaData()
table_name = Table("table_name", metadata, autoload_with=engine)