Learn to Manage Your MySQL Database with a Python Script

To create a Python script that manages MySQL database, we would typically need to perform operations such as connect to a MySQL server, create a database, create a table, insert data into the table, update data, delete data, and select data.

For this, we would be using the mysql-connector-python package. If it’s not installed, you can do so using pip:

pip install mysql-connector-python

Creating a MySQL Database Manager with Python

Here’s a basic example of how you can manage a MySQL database in Python:

import mysql.connector

class MySQLManager:
    def __init__(self, host, user, passwd, database):
        self.mydb = mysql.connector.connect(
            host=host,
            user=user,
            passwd=passwd,
            database=database
        )
        self.mycursor = self.mydb.cursor()
    
    def create_table(self, table_query):
        self.mycursor.execute(table_query)

    def insert_data(self, insert_query, val):
        self.mycursor.execute(insert_query, val)
        self.mydb.commit()

    def select_data(self, select_query):
        self.mycursor.execute(select_query)
        return self.mycursor.fetchall()

    def update_data(self, update_query):
        self.mycursor.execute(update_query)
        self.mydb.commit()

    def delete_data(self, delete_query):
        self.mycursor.execute(delete_query)
        self.mydb.commit()

You can then use the above class as follows:

mysql_manager = MySQLManager("localhost", "user", "password", "database")

# Creating a table
create_table_query = "CREATE TABLE Customers (name VARCHAR(255), address VARCHAR(255))"
mysql_manager.create_table(create_table_query)

# Inserting data
insert_query = "INSERT INTO Customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mysql_manager.insert_data(insert_query, val)

# Selecting data
select_query = "SELECT * FROM Customers"
print(mysql_manager.select_data(select_query))

# Updating data
update_query = "UPDATE Customers SET address = 'Canyon 123' WHERE name = 'John'"
mysql_manager.update_data(update_query)

# Deleting data
delete_query = "DELETE FROM Customers WHERE address = 'Mountain 21'"
mysql_manager.delete_data(delete_query)

Remember to replace “localhost”, “user”, “password”, and “database” with your MySQL server’s host, your username, your password, and the database name respectively.

Please note that the above is a very basic example, and error handling should be implemented for a production-level application. Additionally, make sure you have the right privileges to create, read, update, and delete data in the MySQL server.

Specifying the Port Number

The port parameter is not required in the given above code snippet. The mysql.connector.connect() method establishes a connection to a MySQL server using the provided host, user, passwd, and database parameters. If the MySQL server is running on the default port (3306), you don’t need to specify the port parameter. MySQL Server could be running at a different port other than the default 3306, so we can modify the constructor to let it take an optional port number.

If your MySQL server is running on a different port, you can include the port parameter in the mysql.connector.connect() method and provide the appropriate port number. For example:

self.mydb = mysql.connector.connect(
    host=host,
    user=user,
    passwd=passwd,
    database=database,
    port=your_port_number
)

Replace your_port_number with the actual port number where your MySQL server is running. If you’re using the default port (3306), there’s no need to include the port parameter in your code.

Closing the Connection

It’s generally good practice to close the connection to the MySQL server when you are finished using it. Although not closing the connection may not cause immediate issues, it can lead to problems if your code is running for an extended period or if you are working with a large number of connections.

To close the connection in your MySQLManager class, you can add a close_connection() method:

class MySQLManager:
    # ... existing code ...
    
    def close_connection(self):
        self.mycursor.close()
        self.mydb.close()

You can call this method when you’re done using the MySQLManager instance to release the resources associated with the connection.

For example:

# Create an instance of MySQLManager
manager = MySQLManager(host, user, passwd, database)

# Perform database operations...

# Close the connection
manager.close_connection()

Closing the connection properly ensures that resources are freed up and that the connection is terminated gracefully.

MySQL Connection Timeout

To add a connection timeout when establishing a connection to a MySQL server using mysql-connector-python, you can specify the connect_timeout parameter in the mysql.connector.connect() method. The connect_timeout parameter specifies the number of seconds to wait for the connection to be established before timing out.

Here’s an example of how you can add a connection timeout:

self.mydb = mysql.connector.connect(
    host=host,
    user=user,
    passwd=passwd,
    database=database,
    connect_timeout=10  # Set the timeout value (in seconds) as per your requirement
)

In the above example, the connection timeout is set to 10 seconds (connect_timeout=10). Adjust the value to suit your specific needs. If the connection cannot be established within the specified timeout period, a mysql.connector.errors.InterfaceError will be raised.

By setting a connection timeout, you can control the maximum time your program waits for the connection to be established, preventing indefinite waits in case of network issues or unresponsive servers.

The Complete MySQL Manager in Python

Below is the complete Python source to Build a MySQL Manager, so that it is very simple and convinient to connect to MySQL Database and execute a few queries there.

# require: pip3 install mysql-connector-python
import mysql.connector

class MySQLManager:
    def __init__(self, host, user, passwd, database, port=3306):
        self.mydb = mysql.connector.connect(
            host=host,
            user=user,
            passwd=passwd,
            database=database,
            port=port,
            connect_timeout=30
        )
        self.mycursor = self.mydb.cursor()
    
    def create_table(self, table_query):
        self.mycursor.execute(table_query)

    def insert_data(self, insert_query, val):
        self.mycursor.execute(insert_query, val)
        self.mydb.commit()

    def select_data(self, select_query):
        self.mycursor.execute(select_query)
        return self.mycursor.fetchall()

    def update_data(self, update_query):
        self.mycursor.execute(update_query)
        self.mydb.commit()

    def delete_data(self, delete_query):
        self.mycursor.execute(delete_query)
        self.mydb.commit()

    def close_connection(self):
        self.mycursor.close()
        self.mydb.close()

DevOps / Site Reliability Engineering

• How to Clean Up NVM Node Versions Except One?
• Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
• Python/Bash Script to Print the Optimized Parameters for MySQL Servers
• Learn to Manage Your MySQL Database with a Python Script
• A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
• How to Print MySQL Table Summary using PHP?
• Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
• Bash Script to Check, Repair, Optimise and Backup MySQL database
• Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
• MySQL server stopped due of out of memory exception on Ubuntu VPS
• Running Apache Server (PHP + MySQL) on Raspberry PI
• How to Optimise SQL Queries? Quick Tips
• Recovery Models in SQL Server
• Database Optimisation Script in PHP

–EOF (The Ultimate Computing & Technology Blog) —