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.
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"
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.
import pydbtools as pydb
df = pydb.read_sql_query("SELECT * from a_database.table LIMIT 10")
response = pydb.start_query_execution_and_wait("CREATE DATABASE IF NOT EXISTS my_test_database")
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")
SELECT * FROM db.table WHERE date_col > date '2018-12-31'
SELECT * FROM db.table WHERE datetime_col > timestamp '2018-12-31 23:59:59'
AWS_ATHENA_QUERY_REGION
which will override theseSee changelog for release changes.