Python is a very useful utility when it comes to scripting automatic tasks. When interacting with a database management system in the backend, Python is my favorite programming language to automate the job. Through this blog post, I am going to teach you guys how to automate the process of interacting with a MySQL server by sharing the Python code which I have written for my personal usage.
Make sure you have installed the mysql-connector module
Before progressing with this blog post, you need to make sure you have the required tools installed on your local machine. The Python module which helps to connect and interact with the MySQL server is known as the one which is being shown below.
mysql-connector
To make sure you already have this module installed on your local machine, launch a Python interpreter and type the following command.
What's the one thing every developer wants? More screens! Enhance your coding experience with an external monitor to increase screen real estate.
import mysql
In case the above command does not produce any error, it means that the mysql-connector module is already installed on your machine. Else it means that you have to install it by making use of the following command on the command interpreter of your operating system.
pip install mysql-connector
Once you have managed to install the Python module which is shown above, just run the command shown below to test the existence of the mysql-connector module in the local machine.
import mysql
Create a custom script called mysql.py
Open your favorite text editor and create a new file named mysql.py. Once yo have saved the file, create the following skeleton.
import mysql.connector
class DatabaseManager(object):
pass
Extend the custom script by adding methods to the DatabaseManager class
import mysql.connector
class DatabaseManager(object):
def __init__(self):
pass
def list_databases(self):
pass
def create_database(self):
pass
def close_connection(self):
pass
Code the custom methods
Now that we have defined the custom methods within the skeleton of the class DatabaseManager, it is time to write each one of them. First of all, we need to create a connection with the MySQL server within the __init__ magic method.
import mysql.connector
class DatabaseManager(object):
def __init__(self, username, password, host):
# connect to the MYSQL server, then connect to the database?
try:
self.connection = mysql.connector.connect(
host=host, user=username,
passwd=password)
self.cursor = self.connection.cursor()
# pdb.set_trace()
except mysql.connector.Error as err:
print("ERROR")
def list_databases(self):
pass
def create_database(self):
pass
def close_connection(self):
pass
As you can see from the above piece of Python code, the __init__ method takes three custom arguments; username, password and host. The username specifies the user of the MySQL server, the host specifies the hostname and the password specifies the password for the account of the user in the MySQL server.
Under the __init__ magic method we have a try/except block where we create a connection object with the help of the mysql.connector.connect method. In case of an error during the connection, we print the error on the console.
In other words, the connection to the MySQL server is being made as soon as an object of type DatabaseManager is created.
Now let’s code the custom methods within the class DatabaseManager.
import mysql.connector
class DatabaseManager(object):
def __init__(self, username, password, host):
# connect to the MYSQL server, then connect to the database?
try:
self.connection = mysql.connector.connect(
host=host, user=username,
passwd=password)
self.cursor = self.connection.cursor()
# pdb.set_trace()
except mysql.connector.Error as err:
print("ERROR")
def list_databases(self):
databases = []
sql_statement = 'SHOW DATABASES'
self.execute_sql(sql_statement)
for db in self.cursor:
databases.append(db)
return databases
def create_database(self):
pass
def close_connection(self):
pass
The method list_databases() is a custom method which lists the available databases within the MySQL server, by appending them to a list object.
Another custom method which we need for the purpose of interacting with the MySQL server is the method create_database(). It creates a new database by taking its name from user input.
def create_database(self, database_name):
sql_statement = 'CREATE DATABASE %s' % database_name
self.execute_sql(sql_statement)
Now the script should look like the one shown below.
import mysql.connector
class DatabaseManager(object):
def __init__(self, username, password, host):
# connect to the MYSQL server, then connect to the database?
try:
self.connection = mysql.connector.connect(
host=host, user=username,
passwd=password)
self.cursor = self.connection.cursor()
# pdb.set_trace()
except mysql.connector.Error as err:
print("ERROR")
def list_databases(self):
databases = []
sql_statement = 'SHOW DATABASES'
self.execute_sql(sql_statement)
for db in self.cursor:
databases.append(db)
return databases
def create_database(self):
sql_statement = 'CREATE DATABASE %s' % database_name
self.execute_sql(sql_statement)
def close_connection(self):
pass
We need to make sure that the connection to the database server is being closed once we have finished dealing with the MySQL.
def close_connection(self):
return self.connection.disconnect()
The script should look like the one shown below.
import mysql.connector
class DatabaseManager(object):
def __init__(self, username, password, host):
# connect to the MYSQL server, then connect to the database?
try:
self.connection = mysql.connector.connect(
host=host, user=username,
passwd=password)
self.cursor = self.connection.cursor()
# pdb.set_trace()
except mysql.connector.Error as err:
print("ERROR")
def list_databases(self):
databases = []
sql_statement = 'SHOW DATABASES'
self.execute_sql(sql_statement)
for db in self.cursor:
databases.append(db)
return databases
def create_database(self):
sql_statement = 'CREATE DATABASE %s' % database_name
self.execute_sql(sql_statement)
def close_connection(self):
return self.connection.disconnect()
And finally we need to code the method which is responsible for executing sql queries in the backend.
def execute_sql(self, sql_statement, data=None):
return self.cursor.execute(sql_statement, data)
The final script should look like the one shown below.
import mysql.connector
class DatabaseManager(object):
def __init__(self, username, password, host):
# connect to the MYSQL server, then connect to the database?
try:
self.connection = mysql.connector.connect(
host=host, user=username,
passwd=password)
self.cursor = self.connection.cursor()
# pdb.set_trace()
except mysql.connector.Error as err:
print("ERROR")
def list_databases(self):
databases = []
sql_statement = 'SHOW DATABASES'
self.execute_sql(sql_statement)
for db in self.cursor:
databases.append(db)
return databases
def create_database(self):
sql_statement = 'CREATE DATABASE %s' % database_name
self.execute_sql(sql_statement)
def close_connection(self):
return self.connection.disconnect()
def execute_sql(self, sql_statement, data=None):
return self.cursor.execute(sql_statement, data)
Final thoughts
A combination of Python and MySQL creates a great set of tools. Although we did not automate the entire functionalities offered by MySQL, the script being shared through this blog post is good foundation for the upcoming tutorials.