.. image:: https://readthedocs.org/projects/sqlalchemy_mate/badge/?version=latest
:target: https://sqlalchemy-mate.readthedocs.io/latest/index.html
:alt: Documentation Status
.. image:: https://github.com/MacHu-GWU/sqlalchemy_mate-project/actions/workflows/main.yml/badge.svg
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/actions?query=workflow:CI
.. image:: https://codecov.io/gh/MacHu-GWU/sqlalchemy_mate-project/branch/master/graph/badge.svg
:target: https://codecov.io/gh/MacHu-GWU/sqlalchemy_mate-project
.. image:: https://img.shields.io/pypi/v/sqlalchemy_mate.svg
:target: https://pypi.python.org/pypi/sqlalchemy_mate
.. image:: https://img.shields.io/pypi/l/sqlalchemy_mate.svg
:target: https://pypi.python.org/pypi/sqlalchemy_mate
.. image:: https://img.shields.io/pypi/pyversions/sqlalchemy_mate.svg
:target: https://pypi.python.org/pypi/sqlalchemy_mate
.. image:: https://img.shields.io/badge/Release_History!--None.svg?style=social
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/blob/master/release-history.rst
.. image:: https://img.shields.io/badge/STAR_Me_on_GitHub!--None.svg?style=social
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project
------
.. image:: https://img.shields.io/badge/Link-Document-blue.svg
:target: https://sqlalchemy-mate.readthedocs.io/latest/index.html
.. image:: https://img.shields.io/badge/Link-API-blue.svg
:target: https://sqlalchemy-mate.readthedocs.io/latest/py-modindex.html
.. image:: https://img.shields.io/badge/Link-Source_Code-blue.svg
:target: https://sqlalchemy-mate.readthedocs.io/latest/py-modindex.html
.. image:: https://img.shields.io/badge/Link-Install-blue.svg
:target: `install`_
.. image:: https://img.shields.io/badge/Link-GitHub-blue.svg
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project
.. image:: https://img.shields.io/badge/Link-Submit_Issue-blue.svg
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/issues
.. image:: https://img.shields.io/badge/Link-Request_Feature-blue.svg
:target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/issues
.. image:: https://img.shields.io/badge/Link-Download-blue.svg
:target: https://pypi.org/pypi/sqlalchemy_mate#files
Welcome to ``sqlalchemy_mate`` Documentation
==============================================================================
A sweet syntax sugar library simplify your in writing ``sqlalchemy`` code.
📔 `Full document is HERE <https://sqlalchemy-mate.readthedocs.io/latest/index.html>`_
.. image:: https://sqlalchemy-mate.readthedocs.io/latest/_static/sqlalchemy_mate-logo.png
:target: https://sqlalchemy-mate.readthedocs.io/latest/index.html
Features
------------------------------------------------------------------------------
.. contents::
:class: this-will-duplicate-information-and-it-is-still-useful-here
:depth: 1
:local:
Read Database Credential Safely
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.. contents::
:class: this-will-duplicate-information-and-it-is-still-useful-here
:depth: 1
:local:
Put your database connection credential in your source code is always a **BAD IDEA**.
``sqlalchemy_mate`` provides several options to allow loading credential easily.
If you want to read db secret from other source, such as Bash Scripts that having lots of ``export DB_PASSWORD="xxx"``, AWS Secret Manager, AWS Key Management System (KMS), please take a look at my another project `pysecret <https://pypi.org/project/pysecret/>`_.
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:
.. code-block:: python
{
"credentials": {
"db1": {
"host": "example.com",
"port": 1234,
"database": "test",
"username": "admin",
"password": "admin",
},
"db2": {
...
}
}
}
code:
.. code-block:: python
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**:
.. code-block:: python
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.
.. code-block:: python
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:
.. code-block:: python
{
"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.
.. code-block:: python
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:
.. code-block:: bash
export DB_DEV_HOST="..."
export DB_DEV_PORT="..."
export DB_DEV_DATABASE="..."
export DB_DEV_USERNAME="..."
export DB_DEV_PASSWORD="..."
.. code-block:: python
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
.. code-block:: python
# 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:
.. code-block:: python
with engine.connect() as conn:
for row in data:
try:
conn.execute(table.insert(), row)
conn.commit()
except sqlalchemy.exc.IntegrityError:
conn.rollback()
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:
.. code-block:: python
from sqlalchemy_mate.api 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"}, ...]
# the magic function
inserting.smart_insert(engine, t_users, data)
With ORM:
.. code-block:: python
from sqlalchemy_mate.api 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"), ...]
# the magic method
User.smart_insert(engine_or_session, data) # That's it
Smart Update / Upsert
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Automatically update value by primary key.
.. code-block:: python
# in SQL expression
from sqlalchemy_mate.api import updating
data = [{"id": 1, "name": "Alice}, {"id": 2, "name": "Bob"}, ...]
updating.update_all(engine, table, data)
updating.upsert_all(engine, table, data)
# in ORM
data = [User(id=1, name="Alice"), User(id=2, name="Bob"), ...]
User.update_all(engine_or_session, user_list)
User.upsert_all(engine_or_session, user_list)
.. _install:
Install
------------------------------------------------------------------------------
``sqlalchemy_mate`` is released on PyPI, so all you need is:
.. code-block:: console
$ pip install sqlalchemy_mate
To upgrade to latest version:
.. code-block:: console
$ pip install --upgrade sqlalchemy_mate
Raw data
{
"_id": null,
"home_page": "https://github.com/MacHu-GWU/sqlalchemy_mate-project",
"name": "sqlalchemy-mate",
"maintainer": "Unknown",
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": null,
"author": "Sanhe Hu",
"author_email": "husanhe@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/54/96/67b2f88d460ad6130fd4ff144c34718bc5b6a526b46a31775029e6cbbe59/sqlalchemy_mate-2.0.0.3.tar.gz",
"platform": "Windows",
"description": ".. image:: https://readthedocs.org/projects/sqlalchemy_mate/badge/?version=latest\n :target: https://sqlalchemy-mate.readthedocs.io/latest/index.html\n :alt: Documentation Status\n\n.. image:: https://github.com/MacHu-GWU/sqlalchemy_mate-project/actions/workflows/main.yml/badge.svg\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/actions?query=workflow:CI\n\n.. image:: https://codecov.io/gh/MacHu-GWU/sqlalchemy_mate-project/branch/master/graph/badge.svg\n :target: https://codecov.io/gh/MacHu-GWU/sqlalchemy_mate-project\n\n.. image:: https://img.shields.io/pypi/v/sqlalchemy_mate.svg\n :target: https://pypi.python.org/pypi/sqlalchemy_mate\n\n.. image:: https://img.shields.io/pypi/l/sqlalchemy_mate.svg\n :target: https://pypi.python.org/pypi/sqlalchemy_mate\n\n.. image:: https://img.shields.io/pypi/pyversions/sqlalchemy_mate.svg\n :target: https://pypi.python.org/pypi/sqlalchemy_mate\n\n.. image:: https://img.shields.io/badge/Release_History!--None.svg?style=social\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/blob/master/release-history.rst\n\n.. image:: https://img.shields.io/badge/STAR_Me_on_GitHub!--None.svg?style=social\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project\n\n------\n\n.. image:: https://img.shields.io/badge/Link-Document-blue.svg\n :target: https://sqlalchemy-mate.readthedocs.io/latest/index.html\n\n.. image:: https://img.shields.io/badge/Link-API-blue.svg\n :target: https://sqlalchemy-mate.readthedocs.io/latest/py-modindex.html\n\n.. image:: https://img.shields.io/badge/Link-Source_Code-blue.svg\n :target: https://sqlalchemy-mate.readthedocs.io/latest/py-modindex.html\n\n.. image:: https://img.shields.io/badge/Link-Install-blue.svg\n :target: `install`_\n\n.. image:: https://img.shields.io/badge/Link-GitHub-blue.svg\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project\n\n.. image:: https://img.shields.io/badge/Link-Submit_Issue-blue.svg\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/issues\n\n.. image:: https://img.shields.io/badge/Link-Request_Feature-blue.svg\n :target: https://github.com/MacHu-GWU/sqlalchemy_mate-project/issues\n\n.. image:: https://img.shields.io/badge/Link-Download-blue.svg\n :target: https://pypi.org/pypi/sqlalchemy_mate#files\n\n\nWelcome to ``sqlalchemy_mate`` Documentation\n==============================================================================\nA sweet syntax sugar library simplify your in writing ``sqlalchemy`` code.\n\n\ud83d\udcd4 `Full document is HERE <https://sqlalchemy-mate.readthedocs.io/latest/index.html>`_\n\n.. image:: https://sqlalchemy-mate.readthedocs.io/latest/_static/sqlalchemy_mate-logo.png\n :target: https://sqlalchemy-mate.readthedocs.io/latest/index.html\n\n\nFeatures\n------------------------------------------------------------------------------\n.. contents::\n :class: this-will-duplicate-information-and-it-is-still-useful-here\n :depth: 1\n :local:\n\n\nRead Database Credential Safely\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n.. contents::\n :class: this-will-duplicate-information-and-it-is-still-useful-here\n :depth: 1\n :local:\n\nPut your database connection credential in your source code is always a **BAD IDEA**.\n\n``sqlalchemy_mate`` provides several options to allow loading credential easily.\n\nIf you want to read db secret from other source, such as Bash Scripts that having lots of ``export DB_PASSWORD=\"xxx\"``, AWS Secret Manager, AWS Key Management System (KMS), please take a look at my another project `pysecret <https://pypi.org/project/pysecret/>`_.\n\n\nFrom json file\n++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n\nYou can put your credential in a json file somewhere in your $HOME directory, and let sqlalchemy_mate smartly load from it.\n\nYou need to specify two things:\n\n1. path to json file.\n2. 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.\n\ncontent of json:\n\n.. code-block:: python\n\n {\n \"credentials\": {\n \"db1\": {\n \"host\": \"example.com\",\n \"port\": 1234,\n \"database\": \"test\",\n \"username\": \"admin\",\n \"password\": \"admin\",\n },\n \"db2\": {\n ...\n }\n }\n }\n\ncode:\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import EngineCreator\n\n ec = EngineCreator.from_json(\n json_file=\"path-to-json-file\",\n json_path=\"credentials.db1\", # dot notation json path\n )\n engine = ec.create_postgresql_pg8000()\n\n**Default data fields** are ``host``, ``port``, ``database``, ``username``, ``password``.\n\nIf your json schema is different, you need to add the ``key_mapping`` to **specify the field name mapping**:\n\n.. code-block:: python\n\n ec = EngineCreator.from_json(\n json_file=\"...\",\n json_path=\"...\",\n key_mapping={\n \"host\": \"your-host-field\",\n \"port\": \"your-port-field\",\n \"database\": \"your-database-field\",\n \"username\": \"your-username-field\",\n \"password\": \"your-password-field\",\n }\n )\n\n\nFrom ``$HOME/.db.json``\n++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n\nYou can put lots of database connection info in a ``.db.json`` file in your ``$HOME`` directory.\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import EngineCreator\n\n ec = EngineCreator.from_home_db_json(identifier=\"db1\")\n engine = ec.create_postgresql_psycopg2()\n\n``$HOME/.db.json`` **assumes flat json schema**, but you can use dot notation json path for ``identifier`` to adapt any json schema:\n\n.. code-block:: python\n\n {\n \"identifier1\": {\n \"host\": \"example.com\",\n \"port\": 1234,\n \"database\": \"test\",\n \"username\": \"admin\",\n \"password\": \"admin\",\n },\n \"identifier2\": {\n ...\n }\n }\n\n\nFrom json file on AWS S3\n++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n\nThis is similar to ``from_json``, but the json file is stored on AWS S3.\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import EngineCreator\n ec = EngineCreator.from_s3_json(\n bucket_name=\"my-bucket\", key=\"db.json\",\n json_path=\"identifier1\",\n aws_profile=\"my-profile\",\n )\n engine = ec.create_redshift()\n\n\nFrom Environment Variable\n++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++\n\nYou can put your credentials in Environment Variable. For example:\n\n.. code-block:: bash\n\n export DB_DEV_HOST=\"...\"\n export DB_DEV_PORT=\"...\"\n export DB_DEV_DATABASE=\"...\"\n export DB_DEV_USERNAME=\"...\"\n export DB_DEV_PASSWORD=\"...\"\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import EngineCreator\n # read from DB_DEV_USERNAME, DB_DEV_PASSWORD, ...\n ec = EngineCreator.from_env(prefix=\"DB_DEV\")\n engine = ec.create_redshift()\n\nIf you want to read database credential safely from cloud, for example, AWS EC2, AWS Lambda, you can use AWS KMS to decrypt your credentials\n\n.. code-block:: python\n\n # leave aws_profile=None if you are on cloud\n ec = EngineCreator.from_env(prefix=\"DB_DEV\", kms_decrypt=True, aws_profile=\"xxx\")\n engine = ec.create_redshift()\n\n\nSmart Insert\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\nIn bulk insert, if there are some rows having primary_key conflict, the classic solution is:\n\n.. code-block:: python\n\n with engine.connect() as conn:\n for row in data:\n try:\n conn.execute(table.insert(), row)\n conn.commit()\n except sqlalchemy.exc.IntegrityError:\n conn.rollback()\n\nIt is like one-by-one insert, which is super slow.\n\n``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.\n\nWith sql expression:\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import inserting\n engine = create_engine(...)\n t_users = Table(\n \"users\", metadata,\n Column(\"id\", Integer),\n ...\n )\n # lots of data\n data = [{\"id\": 1, \"name\": \"Alice}, {\"id\": 2, \"name\": \"Bob\"}, ...]\n # the magic function\n inserting.smart_insert(engine, t_users, data)\n\n\nWith ORM:\n\n.. code-block:: python\n\n from sqlalchemy_mate.api import ExtendedBase\n Base = declarative_base()\n class User(Base, ExtendedBase): # inherit from ExtendedBase\n ...\n # lots of users\n data = [User(id=1, name=\"Alice\"), User(id=2, name=\"Bob\"), ...]\n # the magic method\n User.smart_insert(engine_or_session, data) # That's it\n\n\nSmart Update / Upsert\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nAutomatically update value by primary key.\n\n.. code-block:: python\n\n # in SQL expression\n from sqlalchemy_mate.api import updating\n\n data = [{\"id\": 1, \"name\": \"Alice}, {\"id\": 2, \"name\": \"Bob\"}, ...]\n updating.update_all(engine, table, data)\n updating.upsert_all(engine, table, data)\n\n # in ORM\n data = [User(id=1, name=\"Alice\"), User(id=2, name=\"Bob\"), ...]\n User.update_all(engine_or_session, user_list)\n User.upsert_all(engine_or_session, user_list)\n\n\n.. _install:\n\nInstall\n------------------------------------------------------------------------------\n``sqlalchemy_mate`` is released on PyPI, so all you need is:\n\n.. code-block:: console\n\n $ pip install sqlalchemy_mate\n\nTo upgrade to latest version:\n\n.. code-block:: console\n\n $ pip install --upgrade sqlalchemy_mate\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A library extend sqlalchemy module, makes CRUD easier.",
"version": "2.0.0.3",
"project_urls": {
"Download": "https://pypi.python.org/pypi/sqlalchemy_mate/2.0.0.3#downloads",
"Homepage": "https://github.com/MacHu-GWU/sqlalchemy_mate-project"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "64d1a88d4f5252a1b238a0a7373957b0984d6dca1b4578afc175aee229da8f2f",
"md5": "e11a1079e090f9e76b61f27bf160048e",
"sha256": "c2244478a203dc2f17063b701612b97759ba2a757d3768869a40af48dfa898d6"
},
"downloads": -1,
"filename": "sqlalchemy_mate-2.0.0.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "e11a1079e090f9e76b61f27bf160048e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 59928,
"upload_time": "2024-06-06T17:40:42",
"upload_time_iso_8601": "2024-06-06T17:40:42.352646Z",
"url": "https://files.pythonhosted.org/packages/64/d1/a88d4f5252a1b238a0a7373957b0984d6dca1b4578afc175aee229da8f2f/sqlalchemy_mate-2.0.0.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "549667b2f88d460ad6130fd4ff144c34718bc5b6a526b46a31775029e6cbbe59",
"md5": "ca6ec160edca4f5b87ed24d3d2686af7",
"sha256": "f0e74fc40b437fb0c96b79710e68a1f0b46cbc78ac64df1e34e6721621d03ce8"
},
"downloads": -1,
"filename": "sqlalchemy_mate-2.0.0.3.tar.gz",
"has_sig": false,
"md5_digest": "ca6ec160edca4f5b87ed24d3d2686af7",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 54238,
"upload_time": "2024-06-06T17:40:44",
"upload_time_iso_8601": "2024-06-06T17:40:44.668536Z",
"url": "https://files.pythonhosted.org/packages/54/96/67b2f88d460ad6130fd4ff144c34718bc5b6a526b46a31775029e6cbbe59/sqlalchemy_mate-2.0.0.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-06-06 17:40:44",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "MacHu-GWU",
"github_project": "sqlalchemy_mate-project",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"requirements": [],
"lcname": "sqlalchemy-mate"
}
JSON
