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

Connecting SQLite with Python

SQLite is a compact, serverless database system that can be used for storing and managing application data. It is suitable for small and medium-sized applications where a separate database server is not required. Python also provides built-in support for SQLite through the sqlite3 module, making it possible to work with databases directly from Python programs.

In this tutorial, you will learn how to connect SQLite with Python, create a database table, add records, retrieve stored information, and perform update and delete operations.

1. Prerequisites

Before working with SQLite and Python, make sure that Python and SQLite are available on your system. The following steps explain the required setup and basic installation process.

2. Install Python

If Python needs to be installed or updated on your system, use the following commands:

sudo apt-get update
sudo apt-get upgrade python

When the terminal asks for confirmation, enter y and press Enter. The required packages will then be installed or updated.

3. Install SQLite

SQLite can be installed from the terminal using the following command:

sudo apt-get install sqlite3 libsqlite3-dev

After installation, verify that SQLite is working by entering:

sqlite3

If the installation is successful, the terminal will open the SQLite command prompt and display the installed SQLite version.

4. Create a Database

Move to the directory where you want to store the database and execute:

sqlite3 database.db

This command creates a database file named database.db in the selected directory. If the file does not already exist, SQLite creates it automatically.

To check the database information from the SQLite prompt, use:

.databases

This displays the database currently connected to the SQLite session.

Note: Starting from Python version 2.5.x, the SQLite connection module sqlite3 is included by default, so an additional installation of the Python SQLite module is not required.

5. Connect SQLite with Python

After preparing the database environment, Python can be connected to SQLite using the built-in sqlite3 module.

Create a Python file named connect.py and add the following code:

#!/usr/bin/python

import sqlite3

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

print("Opened database successfully")

Run the program using:

python connect.py

The sqlite3.connect() function establishes a connection with the database file. If the specified database does not exist, SQLite can create the file when the connection is opened.

6. Create a Table

Once the connection is established, you can create tables for storing application records. Create a file named createtable.py and use:

#!/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()

Execute the file with:

python createtable.py

This creates an Employees table inside example.db. The table contains fields for an employee’s ID, name, age, address, and salary.

7. Insert Records

After creating the table, records can be added using SQL INSERT statements. Create a file called insert.py:

#!/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 INSERT statements add four employee records to the database. The commit() call saves the changes to the database.

8. Select Records

To read the stored employee information, create a file named select.py. The following program executes a SELECT query and prints each returned row:

#!/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()

Execute it with:

python select.py

The program retrieves the rows from the Employees table and displays the individual column values for each employee.

9. Update and Delete Records

SQLite also allows existing information to be modified or removed through SQL statements.

Update Example

The following statement changes the salary of the employee whose ID is 1:

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

The UPDATE statement changes the selected record, while commit() saves the modification.

Delete Example

To remove the employee record having ID 1, use:

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

The DELETE statement removes the matching record from the table, and the committed transaction makes the change permanent in the database.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT

10. Wrapping Up

Connecting SQLite with Python provides a straightforward way to introduce database functionality into Python applications. The sqlite3 module provides the connection interface, while SQL commands can be used to create tables and manage stored information.

In this guide, you learned the basic workflow for connecting Python to SQLite, creating an Employees table, inserting records, retrieving data, updating existing values, and deleting records.

These operations provide a foundation for developing Python applications that need local database storage and basic data management.

FAQs

1. What is SQLite in Python?
SQLite is a lightweight database system that can be used with Python through the built-in sqlite3 module.

2. How do I connect SQLite with Python?
Use sqlite3.connect() to establish a connection with an SQLite database file.

conn = sqlite3.connect('example.db')

3. Do I need to install the sqlite3 Python module separately?
No. The sqlite3 module is included with Python, so a separate pip installation is generally not required.

4. How can I create a table using Python SQLite?
Use the CREATE TABLE SQL statement through the database connection.

conn.execute("CREATE TABLE Employees (...)")

5. How do I save changes made to an SQLite database?
Use the commit() method after operations such as INSERT, UPDATE, or DELETE.

conn.commit()

6. How can I retrieve records from SQLite using Python?
Execute a SELECT query and iterate through the returned records.

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

for row in data:
    print(row)

Keywords: Connecting SQLite with Python, sqlite3 python, python sqlite3 w3schools, 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,Connecting SQLite with Python