Metadata-Version: 2.4
Name: ping-dataexport
Version: 0.4.1
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"
Dynamic: license-file

# ping-dataexport

## PING (ping-godhand)'s Universal Python Data Exporter

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

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

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

- **ความถูกต้องของชนิดข้อมูลมาก่อนเสมอ** — ข้อมูลจาก cursor → writer
  ทีละ Row ด้วย Data Type ดั้งเดิมเสมอเช่น: `DECIMAL(p,s)` ลงใน Parquet เป็น
  `decimal128(p,s)`, คอลัมน์ 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`)
  ที่ออกแบบมาสำหรับ Airflow

## Description (English)

Universal database data export: connect to a database, run a SQL query, stream
the result to **CSV / TXT / Parquet**. Built for data-platform work — exporting
legacy databases into files a data lake or warehouse can ingest.

- **Type fidelity first** — rows stream cursor → writer with native driver
  types. `DECIMAL(p,s)` lands in Parquet as `decimal128(p,s)`, ints with NULLs
  stay ints, dates stay dates. No pandas, ever.
- **Databases** — SQLite (stdlib, zero setup), SQL Server (MS ODBC / FreeTDS),
  Oracle (native thin mode or ODBC), PostgreSQL, MySQL / MariaDB, any ODBC source.
- **Two front doors** — a CLI and a library API (`ping_dataexport.api`) built
  for Airflow: exceptions instead of exit codes, results as objects.

## 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
```

## 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 | base folder for relative output paths; default: current directory |
| `TempFolder` | path | reserved for a future release |
| `LogFolder` | 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 |
| `LogFile` | path | explicit log **file** (rarely needed) — wins over `LogFolder`; `-lf` on the CLI wins over both |
| `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.

```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
```

## 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:

| 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` | `Yes`: clear the output directory before exporting |
| `-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: validate + preview, no files |

### 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 |
| `-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. 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 |
| `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.

### 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.
- 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).

## 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 |

## 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](LICENSE).
