How kellyjadams/run-sql-in-python works
Project type and purpose
• This is a small Python utility project (an open-source tool) whose sole purpose is to demonstrate how to connect to two different database services: PostgreSQL and Google BigQuery.
• The README confirms it’s not a full-stack web application but a set of scripts to establish database connections, with a blog post for deeper context.
File structure
• Top-level files only:
• README.md – project overview and instructions
• postgresql.py – code for connecting to PostgreSQL, using a secret manager for credentials
• bigquery.py – code for connecting to BigQuery
• No “src” folder or additional submodules; the scripts live at the root.
• No build scripts, no Dockerfile, no CI configuration – minimal deployment footprint.
Extract insights from the README
• The README highlights two distinct database-connection flows.
• It mentions a “secret manager” for the PostgreSQL password, implying an external secrets service.
• BigQuery script likely uses GCP’s client libraries and authentication (service account or ADC).
Main components
a. Python runtime (where the scripts execute)
b. postgresql.py module
c. bigquery.py module
d. Secret Manager service (for PostgreSQL credentials)
e. PostgreSQL database server (could be self-hosted or cloud)
f. Google BigQuery service (GCP API endpoint)
g. (Optional) Local developer workstation or CI environment that runs the scripts
Relationships & interactions
• The Python runtime loads one of the two modules (postgresql.py or bigquery.py).
• postgresql.py queries Secret Manager to obtain DB credentials, then opens a TCP connection to PostgreSQL.
• bigquery.py uses GCP’s authentication mechanism to call the BigQuery API.
• Both modules execute SQL commands over those connections and return results to the caller.
Architectural patterns & design principles
• Single-responsibility scripts: each file handles only one type of database connection.
• Externalize secrets: separation between code and credentials via the secret manager.
• Dependency on managed services (PostgreSQL instance and BigQuery API) rather than in-app persistence.
Elements
• Clear labels:
• “Python Runtime / Client”
• “postgresql.py”
• “bigquery.py”
• “Secret Manager”
• “PostgreSQL Server”
• “Google BigQuery API”
• Credential fetch → Secret Manager
• DB connection request → PostgreSQL Server
• API call → BigQuery service
• Another for secret management
• Another for external services (databases, cloud APIs)