sqlalchemy-mate 0.0.5

A library extend sqlalchemy module, makes CRUD easier.

---

Welcome to sqlalchemy_mate Documentation

A library extend sqlalchemy module, makes CRUD easier.

Features

• Read Database Credential Safely

• Smart Insert

Read Database Credential Safely

• From json file

• From $HOME/.db.json

• From json file on AWS S3

• From Environment Variable

From json file

content of json:

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

code:

from sqlalchemy_mate import EngineCreator

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

Any json scheme should work.

From $HOME/.db.json

from sqlalchemy_mate import EngineCreator

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

$HOME/.db.json assumes flat json schema:

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

From json file on AWS S3

from sqlalchemy_mate 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

from sqlalchemy_mate import EngineCreator

ec = EngineCreator.from_env(prefix="DB_DEV")
engine = ec.create_redshift()

Smart Insert

In bulk insert, if there are some rows having primary_key conflict, the classic solution is:

for row in data:
    try:
        engine.execute(table.insert(), row)
    except sqlalchemy.sql.IntegrityError:
        pass

It is like one-by-one insert, which is super slow.

sqlalchemy_mate uses smart_insert strategy to try with smaller bulk insert, which has higher probabily to work. As a result, total number of commits are greatly reduced.

With sql expression:

from sqlalchemy_mate import inserting

engine = create_engine(...)
t_users = Table(
    "users", metadata,
    Column("id", Integer),
    ...
)
# lots of data
data = [{"id": 1, "name": "Alice}, {"id": 2, "name": "Bob"}, ...]

inserting.smart_insert(engine, t_users, data)

With ORM:

from sqlalchemy_mate import ExtendedBase

Base = declarative_base()

class User(Base, ExtendedBase): # inherit from ExtendedBase
    ...

# lots of users
data = [User(id=1, name="Alice"), User(id=2, name="Bob"), ...]

User.smart_insert(engine_or_session, data) # That's it

Install

sqlalchemy_mate is released on PyPI, so all you need is:

$ pip install sqlalchemy_mate

To upgrade to latest version:

$ pip install --upgrade sqlalchemy_mate