Python MySQL: Introduction and Installation

This tutorial, part of the Python MySQL series by Kotha Abhishek, introduces how to integrate Python with MySQL, a popular open-source relational database management system (RDBMS). We’ll cover the basics of MySQL, the role of the mysql-connector-python library, and how to install it. Examples use mysql-connector-python in a standard Python environment with an active MySQL server. Ensure MySQL is installed and running, and replace placeholder credentials (e.g., host, user, password) with your actual MySQL settings. This tutorial is designed for beginners, with code blocks styled to span 100% width of the content area.

• MySQL Overview:
  MySQL is an RDBMS for storing and managing structured data using SQL queries. Python interacts with MySQL via libraries like mysql-connector-python.
  Example: Connect using mysql.connector.connect(host='localhost', user='root', password='pass')
• mysql-connector-python:
  An official MySQL driver for Python, enabling database connections and operations.
  Example: Install with pip install mysql-connector-python
• Installation:
  Install the library using pip, verify it, and ensure MySQL server is running locally or remotely.
  Example: Check installation with import mysql.connector
• Good Practice:
  Use virtual environments for package management, verify MySQL server access, handle installation and connection errors, and check library versions for compatibility.

Example 1: Verifying MySQL Installation

This example verifies the installation of mysql-connector-python by attempting to import it.

mysql-connector-python is installed successfully!

Example 2: Connecting to MySQL

This example establishes a connection to a MySQL server using mysql-connector-python.

Connected to MySQL successfully!

Example 3: Creating a MySQL Database

This example creates a MySQL database named test_db.

Database 'test_db' created successfully!

Example 4: Creating a MySQL Table

This example creates a users table in the test_db database.

Table 'users' created successfully!

Example 5: Installation and Connection with Error Handling

This example verifies installation and attempts a connection with comprehensive error handling.

mysql-connector-python is installed.
Connection Error: 2003: Can't connect to MySQL server on 'invalid_host'

Python MySQL: Connecting and Creating a Database

• Connecting to MySQL:
  Use mysql.connector.connect() to establish a connection with parameters like host, user, and password.
  Example: mysql.connector.connect(host='localhost', user='root', password='your_password')
• Creating a Database:
  Execute a CREATE DATABASE SQL query using a cursor to create a new database.
  Example: cursor.execute("CREATE DATABASE my_db")
• Error Handling:
  Use try-except to manage connection or query errors, such as invalid credentials or duplicate databases.
  Example: Catch mysql.connector.Error for robust error handling.
• Good Practice:
  Always close cursors and connections, use IF NOT EXISTS to avoid errors, verify connection settings, and handle exceptions gracefully.

Example 1: Basic MySQL Connection

This example establishes a basic connection to a MySQL server and closes it.

Connected to MySQL successfully!

Example 2: Creating a MySQL Database

This example creates a database named my_db using a CREATE DATABASE query.

Database 'my_db' created successfully!

Example 3: Verifying Database Creation

This example connects to MySQL, creates a database, and lists all databases to verify creation.

Available Databases: ['information_schema', 'my_db', 'mysql', 'performance_schema', 'sys']

Example 4: Handling Connection Errors

This example attempts a connection with invalid credentials to demonstrate error handling.

Connection Error: 2003: Can't connect to MySQL server on 'invalid_host'

Example 5: Connecting and Creating Multiple Databases

This example connects to MySQL and creates multiple databases, checking for their existence.

Database 'app_db' created successfully!
Database 'data_db' created successfully!
Available Databases: ['app_db', 'data_db', 'information_schema', 'mysql', 'performance_schema', 'sys']

Python MySQL: Creating a Table

This tutorial explains how to create tables in a MySQL database using the mysql-connector-python library. It covers defining table structures, adding constraints (e.g., primary keys, foreign keys), and verifying table creation. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db (created in the previous tutorial). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Creating a Table:
  Use cursor.execute() with a CREATE TABLE SQL query to define columns and data types.
  Example: CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
• Constraints:
  Add constraints like PRIMARY KEY, NOT NULL, or FOREIGN KEY to enforce data integrity.
  Example: FOREIGN KEY (user_id) REFERENCES users(id)
• Verification:
  Use SHOW TABLES or DESCRIBE to confirm table creation.
  Example: cursor.execute("SHOW TABLES")
• Good Practice:
  Use IF NOT EXISTS to avoid errors, close cursors and connections, validate table structure, and handle exceptions with try-except.

Example 1: Creating a Basic Table

This example creates a simple users table with id and name columns in the my_db database.

Table 'users' created successfully!

Example 2: Creating a Table with Constraints

This example creates a orders table with a foreign key referencing the users table.

Table 'orders' created successfully!

Example 3: Verifying Table Creation

This example creates a products table and lists all tables to verify creation.

Tables in 'my_db': ['users', 'orders', 'products']

Example 4: Handling Table Creation Errors

This example attempts to create a table with an invalid foreign key to demonstrate error handling.

Error: 1822: Failed to add the foreign key constraint. Missing index for constraint 'orders_invalid_ibfk_1' in the referenced table 'nonexistent_table'

Example 5: Creating Multiple Tables

This example creates multiple tables (categories and reviews) with relationships.

Tables created successfully: ['users', 'orders', 'products', 'categories', 'reviews']

Python MySQL: Inserting Data into Tables

This tutorial explains how to insert data into MySQL tables using the mysql-connector-python library. Inserting data involves using the INSERT INTO SQL statement to add records to a table, executed via a cursor in Python. This process is essential for populating databases with data, such as user information, orders, or products in an application. We’ll cover single-row and multiple-row insertions, parameterized queries for security, handling errors, and verifying inserted data. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, orders, products, categories, and reviews (created in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Inserting Data:
  Use cursor.execute() with an INSERT INTO SQL query to add a single record to a table.
  Example: INSERT INTO users (name) VALUES ('Alice')
• Multiple Rows:
  Use cursor.executemany() with parameterized queries to insert multiple records efficiently.
  Example: cursor.executemany("INSERT INTO users (name) VALUES (%s)", [("Bob"), ("Charlie")>)
• Parameterized Queries:
  Use placeholders (e.g., %s) to prevent SQL injection and ensure safe data insertion.
  Example: cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ("Laptop", 999.99))
• Error Handling:
  Use try-except to manage errors, such as invalid data types or foreign key violations.
  Example: Catch mysql.connector.Error for robust error handling.
• Good Practice:
  Always use parameterized queries, commit transactions with connection.commit(), close cursors and connections, and verify data after insertion using SELECT queries.

Example 1: Inserting a Single Row into the Users Table

This example inserts a single record into the users table with a name and age. The INSERT INTO query specifies the columns and values, and the transaction is committed to save the data.

Inserted 1 row(s) into 'users' table.

Example 2: Inserting Multiple Rows Using executemany

This example inserts multiple records into the users table using executemany() with parameterized queries for efficiency and security. A list of tuples provides the data for multiple rows.

Inserted 3 row(s) into 'users' table.

Example 3: Inserting Data with Parameterized Queries

This example uses a parameterized query to safely insert a single record into the products table, preventing SQL injection by separating data from the query.

Inserted 1 row(s) into 'products' table.

Example 4: Inserting Data with Error Handling

This example attempts to insert a record into the orders table with an invalid user_id (non-existent foreign key), demonstrating error handling for foreign key violations.

Error: 1452: Cannot add or update a child row: a foreign key constraint fails (`my_db`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

Example 5: Inserting Data with Foreign Key

This example inserts a record into the orders table with a valid user_id, ensuring the foreign key constraint is satisfied (assumes user_id=1 exists in users).

Inserted 1 row(s) into 'orders' table.

Example 6: Dynamic Data Insertion

This example dynamically inserts data into the categories table based on user input (simulated with a list), using a parameterized query for flexibility.

Inserted category: Electronics
Inserted category: Books
Inserted category: Clothing

Example 7: Inserting and Verifying Data

This example inserts a record into the reviews table and retrieves the inserted data using a SELECT query to verify the insertion.

Inserted 1 row(s) into 'reviews' table.
Inserted data: [(1, 1, 5)]

Python MySQL: Describing, Altering, and Dropping Tables

This tutorial explains how to describe, alter, and drop tables in a MySQL database using the mysql-connector-python library. Describing a table retrieves its structure (columns, data types, constraints), altering a table modifies its structure (e.g., adding or dropping columns), and dropping a table deletes it entirely. These operations are essential for managing database schemas in applications like user management or e-commerce systems. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, orders, and products (created in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Describing a Table:
  Use DESCRIBE or SHOW COLUMNS to retrieve a table’s structure, including column names, data types, and constraints.
  Example: cursor.execute("DESCRIBE users")
• Altering a Table:
  Use ALTER TABLE to modify a table’s structure, such as adding, modifying, or dropping columns, or adding constraints.
  Example: ALTER TABLE users ADD email VARCHAR(255)
• Dropping a Table:
  Use DROP TABLE to delete a table and its data permanently.
  Example: cursor.execute("DROP TABLE IF EXISTS users")
• Error Handling:
  Use try-except to manage errors, such as attempting to alter a non-existent table or violating constraints.
  Example: Catch mysql.connector.Error for robust error handling.
• Good Practice:
  Use IF EXISTS or IF NOT EXISTS to avoid errors, commit changes with connection.commit(), close cursors and connections, and verify changes with DESCRIBE.

Example 1: Describing a Table Structure

This example uses DESCRIBE to retrieve the structure of the users table, showing columns, data types, and constraints.

Structure of 'users' table:
('id', 'int', 'NO', 'PRI', None, 'auto_increment')
('name', 'varchar(255)', 'YES', '', None, '')
('age', 'int', 'YES', '', None, '')

Example 2: Adding a Column to a Table

This example uses ALTER TABLE to add an email column to the users table, demonstrating how to expand a table’s structure.

Column 'email' added to 'users' table.

Example 3: Modifying a Column in a Table

This example modifies the price column in the products table to change its data type to DECIMAL(12, 2) to allow larger values.

Column 'price' modified in 'products' table.

Example 4: Dropping a Column from a Table

This example removes the age column from the users table using ALTER TABLE DROP, demonstrating how to reduce a table’s structure.

Column 'age' dropped from 'users' table.

Example 5: Adding a Constraint to a Table

This example adds a UNIQUE constraint to the email column in the users table to ensure no duplicate emails, showing how to enforce data integrity.

UNIQUE constraint added to 'email' in 'users' table.

Example 6: Dropping a Table

This example drops the orders table using DROP TABLE, with IF EXISTS to avoid errors if the table doesn’t exist.

Table 'orders' dropped successfully.

Example 7: Handling Errors When Altering a Non-Existent Table

This example attempts to alter a non-existent table to demonstrate error handling for invalid operations.

Error: 1146: Table 'my_db.nonexistent_table' doesn't exist

Python MySQL: Selecting Records from Tables

This tutorial explains how to select records from MySQL tables using the mysql-connector-python library. Selecting records involves using the SELECT SQL statement to retrieve data, executed via a cursor in Python. This process is crucial for querying data in applications, such as retrieving user details or product information. We’ll cover basic queries, filtering, sorting, joining tables, limiting results, and aggregating data. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, orders, products, categories, and reviews (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Selecting Records:
  Use cursor.execute() with a SELECT query to retrieve data, and fetch results with fetchall(), fetchone(), or fetchmany().
  Example: SELECT * FROM users
• Filtering Data:
  Use WHERE to filter records based on conditions.
  Example: SELECT * FROM users WHERE age > 25
• Sorting and Limiting:
  Use ORDER BY to sort results and LIMIT to restrict the number of rows returned.
  Example: SELECT * FROM products ORDER BY price DESC LIMIT 2
• Joining Tables:
  Use JOIN to combine data from multiple tables based on related columns.
  Example: SELECT users.name, orders.product FROM users JOIN orders ON users.id = orders.user_id
• Good Practice:
  Use parameterized queries to prevent SQL injection, close cursors and connections, handle empty results, and use specific column names instead of * for clarity.

Example 1: Basic SELECT Query

This example retrieves all records from the users table, displaying all columns and rows.

Users:
(1, 'Alice', None)
(2, 'Bob', None)
(3, 'Charlie', None)
(4, 'Diana', None)

Example 2: Filtering Records with WHERE

This example selects records from the users table where the name starts with 'A', using a parameterized query for safety.

Users with names starting with 'A':
('Alice', None)

Example 3: Sorting and Limiting Results

This example retrieves the top two most expensive products from the products table, sorted by price in descending order.

Top 2 most expensive products:
('Laptop', 999.99)
('Phone', 699.99)

Example 4: Joining Tables

This example joins the users and orders tables to retrieve user names and their ordered products, using an INNER JOIN.

User orders:
('Alice', 'Phone')

Example 5: Handling Empty Results

This example queries the reviews table for a non-existent product ID, handling the case where no records are returned.

No reviews found for product_id 999.

Example 6: Aggregating Data

This example uses aggregate functions (COUNT, AVG) to count reviews and calculate the average rating per product.

Review statistics by product:
(1, 1, 5.0)

Example 7: Selecting with Multiple Conditions and Joins

This example combines multiple conditions, a LEFT JOIN, and sorting to retrieve product names and their categories, including products without a category.

Products with price > 500 and their categories:
('Phone', 'Electronics')
('Laptop', 'Electronics')

Python MySQL: Using WHERE, LIKE, and ORDER BY in SELECT Queries

This tutorial explains how to use the WHERE, LIKE, and ORDER BY clauses in MySQL SELECT queries with the mysql-connector-python library. These clauses are essential for filtering, pattern matching, and sorting data in database queries, enabling precise data retrieval for applications like user searches or product listings. The WHERE clause filters records based on conditions, LIKE enables pattern-based searches, and ORDER BY sorts results. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, products, and categories (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• WHERE Clause:
  Filters records based on specified conditions, such as equality or range checks.
  Example: SELECT * FROM users WHERE id = 1
• LIKE Operator:
  Matches patterns in string columns using wildcards (% for any characters, _ for a single character).
  Example: SELECT * FROM products WHERE name LIKE '%phone%'
• ORDER BY Clause:
  Sorts query results in ascending (ASC) or descending (DESC) order based on one or more columns.
  Example: SELECT * FROM products ORDER BY price DESC
• Good Practice:
  Use parameterized queries to prevent SQL injection, close cursors and connections, handle empty results, and specify columns explicitly for clarity.
  Example: cursor.execute("SELECT name FROM users WHERE id = %s", (1,))

Example 1: Basic WHERE Clause

This example uses a WHERE clause to select a user with a specific ID from the users table, using a parameterized query for safety.

User with ID 1: (1, 'Alice', None)

Example 2: Using LIKE for Pattern Matching

This example uses LIKE with the % wildcard to find products containing "phone" in their name, demonstrating pattern-based searching.

Products containing 'phone':
('Phone', 699.99)

Example 3: Sorting with ORDER BY

This example sorts all products by price in descending order using ORDER BY, retrieving the most expensive products first.

Products sorted by price (descending):
('Laptop', 999.99)
('Phone', 699.99)

Example 4: Combining WHERE and LIKE

This example combines WHERE and LIKE to find categories starting with 'C' and having an ID greater than 1, using parameterized queries.

Categories starting with 'C' and ID > 1:
(3, 'Clothing')

Example 5: Combining WHERE, LIKE, and ORDER BY

This example combines all three clauses to select users whose names contain 'a', sorted by name in ascending order, using parameterized queries.

Users with 'a' in name, sorted by name:
(1, 'Alice')
(4, 'Diana')

Example 6: Handling No Matches with WHERE and LIKE

This example searches for products with a name pattern that doesn’t exist, handling the case where no records are returned.

No products found with 'xyz' in name.

Example 7: Complex Query with Multiple Conditions and Sorting

This example uses WHERE, LIKE, and ORDER BY to select products in a specific category with a price range, sorted by price.

Electronics products priced between 500 and 1000, sorted by price:
('Laptop', 999.99, 'Electronics')
('Phone', 699.99, 'Electronics')

Python MySQL: Deleting Records from Tables

This tutorial explains how to delete records from MySQL tables using the mysql-connector-python library. Deleting records involves using the DELETE FROM SQL statement to remove specific rows or all rows from a table, executed via a cursor in Python. This operation is critical for managing data in applications, such as removing outdated user accounts or canceled orders. We’ll cover basic deletions, filtering with WHERE and LIKE, handling foreign key constraints, verifying deletions, and managing errors. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, products, categories, and reviews (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Deleting Records:
  Use cursor.execute() with a DELETE FROM query to remove records, and commit changes with connection.commit().
  Example: DELETE FROM users WHERE id = 1
• Filtering with WHERE:
  Use WHERE to delete specific records based on conditions, ensuring only targeted rows are removed.
  Example: DELETE FROM products WHERE price < 100
• Using LIKE:
  Combine WHERE with LIKE for pattern-based deletions.
  Example: DELETE FROM categories WHERE name LIKE '%book%'
• Foreign Key Considerations:
  Ensure dependent records are deleted first or use cascading deletes to avoid foreign key constraint errors.
  Example: Delete from reviews before products if foreign keys exist.
• Good Practice:
  Use parameterized queries to prevent SQL injection, commit changes, close cursors and connections, verify deletions with SELECT, and exercise caution to avoid unintended data loss.

Example 1: Basic DELETE Query

This example deletes a specific user from the users table based on their ID, using a parameterized query for safety.

Deleted 1 row(s) from 'users' table.

Example 2: Deleting with WHERE Condition

This example deletes products from the products table where the price is below a certain threshold, using a parameterized query.

Deleted 0 row(s) from 'products' table.

Example 3: Deleting with LIKE

This example deletes categories from the categories table where the name contains "book", using LIKE with a wildcard.

Deleted 1 row(s) from 'categories' table.

Example 4: Deleting with Multiple Conditions

This example deletes reviews from the reviews table where the product ID and rating meet specific conditions, combining multiple WHERE clauses.

Deleted 0 row(s) from 'reviews' table.

Example 5: Handling Foreign Key Constraints

This example attempts to delete a product that has associated reviews, demonstrating a foreign key constraint error, and suggests deleting dependent records first.

Error: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`my_db`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`))

Example 6: Deleting and Verifying

This example deletes a user and verifies the deletion by checking the remaining records in the users table.

Deleted 1 row(s) from 'users' table.
Remaining users: [(3, 'Charlie'), (4, 'Diana')]

Example 7: Deleting All Records with Caution

This example deletes all records from the categories table (without conditions) and verifies the table is empty, emphasizing caution for such operations.

Deleted 2 row(s) from 'categories' table.
Remaining categories: Table is empty.

Python MySQL: Updating Records in Tables

This tutorial explains how to update records in MySQL tables using the mysql-connector-python library. Updating records involves using the UPDATE SQL statement to modify existing data in a table, executed via a cursor in Python. This operation is essential for maintaining data in applications, such as updating user details, product prices, or category names. We’ll cover basic updates, filtering with WHERE and LIKE, updating multiple columns, handling foreign key constraints, verifying updates, and managing errors. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, products, categories, and reviews (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• Updating Records:
  Use cursor.execute() with an UPDATE query to modify records, and commit changes with connection.commit().
  Example: UPDATE users SET name = 'Alicia' WHERE id = 1
• Filtering with WHERE:
  Use WHERE to target specific records for updates, ensuring only intended rows are modified.
  Example: UPDATE products SET price = 799.99 WHERE product_id = 2
• Using LIKE:
  Combine WHERE with LIKE for pattern-based updates.
  Example: UPDATE categories SET name = 'Tech' WHERE name LIKE '%Electronics%'
• Foreign Key Considerations:
  Ensure updates do not violate foreign key constraints, such as updating referenced IDs.
  Example: Avoid updating product_id in products if referenced in reviews.
• Good Practice:
  Use parameterized queries to prevent SQL injection, commit changes, close cursors and connections, verify updates with SELECT, and exercise caution to avoid unintended data changes.

Example 1: Basic UPDATE Query

This example updates the name of a specific user in the users table based on their ID, using a parameterized query for safety.

Updated 1 row(s) in 'users' table.

Example 2: Updating with WHERE Condition

This example updates the price of a product in the products table where the product ID matches, using a parameterized query.

Updated 1 row(s) in 'products' table.

Example 3: Updating with LIKE

This example updates the name of categories in the categories table where the name contains "Electronics", using LIKE with a wildcard.

Updated 1 row(s) in 'categories' table.

Example 4: Updating Multiple Columns

This example updates both the name and email columns for a user in the users table, demonstrating how to modify multiple fields in one query.

Updated 1 row(s) in 'users' table.

Example 5: Handling Foreign Key Constraints

This example attempts to update a product ID in the products table that is referenced by reviews, demonstrating a foreign key constraint error.

Error: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`my_db`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`))

Example 6: Updating and Verifying

This example updates a product’s price and verifies the change by selecting the updated record.

Updated 1 row(s) in 'products' table.
Updated product: ('Laptop', 899.99)

Example 7: Updating with Multiple Conditions

This example updates the rating in the reviews table for a specific product with a low rating, using multiple conditions and verifying the change.

Updated 0 row(s) in 'reviews' table.
Updated reviews: [(1, 5)]

Python MySQL: Using LIMIT, HINT/INDEX, AUTO_INCREMENT, Regular Expressions, and Deleting Duplicate Records

This tutorial explains how to use LIMIT, index hints, AUTO_INCREMENT, regular expressions (REGEXP), and duplicate record deletion in MySQL queries with the mysql-connector-python library. These features enhance query precision, performance, and data management in applications like e-commerce or user systems. LIMIT restricts the number of rows returned, index hints optimize query execution, AUTO_INCREMENT generates unique IDs, REGEXP enables advanced pattern matching, and duplicate deletion ensures data integrity. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, products, and categories (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• LIMIT Clause:
  Restricts the number of rows returned by a query, useful for pagination or top-N results.
  Example: SELECT * FROM products LIMIT 2
• HINT/INDEX:
  Suggests or forces a specific index for query execution to optimize performance.
  Example: SELECT * FROM users USE INDEX (idx_name) WHERE name = 'Diana'
• AUTO_INCREMENT:
  Automatically generates unique, sequential IDs for a column, typically a primary key.
  Example: CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
• Regular Expressions (REGEXP):
  Matches patterns in strings using regular expressions, more powerful than LIKE.
  Example: SELECT * FROM categories WHERE name REGEXP '^Tech.*'
• Deleting Duplicate Records:
  Removes duplicate rows based on specific columns, preserving one instance.
  Example: Delete duplicates from users based on email.
• Good Practice:
  Use parameterized queries, commit changes, close cursors and connections, verify results with SELECT, and ensure indexes exist before using hints.

Example 1: Using LIMIT to Restrict Rows

This example retrieves the first two products from the products table, ordered by price, using LIMIT for pagination.

Top 2 cheapest products:
('Phone', 699.99)
('Laptop', 899.99)

Example 2: Using Index Hint for Query Optimization

This example uses an index hint (USE INDEX) to suggest the idx_name index for querying users by name, assuming the index exists.

User with name 'Diana Smith': (4, 'Diana Smith')

Example 3: Creating a Table with AUTO_INCREMENT

This example creates a new table with an AUTO_INCREMENT primary key and inserts a record to demonstrate automatic ID generation.

Inserted 1 row(s) into 'employees' table with auto-incremented ID.
Inserted employee: (1, 'John Doe')

Example 4: Using REGEXP for Pattern Matching

This example uses REGEXP to select categories starting with "Tech" followed by any characters, demonstrating advanced pattern matching.

Categories matching 'Tech.*':
(1, 'Tech')

Example 5: Deleting Duplicate Records

This example deletes duplicate users based on the email column, keeping the record with the lowest ID, assuming duplicates exist.

Deleted 1 duplicate row(s) from 'users' table.

Example 6: Combining LIMIT and REGEXP

This example combines LIMIT and REGEXP to retrieve the first two products with names matching a pattern (e.g., containing "ap").

Top 2 products with 'ap' in name, sorted by price:
('Laptop', 899.99)

Example 7: Handling Errors with Invalid Index Hint

This example attempts to use a non-existent index hint, demonstrating error handling for invalid query optimization.

Error: 1176: Key 'nonexistent_index' doesn't exist in table 'users'

Python MySQL: Using COMMIT and ROLLBACK for Transaction Management

This tutorial explains how to manage transactions in MySQL using the mysql-connector-python library with COMMIT and ROLLBACK. Transactions ensure data integrity by grouping operations into atomic units that either all succeed (COMMIT) or are undone (ROLLBACK) if an error occurs. This is critical for applications like e-commerce or user management, where operations (e.g., updating inventory and orders) must be consistent. Examples cover committing changes, rolling back on errors, and verifying transaction outcomes. They assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, products, and orders (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks are styled to span 100% width of the content area for seamless integration into your webpage.

• COMMIT:
  Saves all changes in a transaction to the database, making them permanent.
  Example: connection.commit() after an INSERT or UPDATE.
• ROLLBACK:
  Undoes all changes in a transaction if an error occurs, restoring the database to its previous state.
  Example: connection.rollback() in an exception block.
• Transaction Management:
  Use connection.autocommit = False to disable automatic commits, allowing manual control with COMMIT and ROLLBACK.
  Example: Begin a transaction, execute queries, then commit or rollback based on success.
• Good Practice:
  Disable autocommit for transactions, use try-except to handle errors, commit only on success, rollback on failure, close cursors and connections, and verify changes with SELECT.

Example 1: Basic COMMIT After INSERT

This example inserts a new user and commits the transaction to save the change permanently.

Inserted 1 row(s) and committed.

Example 2: ROLLBACK on Error

This example attempts to insert a duplicate user email (violating a UNIQUE constraint) and rolls back the transaction on error.

Error: 1062: Duplicate entry 'diana@example.com' for key 'users.email'
Transaction rolled back.

Example 3: Partial Transaction with COMMIT and ROLLBACK

This example performs multiple operations (insert and update) in a transaction, committing only if both succeed, otherwise rolling back.

Inserted and updated 1 row(s) and committed.

Example 4: Nested Transactions (Simulated)

This example simulates nested transactions by performing sequential operations, rolling back only the second operation on error while committing the first.

First transaction committed.
Second transaction error: 1062: Duplicate entry 'diana@example.com' for key 'users.email'
Second transaction rolled back.

Example 5: Verifying Transaction Outcome

This example inserts an order, commits the transaction, and verifies the insertion with a SELECT query.

Inserted 1 row(s) into 'orders' table.
Inserted order: (4, 'Tablet')

Example 6: Handling Connection Errors

This example simulates a connection error during a transaction and ensures rollback to maintain data integrity.

Error: 2006: MySQL server has gone away
Rollback failed due to connection error.

Example 7: Complex Transaction with Multiple Operations

This example performs a transaction involving inserting an order and updating product stock, committing only if both operations succeed.

Inserted order and updated stock for 1 row(s).
Orders for user_id 4: [(4, 'Tablet'), (4, 'Monitor')]

Python MySQL: Using Joins to Combine Tables

This tutorial explains how to use MySQL joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN) with the mysql-connector-python library to combine data from multiple tables. Joins are essential for querying related data in applications like e-commerce or user management, such as linking users to their orders or products to categories. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, orders, products, and categories (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks and outputs are styled to span 100% width of the content area for seamless integration into your webpage.

• INNER JOIN:
  Returns only matching records from both tables based on the join condition.
  Example: SELECT users.name, orders.product FROM users INNER JOIN orders ON users.id = orders.user_id
• LEFT JOIN:
  Returns all records from the left table and matching records from the right table (NULL for non-matches).
  Example: SELECT users.name, orders.product FROM users LEFT JOIN orders ON users.id = orders.user_id
• RIGHT JOIN:
  Returns all records from the right table and matching records from the left table (NULL for non-matches).
  Example: SELECT users.name, orders.product FROM users RIGHT JOIN orders ON users.id = orders.user_id
• FULL JOIN:
  Returns all records from both tables, with NULLs for non-matches (MySQL simulates via UNION).
  Example: Combine LEFT JOIN and RIGHT JOIN with UNION.
• Good Practice:
  Use parameterized queries, specify columns explicitly, use table aliases, verify join conditions, and handle NULLs appropriately.

Example 1: INNER JOIN

This example uses INNER JOIN to retrieve users and their orders, returning only matching records.

Users and their orders (INNER JOIN):
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')

Example 2: LEFT JOIN

This example uses LEFT JOIN to retrieve all users and their orders, including users without orders (NULL for non-matches).

All users and their orders (LEFT JOIN):
('Eve', None)
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')
('Grace', None)

Example 3: RIGHT JOIN

This example uses RIGHT JOIN to retrieve all orders and their associated users, including orders without users (NULL for non-matches).

All orders and their users (RIGHT JOIN):
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')

Example 4: FULL JOIN (Simulated)

This example simulates a FULL JOIN (not natively supported in MySQL) using LEFT JOIN and RIGHT JOIN with UNION to retrieve all users and orders, including non-matches.

All users and orders (FULL JOIN):
('Eve', None)
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')
('Grace', None)

Example 5: JOIN with Additional Conditions

This example uses INNER JOIN with a WHERE condition to retrieve products and categories where the product price is above 500.

Products over $500 with categories (INNER JOIN):
('Phone', 699.99, 'Tech')
('Laptop', 899.99, 'Tech')

Example 6: Multiple Joins

This example joins users, orders, and products to retrieve user names, ordered products, and product prices.

Users, their orders, and product prices (Multiple INNER JOINs):
('Diana Smith', 'Tablet', 499.99)
('Diana Smith', 'Monitor', 299.99)

Example 7: Handling No Matches with LEFT JOIN

This example uses LEFT JOIN to retrieve products and their categories, handling cases where products have no category (NULL).

Products and their categories (LEFT JOIN, handling no matches):
('Phone', 'Tech')
('Laptop', 'Tech')
('Tablet', 'Tech')
('Monitor', 'Tech')

Python MySQL: Advanced Topics (Good to Know)

This tutorial introduces seven advanced MySQL topics essential for building robust database-driven applications using the mysql-connector-python library: Aggregate Functions and Grouping, Subqueries and Nested Queries, Stored Procedures, Indexes and Query Optimization, Views, Triggers, and Handling Dates and Times. These topics extend your ability to summarize data, write complex queries, optimize performance, and automate database tasks, suitable for applications like e-commerce or user management. Each topic includes a key example to demonstrate its use. Examples assume a standard Python environment with a MySQL server running (e.g., on localhost) and a database named my_db with tables users, orders, products, and categories (created and populated in previous tutorials). Replace placeholder credentials (host, user, password) with your actual MySQL settings. Code blocks and outputs are styled to span 100% width of the content area for seamless integration into your webpage.

• Aggregate Functions and Grouping:
  Use functions like COUNT, SUM, AVG, MIN, MAX with GROUP BY to summarize data, and HAVING to filter groups. Ideal for reporting, e.g., total orders per user.
  Example: SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 0;
• Subqueries and Nested Queries:
  Use subqueries in SELECT, WHERE, or FROM clauses for complex filtering, e.g., finding users with high-value orders.
  Example: SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 500);
• Stored Procedures:
  Create reusable SQL procedures stored on the server, callable from Python, to encapsulate complex logic.
  Example: CALL add_user_order('John Doe', 'Laptop');
• Indexes and Query Optimization:
  Create indexes to speed up queries and use EXPLAIN to analyze query performance.
  Example: CREATE INDEX idx_user_id ON orders(user_id);
• Views:
  Create virtual tables with CREATE VIEW to simplify complex queries.
  Example: CREATE VIEW user_orders AS SELECT u.name, o.product FROM users u LEFT JOIN orders o ON u.id = o.user_id;
• Triggers:
  Automate actions with CREATE TRIGGER on table changes, e.g., updating stock after an order.
  Example: CREATE TRIGGER after_order_insert AFTER INSERT ON orders FOR EACH ROW UPDATE products SET stock = stock - 1 WHERE name = NEW.product;
• Handling Dates and Times:
  Use MySQL date functions like NOW(), DATE_FORMAT, DATEDIFF for time-based queries.
  Example: SELECT * FROM orders WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);
• Good Practice:
  Use parameterized queries, test procedures/triggers, optimize indexes, handle NULLs, and verify date formats.

Example 1: Aggregate Functions and Grouping

This example uses COUNT and GROUP BY with a LEFT JOIN to count orders per user, filtering groups with HAVING to show only users with orders.

Users with order counts (Aggregate):
('Diana Smith', 2)

Example 2: Subqueries and Nested Queries

This example uses a subquery in the WHERE clause to find users who have placed orders with a total greater than 500.

Users with orders over $500 (Subquery):
('Diana Smith',)

Example 3: Stored Procedures

This example creates a stored procedure to insert a user and an order, then calls it from Python. Assumes the procedure is created in MySQL: CREATE PROCEDURE add_user_order(IN uname VARCHAR(100), IN prod VARCHAR(100)) BEGIN INSERT INTO users(name) VALUES(uname); INSERT INTO orders(user_id, product) VALUES(LAST_INSERT_ID(), prod); END;

Stored procedure executed: Added user and order

Example 4: Indexes and Query Optimization

This example creates an index on the user_id column of the orders table to optimize queries.

Index created on orders.user_id

Example 5: Views

This example creates a view to simplify accessing user and order data, then queries it from Python.

User orders from view:
('Eve', None)
('Diana Smith', 'Tablet')
('Diana Smith', 'Monitor')
('Grace', None)

Example 6: Triggers

This example creates a trigger to update product stock after an order is inserted, then tests it by inserting an order.

Trigger created and order inserted

Example 7: Handling Dates and Times

This example retrieves orders from the last 30 days using DATE_SUB and NOW.

Orders from the last 30 days:
(4, 'Tablet', '2025-07-01 10:00:00')
(4, 'Monitor', '2025-07-10 14:30:00')