sqlalchemy-mate 2.0.0.3

From json file

You can put your credential in a json file somewhere in your $HOME directory, and let sqlalchemy_mate smartly load from it.

You need to specify two things:

1. path to json file.

2. field path to the data. If your connect info is nested deeply in the json, you can use the dot notation json path to point to it.

content of json:

{
    "credentials": {
        "db1": {
            "host": "example.com",
            "port": 1234,
            "database": "test",
            "username": "admin",
            "password": "admin",
        },
        "db2": {
            ...
        }
    }
}

code:

from sqlalchemy_mate.api import EngineCreator

ec = EngineCreator.from_json(
    json_file="path-to-json-file",
    json_path="credentials.db1", # dot notation json path
)
engine = ec.create_postgresql_pg8000()

Default data fields are host, port, database, username, password.

If your json schema is different, you need to add the key_mapping to specify the field name mapping:

ec = EngineCreator.from_json(
    json_file="...",
    json_path="...",
    key_mapping={
        "host": "your-host-field",
        "port": "your-port-field",
        "database": "your-database-field",
        "username": "your-username-field",
        "password": "your-password-field",
    }
)

From $HOME/.db.json

You can put lots of database connection info in a .db.json file in your $HOME directory.

from sqlalchemy_mate.api import EngineCreator

ec = EngineCreator.from_home_db_json(identifier="db1")
engine = ec.create_postgresql_psycopg2()

$HOME/.db.json assumes flat json schema, but you can use dot notation json path for identifier to adapt any json schema:

{
    "identifier1": {
        "host": "example.com",
        "port": 1234,
        "database": "test",
        "username": "admin",
        "password": "admin",
    },
    "identifier2": {
        ...
    }
}

From json file on AWS S3

This is similar to from_json, but the json file is stored on AWS S3.

from sqlalchemy_mate.api import EngineCreator
ec = EngineCreator.from_s3_json(
    bucket_name="my-bucket", key="db.json",
    json_path="identifier1",
    aws_profile="my-profile",
)
engine = ec.create_redshift()

From Environment Variable

You can put your credentials in Environment Variable. For example:

export DB_DEV_HOST="..."
export DB_DEV_PORT="..."
export DB_DEV_DATABASE="..."
export DB_DEV_USERNAME="..."
export DB_DEV_PASSWORD="..."

from sqlalchemy_mate.api import EngineCreator
# read from DB_DEV_USERNAME, DB_DEV_PASSWORD, ...
ec = EngineCreator.from_env(prefix="DB_DEV")
engine = ec.create_redshift()

If you want to read database credential safely from cloud, for example, AWS EC2, AWS Lambda, you can use AWS KMS to decrypt your credentials

# leave aws_profile=None if you are on cloud
ec = EngineCreator.from_env(prefix="DB_DEV", kms_decrypt=True, aws_profile="xxx")
engine = ec.create_redshift()