========================
Auctm Database Connector
========================
A python package that provides database connection to Bifrost database, Substrate database, and Decision Science database.
Modules
#########
There are three modules - **bifrost**, **substrate** and **decision_science**.
All the modules provide the ``engine`` for their respective database.
bifrost
********
This module provides the database connection for the Bifrost database. It provides **read-only** access to the database.
The schema for tables in this database can be seen on the `Bifrost Explorer <https://bifrost.squadplatform.com/explorer/play>`__
substrate
**********
This module provides the database connection for the Substrate database. It provides **read-only** access to the database.
decision_science
*****************
This module provides the database connection for the Decision Science database. It provides **read** and **write**
access to the user's schema in the database.
This module provides the database connection for the Decision Science database.
SquadIQ
*****************
This module provides the database connection for the SquadIQ database. It provides **read-only** access to the database.
Usage
------
.. code-block:: python
from database_connector import bifrost, substrate, decision_science, squadiq, sagemaker
bifrost_engine = bifrost.engine
substrate_engine = substrate.engine
ds_engine = decision_science.engine
iq_engine = squadiq.engine
ds_sagemaker_engine = sagemaker.engine
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with bifrost_engine.connect() as conn:
data = conn.execute("Select * from fub_customer")
# data is a dictionary with column name as key and row as value
for row in data:
print(row)
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with substrate_engine.connect() as conn:
data = conn.execute("Select * from substrate_customer")
# data is a dictionary with column name as key and row as value
for row in data:
print(row)
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with iq_engine.connect() as conn:
data = conn.execute("Select * from voice_workflow_campaign")
# data is a dictionary with column name as key and row as value
for row in data:
print(row)
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with ds_sagemaker_engine.connect() as conn:
data = conn.execute("Select * from voice_workflow_campaign")
# data is a dictionary with column name as key and row as value
for row in data:
print(row)
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with ds_engine.connect() as conn:
data = conn.execute("Select * from vedvasu.customers")
# data is a dictionary with column name as key and row as value
for row in data:
print(row)
conn.execute("insert into vedvasu.customers (id, name) values (1, 'Robert-Slack')")
How to install this module inside SageMaker Jobs started from JupyterHub
--------------------------------------------------------------------------
The following commands should be added in the DockerFile to install this module in the docker image. Preferably add these commands near the end before changing the working directory since all the steps these commands will be rebuild and cache won’t be used. Check the release `here <https://discourse.squadstack.com/t/sagemaker-update-launch-training-jobs-inside-vpc/56>`__.
.. code-block:: dockerfile
COPY auctm-database-connector-*.tar.gz /mnt/
RUN pip3 install /mnt/auctm-database-connector-*.tar.gz
UI for the Decision Science Database
-------------------------------------
The UI is available at `Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>`__. It provides a MySQL Workbench like interface for our PostgreSQL database.
Users can login here to view and make changes to their tables via a UI. The credentials for this can be taken from the Admins (currently AGB and abkunal)
How to Onboard a new user on Decision Science Database
********************************************************
- Login as the Admin Account at `Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>`__.
- Create a **User** account for a new user and share the credentials with the user.
- Run the following SQL queries by replacing ``<role-name>``, ``<strong-password>``, and ``<schema-name>``.
Note down the ``role-name`` and ``strong-password``.
.. code-block:: sql
-- Create a new role and GRANT all_read_only role to this new role
CREATE ROLE <role-name> WITH
LOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1
PASSWORD '<strong-password>';
GRANT all_read_only TO <role-name>;
-- Grant this role to the Admin user
GRANT <role-name> TO decision_science;
-- Create schema for the role and make this role owner of this schema
CREATE SCHEMA <schema-name> AUTHORIZATION <role-name>;
-- Grant all_read_only role READ access to this new schema
GRANT USAGE ON SCHEMA <schema-name> TO all_read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema-name> TO all_read_only;
- On JupyterHub, navigate to the user's home directory and create
the credentials file.
.. code-block:: bash
sudo su
cd /home/<user>
nano decision_science_creds.csv
# Eg => cd /home/jupyter-kunal.yadav@squadr-319c0
- Put the role name and password in the csv file as shown below.
.. code-block:: bash
username,password
<role-name>,<strong-password>
- Change the ownership permissions for this file.
.. code-block:: bash
chown <user> decision_science_creds.csv
chgrp <user> decision_science_creds.csv
# Eg => chown jupyter-kunal.yadav@squadr-319c0 decision_science_creds.csv
Documentation
--------------
We use SQL Alchemy as an ORM because it provides additional features than raw `psycopg2`.
You can check out its documentation here - `SQL Alchemy docs <https://docs.sqlalchemy.org/en/13/core/connections.html>`__
How to Modify this Package
----------------------------
1. This package works with Python 3
2. After making the necessary changes run the following commands to build the project and install the package
.. code-block:: bash
python setup.py sdist
sudo -E python -m pip install dist/auctm-database-connector-0.1.tar.gz
3. You can uninstall the package by running the following command - ``sudo -E pip uninstall auctm-database-connector``
Raw data
{
"_id": null,
"home_page": "https://github.com/squadrun/auctm-database-connector",
"name": "squad-sagemaker-db-connector",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "Db connector",
"author": "Ujjwal Gupta",
"author_email": "ujjwal.gupta@squadstack.com",
"download_url": "https://files.pythonhosted.org/packages/3f/13/e4e766328aba7b8b22d499161ae3dc8a3bf1edab5a03ae9288c80ab14d38/squad-sagemaker-db-connector-0.2.tar.gz",
"platform": null,
"description": "========================\nAuctm Database Connector\n========================\n\nA python package that provides database connection to Bifrost database, Substrate database, and Decision Science database.\n\nModules\n#########\n\nThere are three modules - **bifrost**, **substrate** and **decision_science**.\n\nAll the modules provide the ``engine`` for their respective database.\n\nbifrost\n********\n\nThis module provides the database connection for the Bifrost database. It provides **read-only** access to the database.\n\nThe schema for tables in this database can be seen on the `Bifrost Explorer <https://bifrost.squadplatform.com/explorer/play>`__\n\nsubstrate\n**********\n\nThis module provides the database connection for the Substrate database. It provides **read-only** access to the database.\n\ndecision_science\n*****************\n\nThis module provides the database connection for the Decision Science database. It provides **read** and **write**\naccess to the user's schema in the database.\n\nThis module provides the database connection for the Decision Science database.\n\nSquadIQ\n*****************\n\nThis module provides the database connection for the SquadIQ database. It provides **read-only** access to the database.\n\nUsage\n------\n\n.. code-block:: python\n\n from database_connector import bifrost, substrate, decision_science, squadiq, sagemaker\n\n bifrost_engine = bifrost.engine\n substrate_engine = substrate.engine\n ds_engine = decision_science.engine\n iq_engine = squadiq.engine\n ds_sagemaker_engine = sagemaker.engine\n\n # Database connection is automatically closed when connection is used in a context manager i.e. inside \"with\"\n with bifrost_engine.connect() as conn:\n data = conn.execute(\"Select * from fub_customer\")\n\n # data is a dictionary with column name as key and row as value\n for row in data:\n print(row)\n \n # Database connection is automatically closed when connection is used in a context manager i.e. inside \"with\"\n with substrate_engine.connect() as conn:\n data = conn.execute(\"Select * from substrate_customer\")\n\n # data is a dictionary with column name as key and row as value\n for row in data:\n print(row)\n\n # Database connection is automatically closed when connection is used in a context manager i.e. inside \"with\"\n with iq_engine.connect() as conn:\n data = conn.execute(\"Select * from voice_workflow_campaign\")\n\n # data is a dictionary with column name as key and row as value\n for row in data:\n print(row)\n\n # Database connection is automatically closed when connection is used in a context manager i.e. inside \"with\"\n with ds_sagemaker_engine.connect() as conn:\n data = conn.execute(\"Select * from voice_workflow_campaign\")\n\n # data is a dictionary with column name as key and row as value\n for row in data:\n print(row)\n\n # Database connection is automatically closed when connection is used in a context manager i.e. inside \"with\"\n with ds_engine.connect() as conn:\n data = conn.execute(\"Select * from vedvasu.customers\")\n\n # data is a dictionary with column name as key and row as value\n for row in data:\n print(row)\n\n conn.execute(\"insert into vedvasu.customers (id, name) values (1, 'Robert-Slack')\")\n\n\nHow to install this module inside SageMaker Jobs started from JupyterHub\n--------------------------------------------------------------------------\n\nThe following commands should be added in the DockerFile to install this module in the docker image. Preferably add these commands near the end before changing the working directory since all the steps these commands will be rebuild and cache won\u2019t be used. Check the release `here <https://discourse.squadstack.com/t/sagemaker-update-launch-training-jobs-inside-vpc/56>`__.\n \n.. code-block:: dockerfile\n\n COPY auctm-database-connector-*.tar.gz /mnt/\n RUN pip3 install /mnt/auctm-database-connector-*.tar.gz\n\nUI for the Decision Science Database\n-------------------------------------\n\nThe UI is available at `Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>`__. It provides a MySQL Workbench like interface for our PostgreSQL database.\nUsers can login here to view and make changes to their tables via a UI. The credentials for this can be taken from the Admins (currently AGB and abkunal)\n\nHow to Onboard a new user on Decision Science Database\n********************************************************\n\n- Login as the Admin Account at `Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>`__.\n- Create a **User** account for a new user and share the credentials with the user.\n- Run the following SQL queries by replacing ``<role-name>``, ``<strong-password>``, and ``<schema-name>``.\nNote down the ``role-name`` and ``strong-password``.\n\n.. code-block:: sql\n\n -- Create a new role and GRANT all_read_only role to this new role\n CREATE ROLE <role-name> WITH\n LOGIN\n NOSUPERUSER\n NOCREATEDB\n NOCREATEROLE\n INHERIT\n NOREPLICATION\n CONNECTION LIMIT -1\n PASSWORD '<strong-password>';\n\n GRANT all_read_only TO <role-name>;\n\n -- Grant this role to the Admin user\n GRANT <role-name> TO decision_science;\n\n -- Create schema for the role and make this role owner of this schema\n CREATE SCHEMA <schema-name> AUTHORIZATION <role-name>;\n\n -- Grant all_read_only role READ access to this new schema\n GRANT USAGE ON SCHEMA <schema-name> TO all_read_only;\n GRANT SELECT ON ALL TABLES IN SCHEMA <schema-name> TO all_read_only;\n\n- On JupyterHub, navigate to the user's home directory and create\nthe credentials file.\n\n.. code-block:: bash\n\n sudo su\n cd /home/<user>\n nano decision_science_creds.csv\n\n # Eg => cd /home/jupyter-kunal.yadav@squadr-319c0\n\n\n- Put the role name and password in the csv file as shown below.\n\n.. code-block:: bash\n\n username,password\n <role-name>,<strong-password>\n\n\n- Change the ownership permissions for this file.\n\n.. code-block:: bash\n\n chown <user> decision_science_creds.csv\n chgrp <user> decision_science_creds.csv\n\n # Eg => chown jupyter-kunal.yadav@squadr-319c0 decision_science_creds.csv\n\n\nDocumentation\n--------------\n\nWe use SQL Alchemy as an ORM because it provides additional features than raw `psycopg2`.\nYou can check out its documentation here - `SQL Alchemy docs <https://docs.sqlalchemy.org/en/13/core/connections.html>`__\n\nHow to Modify this Package\n----------------------------\n\n1. This package works with Python 3\n2. After making the necessary changes run the following commands to build the project and install the package\n\n.. code-block:: bash\n\n python setup.py sdist\n sudo -E python -m pip install dist/auctm-database-connector-0.1.tar.gz\n\n\n3. You can uninstall the package by running the following command - ``sudo -E pip uninstall auctm-database-connector``\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A pluggable connector that allows users (admins) to execute SQL, view, and export the results.",
"version": "0.2",
"split_keywords": [
"db",
"connector"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2412bb65dad83f9a45f8bba64340f78c0f24e3a91ac42b86f65deb3f140b1f03",
"md5": "790cd89ec297e7d2231d496908a8915f",
"sha256": "2b7d467212fbc5fc282b72e71f7dffbe190b5ae225be7b7245140a430bbf7792"
},
"downloads": -1,
"filename": "squad_sagemaker_db_connector-0.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "790cd89ec297e7d2231d496908a8915f",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 13748,
"upload_time": "2023-04-17T09:30:58",
"upload_time_iso_8601": "2023-04-17T09:30:58.898649Z",
"url": "https://files.pythonhosted.org/packages/24/12/bb65dad83f9a45f8bba64340f78c0f24e3a91ac42b86f65deb3f140b1f03/squad_sagemaker_db_connector-0.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "3f13e4e766328aba7b8b22d499161ae3dc8a3bf1edab5a03ae9288c80ab14d38",
"md5": "4d4dc83eaee8bd64790d43d56237ea20",
"sha256": "0388c668c47f5841a0170bc87dcc4fd3e019623493fc600cb3ceb75cbf1e3f71"
},
"downloads": -1,
"filename": "squad-sagemaker-db-connector-0.2.tar.gz",
"has_sig": false,
"md5_digest": "4d4dc83eaee8bd64790d43d56237ea20",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 9449,
"upload_time": "2023-04-17T09:31:01",
"upload_time_iso_8601": "2023-04-17T09:31:01.234070Z",
"url": "https://files.pythonhosted.org/packages/3f/13/e4e766328aba7b8b22d499161ae3dc8a3bf1edab5a03ae9288c80ab14d38/squad-sagemaker-db-connector-0.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-17 09:31:01",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "squadrun",
"github_project": "auctm-database-connector",
"lcname": "squad-sagemaker-db-connector"
}
JSON
