Skip to main content

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.

(back to top)

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)

(back to top)

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)

(back to top)

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)

(back to top)

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)

(back to top)

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

(back to top)

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

rr_connection_manager-0.4.0.tar.gz (8.5 kB view details)

Uploaded Source

Built Distribution

rr_connection_manager-0.4.0-py3-none-any.whl (9.5 kB view details)

Uploaded Python 3

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

Hashes for rr_connection_manager-0.4.0.tar.gz
Algorithm Hash digest
SHA256 3772aa8daa44b5d5958d9322d6b647f1731d6d757b2345f5a78ae06d159f2b64
MD5 60291617c4b5cd6329ec2ef16560f7e2
BLAKE2b-256 7a0c4cfb8ea34fe4f91290b76d5187f873baa996bdb432c9e8cb6bb14d80194b

See more details on using hashes here.

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

Attestations:

File details

Details for the file rr_connection_manager-0.4.0-py3-none-any.whl.

File metadata

File hashes

Hashes for rr_connection_manager-0.4.0-py3-none-any.whl
Algorithm Hash digest
SHA256 449b516a440c922c94ca1d08f5e67406905f558bddd2b0450f16bd452b0d4b92
MD5 817be648262240e6435b3bfbdbcaa128
BLAKE2b-256 c919a980e4c1c4dd212f2bf66003996314b8542954920df78adf85659eb19ae6

See more details on using hashes here.

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

Attestations:

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page