universal-data-connector 0.1.1

A Python package to query Oracle databases via CLI.

Note Since writing this README, I’ve found that the Python Packaging User Guide tutorial is an excellent general reference for packaging projects. This document focuses specifically on this project and its design decisions.

---

oracle-connector

This project provides a lightweight, opinionated way to execute Oracle SQL files from the command line or Python, with minimal setup. It is designed for data engineers, analysts, and platform teams who want a simple CLI that runs a `.sql` file against Oracle and returns results as a CSV, without writing Python scripts.

This is one possible way to structure and package such a tool as of 2025. The Python packaging ecosystem is large and complex; this repository focuses on a single, practical approach rather than exploring all alternatives.

The emphasis of this project is:

• Clean packaging using pyproject.toml

• A CLI-first user experience

• Environment-variable–based configuration

• Separation between database logic and execution logic

This repository intentionally leaves out some things you may want in a production system (for example: tests, CI, linting, or type checking) so that the focus remains on packaging and usability.

Comments throughout the source files explain why things are structured the way they are.

—

How to use this project

This repository is both a usable tool and a reference implementation.

You can:

• Install it and immediately run Oracle SQL files

• Fork it and adapt it to your organisation’s needs

• Copy parts of it into an existing project

You do not need to copy the entire repository. You can selectively reuse files such as pyproject.toml or the Click-based CLI.

—

Decisions

Before using or adapting this project, there are a few key decisions to be aware of.

Project name

The project name is oracle-connector. This name serves multiple purposes:

• The package name on PyPI

• The import namespace

• The CLI entry point (oracle-run)

If you fork or reuse this project, you should choose a name that is available on PyPI. You can check availability by searching on PyPI.

Version number

The package version is defined in src/oracle_connector/__init__.py as __version__.

When publishing distributions, you should increment this value. While testing your packaging workflow, it is recommended to use development suffixes such as:

__version__ = "0.1.0.dev0"

CLI-first design

This project is intentionally designed so that end users do not need to write Python code. Users interact with the package exclusively through a command- line interface and provide only a SQL file.

Example:

oracle-run --sql main.sql --output results.csv

Python usage is still possible, but it is not the primary interface.

Configuration via environment variables

All Oracle connection details are provided through environment variables. This avoids hard-coded secrets and works well in:

• Local development

• CI/CD pipelines

• Containers

• Cloud platforms (e.g. AWS, Azure)

—

Project layout

This repository follows the src/ layout recommended by modern Python packaging practices.

src/

All Python source code lives under src/oracle_connector:

• connector.py – Oracle database connection and execution logic

• runner.py – High-level SQL execution helper

• cli.py – Click-based command-line interface

This separation keeps responsibilities clear and makes the code easier to test and extend.

pyproject.toml

This file defines:

• Project metadata

• Dependencies

• Python version requirements

• The CLI entry point

It is the most important configuration file in the project and should be the first place you look when adapting this repository.

README.rst

(This file.)

It explains what the project does, how to install it, how to use it, and how to publish it. If you prefer Markdown, you may replace this file with README.md and update the readme field in pyproject.toml accordingly.

Other files

• .gitignore – Prevents build artifacts and caches from being committed

• LICENSE.txt – The project license (you may change this)

—

Preparing your environment

You will need Python 3.9 or newer.

Using a virtual environment is strongly recommended, though not required.

Install build tools

To build distributions, install the development tools:

python -m pip install -r dev-requirements.txt

Or, if provided:

make tools

—

Installation

Local editable install

For development or local testing, install the project in editable mode:

python -m pip install -e .

This allows Python to import directly from your working tree, making iteration fast and convenient.

After installation, the CLI will be available immediately.

—

Usage

Environment variables

Before running the CLI, set the following environment variables:

export ORACLE_USER=your_username
export ORACLE_PASSWORD=your_password
export ORACLE_HOST=your_host
export ORACLE_PORT=1521
export ODWH_SERVICE=your_service_name

Running a SQL file

Execute a SQL file and export results to CSV:

oracle-run --sql main.sql

Specify a custom output file:

oracle-run --sql SQL/ETL/ODWH/main.sql --output stock_data.csv

If the query returns rows, they will be written to the output file. If no rows are returned, the command will still succeed.

—

Making distributions

Create distributions

Build both a source distribution and a wheel:

python -m build --sdist --wheel
python -m twine check dist/*

If successful, the dist/ directory will contain files similar to:

oracle_connector-0.1.0-py3-none-any.whl
oracle_connector-0.1.0.tar.gz

Testing distributions

You can test the built wheel in a fresh virtual environment:

python -m pip install oracle_connector-0.1.0-py3-none-any.whl

Verify that the CLI works as expected.

—

Uploading to PyPI

There are two PyPI instances:

• Test PyPI: https://test.pypi.org

• Production PyPI: https://pypi.org

Create accounts on both before proceeding.

Upload to Test PyPI

python -m twine upload --repository testpypi dist/*

After uploading, review the project page and README formatting.

Note: Uploaded files cannot be replaced. If you need to fix something, you must increment the version number and upload again.

Upload to production PyPI

Once satisfied:

python -m twine upload dist/*

—

Your package is available

Users can now install your package with:

pip install oracle-connector

And immediately run:

oracle-run --sql main.sql

—

Internal / private PyPI usage

This project is designed to be published and consumed from an internal Python package repository rather than the public PyPI service.

This is the recommended approach for:

• Proprietary business logic

• Database access tools

• Organisation-specific conventions

• Controlled distribution and access

Supported private repositories include (but are not limited to):

• Artifactory

• Nexus Repository

• AWS CodeArtifact

• Azure DevOps Artifacts

• GitHub Packages

—

Publishing to an internal repository

Configure repository access

Before uploading, ensure your Python environment is configured to authenticate with your internal package repository.

This is typically done using one of the following:

• ~/.pypirc

• Environment variables

• Repository-specific CLI tools (e.g. AWS CLI, Azure CLI)

Example ~/.pypirc:

[distutils]
index-servers =
    internal

[internal]
repository = https://pypi.company.com/repository/python-internal/
username = __token__
password = <your-access-token>

Never commit credentials or tokens to source control.

—

Build the package

Create distribution artifacts locally:

python -m build --sdist --wheel
python -m twine check dist/*

Verify that the generated files are valid before uploading.

—

Upload to the internal repository

Upload using twine:

python -m twine upload \
    --repository internal \
    dist/*

If successful, the package will be available to internal users immediately.

—

Installing from an internal repository

Users must configure pip to point to the internal index.

Example (temporary, per-install):

pip install oracle-connector \
    --index-url https://pypi.company.com/repository/python-internal/simple

Example (persistent, via pip.conf):

[global]
index-url = https://pypi.company.com/repository/python-internal/simple

Once configured, installation works the same as public PyPI:

pip install oracle-connector

—

Internal usage expectations

This package assumes:

• Users have access to the internal repository

• Oracle credentials are provided via environment variables

• SQL files are sourced from approved repositories or pipelines

The package intentionally avoids:

• Interactive credential prompts

• Embedded secrets

• Environment-specific logic

This makes it suitable for:

• Batch jobs

• CI/CD pipelines

• Scheduled workflows

• Secure shared environments

—

Versioning and release discipline

Because internal repositories do not allow overwriting existing versions, every release must use a new version number.

Recommended workflow:

• Use .devN versions for testing

• Promote to a clean version for release

• Treat releases as immutable artifacts

Example:

0.1.0.dev0   # Internal testing
0.1.0        # First internal release
0.1.1        # Bugfix release

—

CI/CD considerations

When publishing from CI/CD pipelines:

• Use repository-scoped access tokens

• Store credentials in secret managers

• Build artifacts once per commit/tag

• Upload only from protected branches

This ensures traceability, repeatability, and security.

—

Decommissioning and breaking changes

For breaking changes:

• Increment the minor or major version

• Communicate changes in advance

• Allow parallel installation of old and new versions when possible

Internal consumers should pin versions explicitly in production workflows.