Python MySQL

Integrating Python with MySQL allows developers to build robust applications that can store, retrieve, and manipulate data efficiently. Python's mysql-connector-python library facilitates this integration, enabling seamless communication between Python programs and MySQL databases.

Installing MySQL Connector

To begin, ensure that Python and pip are installed on your system. Then, install the MySQL Connector using pip:

pip install mysql-connector-python

Establishing a Connection

After installation, you can connect to your MySQL server using the following Python code:

import mysql.connector

# Establishing the connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

print(db)

This script connects to the MySQL server running on localhost using the provided credentials.

Creating a Database

Once connected, you can create a new database:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

cursor = db.cursor()

cursor.execute("CREATE DATABASE mydatabase")

This code creates a database named mydatabase.

Creating a Table

After creating a database, you can create tables within it:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

This script creates a table named customers with two columns: name and address.

Inserting Data

You can insert data into the table using the INSERT INTO statement:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

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

cursor.execute(sql, val)

db.commit()

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

This code inserts a single record into the customers table.

Retrieving Data

To fetch data from the table, use the SELECT statement:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM customers")

result = cursor.fetchall()

for row in result:
    print(row)

This script retrieves and prints all records from the customers table.

Updating Records

To update existing records in the table:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Canyon 123", "Highway 21")

cursor.execute(sql, val)

db.commit()

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

This code updates the address of records where the current address is Highway 21.

Deleting Records

To delete records from the table:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="mydatabase"
)

cursor = db.cursor()

sql = "DELETE FROM customers WHERE address = %s"
val = ("Canyon 123",)

cursor.execute(sql, val)

db.commit()

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

This script deletes records from the customers table where the address is Canyon 123.

Conclusion

Integrating Python with MySQL using the mysql-connector-python library provides a powerful way to manage databases within your Python applications. By understanding how to connect, create databases and tables, and perform CRUD operations, you can effectively handle data storage and retrieval tasks.