Connecting to MySQL

Prerequisites:

• MySQL database server
• Python 3.x installed
• One of the following data connectors:
  • MySQL Connector/Python
  • PyMySQL
  • SQLAlchemy

Steps:

Install a Data Connector:

• MySQL Connector/Python: pip install mysql-connector-python
• PyMySQL: pip install PyMySQL
• SQLAlchemy: pip install SQLAlchemy

Import the Connector:

import mysql.connector

Establish a Connection:

connection = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

Create a Cursor:

cursor = connection.cursor()

Execute Queries:

• execute() method to execute a query
• fetchall() method to retrieve results as a list of tuples
• fetchone() method to retrieve the next result

Commit Changes:

cursor.execute("INSERT INTO...")
connection.commit()

Close the Connection:

cursor.close()
connection.close()

Methods and Libraries for Connecting Python to MySQL

• MySQL Connector/Python: Official connector from MySQL, easy to use but lacks some advanced features.
• PyMySQL: Third-party connector, faster and provides more flexibility.
• SQLAlchemy: Object-relational mapping (ORM) framework, simplifies database operations.

Example:

import mysql.connector

# Connect to the database
connection = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# Create a cursor
cursor = connection.cursor()

# Execute a query
cursor.execute("SELECT * FROM users")

# Retrieve the results
users = cursor.fetchall()

# Close the cursor and connection
cursor.close()
connection.close()

# Print the users
for user in users:
    print(user)