Hackforge Academy

Explanation

SQLite is a lightweight relational database that is built into Python through the sqlite3 module. Unlike MySQL or PostgreSQL, SQLite does not require a separate database server. The database is stored in a local file, which makes SQLite useful for learning, prototypes, desktop applications, testing, and small projects. Python can use SQLite to create databases and tables and perform CRUD operations such as INSERT, SELECT, UPDATE, and DELETE.

Code:

import sqlite3

connection = sqlite3.connect('students.db')

print('Database connected successfully')

connection.close()

Explanation

After connecting to a SQLite database, we can create a cursor object to execute SQL statements. The cursor() method creates a cursor, and the execute() method executes SQL queries. The CREATE TABLE statement is used to create a table. The IF NOT EXISTS clause prevents an error if the table already exists. SQLite supports common data types such as INTEGER, TEXT, REAL, BLOB, and NULL.

Code:

import sqlite3

connection = sqlite3.connect('students.db')
cursor = connection.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    marks INTEGER
)
''')

connection.commit()

print('Students table created')

connection.close()

Explanation

The INSERT statement is used to add records to a SQLite table. Python's sqlite3 module supports parameterized queries using the question mark (?) placeholder. Parameterized queries are safer than building SQL statements using string concatenation because they help prevent SQL injection. After INSERT, UPDATE, or DELETE operations, commit() should be called to permanently save the changes to the database.

Code:

import sqlite3

connection = sqlite3.connect('students.db')
cursor = connection.cursor()

query = '''
INSERT INTO students (name, marks)
VALUES (?, ?)
'''

cursor.execute(query, ('John', 85))
cursor.execute(query, ('Alice', 92))
cursor.execute(query, ('David', 78))

connection.commit()

print('Students inserted successfully')

connection.close()

Explanation

The SELECT statement is used to retrieve records from a SQLite database. The execute() method runs the SELECT query, and fetchall() retrieves all matching records. Other methods such as fetchone() and fetchmany() can be used when only one or a limited number of records are required. The returned records are represented as tuples by default. Retrieved data can then be processed using Python loops and conditional statements.

Code:

import sqlite3

connection = sqlite3.connect('students.db')
cursor = connection.cursor()

cursor.execute('SELECT * FROM students')

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

Explanation

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations performed on database records. In SQLite, Python can execute INSERT queries to create records, SELECT queries to read records, UPDATE queries to modify records, and DELETE queries to remove records. These operations form the foundation of most database-driven applications. Python functions can be used to organize each database operation into reusable components.

Code:

import sqlite3

connection = sqlite3.connect('students.db')
cursor = connection.cursor()

# Create
cursor.execute(
    'INSERT INTO students (name, marks) VALUES (?, ?)',
    ('Peter', 88)
)

# Read
cursor.execute('SELECT * FROM students')
print('Students:', cursor.fetchall())

# Update
cursor.execute(
    'UPDATE students SET marks = ? WHERE name = ?',
    (95, 'Peter')
)

# Delete
cursor.execute(
    'DELETE FROM students WHERE name = ?',
    ('Peter',)
)

connection.commit()

print('CRUD operations completed')

connection.close()