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.
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: