Adventures in Machine Learning

Mastering Python MySQL Connectivity: CRUD Transactions and More

Connecting MySQL Database in Python

MySQL is one of the most popular and widely used relational database management systems due to its stability, performance, and ease of use. Python, on the other hand, is a high-level programming language that is easy to learn and allows you to build sophisticated applications.

Connecting MySQL with Python can help you create dynamic databases, organize data, and perform analyses and queries. In this article, we will be discussing how to connect MySQL with Python using the MySQL Connector Python module and the arguments required to connect.

MySQL Connector Python module

The MySQL Connector Python module is a Python library used to connect with MySQL. The installation of the connector is straightforward.

You can use pip to install the connector using the following command:

pip install mysql-connector-python

After installing the connector, you are ready to start connecting with MySQL from Python.

Arguments required to connect

To connect to a MySQL server from Python, you need to have the following information:

  • MySQL server: The IP address or the domain name of the MySQL server.
  • Username: The MySQL username that has the necessary permissions.
  • Password: The password for the username.
  • Hostname: The system hostname or IP address.
  • Database name: The name of the database you are connecting to.

To connect with the MySQL server, use the following syntax:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

print(mydb)

This code creates a connection object that contains all the necessary details to connect with the MySQL server.

Creating MySQL Table from Python

Now that we know how to connect with MySQL using Python, let’s see how to create a table in MySQL using Python.

Creating a table in MySQL using Python

Before creating a table, you should open a cursor. The cursor is a control structure that allows you to access and manipulate the rows in the result set.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE Laptop (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price INT)")

This code creates a Laptop table with three columns: id, name, and price. The id column is the primary key, and it is an auto-increment column.

Now let’s see an example to insert data into the table. Example: Creating Laptop Table

Suppose we have a database called Electronics in MySQL that contains various product tables like Laptop, Mobile, TV, etc.

We can create a Laptop table in the Electronics database using the following code:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="Electronics"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE Laptop (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), price INT)")
sql = "INSERT INTO Laptop (name, price) VALUES (%s, %s)"
val = [
  ('Lenovo', 50000),
  ('Dell', 60000),
  ('HP', 45000),
  ('Acer', 55000),
  ('Asus', 58000),
  ('MSI', 65000),
  ('Apple', 90000)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "records were inserted.")

The above code inserts seven rows of laptops and their prices into the Laptop table.

Conclusion

In conclusion, connecting MySQL with Python can help you create powerful yet straightforward databases, allowing you to easily manage, analyze, and query data. The MySQL Connector Python module allows you to connect with MySQL and perform various operations like creating a table.

Moreover, by using Python, you can create dynamic and easy-to-use applications that can help you manage your data efficiently.

Python MySQL CRUD Operations

Python MySQL CRUD operations allow you to perform a variety of operations on your MySQL database using Python. In this section, we will cover the most commonly used CRUD operations for MySQL, including inserting, selecting, updating, deleting, executing stored procedures, parameterized queries, prepared statements, managing transactions, connection pooling, BLOB insertion and retrieval, and MySQL connection arguments.

Inserting Rows into MySQL Table using Python

To insert rows into a MySQL table using Python, you will need to use the “INSERT INTO” command, along with the “VALUES” keyword to insert data into the table. Below is an example of inserting a row into a MySQL table using Python variables:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()

# define query
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")

# execute query
mycursor.execute(sql, val)

# commit transaction
mydb.commit()

print(mycursor.rowcount, "record inserted.")

Selecting Rows from MySQL Table using Python

To select rows from a MySQL table using Python, you can use the “SELECT” statement. After executing the query, you can process the results using the cursor’s fetch methods.

Here is an example of selecting rows from a MySQL table using a SQL SELECT query:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()

# define query
sql = "SELECT * FROM customers"

# execute query
mycursor.execute(sql)

# process results
result = mycursor.fetchall()
for x in result:
  print(x)

Updating Rows of MySQL Table using Python

To update rows of a MySQL table using Python, you can use the “UPDATE” statement with the “WHERE” clause to specify which rows to update. Parameters can be passed to the query using Python variables.

Here is an example of updating rows of a MySQL table using a parameterized query:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()

# define query
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("Valley 345", "John")

# execute query
mycursor.execute(sql, val)

# commit transaction
mydb.commit()

print(mycursor.rowcount, "record(s) affected")

Deleting Table Rows from Python

To delete rows from a MySQL table using Python, you can use the “DELETE FROM” statement along with the “WHERE” clause to specify which rows to delete. Here is an example of deleting rows from a MySQL table using a SQL DELETE query:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()

# define query
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"

# execute query
mycursor.execute(sql)

# commit transaction
mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Executing MySQL Stored Procedures from Python

To execute MySQL stored procedures from Python, you can use the cursor’s “callproc” method. The method takes the stored procedure name and a tuple of arguments as parameters.

IN and OUT parameters can be defined for the stored procedure. Here is an example of executing a MySQL stored procedure with IN and OUT parameters:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()

# define stored procedure with IN and OUT parameters
mycursor.callproc('add_customer', ('John', 'Doe'))

# process results
for result in mycursor.stored_results():
  print(result.fetchall())

Parameterized Query and Prepared Statement in Python MySQL

Parameterized queries and prepared statements can help prevent SQL injection attacks. Parameterized queries use placeholders for values, while prepared statements use question marks (?) for values.

The query is then executed with the values as parameters. Here is an example of using parameterized queries and prepared statements in Python MySQL:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor with prepared statement
mycursor = mydb.cursor(prepared=True)

# define query
sql = "INSERT INTO customers (name, address) VALUES (?, ?)"
val = ("John", "Highway 21")

# execute query
mycursor.execute(sql, val)

# commit transaction
mydb.commit()

print(mycursor.rowcount, "record inserted.")

Commit and Rollback to Manage Transactions in Python MySQL

To manage transactions in Python MySQL, you can use the “commit” and “rollback” methods of the connection object. The “commit” method is used to commit the changes made to the database, while the “rollback” method is used to undo the changes made.

Here is an example of managing transactions in Python MySQL:

import mysql.connector

# establish connection
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

# prepare cursor
mycursor = mydb.cursor()
try:
  # define query
  sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
  val = ("John", "Highway 21")

  # execute query
  mycursor.execute(sql, val)

  # commit transaction
  mydb.commit()

  print(mycursor.rowcount, "record inserted.")
except:
  # rollback transaction
  mydb.rollback()

  print("Transaction rolled back.")
finally:
  # close connection
  mydb.close()

Connection Pooling With MySQL in Python

Connection pooling allows you to reuse database connections to improve performance by reducing the overhead of creating and closing connections. MySQL Connector/Python includes a built-in connection pool.

Here is an example of connection pooling with MySQL in Python:

import mysql.connector
from mysql.connector import pooling

# define connection pool properties
config = {
  "host": "localhost",
  "user": "root",
  "password": "password",
  "database": "test_db",
}

# create connection pool
connection_pool = pooling.MySQLConnectionPool(pool_size=5, **config)

# get connection from pool
connection = connection_pool.get_connection()

# perform database operations
cursor = connection.cursor()
cursor.execute("SELECT * FROM users")
result = cursor.fetchall()

print(result)

# release connection back to pool
connection_pool.release_connection(connection)

BLOB Insert and Retrieve Digital Data in Python MySQL

BLOB data can be used to store digital information such as images, videos, audio, etc., in a MySQL database. To insert and retrieve BLOB data in Python MySQL, you can use the cursor’s “execute” method with a parameter containing the BLOB data.

Here is an example of inserting and retrieving BLOB data in Python MySQL:

import mysql.connector
from mysql.connector import Error

# read BLOB data from file
with open("image.png", "rb") as file:
    blob_data = file.read()
try:
    # establish connection
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="password",
        database="test_db"
    )
    # prepare cursor
    cursor = connection.cursor()
    # define query
    sql = "INSERT INTO images (image) VALUES (%s)"
    val = (blob_data,)
    # execute query
    cursor.execute(sql, val)
    # commit transaction
    connection.commit()
    # select BLOB data from database
    cursor.execute("SELECT * FROM images")
    result = cursor.fetchone()
    with open("new_image.png", "wb") as file:
      file.write(result[1])
except Error as e:
    print("Error reading data from MySQL table", e)
finally:
    # close connection
    connection.close()

Python MySQL Connection Arguments

MySQL Connector/Python provides several connection arguments that can be used to customize the connection properties. Some commonly used connection arguments are listed below:

  • host: The MySQL server hostname or IP address.
  • port: The port number of the MySQL server.
  • user: The username used to authenticate.
  • password: The password used to authenticate.
  • database: The database to use.
  • autocommit: Enables or disables autocommit mode.
  • buffered: Enables or disables buffering of the result sets.
  • connect_timeout: The maximum time in seconds that the client waits for a connection.
  • use_pure: Enables or disables the use of pure Python.
  • ssl_ca: The path to the CA certificate file.
  • ssl_cert: The path to the client certificate file.
  • ssl_key: The path to the client private key file.

Using a Dictionary to Keep MySQL Connection Arguments

When using many connection arguments, it can be tedious to specify all the values each time. A better approach is to use a dictionary to store all the connection arguments.

Here is an example of using a dictionary to keep MySQL connection arguments:

import mysql.connector

# define connection parameters
config = {
  "host": "localhost",
  "user": "root",
  "password": "password",
  "database": "mydatabase",
  "autocommit": True,
  "buffered": True,
  "connect_timeout": 30,
}

# establish connection
connection = mysql.connector.connect(**config)
print(connection.is_connected())

Changing MySQL Connection Timeout from Python

By default, MySQL Connector/Python sets a 20-second timeout for the connection. To change the connection timeout from Python, you can use the “connect_timeout” connection argument.

Here is an example of changing the connection timeout from Python:

import mysql.connector

# define connection parameters with changed timeout
config = {
  "host": "localhost",
  "user": "root",
  "password": "password",
  "database": "mydatabase",
  "connect_timeout": 60,
}

# establish connection
connection = mysql.connector.connect(**config)
print(connection.is_connected())

Connecting to MySQL Using Connector Python C Extension

MySQL Connector/Python C extension is a powerful extension that provides better performance than the pure Python implementation. To use the MySQL Connector/Python C extension, you need to compile and install it on your system.

Here is an example of connecting to MySQL using the MySQL Connector/Python C extension:

import mysql.connector
from mysql.connector import connect

# create connection object with C extension
connection = connect(
  host="localhost",
  user="root",
  password="password",
  database="mydatabase",
  use_pure=False
)
print(connection.is_connected())

In conclusion, Python MySQL connectivity is a crucial aspect of data management and analysis. With the help of MySQL Connector Python, you can establish a connection between MySQL and Python.

You can then use the various Python MySQL CRUD operations, such as inserting, selecting, updating, deleting, executing stored procedures, parameterized queries, prepared statements, managing transactions, connection pooling, and BLOB insertion and retrieval to manipulate and work with the data in MySQL databases. Understanding the various MySQL connection arguments is also an essential aspect of ensuring a smooth and optimized data handling experience.

Taking the time to learn more about Python MySQL connectivity is one key to enhancing your overall data management and analytics skills.

Popular Posts