Python SQLite

Introduction to SQLite

SQLite is a lightweight, self-contained, serverless SQL database engine. It is highly portable, requires minimal setup, and is ideal for embedded database applications. SQLite databases are stored in a single file on the disk, making it easy to manage and share.

Setting Up SQLite

Python comes with SQLite support built-in via the sqlite3 module. You do not need to install any external libraries.

Connecting to SQLite Database


import sqlite3

# Connecting to SQLite database (or creating it if it doesn't exist)
connection = sqlite3.connect('university.db')
cursor = connection.cursor()
print("Connected to SQLite Database successfully!")

Output

Connected to SQLite Database successfully!

Creating Tables in SQLite


# Creating a 'students' table
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    major TEXT
)
''')
connection.commit()
print("Table created successfully!")

Output

Table created successfully!

Inserting Data into SQLite


# Inserting a new student into the table
cursor.execute("INSERT INTO students (name, age, major) VALUES ('Jane Doe', 22, 'Computer Science')")
connection.commit()
print("Data inserted successfully!")

Output

Data inserted successfully!

Fetching Data from SQLite


# Fetching all records from the 'students' table
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()

for row in rows:
    print(row)

Output

(1, 'Jane Doe', 22, 'Computer Science')

Explanation

Connection: Establishes a connection to the SQLite database file.
Cursor: Used to execute SQL queries.
Table Creation and Data Manipulation: Demonstrates how to create tables and insert, update, or fetch records.

Closing the Connection

Always close the database connection after completing operations:


connection.close()
print("SQLite connection closed.")

Output

SQLite connection closed.