Group by

Python Practice › SQLite Programs › Program 10

Group by

EasySQLite Programs · 10 of 20

Write a Python program to count employees and average salary per department.

Output
('Dev', 2, 64000.0)
('QA', 2, 50000.0)

Approach

  1. GROUP BY dept.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE emp (name TEXT, dept TEXT, salary INTEGER)")conn.executemany("INSERT INTO emp VALUES (?, ?, ?)",                 [("Asha", "QA", 52000), ("Ravi", "Dev", 61000), ("Meena", "QA", 48000), ("Sara", "Dev", 67000)])for row in conn.execute("SELECT dept, COUNT(*), AVG(salary) FROM emp GROUP BY dept ORDER BY dept"):    print(row)
Output
('Dev', 2, 64000.0)
('QA', 2, 50000.0)

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Join two tables

Python Practice › SQLite Programs › Program 11

Join two tables

MediumSQLite Programs · 11 of 20

Write a Python program to list each order with the customer’s name.

Output
(10, 'Asha', 500.0)
(11, 'Ravi', 250.0)
(12, 'Asha', 900.0)

Approach

  1. JOIN ... ON.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.executescript("""    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);    CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);    INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');    INSERT INTO orders VALUES (10, 1, 500), (11, 2, 250), (12, 1, 900);""")query = "SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON c.id = o.customer_id ORDER BY o.id"for row in conn.execute(query):    print(row)
Output
(10, 'Asha', 500.0)
(11, 'Ravi', 250.0)
(12, 'Asha', 900.0)

Line by line

Lines 4-9

executescript runs several statements at once, which is handy for setup.

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Aggregate functions

Aggregate functions

EasySQLite Programs · 9 of 20

Write a Python program to get the count, average, minimum and maximum marks.

Output
(4, 79.5, 65, 91)

Approach

  1. COUNT, AVG, MIN, MAX in one query.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Ravi", 74), ("Meena", 88), ("John", 65)])print(conn.execute("SELECT COUNT(*), ROUND(AVG(marks), 2), MIN(marks), MAX(marks) FROM students").fetchone())
Output
(4, 79.5, 65, 91)

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Rows as dictionaries

Rows as dictionaries

EasySQLite Programs · 8 of 20

Write a Python program to read rows by column name.

Output
Asha Pune {'name': 'Asha', 'city': 'Pune'}

Approach

  1. conn.row_factory = sqlite3.Row.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.row_factory = sqlite3.Rowconn.execute("CREATE TABLE users (name TEXT, city TEXT)")conn.execute("INSERT INTO users VALUES ('Asha', 'Pune')")row = conn.execute("SELECT * FROM users").fetchone()print(row["name"], row["city"], dict(row))
Output
Asha Pune {'name': 'Asha', 'city': 'Pune'}

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

fetchone, fetchmany, fetchall

fetchone, fetchmany, fetchall

EasySQLite Programs · 7 of 20

Write a Python program that shows the three fetch methods.

Output
(1,) [(2,), (3,)] [(4,), (5,), (6,)]

Approach

  1. Each call continues where the previous one stopped.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE n (v INTEGER)")conn.executemany("INSERT INTO n VALUES (?)", [(i,) for i in range(1, 7)])cur = conn.execute("SELECT v FROM n")print(cur.fetchone(), cur.fetchmany(2), cur.fetchall())
Output
(1,) [(2,), (3,)] [(4,), (5,), (6,)]

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Delete rows

Delete rows

EasySQLite Programs · 6 of 20

Write a Python program to delete students with marks below 70.

Output
deleted: 1 [('Asha',), ('Ravi',)]

Approach

  1. DELETE FROM ... WHERE ....

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Ravi", 74), ("John", 65)])cur = conn.execute("DELETE FROM students WHERE marks < 70")print("deleted:", cur.rowcount, conn.execute("SELECT name FROM students").fetchall())
Output
deleted: 1 [('Asha',), ('Ravi',)]

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Select with a condition

Select with a condition

EasySQLite Programs · 4 of 20

Write a Python program to select students who scored above 80.

Output
Asha 91
Meena 88

Approach

  1. WHERE marks > ?.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Ravi", 74), ("Meena", 88)])for name, marks in conn.execute("SELECT name, marks FROM students WHERE marks > ? ORDER BY marks DESC", (80,)):    print(name, marks)
Output
Asha 91
Meena 88

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Update rows

Update rows

EasySQLite Programs · 5 of 20

Write a Python program to add 5 bonus marks to students below 80.

Output
updated: 2
[('Asha', 91), ('Ravi', 79), ('John', 70)]

Approach

  1. UPDATE ... SET marks = marks + 5 WHERE ...; rowcount tells how many rows changed.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Ravi", 74), ("John", 65)])cur = conn.execute("UPDATE students SET marks = marks + 5 WHERE marks < 80")print("updated:", cur.rowcount)print(conn.execute("SELECT * FROM students").fetchall())
Output
updated: 2
[('Asha', 91), ('Ravi', 79), ('John', 70)]

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Insert a row with parameters

Insert a row with parameters

EasySQLite Programs · 2 of 20

Write a Python program to insert a row safely with ? placeholders.

Output
new id: 1
[(1, 'Asha', 91)]

Approach

  1. Pass values separately from the SQL.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, marks INTEGER)")cur = conn.execute("INSERT INTO students (name, marks) VALUES (?, ?)", ("Asha", 91))conn.commit()print("new id:", cur.lastrowid)print(conn.execute("SELECT * FROM students").fetchall())
Output
new id: 1
[(1, 'Asha', 91)]

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs

Insert many rows

Insert many rows

EasySQLite Programs · 3 of 20

Write a Python program to insert several rows at once.

Output
4

Approach

  1. executemany.

Solution

Python
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")rows = [("Asha", 91), ("Ravi", 74), ("Meena", 88), ("John", 65)]conn.executemany("INSERT INTO students VALUES (?, ?)", rows)print(conn.execute("SELECT COUNT(*) FROM students").fetchone()[0])
Output
4

Learn the concept

New to this topic? Read the lesson, then come back.

More sqlite programs

← All 20 SQLite Programs