SQL with Python

Learn how to write and execute SQL queries from Python applications using SQLite.

What is SQL with Python?

SQL is a language used to communicate with relational databases.

Python can execute SQL statements using database modules such as sqlite3.

SQL with Python Flow

Connecting to SQLite

import sqlite3

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

cursor = connection.cursor()

print("Database connected")

Output:

Database connected

CREATE TABLE

The CREATE TABLE statement is used to create a new table.

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

connection.commit()

print("Table created")

Output:

Table created

INSERT Query

The INSERT statement is used to add new records.

cursor.execute(
    "INSERT INTO students (name, course, marks) VALUES (?, ?, ?)",
    ("Rahul", "Python", 85)
)

connection.commit()

print("Record inserted")

Output:

Record inserted

SELECT Query

The SELECT statement is used to retrieve records from the database.

cursor.execute(
    "SELECT * FROM students"
)

rows = cursor.fetchall()

for row in rows:

    print(row)

Example Output:

(1, 'Rahul', 'Python', 85)

SELECT with WHERE

The WHERE clause is used to retrieve records that match a condition.

cursor.execute(
    "SELECT * FROM students WHERE marks >= ?",
    (80,)
)

rows = cursor.fetchall()

for row in rows:

    print(row)

Example Output:

(1, 'Rahul', 'Python', 85)

ORDER BY

The ORDER BY clause is used to sort database records.

cursor.execute(
    "SELECT * FROM students ORDER BY marks DESC"
)

rows = cursor.fetchall()

for row in rows:

    print(row)

The DESC keyword sorts the records from highest to lowest.

UPDATE Query

The UPDATE statement is used to modify existing records.

cursor.execute(
    "UPDATE students SET marks = ? WHERE id = ?",
    (90, 1)
)

connection.commit()

print("Record updated")

Output:

Record updated

DELETE Query

The DELETE statement is used to remove records.

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)

connection.commit()

print("Record deleted")

Output:

Record deleted

Aggregate Functions

SQL provides aggregate functions such as COUNT(), SUM(), AVG(), MAX() and MIN().

cursor.execute(
    "SELECT COUNT(*) FROM students"
)

result = cursor.fetchone()

print("Total students:", result[0])

Example Output:

Total students: 5

SUM and AVG

cursor.execute(
    "SELECT SUM(marks), AVG(marks) FROM students"
)

result = cursor.fetchone()

print("Total marks:", result[0])
print("Average marks:", result[1])

Example Output:

Total marks: 425
Average marks: 85.0

Parameterized SQL Queries

Parameterized queries allow Python values to be passed safely into SQL statements.

name = "Priya"
marks = 92

cursor.execute(
    "INSERT INTO students (name, course, marks) VALUES (?, ?, ?)",
    (name, "Python", marks)
)

connection.commit()

This approach is preferred when working with values received from users or other external sources.

Example 🤓👀

The following example creates a table, inserts student data, retrieves records and calculates the average marks.

import sqlite3

connection = sqlite3.connect("college.db")

cursor = connection.cursor()

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

cursor.execute(
    "INSERT INTO students (name, course, marks) VALUES (?, ?, ?)",
    ("Amit", "Python", 88)
)

connection.commit()

cursor.execute(
    "SELECT name, marks FROM students"
)

students = cursor.fetchall()

for student in students:

    print(student)

cursor.execute(
    "SELECT AVG(marks) FROM students"
)

average = cursor.fetchone()

print("Average marks:", average[0])

connection.close()

Example Output:

('Amit', 88)
Average marks: 88.0

Common SQL Commands

Best Practices

Common Mistakes

• Forgetting to import sqlite3.
• Writing incorrect SQL syntax.
• Forgetting to call commit().
• Not closing the database connection.
• Building SQL statements directly from user input.

Practice Programs

1. Create a students table using Python.
2. Insert five student records using SQL.
3. Retrieve students whose marks are above 75.
4. Update the marks of a student.
5. Delete a student using an id.
6. Find the total and average marks using SQL.