import my database connection with python

Question

is It possible to create a single py file with my database credentials for connecting to MySQL database Ex.

con = [ [ip='0.0.0.0'],[username = 'root'],
[password='pswd'],[database='test']]

And then use this file on another file.

like

import dbcon.py*

dbcon.sort

con = mdb.connect(info from py file goes here.)

Answer 1

It is possible, but it's not good idea to mix code and data (any kind - configuration, HTML etc), for at least two reasons:

• Design - you end up with so called high coupling. Situation where there is a lot of dependencies, hard to follow, and your app is more and more difficult to modify.
• Security - your credentials sooner or later end up in some code backup archive or repository. Config file can be additionally encrypted, py file not really. If it's a web app, it's easier to restrict access to single config file then to all py files that can have sensitive data.

You can still create this separate, easy to use, connection handling function. But move your connection credentials to separate configuration file.

config.ini:

[mysqlDB]
host = '0.0.0.0'
db = 'test'
user = 'root'
pass = 'pswd'

You can read configuration in your connection py file or make it more global (ie singleton?). If you want to read configuration in connection file:

storage.py:

import configparser
import MySQLdb.cursors

config = configparser.ConfigParser()
config.read('config.ini')

def connect():
    return MySQLdb.connect(host = config['mysqlDB']['host'],
                           user = config['mysqlDB']['user'],
                           passwd = config['mysqlDB']['pass'],
                           db = config['mysqlDB']['db'])

Usage example:

import storage

conn = storage.connect()

Answer 2

• For this example, I was created on GitHub where you can view and run code.
• The test presents the solution of the question itself, ie how the connection is included from the second module, as well as the parameters for the database.

└──GitHub
   ├── config.ini
   ├── db_lib.py
   ├── python_mysql_dbconfig.py
   └── test.py

A shorter version is presented below

First, create a database configuration file named config.ini and define a section with four parameters as follows:

[mysql]
HOST     = 127.0.0.1
USER     = root
PASSWORD = root
DATABASE = db

Second, create a new module named python_mysql_dbconfig.py that reads the database configuration from the config.ini file and returns a dictionary object:

pip install configparser

from configparser import ConfigParser


def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    """
    # create parser and read ini configuration file
    parser = ConfigParser()
    parser.read(filename)

    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
    else:
        raise Exception('{0} not found in the {1} file'.format(section, filename))

    return db

Let’s test this module:

# db_lib.py

from python_mysql_dbconfig import read_db_config

print(read_db_config())

output:

{'host': '127.0.0.1', 'user': 'root', 'password': 'root', 'database': 'db'}

Third, in the db_lib.py module (comment on the test) that uses the MySQLConnection object to connect to the python_mysql database.

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

# print(read_db_config())


def connect():
    """ Connect to MySQL database """

    db_config = read_db_config()
    conn = None
    try:
        print("Connecting to MySQL database...")
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            print("Connection established.")

            cursor = conn.cursor()
            cursor.execute("SELECT VERSION()")

            row = cursor.fetchone()
            print("Server version:", row[0])
        else:
            print("Connection failed.")

    except Exception as error:
        print(error)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
            print("Connection closed.")


if __name__ == "__main__":
    connect()

Let’s examine the module in greater detail:

• First, import necessary objects including MySQLConnection - pip install mysql-connector-python, Error from MySQL Connector/Python package and read_db_config from python_mysql_dbconfig module.
• Second, read the database configuration and pass it to create a new instance of MySQLConnection object in the connect() function.

output:

>python db_lib.py
Connecting to MySQL database...
Connection established.
Server version: 8.0.22
Connection closed.

---

Note

• There are other drivers for connecting to the base, such as PyMySQL

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db')

Configuration files in python:

• ConfigParser [.ini format] - has already been introduced
• JSON [.json format]

# Write data to a file:

import json

config = {"key1": "value1", "key2": "value2"}

with open('config1.json', 'w') as f:
    json.dump(config, f)


# Read data from a file:

import json

with open('config.json', 'r') as f:
    config = json.load(f)

#edit the data
config['key3'] = 'value3'

#write it back to the file
with open('config.json', 'w') as f:
    json.dump(config, f)

• YAML

import yaml

with open("example.yaml", 'r') as stream:
    try:
        print(yaml.safe_load(stream))
    except yaml.YAMLError as exc:
        print(exc)

• ENV

import os

os.getenv('DATABASE_NAME')

• envparse

# Standard
from envparse import env

# Schema
from envparse import Env

env = Env(BOOLEAN_VAR=bool, LIST_VAR=dict(cast=list, subcast=int))