MySQL-Python – 5 Logging and MySQL Connector

This is a continuation from previous post MySQL-Python - 4.

In this page:


1. About logging

Logging activity for MySQL connector use default Python logging features. By default events with level WARNING are logged and are printed to terminal where we run, i.e. sys.stderr 

Configuring logging, we can change logged events level and we can print messages to other destination than stderr.


2. Sample code for classical logging

Below is a sample code for logging used in case when MySql Connector is used with insert. Use can be similar for other MySQL connector usecase. 

import logging

import datetime

import mysql.connector


#--this is code for loggers

logger = logging.getLogger("mysql.connector")

logger.setLevel(logging.DEBUG)


formatter = logging.Formatter("%(asctime)s - %(name)s - %(levelname)s- %(message)s")


# create console handler named stream_handler

stream_handler = logging.StreamHandler()

# add formatter to stream_handler

stream_handler.setFormatter(formatter)

# add  console handler  stream_handler to logger

logger.addHandler(stream_handler)

# create a file handler

file_handler = logging.FileHandler("conn_log.log")

# add formatter to file handler

file_handler.setFormatter(formatter)

# add  file handler to logger

logger.addHandler(file_handler)


#---this is code where mysql connector is used

connection = mysql.connector.connect(user='u2024', password='password1234', host='localhost', port=3306, database='sakila')

logger.debug('user u2024 logged')

cursor = connection.cursor()

logger.info('mysql.connector created cursor')


SQL_insert_actor = ("INSERT INTO actor (first_name, last_name, last_update)"

            "VALUES  (%s, %s, %s)")

values_for_actor = ('NICK','WALKEN', datetime.date(2023, 12, 16))

cursor.execute(SQL_insert_actor, values_for_actor)

# commit to the database

connection.commit()

logger.info('mysql.connector entry inserted in DB')


cursor.close()

connection.close() 


3. Code comments

First we defined a logger using logger = logging.getLogger("mysql.connector"), here mysql.connector is the logger name, it can be anything, we  named it  mysql.connector for visibility.

Change log level to DEBUG using logger.setLevel(logging.DEBUG)

Permitted log level are here

Line: formatter = logging.Formatter("%(asctime)s - %(name)s - %(levelname)s- %(message)s") 

define how log record will appear. This line is not mandatory, if we not use it, by default will be used string like '%(message)s' which means only the message will be in log record.

In our example we use a formatter object  thus we will display in log line: time, name, log level of the message and message itself. More about possibility for format message in formatter-objects


Next lines:

# create console handler named stream_handler

stream_handler = logging.StreamHandler()

# add formatter to stream_handler

stream_handler.setFormatter(formatter)

# add  console handler  stream_handler to logger

logger.addHandler(stream_handler)


create a stream handler that is added to logger, this stream handler is for logging to console, means lines for example that appear if we run python script in command prompt, or lines that appear on the screen as we will see when run in PyCharm


Lines:

file_handler = logging.FileHandler("conn_log.log")

# add formatter to file handler

file_handler.setFormatter(formatter)

# add  file handler to logger

logger.addHandler(file_handler)


those create a file handler and add it to logger, those lines are responsible for what will be written in log file named "conn_log.log".


All the lines that follow after 

#---this is code where mysql connector is used

are to exemplify. From mysql connector point of view, those contain lines for inserting in DB. 

Between those lines, we added lines for logging, those contain logger.debug or logger.info

For example line like 

logger.debug('user u2024 logged')

will sent message 'user u2024 logged' with proper formatting, to console or file.


4. Sample logging output 

Below is an image with sample code run in Pycharm.


Logging in mysql connector, PyCharm


We see with red lines in output log lines from console handler defined prior. 

In  file "conn_log.log" appear also lines, this time from file handler. Below is how file looks like: 

Logging in file