QueryCasequerycase.com
Lab reference guide

Python Automation Cheat Sheet

The moves that turn a morning of clicking into a job that runs itself: files, workbooks, letters, email and the web.

The examples work on a small desk of evidence: a seized laptop folder, two scam reports, three CCTV clips and an overtime workbook. Each shows what comes back after # →.

One example, all the moving parts
from pathlib import Path

laptop = Path("laptop")
for item in sorted(laptop.iterdir()):
    print(item.name, item.suffix)

Path names a folder, .iterdir() walks what is inside it, and every item knows its own .name and .suffix.

forkeywordslenbuilt-in functions"text"text42numbers# → 3what comes back
01

Folders and Files

Path and .iterdir()

Path("folder") names a folder or a file. .iterdir() walks everything inside a folder, in no promised order.

Example
from pathlib import Path
laptop = Path("laptop")
len(list(laptop.iterdir()))  # → 3

.name, .stem and .suffix

The parts of a path: the whole file name, the name without its ending, and the ending itself.

Example
from pathlib import Path
item = Path("laptop/notes.txt")
item.name  # → notes.txt
item.stem  # → notes
item.suffix  # → .txt

.glob()

Only the names matching a pattern, * standing for anything. Wrap it in sorted() for a fixed order.

Example
from pathlib import Path
books = sorted(Path("laptop").glob("*.xlsx"))
len(books)  # → 2

/ and .read_text()

/ joins a folder and a name into one path. .read_text() reads the whole file as text.

Example
from pathlib import Path
(Path("laptop") / "notes.txt").read_text()  # → Ask about the float.

.mkdir()

Makes a folder. parents=True makes any folders above it too, and exist_ok=True means no error when it is already there.

Example
from pathlib import Path
(Path("sorted") / "photos").mkdir(parents=True, exist_ok=True)
Path("sorted/photos").exists()  # → True

.rename() and shutil.copy()

.rename() moves a file to a new path. shutil.copy() leaves the original where it is and puts a copy at the new one.

Example
import shutil
from pathlib import Path
Path("kept").mkdir(exist_ok=True)
shutil.copy(Path("laptop") / "notes.txt", Path("kept") / "notes.txt")
Path("laptop/notes.txt").exists()  # → True
02

Patterns in Text

re.search()

Finds the first place a pattern matches, or None. \d is any digit and {8} means exactly eight of them.

Pattern
re.search(r"pattern", text)
Example
import re
found = re.search(r"\d{8}", "Moved 900 to account 41129078 on Tuesday.")
found.group()  # → 41129078
TipWrite patterns as raw strings, r"...", so a backslash reaches the pattern instead of being read by Python first.

Every file searched

The same search in a loop over a folder, keeping each match.

Example
import re
from pathlib import Path
accounts = []
for item in sorted(Path("reports").glob("*.txt")):
    accounts.append(re.search(r"\d{8}", item.read_text()).group())
accounts  # → ['41129078', '41129078']

Pattern pieces

\d a digit, \w a letter, digit or underscore, . any character, + one or more, {n} exactly n, [A-Z] one capital letter.

Example
import re
re.search(r"[A-Z]{2}\d{2}", "Plate KX19 seen").group()  # → KX19
03

Times

CodeMeansExample
%YFour-digit year2026
%mMonth, two digits11
%dDay of the month, two digits05
%HHour, 24-hour clock01
%MMinute45
%SSecond30

datetime.strptime()

Reads a time out of text, given the format the text is written in.

Pattern
datetime.strptime(text, "%Y-%m-%d_%H%M")
Example
from datetime import datetime
start = datetime.strptime("2026-11-05_0145", "%Y-%m-%d_%H%M")
start.hour  # → 1

One time minus another

Subtracting two times gives a gap, a timedelta. Compare it with timedelta(minutes=15) to find a gap too long.

Example
from datetime import datetime, timedelta
from pathlib import Path
times = []
for clip in sorted(Path("cctv").glob("cam2_*.mp4")):
    times.append(datetime.strptime(clip.stem[5:], "%Y-%m-%d_%H%M"))
times[2] - times[1] > timedelta(minutes=15)  # → True
04

Reading Workbooks

load_workbook()

Opens an Excel file. .sheetnames lists its tabs, and a tab is picked by name in square brackets.

Example
from openpyxl import load_workbook
wb = load_workbook("overtime.xlsx")
wb.sheetnames  # → ['October']
ws = wb["October"]

A cell by its address

The sheet takes an Excel address in square brackets. .value is what the cell holds.

Example
from openpyxl import load_workbook
ws = load_workbook("overtime.xlsx")["October"]
ws["C2"].value  # → 12

.iter_rows()

Every row in turn. min_row=2 skips the header, and values_only=True gives the values instead of the cells.

Example
from openpyxl import load_workbook
ws = load_workbook("overtime.xlsx")["October"]
total = 0
for row in ws.iter_rows(min_row=2, values_only=True):
    total = total + row[2]
total  # → 26

pd.read_excel()

A whole tab straight into a pandas table, when the job is to analyse it rather than walk it.

Example
import pandas as pd
sheet = pd.read_excel("overtime.xlsx", sheet_name="October")
sheet["hours"].sum()  # → 26
05

Jobs That Survive

try and except

Runs the lines under try, and if one of them raises an error, jumps to except instead of stopping the whole job.

Pattern
try:
    risky()
except Exception:
    handle_it()
Example
failed = []
for name in ["12", "eight", "30"]:
    try:
        int(name)
    except ValueError:
        failed.append(name)
failed  # → ['eight']
TipCatch the error you expect, like ValueError, where you can. except Exception also hides the mistakes you did not expect.

logging

A job nobody is watching writes down what it did. basicConfig once at the top, then logging.info and logging.error as it goes.

Example
import logging
logging.basicConfig(filename="job.log", filemode="w", level=logging.INFO, format="%(levelname)s %(message)s", force=True)
logging.info("job started")
logging.error("could not open claims.xlsx")
open("job.log").read().split("\n")[1]  # → ERROR could not open claims.xlsx

Safe to run twice

Check for the output before doing the work, so a job run again skips what it already finished.

Example
from pathlib import Path
out = Path("done.txt")
if not out.exists():
    out.write_text("paid")
out.read_text()  # → paid
06

Writing Workbooks

Workbook() and .append()

A new workbook, its first sheet, a row at a time, then .save(). Read it back to check what was written.

Example
from openpyxl import Workbook, load_workbook
wb = Workbook()
ws = wb.active
ws.title = "Minutes"
ws.append(["collar", "minutes"])
ws.append(["N2031", 540])
wb.save("minutes.xlsx")
load_workbook("minutes.xlsx")["Minutes"]["B2"].value  # → 540

Bold, widths and frozen panes

Font(bold=True) on the header, a column's width, and freeze_panes so the header stays on screen.

Example
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.append(["supplier", "amount"])
for cell in ws[1]:
    cell.font = Font(bold=True)
ws.column_dimensions["A"].width = 30
ws.freeze_panes = "A2"
ws["A1"].font.b  # → True

Shading by a rule

A PatternFill painted onto every cell that passes a test. Walk the rows as cells, not values, so there is a cell to paint.

Example
from openpyxl import Workbook
from openpyxl.styles import PatternFill
wb = Workbook()
ws = wb.active
for row in [["payee", "amount"], ["Harbour Marine", 4950], ["Kelby Print", 120]]:
    ws.append(row)
red = PatternFill(fill_type="solid", start_color="FFC7CE")
shaded = 0
for row in ws.iter_rows(min_row=2):
    if 4500 <= row[1].value < 5000:
        row[1].fill = red
        shaded = shaded + 1
shaded  # → 1

A sheet per group

pandas writes one workbook with a tab for each group, through an ExcelWriter held open for all of them.

Example
import pandas as pd
from openpyxl import load_workbook
df = pd.DataFrame({"victim": ["Ann", "Bo", "Ann"], "amount": [500, 300, 250]})
with pd.ExcelWriter("victims.xlsx") as writer:
    for victim, rows in df.groupby("victim"):
        rows.to_excel(writer, sheet_name=victim, index=False)
load_workbook("victims.xlsx").sheetnames  # → ['Ann', 'Bo']
07

Letters, Bundles and Email

string.Template

A letter with $ gaps, filled by name. .substitute() raises a KeyError if a gap is left without a value.

Example
from string import Template
letter = Template("Dear $name, you are owed £$amount.")
letter.substitute(name="Priya", amount=425)  # → Dear Priya, you are owed £425.

A letter for every row

.to_dict("records") turns a table into one dictionary per row, ready to fill a template and write a file each.

Example
import pandas as pd
from pathlib import Path
from string import Template
letter = Template("Dear $name, you are owed £$amount.")
buyers = pd.DataFrame({"ref": ["CO-1", "CO-2"], "name": ["Priya", "Sam"], "amount": [425, 80]})
Path("letters").mkdir(exist_ok=True)
for buyer in buyers.to_dict("records"):
    text = letter.substitute(name=buyer["name"], amount=buyer["amount"])
    (Path("letters") / (buyer["ref"] + ".txt")).write_text(text)
len(list(Path("letters").iterdir()))  # → 2

zipfile

A bundle of files in one zip. Open it for writing with "w", add each file, and .namelist() checks what went in.

Example
import zipfile
from pathlib import Path
with zipfile.ZipFile("bundle.zip", "w") as bundle:
    for path in sorted(Path("reports").iterdir()):
        bundle.write(path, path.name)
len(zipfile.ZipFile("bundle.zip").namelist())  # → 2

EmailMessage

Headers set like dictionary keys, a body, and files attached as bytes. .as_bytes() is the finished email, ready to save or send.

Example
from email.message import EmailMessage
from pathlib import Path
msg = EmailMessage()
msg["Subject"] = "Bundle for a charging decision"
msg.set_content("The full bundle is attached.")
data = Path("reports/report-01.txt").read_bytes()
msg.add_attachment(data, maintype="text", subtype="plain", filename="report-01.txt")
len(list(msg.iter_attachments()))  # → 1

main() and the guard

The whole job in one function, run only when the file itself is run, which is how a scheduler starts it.

Pattern
def main():
    ...

if __name__ == "__main__":
    main()
08

Web Requests

StatusMeansWhat to do
200It worked.Read the body with .json().
401No key, or the wrong one.Send the key in headers={...}.
404Nothing at that address.Check the address and the record.
500The server itself failed.Catch it, log it, and carry on with the rest.

requests.get()

Asks an address for a page or data. .status_code says how it went, and .json() reads a JSON body into dictionaries and lists.

Pattern
response = requests.get(url)
Example
import requests
response = requests.get("https://example.com/v1/status")
response.status_code
data = response.json()

params and headers

params adds options to the address (?plate=...); headers carries things like an API key.

Example
import requests
seen = requests.get("https://example.com/v1/sightings", params={"plate": "LR68XKD"}, headers={"X-Api-Key": "KEY"}).json()

Every page

A long list comes a page at a time, each pointing to the next. Follow them until there is no next page.

Example
import requests
rows = []
url = "https://example.com/v1/incidents"
while url:
    page = requests.get(url).json()
    rows = rows + page["results"]
    url = page["next"]
TipCheck the loop moves url on every turn. One that asks for the same page again never ends.

raise_for_status()

Turns a failed status into an error you can catch, so one broken source does not stop the whole job.

Example
import requests
try:
    response = requests.get("https://example.com/v1/shops/QSY/stock")
    response.raise_for_status()
except requests.HTTPError:
    print("QSY is down, carrying on")
09

Web Pages

robots.txt first

A site says which pages robots may read. RobotFileParser reads its rules and can_fetch asks about one address.

Example
from urllib.robotparser import RobotFileParser
robots = RobotFileParser()
robots.parse(["User-agent: *", "Disallow: /site/sellers/"])
robots.can_fetch("*", "https://example.com/site/listings")  # → True
robots.can_fetch("*", "https://example.com/site/sellers/velo")  # → False

BeautifulSoup

Reads a page's HTML so its parts can be found by what they are: .select() every match, .select_one() the first, .get_text() the words.

Example
from bs4 import BeautifulSoup
html = '<tr class="listing"><td class="frame">WTU123</td></tr><tr class="listing"><td class="frame">WTU456</td></tr>'
soup = BeautifulSoup(html, "html.parser")
len(soup.select("tr.listing"))  # → 2
soup.select_one("td.frame").get_text()  # → WTU123
Python Automation Cheat Sheet · QueryCaseThe full course is at querycase.com