Automating & delivering reports
Databases, APIs & sources
SQL queries, REST APIs, Google Sheets and cloud files as the input to a report.
Start with the full guides
The long-form treatment of databases, apis & sources, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toCache API Responses When Building Excel ReportsStop a report script from calling the same API hundreds of times while you develop it or rerun it: a transparent HTTP cache with requests-cache and expiry rules per endpoint, a hand-rolled file cache for responses that are not plain GETs, caching only successful responses, forcing a refresh for the production run, and recording in the workbook how old the cached data was.
- How-toCompare an Excel File with a Database TableFind every difference between a spreadsheet and the database table it is supposed to match: normalise both sides so formatting noise does not count, outer-join on the key, list rows only in Excel or only in the database, compare fields with tolerances for numbers and dates, write a highlighted difference report, and preview the SQL that would bring the table in line.
- How-toDownload New Excel Files from an SFTP ServerCollect the spreadsheets a partner drops on an SFTP server with paramiko: connect with a pinned host key, list the folder with sizes and modification times, ignore partial uploads and files still being written, download only files not fetched before — or fetched before but changed since — into a temporary name and rename, check each workbook opens, keep a small state file, and test it all against a local SFTP server.
- How-toExport a Large Database Table to Excel in ChunksExport a database table too big to load at once into Excel with flat memory use: stream rows in chunks from SQLAlchemy with a server-side cursor, write them straight to disk with XlsxWriter constant_memory mode, roll over to a new sheet before the 1,048,576-row limit, keep dates and numbers typed, and check the row counts and memory afterwards.
- How-toExport Database Lookup Tables as Excel Dropdown ListsBuild an Excel input template whose dropdowns come straight from the database: read the active cost centres, projects and expense types, write them to a hidden lists sheet, give each list a named range, attach data validation to the input columns, show codes with their descriptions while storing a code that maps back cleanly, refresh the lists in an existing template, and check the returned file against the database.
- How-toExport SQL Query Results to Excel with PythonRun a parameterised query with SQLAlchemy and write the result to a formatted workbook: one sheet per result set, correct date and money formats, sized columns, and chunked reads for big results.
- How-toFetch API Data into Excel with Python requestsPull JSON from an HTTP API into a workbook: a session with timeouts and retries, pagination that terminates, json_normalize for nested records, and a cache so a rerun does not hammer the endpoint.
- How-toFetch Paginated API Data into ExcelPull every record from a REST API that returns results a page at a time and write them to Excel: page-number, offset and cursor pagination, following next links, handling 429 rate limits with Retry-After, retrying transient errors, flattening nested JSON with json_normalize, de-duplicating across pages, and checking you received as many records as the API says exist.
- How-toGenerate SQL INSERT Statements from an Excel SheetTurn an Excel sheet into a reviewable .sql script for a database administrator to run: map pandas types to SQL literals, quote text safely by doubling single quotes, write NULL for blanks, dates as ISO literals, batch rows into multi-row INSERTs inside one transaction, add an upsert variant for PostgreSQL, and prove the script by running it against a scratch database and comparing every value with the sheet.
- How-toLoad an Excel File into a SQL Database with pandasTake a spreadsheet people filled in and get it into a table safely: normalise the columns, pin the types, stage the rows, validate them, then merge in one transaction that either succeeds or leaves nothing behind.
- How-toLoad Excel Data into MongoDB with PythonTurn spreadsheet rows into well-shaped MongoDB documents with pandas and PyMongo: nested fields from dotted column names, real dates and numbers instead of strings, blanks left out rather than stored as NaN, upserts on a business key with bulk_write so reloading a file is safe, a unique index, and a check of what was inserted, updated and skipped.
- How-toLoad Excel into SQLite with a Defined SchemaLoad Excel sheets into SQLite tables that you designed — proper types, primary keys, NOT NULL and CHECK constraints, foreign keys and STRICT typing — instead of whatever pandas.to_sql guesses: create the schema first, convert dates and decimals deliberately, insert in one transaction so a bad row rolls the whole load back, and report exactly which rows broke which rule.
- How-toQuery a Database for IDs Listed in an Excel SheetSomeone sends a spreadsheet of customer or order IDs and wants the matching records. Clean the IDs, pass them to SQL safely as parameters, split long lists into chunks the database driver accepts, switch to a temporary table for very long lists, and report the IDs that were not found — in the order the sheet listed them.
- How-toQuery Excel Files with SQL Using DuckDBRun SQL directly against .xlsx files with DuckDB's excel extension — read a range below a report header, stop at the first empty row, handle text in numeric columns, combine several workbooks, join Excel with pandas DataFrames and write query results back to a workbook.
- How-toRead Excel Attachments into a Database NightlyLoad the spreadsheets that arrive by email into a database every night without duplicates or half-loaded files: take the saved attachments from an inbox folder, skip files already loaded by their content hash, validate each workbook's columns and values, write each file in its own transaction with an upsert on the business key, record every load in a log table, move rejected files to a quarantine folder, and report the night's results.
- How-toRefresh an Excel Report from a Database on a ScheduleKeep a workbook current without anyone asking: an incremental query with a watermark, an atomic write nobody can catch half-finished, a freshness stamp readers can see, and a check that the refresh actually ran.
- How-toScrape a Web Page Table into Excel with pandasTurn an HTML table on a web page into a clean Excel sheet. Fetch the page with requests, pick the right table out of several with read_html, flatten two-row headers, strip footnote markers and dashes so numbers become numbers, keep the links, and save the result with its source and time.
- How-toSync Google Sheets with Excel Using PythonMove data both ways between Google Sheets and .xlsx: service-account auth with gspread, reading a tab into pandas, writing back, exporting a whole spreadsheet and scheduling it.
- How-toUpsert Excel Rows into a Database Table with PythonLoad a maintained Excel sheet into a database table without duplicating rows — insert new keys, update changed ones and leave unchanged rows alone. Use a staging table and INSERT … ON CONFLICT, a to_sql method for smaller loads, and checks for duplicate keys and rows deleted from the sheet.
- How-toRead an Excel File Directly from Amazon S3 with pandasLoad a workbook from S3 into a DataFrame without a temporary file: BytesIO reads, paginated prefix scans, readable error translation, and a Parquet cache for repeat runs.
- How-toStream Excel Rows into a Database with openpyxlLoad a large Excel sheet into a database without ever holding it in memory — read rows with openpyxl's read-only mode, convert and validate each row, insert in batches inside a transaction, divert bad rows to a rejects table and make the load safe to rerun.
- How-toBuild a Cash Position Report from Bank StatementsProduce a daily cash position across several banks and currencies from their statement exports with pandas: map each bank's own CSV layout to one standard, prove every statement adds up from opening to closing balance, convert to the reporting currency, compare each account with its minimum balance and overdraft limit, and write a one-page position with the largest movements of the day.
- How-toDownload Excel Attachments from an Email Inbox with PythonPick up the spreadsheets suppliers and colleagues email in, without anyone saving them by hand. Search a mailbox over IMAP, save only genuine Excel attachments under safe file names, decode international file names, skip messages already processed, and move handled mail to a folder so the job can run every few minutes.
- How-toConvert XML to Excel with pandas read_xmlTurn an XML export — orders, invoices, a feed from a legacy system — into a workbook with one sheet per record type. Use pd.read_xml with XPath and namespaces, keep nested line items linked to their parent record, preserve codes with leading zeros, and stream very large files with iterparse.
- How-toRead an Excel File from a URL or Bytes in PythonLoad a workbook straight from HTTP, S3, a database blob or an upload: BytesIO, content-type checks, authentication, retries, streaming downloads and caching.
- How-toGet External Data into Python in Excel with Power QueryPython in Excel cannot read files or call the internet, so bring CSVs, databases and web data in with Power Query, then analyse them with xl() — tables versus connection-only queries, refresh order, parameters and size limits.
Follow it through a track
Prefer an ordered route? These tracks cover databases, apis & sources along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Databases, APIs & sources is part of Automating & delivering reports.