import my database connection with python

• 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))