A library extend sqlalchemy module, makes CRUD easier.
Project description
Welcome to sqlalchemy_mate Documentation
A library extend sqlalchemy module, makes CRUD easier.
Features
Read Database Credential Safely
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()
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()
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
Smart Update / Upsert
Automatically update value by primary key.
# in sql expression
from sqlalchemy_mate import updating
updating.update_all(engine, table, data)
updating.upsert_all(engine, table, data)
# in ORM
User.update_all(engine_or_session, user_list)
User.upsert_all(engine_or_session, user_list)
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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for sqlalchemy_mate-0.0.7-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 5c3ace7b1d8522dd0b2f607811e12cc1df8019a13094a966080d4c84167fdd9d |
|
MD5 | 3e7fa2c0429e56aa82cd816b36c80d40 |
|
BLAKE2b-256 | 304d6fdd22531c195158abc39d1a76af0dd096e37bb93e9ac7667677a24bce60 |