Python sqlite3 database

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
Between	Records matching a range using between
Case	Grouping Records based on matching conditions
check	Add, update Constraint CHECK for data validation
insert	Add records to table
IIF	Condition checking
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
check(date)	Add, update Constraint CHECK for date validation
SQLite Connector	Tkinter Application to Manage Database and create new database
SQLite Blob	Managing binary data by using Blob Column
Pygsheets	Data from SQLite to Google sheet and vice-versa using Pands DataFrame
Excel	Data from SQLite to MS Excel and vice-versa using Pandas DataFrame

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

Using Tkinter : Tkinter provides a GUI environment for users. We can use Tkinter window to manage SQLite database by developing different applications. Check here for Tkinter and SQLite applications.

Creating Database

import sqlite3
my_conn = sqlite3.connect('my_db')
print("Created database successfully");

Drop Database

As SQLite is a file based database, we can delete the file to remove the Database. Here is the code to delete any file from the system.

import os
path='D:\\testing\\my_db\\my_db.db' # update your path
try:
    os.remove(path) # delete file my_db.db 
except OSError as e:
    print(e) # Specific error message 
    print ("Failed to delete %s " % path)
else:
    print ("Successfully deleted the file %s " % path)

Creating table

my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender 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, 'gender', 'text', 0, None, 0)

Getting column names of the table as list

l1=my_conn.execute("PRAGMA table_info(student);")
columns=[r[1] for r in l1] # list of column headers of the table

Adding records to student table

r_set=my_conn.execute('''INSERT INTO `student` (`id`, `name`, `class`, `mark`, `gender`) 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');''')

Full code to copy and add all rows to student table is here

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`, `gender`) 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

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

Python & SQLite3

SQLAlchemy

Basic template using SQLAlchemy with error handling

Managing Date

Creating Database

Drop Database

Creating table

table structure

Getting column names of the table as list

Adding records to student table

Displaying records from student table

Total number of records in a table

adding a single record

getting id of last inserted row

Deleting records using condition

Listing of all tables in the database

Listing of all tables with number of records in the database

Subscribe