attapon-th/dbhelper

#PYTHON #DATABASE #MYSQL #VERTICA #PARQUST #CSV

Database Helper

Version: 0.7.x

• Database Helper
  • Version: 0.7.x
  • Getting Started
    • Use:
    • Install SQLAlchemy Database API
  • Process SQL file
    • Usage
  • Dump SQL Query to CSV file
    • Usage
  • Dump SQL Query to Parquet file

Getting Started

pip install git+https://github.com/attapon-th/dbhelper@latest

Use:

dbper --help

Install SQLAlchemy Database API

# mysql
# dsn=mysql+pymysql://user:pass@host:port/dbname
pip install pymysql

# postgres
# dsn=postgresql+psycopg2://user:pass@host:port/dbname
pip install psycopg2-binary

# vertica
# dsn=vertica+vertica_python://user:pass@host:port/dbname
pip install git+https://github.com/attapon-th/sqlalchemy-vertica-python.git@latest

# other sqlalchemy support

other sqlalchemy support https://docs.sqlalchemy.org/en/20/core/engines.html

Process SQL file

Usage

export DB_DSN=vertica+vertica_python://user:pass@host:port/dbname
dbper process test.sql 

# or 

dbper process --dsn "vertica+vertica_python://user:pass@host:port/dbname" test.sql

example test.sql****

-- test.sql
-- create table
CREATE TABLE IF NOT EXISTS test (
  id int,
  name text
);

-- insert data
INSERT INTO test (id, name) VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c');

-- select data
SELECT * FROM test;

---

Dump SQL Query to CSV file

Usage

export DB_DSN=vertica+vertica_python://user:pass@host:port/dbname

dbper csv \
  --output test.csv
  "SELECT * FROM test"

# or
dbper csv \
  --dsn "vertica+vertica_python://user:pass@host:port/dbname" \
  --output test.csv
  "SELECT * FROM test"

Dump SQL Query to Parquet file

export DB_DSN=vertica+vertica_python://user:pass@host:port/dbname
dbper parquet \
  --output test.parquet \
  "SELECT * FROM test" 


# or
dbper parquet \
  --dsn "vertica+vertica_python://user:pass@host:port/dbname" \
  --output test.parquet \
  "SELECT * FROM test"