Skip to content

Latest commit

 

History

History
74 lines (61 loc) · 2.42 KB

README.md

File metadata and controls

74 lines (61 loc) · 2.42 KB
Raw

DBConnect

Why use this module?

  • Make it easy to discover new databases at DataCamp
  • Allow us to rotate DB credentials, without anyone having to update their scripts
  • Make it easy to open the documentation of a database
  • Allow fine-grained access permissions by using the AWS Key and Secrets for each client.

Installation

Make sure to ask the IDE team for your AWS Key and Secret.

pip install awscli

aws configure
> AWS Access Key ID: <enter your key>
> AWS Secret Access Key: <enter your secret>
> Default region name: us-east-1
> Default output format: <leave blank>

pip install git+https://github.com/datacamp/dbconnect-python

Given that this package connects to databases, you will most likely need to install the MySQL and/or PostgreSQL clients for your OS.

How to use

For security reasons, we only allow DB connections from our VPN. Make sure to be connected when using this module.

Get a list of our databases you can connect to:

import dbconnect as dbc
dbc.get_databases()
['main-app',
 'teach-app',
 'challenges-app',
 'projects-app',
 'mobile-app',
 'messenger-app',
 'projector-app',
 'datachats',
 'timetracker']

Connect to a database and execute pandas queries:

import dbconnect as dbc
import pandas as pd

engine = dbc.create_connection(database = 'main-app') # The 'main-app' string you can find from dbc.get_databases()
pd.read_sql('SELECT id, email FROM users ORDER BY id ASC LIMIT 10', engine)

Open the documentation of a database

dbc.get_docs(database="main-app")
Opening "https://github.com/datacamp/main-app/wiki/Database-Documentation"

AWS Parameter Store Structure

Parameter Value
/dbconnect/dbnames List of <application-name>s, separated with comma.

For every application:

Parameter Value
/dbconnect/<application-name>/type Type of database (mysql, postgresql)
/dbconnect/<application-name>/endpoint Endpoint of the replica
/dbconnect/<application-name>/port Port of the replica
/dbconnect/<application-name>/user Username of the connection
/dbconnect/<application-name>/database Name of the database
/dbconnect/<application-name>/docs URL of the database documentation
/dbconnect/<application-name>/password Password of the database. Make sure the type is set to SecureString

These paramters are managed by terraform. (Ask the IDE team)