Python sqlite Connection and creation of database and tables

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

Download the SQLite database my_db.db with sample tables and .ipynb files at the end of this page.
OR
Download the database file only from here.
https://www.plus2net.com/python/download/my_db.db

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 different path and executing query to show records.

import sqlite3
my_conn = sqlite3.connect('D:\\testing\\my_db.db')
query="SELECT *  FROM student LIMIT 0,5"
my_data=list(my_conn.execute(query)) # 
print(my_data)

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 remains the same; 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.
Using Current location of the application and adding the relative path of the database.

from sqlalchemy import create_engine,text
import os
dir_name=os.path.dirname(os.path.abspath(__file__)) # current directory name
#print(os.path.join(dir_name,'sqlite\my_db.db'))
#Adding relative location to the current path 
my_conn = create_engine('sqlite:///'+os.path.join(dir_name,'sqlite\my_db.db')) 
my_conn=my_conn.connect()

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 advantage here.

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

There are some changes in recent version of SQLAlchemy. You may get error message like this,

AttributeError: 'Engine' object has no attribute 'execute'

my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")")
my_conn=my_conn.connect() # add this line

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object:
We have to import text before using the same.

from sqlalchemy import create_engine,text

query="SELECT *  FROM student LIMIT 0,5"
my_data=list(my_conn.execute(text(query)))

Using Parameterized query

query=text("SELECT *  FROM student_address  WHERE id=:id")
my_data={'id':2} # dictionary with key and value pair
my_cursor=my_conn.execute(query,my_data)
data_row=my_cursor.fetchone() # tuple with all column data 
print(data_row[1])

my_data={'tasks':e1.get(),'status':False} # dictionary 
r_set=my_conn.execute(text("INSERT INTO my_tasks (tasks, status) VALUES(:tasks,:status)"),my_data)

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

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path="E:\\testing\\sqlite\\my_db.db" #Change the path 
my_conn = create_engine("sqlite:///"+ my_path)
try:
    #my_conn.execute('DROP TABLE student3')
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student3(id integer primary key, 
                      name text, 
                      class text, 
                      social int(3) NOT NULL DEFAULT '0',
                      science int(3) NOT NULL,
                      math int(3) NOT NULL
                      );''')  
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("Student3 Table created successfully..")
try:
  r_set=my_conn.execute('''INSERT INTO `student3` 
  (`id`, `name`, `class`, `social`, `science`, `math`) VALUES
  (2, 'Max Ruin', 'Three', 85, 56, 85),
(3, 'Arnold', 'Three', 55, 40, 75),
(4, 'Krish Star', 'Four', 60, 50, 70),
(5, 'John Mike', 'Four', 60, 80, 90),
(6, 'Alex John', 'Four', 55, 90, 80),
(7, 'My John Rob', 'Fifth', 78, 60, 70),
(8, 'Asruid', 'Five', 85, 80, 90),
(9, 'Tes Qry', 'Six', 78, 60, 70),
(10, 'Big John', 'Four', 55, 40, 55);''')
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records added : ",r_set.rowcount)

Create a table with sample dates ( today's date as base date ).

There is no date column in SQLite. Here we used today's date as base date and created rows to store different formatted dates in a table.

from datetime import date,timedelta
import sqlite3

my_data=[] # List to store data
dt=date.today() # Todays date as base date 
for delta in range(-3,3): # change the range values 
    dt2=dt + timedelta(days=delta) # new date object 
    ## One Tuple with three elements is added to list 
    my_data.append((delta,dt2.strftime('%Y-%m-%d'),dt2.strftime('%b-%Y')))
print(my_data) # to check the list with Tuples

my_path='E:\\testing\\my_db2.db' # to store SQLite database 
my_conn = sqlite3.connect(my_path)
## Delete the table if required 
r_set=my_conn.execute('''DROP TABLE table_dt''')
try:
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS `table_dt` (
  `record_id`  INTEGER PRIMARY KEY,
  `my_dt` TEXT NOT NULL,
  `month_year` TEXT 
    );''')
    my_conn.commit()
    print("Table created successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
 
r_set=my_conn.executemany("INSERT INTO table_dt VALUES(?, ?, ?)", my_data)
my_conn.commit()
print("No. of Records added : ",r_set.rowcount)

## Check the data by displaying 
r_set=my_conn.execute('''SELECT * from table_dt ''');
for row in r_set:
    print(row)

Copy this code from Github and run in your Google colab platform to Create and download SQLite database with data
https://github.com/plus2net/Python-basics/blob/main/SQLite_sample_table_date_column_dump.ipynb

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

SQLite table to MySQL

import pandas as pd

from sqlalchemy import create_engine
mysql_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db") #fill details
mysql_conn=mysql_conn.connect() # add this line if error

sqlite_conn = create_engine("sqlite:///C:\\xampp\\htdocs\\dir1\\my_db.db")
sqlite_conn=sqlite_conn.connect()

q="SELECT * FROM y_all WHERE month='24-Apr' " # table with condition
df=pd.read_sql(q,sqlite_conn) # create Pandas DataFrame 

print(df.head()) # check data by displaying top 5 rows 
#df.to_excel('path_to_excel_file',index=False) # create excel file with data 
df.to_sql(con=mysql_conn,name='y_all',if_exists='replace',index=False) # to store in MySQL

Introduction to SQLAlchemy ORM

SQLAlchemy ORM (Object-Relational Mapping) is a powerful tool in Python for interacting with databases. It allows you to manage database tables and relationships using Python objects, eliminating the need for raw SQL queries. This approach enhances maintainability and simplifies database operations.

Learn More About SQLAlchemy ORM

Download .zip file with my_db.db, and .ipynb files
Sqlite INSERT DELETE UPDATE SELECT ORDER By LIMIT IIF

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

	21-02-2023
I am new to tkinter and sqlite3. can i post a question regarding my learning project?

SQLite connection and creating table