Metadata-Version: 2.4
Name: ping-dataexport
Version: 0.6.0
Summary: PING's Data Export Tool - To Parquet/CSV/TEXT as Accelerator for Data & AI Project
Author: Vorapol Ping (ping-godhand)
Maintainer: Godhand.DEV
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/ping-godhand/python-ping-dataexport
Project-URL: Source, https://github.com/ping-godhand/python-ping-dataexport
Project-URL: Issues, https://github.com/ping-godhand/python-ping-dataexport/issues
Project-URL: Organization, https://godhand.dev
Keywords: export,database,csv,parquet,etl,data-platform,airflow,sql-server,oracle,postgresql,mysql,mariadb,sqlite
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyarrow>=18
Provides-Extra: odbc
Requires-Dist: pyodbc>=5; extra == "odbc"
Provides-Extra: oracle
Requires-Dist: oracledb>=2; extra == "oracle"
Provides-Extra: postgresql
Requires-Dist: psycopg[binary]>=3.1; extra == "postgresql"
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == "mysql"
Provides-Extra: drivers
Requires-Dist: pyodbc>=5; extra == "drivers"
Requires-Dist: oracledb>=2; extra == "drivers"
Requires-Dist: psycopg[binary]>=3.1; extra == "drivers"
Requires-Dist: pymysql>=1.1; extra == "drivers"
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Dynamic: license-file

# ping-dataexport

[![PyPI](https://img.shields.io/pypi/v/ping-dataexport.svg)](https://pypi.org/project/ping-dataexport/)
[![Python](https://img.shields.io/pypi/pyversions/ping-dataexport.svg)](https://pypi.org/project/ping-dataexport/)
[![License: Apache-2.0](https://img.shields.io/badge/license-Apache--2.0-blue.svg)](https://github.com/ping-godhand/python-ping-dataexport/blob/main/LICENSE)

**Fast, simple export from a database to cloud storage or local storage — very
fast — an accelerator for data analytics projects.**

Connect to a database, run a SQL query, stream the result to **Parquet** (or
CSV / CSV.gz / TXT) on local disk or **directly into `s3://` `gs://` `abfs://`**.
Correct data types in Parquet, `row_id` / `data_date` audit columns, newline and
meta-character stripping, and one pipeline behind three front doors: **CLI**,
**Airflow**, **Python import**. Batch, watermark (incremental), split by column,
split by date, backdate and backfill patterns are built in.

> Provided as-is. No support, no warranty, no roadmap commitments, use at your own risk.

## คำอธิบาย (ภาษาไทย)

โปรแกรมช่วย Export ข้อมูลอย่างง่ายและเร็ว: เชื่อมต่อฐานข้อมูล รันคำสั่ง SQL
แล้ว Export ผลลัพธ์ออกเป็นไฟล์ **Parquet / CSV / TXT** ลงดิสก์หรือตรงเข้า
**S3 / Google Cloud Storage / Azure Data Lake** สร้างมาเพื่องาน Data Platform
โดยเฉพาะ — ส่งออกข้อมูลจากฐานข้อมูลเดิม ให้เป็นไฟล์ที่ Data Lake หรือ
Data Warehouse นำไปใช้ต่อได้ทันที

- **ความถูกต้องของชนิดข้อมูลมาก่อนเสมอ** — ข้อมูลไหลจาก cursor → writer
  ทีละ Row ด้วย Data Type ดั้งเดิม: `DECIMAL(p,s)` ลงใน Parquet เป็น
  `decimal128(p,s)` ไม่ผ่าน float, คอลัมน์ int ที่มี NULL ยังคงเป็น int,
  วันที่ยังคงเป็นวันที่ และไม่ใช้ pandas เด็ดขาด
- **ฐานข้อมูลที่รองรับ** — SQLite (Library มาตรฐาน ไม่ต้องติดตั้งอะไรเพิ่ม),
  SQL Server (MS ODBC / FreeTDS), Oracle (โหมด thin แบบ Native หรือ ODBC),
  PostgreSQL, MySQL / MariaDB และแหล่งข้อมูล ODBC ใดก็ได้
- **ใช้งานได้สามทาง** — CLI `ping-dataexport`, Library API (`ping_dataexport.api`)
  สำหรับ Python script และ Airflow task — pipeline เดียวกันทั้งหมด

## Why this tool

| | |
|---|---|
| **Correct types, first of all** | Rows stream cursor → writer with the driver's native Python types. The Parquet schema comes from `cursor.description`, never from guessing at the data. No pandas, no numpy, ever — they silently turn `int`-with-NULL into `float` and `Decimal` into binary floats. |
| **Fast** | Streaming `fetchmany` batches into typed Arrow `RecordBatch`es; server-side cursors for PostgreSQL and MySQL; `arraysize` tuned for Oracle and ODBC. Memory stays flat however large the table. |
| **Simple** | A data engineer operates it from `config.ini` + `job.ini`. One line (`ExportFolder=s3://lake/exports`) moves every job from disk to the lake. |
| **Three front doors, one pipeline** | The CLI, the Python API and Airflow tasks run the identical build → validate → resolve → execute path. Exceptions, not exit codes, in library mode. |
| **Ingestion patterns** | Full dump, split by row count, one file per distinct value, per day, per month, relative windows (`@@CURRENT_DATE@@-7@@`), and **watermark** incremental runs with state kept beside the output — in the bucket if that is where the output goes. |
| **Zero extra cloud dependencies** | `s3://`, `gs://`, `abfs://` / `abfss://` are served by `pyarrow.fs` — the same PyArrow that writes the Parquet. No boto3, no SDKs, no fsspec. |

### What the lake sees

| Database column | Parquet type | Notes |
|-----------------|--------------|-------|
| `DECIMAL(18,4)`, Oracle `NUMBER(18,4)`, PG `numeric(18,4)` | `decimal128(18,4)` | exact — never through `float` |
| `INT` / `SMALLINT` with NULLs | `int32` / `int64`, nullable | stays an integer |
| `BIGINT`, Oracle `NUMBER(p,0)` | `int64` | |
| `DATE` | `date32` | |
| `DATETIME` / `TIMESTAMP` | `timestamp[us]` | |
| `TIME` | `time32[ms]` | |
| `BIT` / `BOOLEAN` | `bool` | |
| `VARBINARY` / `BLOB` / `bytea` | `binary` | |
| text of any kind | `string` (UTF-8) | strip rules applied when requested |

CSV / TXT render the same values losslessly (`Decimal` via `str()`, ISO dates,
hex for binary). The complete per-driver mapping — and the short, public list of
known precision edge cases — is in
[docs/DATA_TYPES.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/DATA_TYPES.md).

### What it is not

No change-data-capture, no loading into the warehouse (it stops at the file or
the bucket), no scheduler (Airflow does that), no pandas.

## Install

Requires Python 3.10 or newer.

```bash
pip install ping-dataexport               # base (pyarrow) — sqlite works out of the box
pip install ping-dataexport[odbc]         # + pyodbc      (SQL Server, FreeTDS, any ODBC)
pip install ping-dataexport[oracle]       # + oracledb    (Oracle native, thin mode)
pip install ping-dataexport[postgresql]   # + psycopg     (PostgreSQL)
pip install ping-dataexport[mysql]        # + pymysql     (MySQL and MariaDB)
pip install ping-dataexport[drivers]      # + all of the above
```

## Quick start

```ini
; config.ini
[Main]
ExportFolder=./output
RowIDColumn=row_id
DataDateColumn=data_date

[PG01]
Type=postgresql
Host=pghost
Database=analytics
Username=app
Password=secret
```

```bash
ping-dataexport -s PG01 -q "SELECT * FROM sales" -o sales.parquet                       # one Parquet file
ping-dataexport -s PG01 -q "SELECT * FROM sales" -m groupby -col region -dir by_region -pf sales
ping-dataexport -s PG01 -q "SELECT * FROM sales" -o s3://lake/raw/sales.parquet         # straight to S3
```

```python
from ping_dataexport.api import run_export
result = run_export("PG01", "SELECT * FROM sales", output="sales.parquet")
print(result.rows, result.files)
```

The rest of this page is the reference: config keys (§1), CLI options (§2),
export modes (§3), job files (§4), the Python / Airflow API (§5).

## 1. Create a config.ini

One `[Main]` section for defaults, then one section per database source. The
section name is what `-s` / `Source=` refers to:

```ini
[Main]
; base for relative output paths (default: current directory)
ExportFolder=./output
; default: {ExportFolder}/logs — the log file is dataexport-YYYYMMDD.log
LogFolder=./logs
FetchSize=10000
ForceMakeDir=Yes
; optional audit columns
RowIDColumn=row_id
DataDateColumn=data_date

[SQLITE01]
Name=DEMO-SQLITE
Type=sqlite
Database=./demo.sqlite

[DB01]
Name=SQLSERVER-PRD
Type=mssql-odbc
Host=host
Port=1433
Database=DB
Username=user
Password=secret

[PG01]
Name=POSTGRES-PRD
Type=postgresql
ConnectionString=host=pghost port=5432 dbname=analytics sslmode=require
Username=app
Password=secret
```

A full sample with every `Type` in both connection forms ships as
`config.sample.ini` in the source distribution. Comments must be on their own
line: a `;` after a value is part of the value (connection strings contain `;`).

**Where the file is looked up.** `-cf` / `configfile=` point anywhere. When
you do not specify one, `config.ini` is searched in this order and the run
fails with a message naming both places if neither has it:

1. the current working directory (where you run the command from)
2. the directory of the running `.py` script (e.g. next to your `main.py`)

`job.ini` follows the same rule.

### 1.1 Main config keys

All optional; a missing `[Main]` yields the defaults.

| Key | Type | Possible values / default |
|-----|------|---------------------------|
| `ExportFolder` | path or URI | base folder for relative output paths; default: current directory. Local (`./out`, `D:\export`, `/Volumes/data`, `file:///data/out`) **or a cloud URI** (`s3://bucket/prefix`, `gs://…`, `abfs://…`) — see [1.3](#13-cloud-destinations--cloud) |
| `TempFolder` | local path | reserved for a future release; **always local** |
| `LogFolder` | local path | folder for log files; default `{ExportFolder}/logs`. The log file inside it is **`dataexport-YYYYMMDD.log`** — one per day, every job appends. Relative = under `ExportFolder`. No `ExportFolder` and no `LogFolder` ⇒ console only. **Always local** — with a cloud `ExportFolder` set it explicitly or get console only |
| `LogFile` | local path | explicit log **file** (rarely needed) — wins over `LogFolder`; `-lf` on the CLI wins over both; **always local** |
| `FetchSize` | int > 0 | rows per batch; default `50000` |
| `PreviewRow` | int > 0 | rows shown in console/test preview; default `10` |
| `ForceMakeDir` | bool | `Yes`/`No`, `True`/`False`, `1`/`0`, `on`/`off`; default `Yes` |
| `RowIDColumn` | string | audit column name (running row number); absent = no column |
| `DataDateColumn` | string | audit column name (data extract timestamp); absent = no column |

### 1.2 Database config keys — type / possible values

Every database section has `Name` + `Type`, then **one** way to say where the
database is:

- **(A) `ConnectionString`** — the driver's own string, passed through as-is.
  **`ConnectionString` overrides all**: when it is set, `Host`, `Port`,
  `Database`, `Service`, `SID`, `Driver` and `DSN` are ignored. Only
  `Username` / `Password` are merged in, and only when the string does not
  already carry them (`UID=`/`PWD=`, `user=`/`password=`).
- **(B) discrete keys** — `Host` / `Port` / `Database` (+ `Service` or `SID` for
  Oracle, `Driver` for ODBC); the connector builds the string for you.

| Key | Type | Possible values / notes |
|-----|------|--------------------------|
| `Type` | enum | `sqlite` \| `mssql-odbc` \| `freetds` \| `oracle-odbc` \| `odbc` \| `oracle` \| `postgresql` \| `mysql` \| `mariadb` — case-insensitive, aliases below. Omitted with `ConnectionString`/`DSN` present ⇒ `odbc` |
| `Name` | string | display name in logs; default: the section name |
| `ConnectionString` | string | **(A) overrides all** — form depends on `Type`, see *Per database* below |
| `DSN` | string | ODBC data source name (pyodbc Types only); like `ConnectionString`, wins over discrete keys |
| `Host` / `Port` | string / int | (B) server address; default port per Type: 1433 / 1521 / 5432 / 3306 |
| `Database` | string | (B) database name; for `sqlite`: the **file path** (opened read-only) |
| `Service` / `SID` | string | (B) Oracle service name or SID |
| `Driver` | string | (B) ODBC driver name override, e.g. `ODBC Driver 17 for SQL Server` |
| `Username` | string | login user |
| `Password` | string | plain-text password |
| `PasswordBase64` | string | base64-encoded password (obfuscation, not encryption); when both are set, `Password` wins |
| `Encoding` | string | text encoding: pyodbc `setdecoding` (e.g. `tis-620`), PostgreSQL `client_encoding`, MySQL `charset` (default `utf8mb4`) |

`Type` values and the aliases accepted for each (one canonical name per line):

| `Type` | Aliases | Database | Python package (pip) | OS-level |
|--------|---------|----------|----------------------|----------|
| `sqlite` | — | SQLite file | nothing (stdlib) | nothing |
| `mssql-odbc` | `mssql`, `sqlserver` | SQL Server | `pyodbc` | Microsoft ODBC Driver 17/18 |
| `freetds` | — | SQL Server / Sybase (legacy) | `pyodbc` | FreeTDS |
| `oracle-odbc` | — | Oracle via ODBC | `pyodbc` | Oracle ODBC driver |
| `odbc` | — | anything with an ODBC driver | `pyodbc` | that driver |
| `oracle` | — | Oracle native (thin mode) | `oracledb` | nothing |
| `postgresql` | `postgres`, `pgsql`, `pg` | PostgreSQL | `psycopg[binary]` | nothing |
| `mysql` | — | MySQL | `pymysql` | nothing |
| `mariadb` | `maria` | MariaDB | `pymysql` | nothing (Connector/C **not** needed) |

#### Per database — both forms

Each block shows **(A)** `ConnectionString` (overrides all) and **(B)** discrete
keys. `Username`/`Password` (or `PasswordBase64`) go in either form.

**SQLite** — `Type=sqlite`. `Database=` is the file path, opened read-only.

```ini
[SQLITE01]
Type=sqlite
; (B)
Database=./demo.sqlite
; (A) sqlite URI
;ConnectionString=file:/data/demo.sqlite?mode=ro
```

**SQL Server** — `Type=mssql-odbc` (`mssql`, `sqlserver`). `Driver=` optional,
default `ODBC Driver 18 for SQL Server`; default port 1433.

```ini
[MSSQL01]
Type=mssql-odbc
; (B)
Host=sqlhost
Port=1433
Database=DB
;Driver=ODBC Driver 17 for SQL Server
; (A)
;ConnectionString=Driver={ODBC Driver 18 for SQL Server};Server=sqlhost,1433;Database=DB;Encrypt=no
Username=sa
Password=secret
```

**SQL Server / Sybase via FreeTDS** — `Type=freetds`. For legacy servers
(SQL 2000/2005). `Driver=` default `FreeTDS`; `Encoding=` for old codepages.

```ini
[TDS01]
Type=freetds
; (B)
Host=legacyhost
Port=1433
Database=DB
; (A) DSN from odbc.ini
;ConnectionString=DSN=legacy2000;TDS_Version=7.2
Username=sa
Password=secret
;Encoding=tis-620
```

**Oracle via ODBC** — `Type=oracle-odbc`. `Driver=` is **required** in (B).

```ini
[ORAODBC01]
Type=oracle-odbc
; (B)
Driver=Oracle in OraClient19Home1
Host=orahost
Port=1521
Service=ORCLPDB
; (A)
;ConnectionString=Driver={Oracle in OraClient19Home1};DBQ=orahost:1521/ORCLPDB
Username=scott
Password=tiger
```

**Any ODBC source** — `Type=odbc` (also the default when `Type` is omitted but
`ConnectionString`/`DSN` is present). No (B) form: give `DSN=` or (A).

```ini
[ODBC01]
Type=odbc
; a DSN from odbc.ini ...
DSN=my_dsn
; ... or (A)
;ConnectionString=Driver={PostgreSQL Unicode};Server=host;Port=5432;Database=DB
Username=user
Password=secret
```

**Oracle native** — `Type=oracle`. Thin mode, no Oracle client install.
(B) needs `Service` or `SID`; default port 1521. (A) is an Easy Connect string
or a full TNS descriptor. `NUMBER` columns arrive as `int` / `Decimal`, never
`float`.

```ini
[ORA01]
Type=oracle
; (B)
Host=orahost
Port=1521
Service=ORCLPDB
;SID=ORCL
; (A) Easy Connect
;ConnectionString=orahost:1521/ORCLPDB
;ConnectionString=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB)))
Username=scott
Password=tiger
```

**PostgreSQL** — `Type=postgresql` (`postgres`, `pgsql`, `pg`). Default port
5432; `Encoding=` → `client_encoding`. (A) is a libpq conninfo or URI. Rows
stream through a server-side cursor; `json`/`jsonb` export as text.

```ini
[PG01]
Type=postgresql
; (B)
Host=pghost
Port=5432
Database=analytics
; (A) conninfo
;ConnectionString=host=pghost port=5432 dbname=analytics sslmode=require
; (A) URI
;ConnectionString=postgresql://app:secret@pghost:5432/analytics
Username=app
Password=secret
```

**MySQL** — `Type=mysql`. Default port 3306; `Encoding=` → `charset` (default
`utf8mb4`). (A) is `key=value;key=value` handed to `pymysql.connect()`
(`host, port, user, password, database, charset, unix_socket, connect_timeout, …`).
Rows stream through an unbuffered cursor.

```ini
[MY01]
Type=mysql
; (B)
Host=myhost
Port=3306
Database=shop
; (A)
;ConnectionString=host=myhost;port=3306;database=shop;charset=utf8mb4
Username=app
Password=secret
```

**MariaDB** — `Type=mariadb` (`maria`). Same driver and keys as MySQL
(MariaDB speaks the MySQL protocol; the MariaDB Connector/C is not needed).
The separate `Type` keeps configs readable.

```ini
[MARIA01]
Type=mariadb
; (B)
Host=mariahost
Port=3306
Database=shop
; (A)
;ConnectionString=host=mariahost;port=3306;database=shop
Username=app
Password=secret
```

```ini
PasswordBase64=c2VjcmV0
; = base64("secret"); Password= wins when both are present
```

### 1.3 Cloud destinations — `[Cloud]`

Any output — `-o` / `-dir`, `Output=` / `OutputDir=`, or `ExportFolder`
itself — may point at an object store instead of a disk. The files are written
**directly** through `pyarrow.fs` (no staging copy, no extra package):

```
s3://bucket/prefix                                 Amazon S3 (MinIO etc. via AWSEndpoint)
gs://bucket/prefix                                 Google Cloud Storage
abfs://container@account.dfs.core.windows.net/p    Azure Data Lake Gen2 / Blob (abfss:// same)
abfs://container/prefix                            Azure, account from AzureAccountName
file:///data/export                                local disk spelled as a URI
```

```bash
ping-dataexport -s ORA01 -q "SELECT * FROM sales" -o s3://lake/raw/sales.parquet
ping-dataexport -s ORA01 -q "SELECT * FROM sales" -m groupby -col region -dir gs://lake/by_region -pf sales
```

Set `ExportFolder=s3://lake/exports` and every relative `Output=`/`OutputDir=`
in your job files lands in the bucket — the same job.ini works on disk and in
the cloud. Watermark state is kept beside the output, so an incremental job
into a cloud folder resumes from the bucket. `LogFolder`, `LogFile` and
`TempFolder` stay on the machine that runs the export.

Credentials — every key optional; the first level with a value wins (explicit
beats ambient). `PreferredAuthenticationType = Config | ENV | OS` pins one level
instead. Azure: `AzureAccountKey` wins over the service principal when both are set.

| Level | AWS | Azure | Google |
|-------|-----|-------|--------|
| `[Cloud]` | `AWSAccessKey` + `AWSSecretKey` (+ `AWSSessionToken`, `AWSRegion`, `AWSEndpoint`) | `AzureAccountKey`, or `AzureTenantID` + `AzureClientID` + `AzureClientSecret`; `AzureAccountName` | `GoogleCredentialJSONPath` |
| environment | `AWS_ACCESS_KEY_ID` / `AWS_ACCESS_KEY`, `AWS_SECRET_ACCESS_KEY` / `AWS_SECRET_KEY`, `AWS_REGION` | `AZURE_STORAGE_ACCOUNT_KEY`, or `AZURE_TENANT_ID`, `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`; `AZURE_STORAGE_ACCOUNT_NAME` | `GOOGLE_APPLICATION_CREDENTIALS` |
| the provider's own chain | `~/.aws`, EC2/ECS instance role | managed identity, `az login` | `gcloud auth application-default login`, GCE/GKE metadata |

```ini
[Cloud]
PreferredAuthenticationType=
GoogleCredentialJSONPath=
AzureAccountName=
AzureTenantID=
AzureClientID=
AzureClientSecret=
AWSAccessKey=
AWSSecretKey=
```

Needs a pyarrow build that includes the provider's filesystem: the PyPI wheel
has S3, GCS and Azure; conda's pyarrow lacks GCS and Azure. Full rules, log
lines and the library `cloud=` argument:
[docs/FILESYSTEM.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/FILESYSTEM.md).

## 2. Run an export (CLI)

`ping-dataexport` and `python -m ping_dataexport` are equivalent. The config
file defaults to `./config.ini` (`-cf` to point elsewhere).

```bash
# preview to the console (no -o/-dir): prints the first rows
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales"

# single file — format inferred from the extension (.csv/.txt/.parquet)
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.parquet
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.csv -gz   # gzip

# validate + preview only, write nothing
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.csv -t
```

All options. The short form is the job.ini key's initials (`-df` = `DateFrom`),
the long form the key in lowercase; both are stable:

| Option | Full name | Meaning |
|--------|-----------|---------|
| `-s` | `--source` | DB config section name (e.g. `DB01`) |
| `-q` | `--query` | SQL query |
| `-o` | `--output` | single output file (format from extension, or `-f`) |
| `-dir` | `--outputdir` | output directory (required for `-m` modes); `-od` still works as a legacy alias |
| `-pf` | `--prefix` | output filename prefix for mode exports; `-bn`/`--basename` still work as legacy aliases |
| `-f` | `--format` | `csv`, `txt`, `parquet` |
| `-gz` | `--gzip` | gzip the output (CSV/TXT only) |
| `-sep` | `--sep` | field separator (default `,`; `\t` maps to tab) |
| `-quo` | `--quote` | quote character (default `"`) |
| `-stnl` | `--stripnewline` | newline handling in text columns: `space` \| `blank` \| `escape` \| `doubleescape` |
| `-stmc` | `--stripmetachar` | strip invisible/control characters from text columns |
| `-fs` | `--fetchsize` | fetch size (rows per batch) |
| `-m` | `--mode` | export mode: `groupby` \| `date` \| `relativedate` \| `monthbydate` \| `splitrow` \| `watermark` — see section 3 |
| `-col` | `--column` | column the mode partitions on |
| `-row` | `--row` | rows per file (`-m splitrow`) |
| `-df` | `--datefrom` | start / anchor date `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (see *Date tokens*); omitted with `-dr`/`-mr` ⇒ today (system date) |
| `-dt` | `--dateto` | end date `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (`-m date`) |
| `-dr` | `--daysrelative` | days back from `-df` (`-m relativedate`) |
| `-mr` | `--monthsrelative` | months back from `-df` (`-m monthbydate`) |
| `-rm` | `--removeexisting` | clear the output directory before exporting; `-rm` alone means Yes (`-rm No` is accepted) |
| `-j` | `--job` | run one job.ini section — see section 4 |
| `-jf` | `--jobfile` | job file path (default `./job.ini`) |
| `-p` | `--parallel` | run ALL job sections in parallel with N workers (default: CPU count); `-pl` still works as a legacy alias |
| `-n` | `--name` | job name shown in the log's jobname column |
| `-cf` | `--configfile` | config file path (default `config.ini`: current dir, then the script's dir) |
| `-lf` | `--logfile` | log file path (overrides `LogFolder`/`LogFile`) |
| `-t` | `--test` | test mode (dry run): validate + preview, no files; `--dry-run` is the same flag |
| `-V` | `--version` | print the version and exit |

### Which options must go together

| If you use… | You must also have… | Notes |
|-------------|--------------------|-------|
| (anything) | `-s` + `-q` | the two basics — always required, unless `-j` supplies them from a job file |
| `-o` | nothing else | single file; **cannot** be combined with `-dir`/`-pf` |
| `-m` (any mode) | `-dir` + `-pf` | every mode writes multiple files, so it needs a directory + filename prefix |
| `-dir` or `-pf` | `-m` | they only make sense for mode exports |
| `-m groupby` / `watermark` | `-col` | the column to group / track |
| `-m date` | `-col` + `-df` + `-dt` | the date column and an explicit range |
| `-m relativedate` | `-col` + `-dr` | `-df` optional (defaults to today) |
| `-m monthbydate` | `-col` + `-mr` | `-df` optional (defaults to today) |
| `-m splitrow` | `-row` | no `-col` needed |
| `-rm` | `-m` (not `watermark`) | clearing the directory would destroy the watermark state |
| `-gz` | CSV/TXT output | rejected with parquet (it compresses itself) |
| no `-o` and no `-dir` | — | console preview only (first `PreviewRow` rows, nothing written) |

## 3. Export modes (partitioned outputs)

Modes split one query into multiple files. All need `-dir` (output directory)
+ `-pf` (filename prefix); partition values are always bound SQL parameters.

### Date tokens for `-df` / `-dt` (`DateFrom` / `DateTo`)

Instead of a fixed `yyyy-MM-dd`, both accept the current date of the machine
running the export, optionally shifted by whole days:

| Token | Meaning |
|-------|---------|
| `@@CURRENT_DATE@@` | today |
| `@@CURRENT_DATE@@-N@@` | today minus N days, e.g. `@@CURRENT_DATE@@-7@@` |
| `@@CURRENT_DATE@@+N@@` | today plus N days, e.g. `@@CURRENT_DATE@@+1@@` |

`N` is a plain integer (`-1`, `-30`, `-100`, `+10` …). The token is
case-insensitive. Anything else — a missing closing `@@`, a non-integer, a
space, a double sign — is rejected with a validation error before anything runs.
Handy in job files that run daily:

```ini
[Last7Days]
Source=DB01
Query=SELECT * FROM sales
Mode=date
Column=txn_date
DateFrom=@@CURRENT_DATE@@-7@@
DateTo=@@CURRENT_DATE@@-1@@
OutputDir=out/last7
BaseName=sales
```

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m date -col txn_date \
    -df "@@CURRENT_DATE@@-7@@" -dt "@@CURRENT_DATE@@" -dir last7 -pf sales
```

**`groupby` — one file per distinct value of a column:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m groupby -col region -dir by_region -pf sales
```
```
by_region/
├── sales_@@NULL@@.csv  <- rows where region IS NULL
├── sales_MY.csv
├── sales_SG.csv
├── sales_TH.csv
└── sales_VN.csv
```

**`date` — one file per day in a date range (inclusive):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m date -col txn_date \
    -df 2026-01-01 -dt 2026-01-05 -dir daily -pf sales
```
```
daily/
├── sales_2026-01-01.csv
├── sales_2026-01-02.csv
├── sales_2026-01-03.csv
├── sales_2026-01-04.csv
└── sales_2026-01-05.csv
```

**`relativedate` — N days back from an anchor date, anchor included
(omit `-df` to anchor on today):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m relativedate -col txn_date \
    -df 2026-01-10 -dr 3 -dir rel -pf sales
```
```
rel/
├── sales_2026-01-07.csv
├── sales_2026-01-08.csv
├── sales_2026-01-09.csv
└── sales_2026-01-10.csv
```

**`monthbydate` — N months back plus the anchor month (the anchor month stops
at the anchor date; omit `-df` to anchor on today):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m monthbydate -col txn_date \
    -df 2026-03-15 -mr 3 -dir monthly -pf sales
```
```
monthly/
├── sales_202512.csv
├── sales_202601.csv
├── sales_202602.csv
└── sales_202603.csv    <- 2026-03-01 .. 2026-03-15 only
```

**`splitrow` — rotate to a new file every N rows:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales ORDER BY id" -m splitrow -row 100000 -dir split -pf part
```
```
split/
├── part_1.csv          <- 100,000 rows each
├── part_2.csv
└── part_3.csv          <- remainder
```

**`watermark` — incremental: only rows newer than the last run:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m watermark -col txn_date -dir incr -pf sales
```
```
incr/
├── sales_20260831_120000.csv    <- filename = run timestamp
└── __watermark__.ini            <- state file: highest txn_date exported so far
```

Run the watermark job again: no new rows → no new file; new rows → one more
timestamped file. The state advances only after a successful export. Delete
`__watermark__.ini` to start over from a full export.

Every run also prints structured log lines to the console (and to the log
file — see *Output and logs* below):

```
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | CONFIG | JOB | source=DB01 mode=groupby
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | PARTITION | region = 'TH'
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | FILE | ./output/by_region/sales_TH.csv
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | PROGRESS | 200 rows (finished)
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | RESULT | COMPLETED | 0.2 seconds
```

## 4. Job files (job.ini)

Put recurring exports in a job file so you never retype long commands. Each
`[Section]` is **one job**: run one with `-j SectionName`, or all of them at
once with `-p`. Every key is simply a CLI option written as a config key:

```ini
[DailySales]
Source=DB01
Query=SELECT id, txn_date, amount FROM sales
    WHERE region = 'TH'
Mode=date
Column=txn_date
DateFrom=2026-01-01
DateTo=2026-01-31
OutputDir=out/daily_sales
BaseName=sales
Format=csv

[FullDump]
Source=DB01
Query=SELECT * FROM sales
Output=out/full_dump.parquet
```

```bash
ping-dataexport -j DailySales                 # one section (job.ini by default, -jf elsewhere)
ping-dataexport -j DailySales -dir other_dir   # CLI options override the section
ping-dataexport -p 4                          # ALL sections in parallel, 4 workers
```

Every job.ini key and the CLI option it mirrors (keys are case-insensitive):

| Job key | CLI option | Meaning |
|---------|------------|---------|
| `Source` | `-s` / `--source` | which config.ini DB section to export from |
| `Query` | `-q` / `--query` | the SQL to run; indent continuation lines for multi-line queries |
| `Output` | `-o` / `--output` | single output file path |
| `OutputDir` | `-dir` / `--outputdir` | output directory (needed when `Mode=` is set) |
| `BaseName` | `-pf` / `--prefix` | output filename prefix for mode exports; `Prefix=` is accepted as the same key |
| `Format` | `-f` / `--format` | `csv` / `txt` / `parquet` (else inferred from `Output` extension) |
| `Gzip` | `-gz` / `--gzip` | `Yes`/`No` — gzip the output (CSV/TXT only) |
| `Sep` | `-sep` / `--sep` | field separator (default `,`; `\t` = tab) |
| `Quote` | `-quo` / `--quote` | quote character (default `"`) |
| `StripNewLine` | `-stnl` / `--stripnewline` | `space` \| `blank` \| `escape` \| `doubleescape` |
| `StripMetaChar` | `-stmc` / `--stripmetachar` | `Yes`/`No` — strip invisible/control characters |
| `FetchSize` | `-fs` / `--fetchsize` | rows per batch |
| `Mode` | `-m` / `--mode` | `groupby` / `date` / `relativedate` / `monthbydate` / `splitrow` / `watermark` |
| `Column` | `-col` / `--column` | the column the mode partitions on |
| `Row` | `-row` / `--row` | rows per file (`Mode=splitrow`) |
| `DateFrom` | `-df` / `--datefrom` | start / anchor date, `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` |
| `DateTo` | `-dt` / `--dateto` | end date, `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (`Mode=date`) |
| `DaysRelative` | `-dr` / `--daysrelative` | days back from `DateFrom` (`Mode=relativedate`) |
| `MonthsRelative` | `-mr` / `--monthsrelative` | months back from `DateFrom` (`Mode=monthbydate`) |
| `RemoveExisting` | `-rm` / `--removeexisting` | `Yes`: clear `OutputDir` before exporting |
| `Name` | `-n` / `--name` | job name shown in logs; default: the section name |

A full sample ships as `job.sample.ini` in the source distribution.

## 5. Run as Python import (library / Airflow)

`from ping_dataexport.api import run_export, run_job, run_jobfile`. Every CLI
option is a keyword argument; every call returns an `ExportResult`
(`ok`, `rows`, `partitions`, `files`, `seconds`) or raises. Failures raise
exceptions (`ConfigError`, `JobValidationError`, `ExportError`) — never
`sys.exit` — so an Airflow task fails cleanly. `run_jobfile` is the exception:
one job's failure never stops the others; check each `ExportResult.status`.

### 5.1 With a config file

`configfile=` points at your `config.ini` (default `config.ini`, looked up in
the current directory, then next to the running script — see section 1).

```python
from ping_dataexport.api import run_export, run_job, run_jobfile

# ad-hoc — source = a config.ini section name
result = run_export("DB01", "SELECT * FROM sales",
                    configfile="config.ini", output="sales.parquet")
print(result.rows, result.files)

# groupby mode: outputdir + basename, like -dir/-pf on the CLI
run_export("DB01", "SELECT * FROM sales", configfile="config.ini",
           mode="groupby", column="region", outputdir="by_region", basename="sales")

# one job.ini section (overrides win over the section, like CLI options)
result = run_job("DailySales", jobfile="job.ini", configfile="config.ini",
                 overrides={"outputdir": "other_dir"})

# every job.ini section in parallel
results = run_jobfile(jobfile="job.ini", configfile="config.ini", workers=4)
failed = [r for r in results if not r.ok]
```

### 5.2 Without a config file

Define the database in code — as a dict of the same keys as a config.ini
section (`Type`, `Host`, `ConnectionString`, …; same rules, `ConnectionString`
overrides all) or as a `DBConfig` — and pass `[Main]` settings as `main=`.
No `config.ini` is read or required.

```python
from ping_dataexport.api import run_export, run_job, run_jobfile

PG = {"Type": "postgresql", "Host": "pghost", "Port": 5432,
      "Database": "analytics", "Username": "app", "Password": "secret"}
MAIN = {"ExportFolder": "./output", "FetchSize": 10000, "RowIDColumn": "row_id"}

# ad-hoc — source = the dict itself
result = run_export(PG, "SELECT * FROM sales", main=MAIN, output="sales.parquet")

# a typed object works the same
from ping_dataexport.config import DBConfig
sqlite = DBConfig(section="LOCAL", name="LOCAL", type="sqlite", database="demo.sqlite")
run_export(sqlite, "SELECT * FROM t", output="t.csv")

# job files without config.ini: sources= maps every Source= name used in job.ini
SOURCES = {"DB01": PG, "MY01": {"Type": "mysql", "ConnectionString": "host=myhost;database=shop",
                                "Username": "app", "PasswordBase64": "c2VjcmV0"}}
run_job("DailySales", jobfile="job.ini", sources=SOURCES, main=MAIN)
run_jobfile(jobfile="job.ini", sources=SOURCES, main=MAIN, workers=4)
```

`sources=` and `main=` also work **alongside** a config file: an in-code
source with the same name as a config.ini section wins. `cloud=` passes the
`[Cloud]` credentials the same way — e.g. from an Airflow connection:

```python
run_export(PG, "SELECT * FROM sales", output="s3://lake/raw/sales.parquet",
           cloud={"AWSAccessKey": conn.login, "AWSSecretKey": conn.password})
```

### 5.3 Airflow

```python
from airflow.decorators import task
from ping_dataexport.api import run_job

@task
def export_daily_sales():
    result = run_job("DailySales", jobfile="/opt/airflow/dags/job.ini",
                     configfile="/opt/airflow/dags/config.ini")
    return result.rows            # an exception fails the task
```

The package also works with **no pip install at all**: copy the
`ping_dataexport/` folder next to your code and import it — relative imports
only, no metadata lookups.

## Output and logs

- Relative output paths resolve under `ExportFolder`; `ForceMakeDir=Yes`
  creates missing directories. Outputs and `ExportFolder` may be cloud URIs
  (`s3://`, `gs://`, `abfs://`, `abfss://`, [1.3](#13-cloud-destinations--cloud));
  logs never are.
- Every run prints structured lines to the console and appends them to **one
  log file per day**: `{LogFolder}/dataexport-YYYYMMDD.log`, where
  `LogFolder` defaults to `{ExportFolder}/logs`. Sequential and parallel runs,
  and every job, share that file — the second column of each line is the job
  name. Priority: `-lf path` > `LogFile=` > `LogFolder=` > default. With no
  `ExportFolder` and no `LogFolder` the log goes to the console only.
- CLI exit codes: `0` success, `1` failure (in `-p` parallel mode: `1` if any
  job failed), `130` when interrupted.

## Driver prerequisites

| `Type` | pip | OS-level |
|--------|-----|----------|
| `sqlite` | nothing (stdlib) | nothing |
| `oracle` | `oracledb` | nothing (thin mode) |
| `postgresql` | `psycopg[binary]` | nothing |
| `mysql` / `mariadb` | `pymysql` | nothing (pure Python; the MariaDB Connector/C is **not** needed) |
| `mssql-odbc` | `pyodbc` | Microsoft ODBC Driver 17/18 for SQL Server |
| `freetds` | `pyodbc` | FreeTDS (`apt install tdsodbc`) |
| `oracle-odbc` / `odbc` | `pyodbc` | your ODBC driver |

## More

- Source, issues and the developer docs (data types, connectors, filesystem,
  job handling, logging, testing):
  <https://github.com/ping-godhand/python-ping-dataexport>
- Runnable examples to copy (`main.py`, `simple_parquet.py`, an Airflow DAG):
  [main/](https://github.com/ping-godhand/python-ping-dataexport/tree/main/main)

## Author & License

Built by **Vorapol Ping (ping-godhand)** — https://github.com/ping-godhand
for **Godhand.DEV** — https://godhand.dev

Copyright (c) 2026 Godhand.DEV.
Licensed under the [Apache License 2.0](https://github.com/ping-godhand/python-ping-dataexport/blob/main/LICENSE).
