SQLite is a file based database with full functional capacity like any other Relational Database. Portability is the main advantage of SQLite database. Using sqlite3 library we can manage SQLite database by using Standard Query languages.
Without installing use SQLite database from google Drive by using Colab.
Connection | Create database and connection details |
insert | Add records to table |
update | Update records of table |
delete | Delete records from table |
distinct | Unique data from columns |
select | SELECT records from table |
limit | Limiting records returned from table |
Order By | Limiting records returned from table |
Like | Matching wildcards to get records from table |
Between | Records matching a range using between |
Case | Grouping Records based on matching conditions |
check | Add, update Constraint CHECK for data validation |
check(date) | Add, update Constraint CHECK for date validation |
SQLAlchemy
delete | Delete records |
insert | Insert single or multiple records |
Basic template using SQLAlchemy with error handling
from sqlalchemy.exc import SQLAlchemyError
q="SELECT * FROM student"
try:
r_set=my_conn.execute(q)
r_list=r_set.fetchall()
except SQLAlchemyError as e:
error=str(e.__dict__['orig'])
print(error)
else:
print("Total Number of rows : ",len(r_list))
Managing Date
For date queries use the fresh copy of SQL dump of date table to apply queries using present date as reference.
Date query | Filter records based on different date values |
Creating Database
import sqlite3
my_conn = sqlite3.connect('my_db')
print("Created database successfully");
Creating table
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key,
name text,
class text,
mark integer,
sex text
);''')
my_conn.commit()
print("Student Table created successfully");
table structure
r_set=my_conn.execute('''PRAGMA table_info(student);''')
for row in r_set:
print(row)
Output
(0, 'id', 'integer', 0, None, 1)
(1, 'name', 'text', 0, None, 0)
(2, 'class', 'text', 0, None, 0)
(3, 'mark', 'integer', 0, None, 0)
(4, 'sex', 'text', 0, None, 0)
Adding records to student table
r_set=my_conn.execute('''INSERT INTO `student` (`id`, `name`, `class`, `mark`, `sex`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
-----------
-----------
(4, 'Krish Star', 'Four', 60, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female');''')
Displaying records from student table
r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
print(row)
Output
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
---------------
---------------
(34, 'Gain Toe', 'Seven', 69, 'male')
(35, 'Rows Noump', 'Six', 88, 'female')
Total number of records in a table
Used count query without any WHERE condition to get total number of records.
r_set=my_conn.execute("SELECT count(*) as no from STUDENT")
data_row=r_set.fetchone()
nume=data_row[0] # 35
adding a single record
r_set=my_conn.execute('''INSERT INTO `student`
( `name`, `class`, `mark`, `sex`) VALUES
('New _name', 'Four', 45, 'female')''')
getting id of last inserted row
We can use last_insert_rowid()
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])
Deleting records using condition
r_set=my_conn.execute('''DELETE FROM STUDENT WHERE class='Four' ''')
Getting number of rows affected or deleted or updated by recent query. We can use changes()
x=my_conn.execute('''select changes()''')
id=x.fetchone()
print(id[0])
Listing of all tables in the database
r_set=my_conn.execute('''select name from sqlite_master where type = 'table' ''')
for row in r_set:
print(row)
Listing of all tables with number of records in the database
r_set=my_conn.execute('''select name from sqlite_master
where type = 'table' ''')
for row in r_set:
r_set=my_conn.execute("SELECT count(*) as no from "+ row[0])
data_row=r_set.fetchone()
print(row[0],"(",data_row[0],")")
Output
student ( 35 )
category ( 4 )
subcategory ( 16 )
insert
Download .zip file with my_db.db, and .ipynb files ⇓