Python SQLite: Detailed Notes for BCA Students
What is SQLite?
- SQLite is a lightweight, serverless, self-contained database engine.
- It is embedded in Python using the sqlite3 module.
- Suitable for small to medium applications, prototyping, or learning.
Importing the sqlite3 Module
Before using SQLite in Python, import the module:
import sqlite3
Connecting to a Database: sqlite3.connect()
Syntax:
conn = sqlite3.connect('my_database.db')
- Creates a connection object.
- Creates the database file if it doesn't exist.
Cursor Object: conn.cursor()
Purpose: Allows executing SQL commands.
Syntax:
cursor = conn.cursor()
Executing SQL Commands: cursor.execute()
Python SQLite: Detailed Notes for BCA Students
Syntax:
cursor.execute("SQL_QUERY")
Example:
cursor.execute("CREATE TABLE student (id INTEGER, name TEXT)")
Committing Changes: conn.commit()
Saves all changes to the database.
Syntax:
conn.commit()
Closing the Connection: conn.close()
Closes the database connection.
Syntax:
conn.close()
Creating a Table
SQL:
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
Example:
cursor.execute("""
Python SQLite: Detailed Notes for BCA Students
CREATE TABLE IF NOT EXISTS student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
marks INTEGER
""")
Table Operations
Insert Record:
cursor.execute("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (1, 'Alice', 85))
Select Record:
cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()
for row in rows:
print(row)
Update Record:
cursor.execute("UPDATE student SET marks = 90 WHERE id = 1")
Delete Record:
cursor.execute("DELETE FROM student WHERE id = 1")
Drop Table:
Python SQLite: Detailed Notes for BCA Students
cursor.execute("DROP TABLE IF EXISTS student")
Complete Example
import sqlite3
conn = sqlite3.connect('student.db')
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
marks INTEGER
""")
cursor.execute("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (1, 'Alice', 85))
cursor.execute("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (2, 'Bob', 78))
cursor.execute("SELECT * FROM student")
for row in cursor.fetchall():
print(row)
cursor.execute("UPDATE student SET marks = 90 WHERE id = 1")
Python SQLite: Detailed Notes for BCA Students
cursor.execute("DELETE FROM student WHERE id = 2")
conn.commit()
conn.close()
Summary Table
Operation | Method | Description
------------|----------------------|-------------------------------
Connect | sqlite3.connect() | Connects to the database
Cursor | conn.cursor() | Executes SQL operations
Execute | cursor.execute() | Executes SQL statements
Commit | conn.commit() | Saves changes
Close | conn.close() | Closes connection
Insert | INSERT INTO | Adds new record
Select | SELECT | Retrieves data
Update | UPDATE | Updates existing data
Delete | DELETE | Deletes specific data
Drop | DROP TABLE | Removes the table
