MySQL Database in Python

MySQL is an open-source relational database management system that is widely used for applications requiring a robust, secure, and scalable database. In Python, you can interact with MySQL databases using the mysql-connector-python library.

1. Setting Up MySQL Database in Python

To use MySQL in Python, you need to install the mysql-connector-python library, which allows Python to interact with MySQL databases.

Step 1: Install MySQL Connector

You can install the MySQL connector via pip.

pip install mysql-connector-python

2. Connecting to MySQL Database

Once the connector is installed, you can connect to the MySQL database using the connect() method.

Example: Connecting to MySQL

import mysql.connector
# Establish the connection to the database
conn = mysql.connector.connect(
host="localhost", # Database host
user="root", # MySQL username
password="password", # MySQL password
database="mydatabase" # Name of the database
)
# Create a cursor object to interact with the database
cursor = conn.cursor()

Replace "localhost", "root", "password", and "mydatabase" with your MySQL server details and database name.

3. Creating a Database

To create a database, you can execute the CREATE DATABASE SQL query.

Example: Creating a Database

# Create a new database
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
# Commit the changes
conn.commit()

The CREATE DATABASE IF NOT EXISTS command creates the database only if it does not already exist.

4. Creating a Table

You can create a table within the database by executing an SQL CREATE TABLE query.

Example: Creating a Table

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL)''')
# Commit the changes
conn.commit()

This creates a table called users with columns id, name, and age.

5. Inserting Data into the Table

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

Example: Inserting Data

# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 30))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 25))
# Commit the changes
conn.commit()

The %s placeholders are used to insert values into the query. Using placeholders helps prevent SQL injection attacks.

6. Fetching Data from the Table

You can retrieve data from the table using the SELECT statement.

Example: Fetching Data

# Fetch data from the table
cursor.execute("SELECT * FROM users")
# Fetch all rows from the result
rows = cursor.fetchall()
# Print the rows
for row in rows:
print(row)

fetchall() retrieves all rows from the result of the query.

7. Updating Data in the Table

To update existing data, you can use the UPDATE SQL statement.

Example: Updating Data

# Update data in the table
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (28, "Bob"))
# Commit the changes
conn.commit()

The UPDATE statement changes the age of "Bob" to 28.

8. Deleting Data from the Table

You can delete data from the table using the DELETE statement.

Example: Deleting Data

# Delete data from the table
cursor.execute("DELETE FROM users WHERE name = %s", ("Alice",))
# Commit the changes
conn.commit()

This command deletes the record where the name is "Alice".

9. Handling Errors

It’s important to handle exceptions and errors during database operations.

Example: Handling Errors

try:
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Charlie", 35))
conn.commit()
except mysql.connector.Error as err:
print(f"Error: {err}")
conn.rollback() # Rollback in case of error

mysql.connector.Error handles MySQL-specific errors such as connection failures, invalid queries, etc.

10. Closing the Connection

Once you’re done with your database operations, always close the connection to release resources.

Example: Closing Connection

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

Complete Example

Here is a complete example that demonstrates all the steps above:

import mysql.connector
# Step 1: Connect to MySQL Database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
# Step 2: Create a cursor object
cursor = conn.cursor()
# Step 3: Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT NOT NULL)''')
# Step 4: Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Alice", 30))
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("Bob", 25))
conn.commit()
# Step 5: Fetch data from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
# Step 6: Update data in the table
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (28, "Bob"))
conn.commit()
# Step 7: Delete data from the table
cursor.execute("DELETE FROM users WHERE name = %s", ("Alice",))
conn.commit()
# Step 8: Fetch updated data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("\nUpdated Table:")
for row in rows:
print(row)
# Step 9: Close the connection
cursor.close()
conn.close()

Conclusion

MySQL is a powerful relational database management system, and Python’s mysql-connector-python library makes it easy to interact with MySQL databases. With Python, you can perform all common database operations like creating, reading, updating, and deleting data in a MySQL database.

Let me know if you need further clarification or more examples! 😊

MySQL Database in Python