python

Group by

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

#11MediumJoin two tables#12EasyCustomers without orders#13EasyTransaction with rollback#14EasyUse a database file

← All 20 SQLite Programs

Join two tables

by
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

#12EasyCustomers without orders#13EasyTransaction with rollback#14EasyUse a database file#15EasyColumn names of a query

← All 20 SQLite Programs

Aggregate functions

by
Python Practice › SQLite Programs › Program 9

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

#10EasyGroup by#11MediumJoin two tables#12EasyCustomers without orders#13EasyTransaction with rollback

← All 20 SQLite Programs

Rows as dictionaries

by
Python Practice › SQLite Programs › Program 8

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

#9EasyAggregate functions#10EasyGroup by#11MediumJoin two tables#12EasyCustomers without orders

← All 20 SQLite Programs

fetchone, fetchmany, fetchall

by
Python Practice › SQLite Programs › Program 7

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

#8EasyRows as dictionaries#9EasyAggregate functions#10EasyGroup by#11MediumJoin two tables

← All 20 SQLite Programs

Delete rows

by
Python Practice › SQLite Programs › Program 6

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

#7Easyfetchone, fetchmany, fetchall#8EasyRows as dictionaries#9EasyAggregate functions#10EasyGroup by

← All 20 SQLite Programs

Update rows

by
Python Practice › SQLite Programs › Program 5

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

#6EasyDelete rows#7Easyfetchone, fetchmany, fetchall#8EasyRows as dictionaries#9EasyAggregate functions

← All 20 SQLite Programs

Insert a row with parameters

by
Python Practice › SQLite Programs › Program 2

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

#3EasyInsert many rows#4EasySelect with a condition#5EasyUpdate rows#6EasyDelete rows

← All 20 SQLite Programs

Insert many rows

by
Python Practice › SQLite Programs › Program 3

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

#4EasySelect with a condition#5EasyUpdate rows#6EasyDelete rows#7Easyfetchone, fetchmany, fetchall

← All 20 SQLite Programs

Python RegEx Programs with Solutions

by
Python Practice › RegEx Programs

Python RegEx Programs with Solutions

20 programs19 easy1 medium0 hard

20 regular expression programs: validating emails, phone numbers, PIN codes and passwords, extracting numbers, dates, hashtags and URLs, and cleaning or reformatting text with re.sub.

Before you start

Read the RegEx lesson first if the topic is new to you.

All programs