Connecting SQLite with Python: A Step-by-Step Guide

Connecting SQLite with Python

SQLite is a lightweight, fast, and serverless database engine that’s ideal for small to medium-sized applications. Python’s built-in support for SQLite makes database operations seamless and efficient. This guide walks you through the process of connecting SQLite with Python, creating tables, and performing basic database operations.

Complete Python Course with Advance topics:-Click here

1. Prerequisites

Before we dive in, ensure you have Python and SQLite installed on your system. Follow the steps below for installation.

2. Install Python

To install or update Python on your machine, do the following commands:

sudo apt-get update  
sudo apt-get upgrade python  

When prompted, press y. The installation will complete within seconds.

3. Install SQLite

To install SQLite, use the following command:

sudo apt-get install sqlite3 libsqlite3-dev  

Verify the installation by entering:

sqlite3

You should see a prompt with the SQLite version.

4. Create a Database

Navigate to your desired folder and create a database file:

sqlite3 database.db

This creates a file named database.db in the specified folder. To verify its creation, enter the SQLite prompt and type:

.databases

Note: Starting from Python version 2.5.x, the SQLite connection module (sqlite3) is included by default, so no additional installation is required.

5. Connecting SQLite with Python

Make a file called connect.py and add the following code to link SQLite with Python:

#!/usr/bin/python  

import sqlite3  

# Connect to the database
conn = sqlite3.connect('example.db')  

print("Opened database successfully")

Execute the script:

python connect.py

This establishes a connection with the database and confirms it with a success message.

6. Create a Table

To create a table in the database, write the following code in a file named createtable.py:

#!/usr/bin/python  

import sqlite3  

conn = sqlite3.connect('example.db')  
print("Opened database successfully")

# Create a table
conn.execute('''CREATE TABLE Employees
                (ID INT PRIMARY KEY NOT NULL,
                 NAME TEXT NOT NULL,
                 AGE INT NOT NULL,
                 ADDRESS CHAR(50),
                 SALARY REAL);''')

print("Table created successfully")  
conn.close()

Run the script:

python createtable.py

This creates a table named Employees in the example.db database.

7. Insert Records

To insert records into the Employees table, create a file named insert.py with the following code:

#!/usr/bin/python  

import sqlite3  

conn = sqlite3.connect('example.db')  
print("Opened database successfully")

# Insert records
conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
              VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00)")

conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
              VALUES (2, 'Allen', 22, 'London', 25000.00)")

conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
              VALUES (3, 'Mark', 29, 'California', 200000.00)")

conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
              VALUES (4, 'Kanchan', 22, 'Ghaziabad', 65000.00)")

conn.commit()
print("Records inserted successfully")
conn.close()

Run the script:

python insert.py

The records are now inserted into the Employees table.

8. Select Records

To fetch and display the records, create a file named select.py with the following code:

#!/usr/bin/python  

import sqlite3  

conn = sqlite3.connect('example.db')  
print("Fetching records:")

# Fetch records
data = conn.execute("SELECT * FROM Employees")

for row in data:  
    print(f"ID = {row[0]}")
    print(f"NAME = {row[1]}")
    print(f"AGE = {row[2]}")
    print(f"ADDRESS = {row[3]}")
    print(f"SALARY = {row[4]}\n")  

conn.close()

Run the script:

python select.py

This script displays all records in the Employees table.

9. Update and Delete Records

You can also perform update and delete operations. For instance:

Update Example:

conn.execute("UPDATE Employees SET SALARY = 30000.00 WHERE ID = 1")
conn.commit()

Delete Example:

conn.execute("DELETE FROM Employees WHERE ID = 1")
conn.commit()

10. Wrapping Up

By following these steps, you can effortlessly use SQLite with Python for database operations. This guide serves as a foundation for developing more complex database-driven applications.

Download New Real Time Projects :-Click here
PHP PROJECT:- CLICK HERE

sqlite3 python
python sqlite3 w3schools
Connecting SQLite with Python
Connecting SQLite with Python
python sqlite3 tutorial
pip install sqlite3
install sqlite3 python
python: sqlite3 connect to database with password
sqlalchemy sqlite
connect to sqlite database python sqlalchemy
connecting sqlite with python pdf
Connecting SQLite with Python: A Step-by-Step Guide
connecting sqlite with python w3schools
connecting sqlite with python example