S_Connector

Simple to use connector to DB for Python code.
Support DB engine: MySQL and Redshift Amazon

Description

It is wrapper for simple connecting to DB in Python.

Dependencies

• python >= 3.7 or more (maybe will be work on Python 2.x, but I didn't test it).
• PyYAML or more (needed for file settings)
• psycopg2 module for connection to Redshift Amazon DB
• mysqlclient module for connection to MySQL DB
• s_logger implementation of a simple logger module

Before install

Before install the mysqlclient need to do some extra steps:

• sudo apt-get install python3-dev default-libmysqlclient-dev # Debian / Ubuntu
• sudo yum install python3-devel mysql-devel # Red Hat / CentOS
• brew install mysql-connector-c # macOS (Homebrew) (Currently, it has bug. See below)

open file: /usr/local/Cellar/mysql-connector-c/x.x.x/bin/mysql-config - instead x.x.x number of mysql-connector-c version

find follow lines

# on macOS, on or about line 112:
# Create options
libs="-L$pkglibdir"
libs="$libs -l "

and change to

# Create options
libs="-L$pkglibdir"
libs="$libs -lmysqlclient -lssl -lcrypto"

But, before start changing this file need to add access to write:

chmod 0666 /usr/local/Cellar/mysql-connector-c/x.x.x/bin/mysql-config

and after changing need to restore access:

chmod 0555 /usr/local/Cellar/mysql-connector-c/x.x.x/bin/mysql-config

How use

File main.py have a little example.

• need install module: pip install s_connector
• create 'db_credential.yaml' which contains path to file with connection credential. See format below
• create file with connection credential. See format below file name credential.yaml
• in your code need use import fro importing module

from s_connector.S_Connector import MySQLConnector

or/and

from s_connector.SConnector import RedshiftConnector

• create the object of class S_Logger and give it settings file name

connector = RedshiftConnector('redshift_credential')    # 'redshift_credential' - name of group for Redshift Amaon credential

or/and

connector = MySQLConnector('mysql_credential')          # 'mysql_credential' - name og group for MySql credential

We can use open method for establish connection and close method for close connection

connector.open()
res = connector.select('SELECT 1;')
print(res[0][0])
connector.close()

or

use Context Manager:

with MySQLConnector('mysql_credential') as connector:
    res = connector.select('SELECT 1;')
    print(res[0][0])

Example:

from s_connector.SConnector import MySQLConnector
from s_connector.SConnector import RedshiftConnector


if __name__ == '__main__':
    connector = RedshiftConnector('redshift_credential')
    connector.open()
    print(connector.select('SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;'))
    connector.close()

    with RedshiftConnector('redshift_credential') as connector:
        print(connector.select('SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;'))

    connector = MySQLConnector('mysql_credential')
    connector.open()
    print(connector.select('SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;'))
    connector.close()

    with MySQLConnector('mysql_credential') as connector:
        print(connector.select('SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;'))

format db_credential.yaml:

path_to_cred: credential.yaml

format credential.yaml:

IMPORTANT This file need to move to protected place.

redshift_credential:            # connection name
    host: redshift.data.db.test # or IP
    port: 5432
    database: test
    user: user_db
    passcode: somepwd

mysql_credential:               # connection name
    host: mysql.data.db.test    # or IP
    port: 3306
    database: test_db
    user: user_db
    passcode: somepwd

Current version: 1.0.2