A package to help you connect to all Registry databases
Project description
RR Connection Manager
A package to help you connect to all Registry databases
·
Report Bug
·
Request Feature
About The Project
This package wraps pykeepass and sshtunnel along with various connection tools including SQLAlchemy, to allow an all in one solution for connecting via SSH to our servers without the need to store connection details locally
Getting Started
poetry add rr-connection-manager
or
pip install rr-connection-manager
Prerequisites
You will need to ensure make sure you meet all the expected requirements for using pyscopg2
Connection Variables
The primary use case is for the connection variables to be taken from a keepass file. This will require some setup which includes running the RR Key Manager. Once you have done that connection manager should work out of the box.
Adding a conf.json file at the root of your project will override any attempt to connect to keepass. This is useful if you can't access the keepass file or you want use this on one of the servers. You can add connection details for as many servers as you like but the app_name field in the conf file must match the app_name variable past to the function to create your connection.
[
{
"app_name": "app_name",
"app_host": "app_host",
"db_host": "db_host",
"database": "database_name",
"db_user": "database_username",
"db_password": "db_user_password",
"db_port": "database_port",
"tunnel_user": "tunnel_user",
"tunnel_port": "tunnel_port"
},
]
Not all of these are required for each connection. SQL Server connections for example only require DB_HOST and DATABASE.
SQL Server Connection
To create a SQL Server connection object
conn = SQLServerConnection(app="app_name")
From this you can choose to open a pyodbc cursor and use that to query the database
conn = SQLServerConnection(app="app_name")
cur = conn.cursor()
cur.execute("SELECT @@version")
info = cur.fetchone()
cur.close()
print(info)
You can pass in any extra config arguments you like when creating a cursor
conn = SQLServerConnection(app="app_name")
cur = conn.cursor(timeout=30, autocommit=True)
Postgres Connection
To create a Postgres connection object
conn = PostgresConnection(app="app_name")
From this you can choose to open a pyscopg2 cursor and use that to query the database
conn = PostgresConnection(app="app_name")
cur = conn.cursor()
cur.execute("SELECT version()")
info = cur.fetchone()
cur.close()
print(info)
You can pass in any extra config arguments you like when creating a cursor
conn = PostgresConnection(app="app_name")
cur = conn.cursor(connect_timeout=30, autocommit=True)
SQLite Connection
⚠️ Currently unsupported
SQL Alchemy
All connection types also wrap sqlalchemy so you are able to access a session. This uses the standard set of arguments when creating the engine.
conn = PostgresConnection(app="app_name")
session = conn.session()
You can build the engine yourself which you allows you to pass arguments
conn = PostgresConnection(app="app_name")
eng = conn.engine(echo=True)
session = conn.session(eng)
A session maker is also accessible and allows you to pass arguments
conn = PostgresConnection(app="app_name")
Session = conn.session_maker(expire_on_commit=True)
session = Session()
You can combine both
conn = PostgresConnection(app="app_name")
eng = conn.engine(echo=True)
Session = conn.session_maker(eng, expire_on_commit=True)
session = Session()
check_message = session.execute(text("""select version()""")).first()
session.close()
conn.close()
print(check_message)
Connection check
To make testing the connection simple each class has a connection_check function that checks the version of the database it is connecting to. This uses the base packages not SQLAlchemy but it is assumed if they work so should SQLAlchemy.
from rr_connection_manager import PostgresConnection
from rr_connection_manager import SQLServerConnection
conn = PostgresConnection(app="app_name")
conn.connection_check()
conn = SQLServerConnection(app="app_name")
conn.connection_check()
Using a Tunnel
To connect to a database over SSH you need to add the tunnel argument.
conn = PostgresConnection(app="app_name", tunnel=True)
In cases where you want to tunnel through an app server to the database server you can add the via_app argument
conn = PostgresConnection(app="app_name", tunnel=True, via_app=True)
Using a Specific Local Port
To use a specific local port with your connection you can pass the local_port argument.
conn = PostgresConnection(app="app_name", tunnel=True, local_port=6100)
License
Distributed under the MIT License. See LICENSE.txt
for more information.
Contact
Renal Registry - @UKKidney - rrsystems@renalregistry.nhs.uk
Project Link: https://github.com/renalreg/rr-connection-manager
Acknowledgments
Project details
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
File details
Details for the file rr_connection_manager-0.4.0.tar.gz
.
File metadata
- Download URL: rr_connection_manager-0.4.0.tar.gz
- Upload date:
- Size: 8.5 kB
- Tags: Source
- Uploaded using Trusted Publishing? Yes
- Uploaded via: twine/5.1.1 CPython/3.12.7
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 3772aa8daa44b5d5958d9322d6b647f1731d6d757b2345f5a78ae06d159f2b64 |
|
MD5 | 60291617c4b5cd6329ec2ef16560f7e2 |
|
BLAKE2b-256 | 7a0c4cfb8ea34fe4f91290b76d5187f873baa996bdb432c9e8cb6bb14d80194b |
Provenance
The following attestation bundles were made for rr_connection_manager-0.4.0.tar.gz
:
Publisher:
python_publish.yml
on renalreg/rr-connection-manager
-
Statement type:
https://in-toto.io/Statement/v1
- Predicate type:
https://docs.pypi.org/attestations/publish/v1
- Subject name:
rr_connection_manager-0.4.0.tar.gz
- Subject digest:
3772aa8daa44b5d5958d9322d6b647f1731d6d757b2345f5a78ae06d159f2b64
- Sigstore transparency entry: 149617198
- Sigstore integration time:
- Predicate type:
File details
Details for the file rr_connection_manager-0.4.0-py3-none-any.whl
.
File metadata
- Download URL: rr_connection_manager-0.4.0-py3-none-any.whl
- Upload date:
- Size: 9.5 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? Yes
- Uploaded via: twine/5.1.1 CPython/3.12.7
File hashes
Algorithm | Hash digest | |
---|---|---|
SHA256 | 449b516a440c922c94ca1d08f5e67406905f558bddd2b0450f16bd452b0d4b92 |
|
MD5 | 817be648262240e6435b3bfbdbcaa128 |
|
BLAKE2b-256 | c919a980e4c1c4dd212f2bf66003996314b8542954920df78adf85659eb19ae6 |
Provenance
The following attestation bundles were made for rr_connection_manager-0.4.0-py3-none-any.whl
:
Publisher:
python_publish.yml
on renalreg/rr-connection-manager
-
Statement type:
https://in-toto.io/Statement/v1
- Predicate type:
https://docs.pypi.org/attestations/publish/v1
- Subject name:
rr_connection_manager-0.4.0-py3-none-any.whl
- Subject digest:
449b516a440c922c94ca1d08f5e67406905f558bddd2b0450f16bd452b0d4b92
- Sigstore transparency entry: 149617201
- Sigstore integration time:
- Predicate type: