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:
1 | pip install mysql-connector-python |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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() |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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) |
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:
1 2 3 4 5 6 7 | self.mydb = mysql.connector.connect( host=host, user=user, passwd=passwd, database=database, port=your_port_number ) |
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:
1 2 3 4 5 6 | class MySQLManager: # ... existing code ... def close_connection(self): self.mycursor.close() self.mydb.close() |
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:
1 2 3 4 5 6 7 | # Create an instance of MySQLManager manager = MySQLManager(host, user, passwd, database) # Perform database operations... # Close the connection manager.close_connection() |
# 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:
1 2 3 4 5 6 7 | 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 ) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | # 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() |
# 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()–EOF (The Ultimate Computing & Technology Blog) —
loading...
Last Post: How to Compute the Chain Age, Uptime and Downtime for Steem Blockchain?
Next Post: Teaching Kids Programming - Minimum Operations to Reduce an Integer to 0 (Greedy Recursion/Top Down Dynamic Programming Algorithm)
