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: [Link]()
Syntax:
conn = [Link]('my_database.db')
- Creates a connection object.
- Creates the database file if it doesn't exist.
Cursor Object: [Link]()
Purpose: Allows executing SQL commands.
Syntax:
cursor = [Link]()
Executing SQL Commands: [Link]()
Python SQLite: Detailed Notes for BCA Students
Syntax:
[Link]("SQL_QUERY")
Example:
[Link]("CREATE TABLE student (id INTEGER, name TEXT)")
Committing Changes: [Link]()
Saves all changes to the database.
Syntax:
[Link]()
Closing the Connection: [Link]()
Closes the database connection.
Syntax:
[Link]()
Creating a Table
SQL:
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
Example:
[Link]("""
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:
[Link]("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (1, 'Alice', 85))
Select Record:
[Link]("SELECT * FROM student")
rows = [Link]()
for row in rows:
print(row)
Update Record:
[Link]("UPDATE student SET marks = 90 WHERE id = 1")
Delete Record:
[Link]("DELETE FROM student WHERE id = 1")
Drop Table:
Python SQLite: Detailed Notes for BCA Students
[Link]("DROP TABLE IF EXISTS student")
Complete Example
import sqlite3
conn = [Link]('[Link]')
cursor = [Link]()
[Link]("""
CREATE TABLE IF NOT EXISTS student (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
marks INTEGER
""")
[Link]("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (1, 'Alice', 85))
[Link]("INSERT INTO student (id, name, marks) VALUES (?, ?, ?)", (2, 'Bob', 78))
[Link]("SELECT * FROM student")
for row in [Link]():
print(row)
[Link]("UPDATE student SET marks = 90 WHERE id = 1")
Python SQLite: Detailed Notes for BCA Students
[Link]("DELETE FROM student WHERE id = 2")
[Link]()
[Link]()
Summary Table
Operation | Method | Description
------------|----------------------|-------------------------------
Connect | [Link]() | Connects to the database
Cursor | [Link]() | Executes SQL operations
Execute | [Link]() | Executes SQL statements
Commit | [Link]() | Saves changes
Close | [Link]() | 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
