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

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")

Advantage of SQLalchem

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.

try:
    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")
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

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'),
(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');''')
my_conn.commit()

Displaying records of student table

r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
    print(row)

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 or dropping student table

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

View and Download sqlite-connection ipynb file ( .html format )

Download my_db.db, - SQLite Database with sample tables

Sqlite insert delete update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

SQLite connection and creating table