Python sqlite Connection and creation of database and tables

SQLite

Database Name: my_db.db
Table Name : student,category, subcategory

Download the SQLite database my_db.db with sample tables at the end of this page

First create the database.

import sqlite3
my_conn = sqlite3.connect('my_db.db')
print("Connected to database successfully")

Output ( A new database is created or a connection is established to the existing database if available )

Connected to database successfully

Without installing use SQLite database from google Drive by using Colab.

Using SQLAlchemy

In above code we used sqlite3 library to connect and manage SQLite database. We can also use SQLAlchemy to manage SQLite. The script part remain same and only the connection string changes. Here we have created the connection engine ( the variable ) my_conn. We used a common varaible my_conn as output of both connection engines so the script part remain same throughout.

from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

In windows system, the absolute path is used in above code.

Advantage of SQLalchemy

If you want to change to other database ( say MySQL ) then only connection string needs to be changed as by using SQLAlchemy can be used to connect different databases. Here the script part mostly remain same. Easy portability is the main advantages here.

There is a small change at Parameterized query where for the placeholders we have to use %s for MySQL and ? for SQLite.

Creating table

Here is out student table, we used try except to handle any error during the table creation process.
Using SQLAlchemy
Using sqlite3

try:
    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")
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

Student Table created successfully

Listing tables in the database

r_set=my_conn.execute('''select name from sqlite_master 
		where type = 'table' ''')
for row in r_set:
    print(row)

Output ( as we have already created on table in above script, we will get this output )

('student',)

Adding records to student table ( with create table )

SQLAlchemy

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = create_engine("sqlite:///"+ my_path)
try:
    #my_conn.execute('DROP TABLE student')
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("Student Table created successfully..")
try:
  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'),
  (5, 'John Mike', 'Four', 60, 'female'),
  (6, 'Alex John', 'Four', 55, 'male'),
  (7, 'My John Rob', 'Five', 78, 'male'),
  (8, 'Asruid', 'Five', 85, 'male'),
  (9, 'Tes Qry', 'Six', 78, 'male'),
  (10, 'Big John', 'Four', 55, 'female'),
  (11, 'Ronald', 'Six', 89, 'female'),
  (12, 'Recky', 'Six', 94, 'female'),
  (13, 'Kty', 'Seven', 88, 'female'),
  (14, 'Bigy', 'Seven', 88, 'female'),
  (15, 'Tade Row', 'Four', 88, 'male'),
  (16, 'Gimmy', 'Four', 88, 'male'),
  (17, 'Tumyu', 'Six', 54, 'male'),
  (18, 'Honny', 'Five', 75, 'male'),
  (19, 'Tinny', 'Nine', 18, 'male'),
  (20, 'Jackly', 'Nine', 65, 'female'),
  (21, 'Babby John', 'Four', 69, 'female'),
  (22, 'Reggid', 'Seven', 55, 'female'),
  (23, 'Herod', 'Eight', 79, 'male'),
  (24, 'Tiddy Now', 'Seven', 78, 'male'),
  (25, 'Giff Tow', 'Seven', 88, 'male'),
  (26, 'Crelea', 'Seven', 79, 'male'),
  (27, 'Big Nose', 'Three', 81, 'female'),
  (28, 'Rojj Base', 'Seven', 86, 'female'),
  (29, 'Tess Played', 'Seven', 55, 'male'),
  (30, 'Reppy Red', 'Six', 79, 'female'),
  (31, 'Marry Toeey', 'Four', 88, 'male'),
  (32, 'Binn Rott', 'Seven', 90, 'female'),
  (33, 'Kenn Rein', 'Six', 96, 'female'),
  (34, 'Gain Toe', 'Seven', 69, 'male'),
  (35, 'Rows Noump', 'Six', 88, 'female');''')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records added : ",r_set.rowcount)

sqlite3

import sqlite3
my_path="D:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = sqlite3.connect(my_path) 
try:
    #my_conn.execute('DROP TABLE student') # remove table if required
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );''')  
except sqlite3.Error as e:
    print("An error occurred:", e.args[0])
else:
  print("Student Table created successfully..")
try:
  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'),
  (5, 'John Mike', 'Four', 60, 'female'),
  (6, 'Alex John', 'Four', 55, 'male'),
  (7, 'My John Rob', 'Five', 78, 'male'),
  (8, 'Asruid', 'Five', 85, 'male'),
  (9, 'Tes Qry', 'Six', 78, 'male'),
  (10, 'Big John', 'Four', 55, 'female'),
  (11, 'Ronald', 'Six', 89, 'female'),
  (12, 'Recky', 'Six', 94, 'female'),
  (13, 'Kty', 'Seven', 88, 'female'),
  (14, 'Bigy', 'Seven', 88, 'female'),
  (15, 'Tade Row', 'Four', 88, 'male'),
  (16, 'Gimmy', 'Four', 88, 'male'),
  (17, 'Tumyu', 'Six', 54, 'male'),
  (18, 'Honny', 'Five', 75, 'male'),
  (19, 'Tinny', 'Nine', 18, 'male'),
  (20, 'Jackly', 'Nine', 65, 'female'),
  (21, 'Babby John', 'Four', 69, 'female'),
  (22, 'Reggid', 'Seven', 55, 'female'),
  (23, 'Herod', 'Eight', 79, 'male'),
  (24, 'Tiddy Now', 'Seven', 78, 'male'),
  (25, 'Giff Tow', 'Seven', 88, 'male'),
  (26, 'Crelea', 'Seven', 79, 'male'),
  (27, 'Big Nose', 'Three', 81, 'female'),
  (28, 'Rojj Base', 'Seven', 86, 'female'),
  (29, 'Tess Played', 'Seven', 55, 'male'),
  (30, 'Reppy Red', 'Six', 79, 'female'),
  (31, 'Marry Toeey', 'Four', 88, 'male'),
  (32, 'Binn Rott', 'Seven', 90, 'female'),
  (33, 'Kenn Rein', 'Six', 96, 'female'),
  (34, 'Gain Toe', 'Seven', 69, 'male'),
  (35, 'Rows Noump', 'Six', 88, 'female');''')
except sqlite3.Error as e:
    print("An error occurred:", e.args[0])
else:
  print("No of Records added : ",r_set.rowcount)

Displaying records of student table

output will be the rows of records .. ..

(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')

Deleting records

Deleting or dropping student table

Query to delete table from SQLite database by using DROP table in try except error handling code

Using SQLAlchemy
Using sqlite3

try:
  my_conn.execute('''DROP table student;''')
  print("student table deleted")
except sqlite3.Error as my_error:
  print("error: ",my_error)
my_conn.commit()

Create Table with Blob column data type

from sqlalchemy import create_engine
#my_conn = create_engine("sqlite:///my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text, 
                      photo blob
                      );''')

Delete > Create > Add records to student table

Delete ( manually the file ) the database and then run this code. Note that the path in line 3 needs to be changed to match your system. This code will create the database my_db.db inside the sqlite directory. This will print the success message in your console.

my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")

from from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")
try:
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("Student Table created successfully..")
try:
  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'),
  (5, 'John Mike', 'Four', 60, 'female'),
  (6, 'Alex John', 'Four', 55, 'male'),
  (7, 'My John Rob', 'Five', 78, 'male'),
  (8, 'Asruid', 'Five', 85, 'male'),
  (9, 'Tes Qry', 'Six', 78, 'male'),
  (10, 'Big John', 'Four', 55, 'female'),
  (11, 'Ronald', 'Six', 89, 'female'),
  (12, 'Recky', 'Six', 94, 'female'),
  (13, 'Kty', 'Seven', 88, 'female'),
  (14, 'Bigy', 'Seven', 88, 'female'),
  (15, 'Tade Row', 'Four', 88, 'male'),
  (16, 'Gimmy', 'Four', 88, 'male'),
  (17, 'Tumyu', 'Six', 54, 'male'),
  (18, 'Honny', 'Five', 75, 'male'),
  (19, 'Tinny', 'Nine', 18, 'male'),
  (20, 'Jackly', 'Nine', 65, 'female'),
  (21, 'Babby John', 'Four', 69, 'female'),
  (22, 'Reggid', 'Seven', 55, 'female'),
  (23, 'Herod', 'Eight', 79, 'male'),
  (24, 'Tiddy Now', 'Seven', 78, 'male'),
  (25, 'Giff Tow', 'Seven', 88, 'male'),
  (26, 'Crelea', 'Seven', 79, 'male'),
  (27, 'Big Nose', 'Three', 81, 'female'),
  (28, 'Rojj Base', 'Seven', 86, 'female'),
  (29, 'Tess Played', 'Seven', 55, 'male'),
  (30, 'Reppy Red', 'Six', 79, 'female'),
  (31, 'Marry Toeey', 'Four', 88, 'male'),
  (32, 'Binn Rott', 'Seven', 90, 'female'),
  (33, 'Kenn Rein', 'Six', 96, 'female'),
  (34, 'Gain Toe', 'Seven', 69, 'male'),
  (35, 'Rows Noump', 'Six', 88, 'female');''')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records added : ",r_set.rowcount)

Create table category

r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS category(cat_id integer primary key,\
                        category text)")
my_conn.commit()

Add data to category

r_set=my_conn.execute("INSERT INTO `category` (`cat_id`, `category`) VALUES \
(1, 'Fruits'),\
(2, 'Colors'),\
(3, 'Games'),\
(4, 'Vehicles');")
my_conn.commit()

Create table subcategory

r_set=my_conn.execute("CREATE TABLE IF NOT EXISTS subcategory(cat_id integer,\
                        subcategory text)")
my_conn.commit()

Add data to subcategory

r_set=my_conn.execute("INSERT INTO `subcategory` (`cat_id`, `subcategory`) VALUES \
(1, 'Mango'),\
(1, 'Banana'),\
(1, 'Orange'),\
(1, 'Apple'),\
(2, 'Red'),\
(2, 'Blue'),\
(2, 'Green'),\
(2, 'Yellow'),\
(3, 'Cricket'),\
(3, 'Football'),\
(3, 'Baseball'),\
(3, 'Tennis'),\
(4, 'Cars'),\
(4, 'Trucks'),\
(4, 'Bikes'),\
(4, 'Train')")
my_conn.commit()

SQL part only to create table and insert records

The query part to create student table ( without any Python code )

Add records to student table

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'),
  (5, 'John Mike', 'Four', 60, 'female'),
  (6, 'Alex John', 'Four', 55, 'male'),
  (7, 'My John Rob', 'Five', 78, 'male'),
  (8, 'Asruid', 'Five', 85, 'male'),
  (9, 'Tes Qry', 'Six', 78, 'male'),
  (10, 'Big John', 'Four', 55, 'female'),
  (11, 'Ronald', 'Six', 89, 'female'),
  (12, 'Recky', 'Six', 94, 'female'),
  (13, 'Kty', 'Seven', 88, 'female'),
  (14, 'Bigy', 'Seven', 88, 'female'),
  (15, 'Tade Row', 'Four', 88, 'male'),
  (16, 'Gimmy', 'Four', 88, 'male'),
  (17, 'Tumyu', 'Six', 54, 'male'),
  (18, 'Honny', 'Five', 75, 'male'),
  (19, 'Tinny', 'Nine', 18, 'male'),
  (20, 'Jackly', 'Nine', 65, 'female'),
  (21, 'Babby John', 'Four', 69, 'female'),
  (22, 'Reggid', 'Seven', 55, 'female'),
  (23, 'Herod', 'Eight', 79, 'male'),
  (24, 'Tiddy Now', 'Seven', 78, 'male'),
  (25, 'Giff Tow', 'Seven', 88, 'male'),
  (26, 'Crelea', 'Seven', 79, 'male'),
  (27, 'Big Nose', 'Three', 81, 'female'),
  (28, 'Rojj Base', 'Seven', 86, 'female'),
  (29, 'Tess Played', 'Seven', 55, 'male'),
  (30, 'Reppy Red', 'Six', 79, 'female'),
  (31, 'Marry Toeey', 'Four', 88, 'male'),
  (32, 'Binn Rott', 'Seven', 90, 'female'),
  (33, 'Kenn Rein', 'Six', 96, 'female'),
  (34, 'Gain Toe', 'Seven', 69, 'male'),
  (35, 'Rows Noump', 'Six', 88, 'female')

View and Download sqlite-connection ipynb file ( .html format )
Download .zip file with my_db.db, and .ipynb files
Sqlite insert delete update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite connection and creating table