pydbtools
A package that is used to run SQL queries speficially configured for the Analytical Platform. This packages uses AWS Wrangler's Athena module but adds additional functionality (like Jinja templating, creating temporary tables) and alters some configuration to our specification.
Installation
Requires a pip release above 20.
## To install from pypi
pip install pydbtools
## Or install from git with a specific release
pip install "pydbtools @ git+https://github.com/moj-analytical-services/pydbtools@v4.0.1"
Quickstart guide
The examples directory contains more detailed notebooks demonstrating the use of this library, many of which are borrowed from the mojap-aws-tools-demo repo.
Read an SQL Athena query into a pandas dataframe
import pydbtools as pydb
df = pydb.read_sql_query("SELECT * from a_database.table LIMIT 10")
Run a query in Athena
response = pydb.start_query_execution_and_wait("CREATE DATABASE IF NOT EXISTS my_test_database")
Create a temporary table to do further separate SQL queries on later
pydb.create_temp_table("SELECT a_col, count(*) as n FROM a_database.table GROUP BY a_col", table_name="temp_table_1")
df = pydb.read_sql_query("SELECT * FROM __temp__.temp_table_1 WHERE n < 10")
pydb.dataframe_to_temp_table(my_dataframe, "my_table")
df = pydb.read_sql_query("select * from __temp__.my_table where year = 2022")
Notes
- Amazon Athena using a flavour of SQL called trino. Docs can be found here
- To query a date column in Athena you need to specify that your value is a date e.g.
SELECT * FROM db.table WHERE date_col > date '2018-12-31' - To query a datetime or timestamp column in Athena you need to specify that your value is a timestamp e.g.
SELECT * FROM db.table WHERE datetime_col > timestamp '2018-12-31 23:59:59' - Note dates and datetimes formatting used above. See more specifics around date and datetimes here
- To specify a string in the sql query always use '' not "". Using ""'s means that you are referencing a database, table or col, etc.
- If you are working in an environment where you cannot change the default AWS region environment
variables you can set
AWS_ATHENA_QUERY_REGIONwhich will override these. - You can override the bucket where query results are outputted to with the
ATHENA_QUERY_DUMP_BUCKETenvironment variable. This is mandatory if you set the region to something other thaneu-west-1.
See changelog for release changes.
6 maintainers
Metadata
6 maintainers
Release files for pydbtools 5.9.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| pydbtools-5.9.0.tar.gz | 14.8 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| pydbtools-5.9.0-py3-none-any.whl | Python 3 | none | any | Details |
Total release size: 27.4 kB
Release files / pydbtools-5.9.0.tar.gz
| Download URL | pydbtools-5.9.0.tar.gz |
|---|---|
| Size | 14.8 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
d81c28ac89bb86c987e47470c7cd5c2f7139c725582d0e520834cc8d79b4daba
|
|
BLAKE2b-256 checksum How to use checksums |
6325b47e60c9e3e130d112480c29ccf8339e453747c8c86bb531c03be62a581c
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.12.5 {"installer":{"name":"uv","version":"0.12.5","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Aug 18, 2026.
Transparency logRelease files / pydbtools-5.9.0-py3-none-any.whl
| Download URL | pydbtools-5.9.0-py3-none-any.whl |
|---|---|
| Size | 12.7 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
881c42abc7a7155f14d1dcbe46544cce07daf2e1defa546d9e8cdfa1e13e49a3
|
|
BLAKE2b-256 checksum How to use checksums |
ddc5f85f7e4727d7b8dbd0debdfbb0b37b85a435d7dce1f816eb8ab013c80de8
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
uv/0.12.5 {"installer":{"name":"uv","version":"0.12.5","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"Ubuntu","version":"24.04","id":"noble","libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":true}
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Aug 18, 2026.
Transparency log
