YouTip

MySQL is one of the most popular open-source relational databases, and Python is one of the most popular programming languages today. Combining Python with MySQL allows us to easily develop database-driven applications. This article will detail how to connect to and operate MySQL databases using Python, covering the following topics: 1. How to install MySQL Python drivers 2. Establishing and closing database connections 3. Executing various SQL queries 4. Transaction management and error handling 5. Best practices for database operations * * * ## Preparation ### Installing Required Software Before starting, please ensure you have installed the following software: * **Python** (version 3.6 or higher recommended) * **MySQL Server** (Community Edition is sufficient) * **MySQL Connector/Python** (Python MySQL driver) ### Installing MySQL Connector/Python You can install the official MySQL Python driver via pip: pip install mysql-connector-python Or install PyMySQL (another popular MySQL Python driver): pip install pymysql * * * ## Connecting to MySQL Database ### Establishing a Basic Connection Here is the basic code for establishing a database connection using `mysql-connector-python`: ## Example import mysql.connector # Create database connection db = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) print("Database connection successful!") ### Connection Parameter Explanation * `host`: MySQL server address (use "localhost" for local) * `user`: Database username * `password`: User password * `database`: Name of the database to connect to (optional) ### Connecting with PyMySQL If you choose to use PyMySQL, the connection method is slightly different: ## Example import pymysql # Create database connection db = pymysql.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) print("Database connection successful!") * * * ## Executing SQL Queries ### Creating a Cursor Object Before executing SQL statements, we need to create a cursor object: ## Example cursor = db.cursor() ### Executing SELECT Queries ## Example cursor.execute("SELECT * FROM your_table") # Get all results results = cursor.fetchall() for row in results: print(row) ### Executing INSERT, UPDATE, DELETE Operations ## Example # Insert data sql ="INSERT INTO users (name, age) VALUES (%s, %s)" values =("Zhang San",25) cursor.execute(sql, values) # Commit transaction db.commit() print(cursor.rowcount,"records inserted successfully") ### Using Parameterized Queries To prevent SQL injection, you should always use parameterized queries: ## Example sql ="SELECT * FROM users WHERE name = %s" name =("Zhang San",) cursor.execute(sql, name) * * * ## Transaction Management ### Basic Concepts of Transactions MySQL transactions are a set of atomic SQL queries that either all execute successfully or none at all. ### Using Transactions ## Example try: # Start transaction cursor.execute("START TRANSACTION") # Execute multiple SQL statements cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1") cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2") # Commit transaction db.commit() print("Transaction executed successfully") except Exception as e: # Error occurred, rollback transaction db.rollback() print("Transaction execution failed:", e) * * * ## Error Handling ### Catching Database Errors ## Example try: cursor.execute("SELECT * FROM non_existent_table") except mysql.connector.Error as err: print("Database error:", err) ### Common Error Codes * `1045`: Access denied (wrong username or password) * `1049`: Unknown database * `1146`: Table doesn't exist * `1062`: Duplicate key value * * * ## Closing Connections ### Properly Closing Connections After completing database operations, you should close the cursor and connection: ## Example cursor.close() db.close() print("Database connection closed") ### Using with Statement Python's `with` statement can automatically manage resources: ## Example with mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" )as db: with db.cursor()as cursor: cursor.execute("SELECT * FROM users") results = cursor.fetchall() for row in results: print(row) # Connection automatically closes after leaving with block * * * ## Best Practices ### Connection Pooling For applications that frequently connect to databases, it is recommended to use connection pooling: ## Example from mysql.connector import pooling # Create connection pool db_pool = pooling.MySQLConnectionPool( pool_name="mypool", pool_size=5, host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) # Get connection from pool db = db_pool.get_connection() ### ORM Frameworks For complex applications, consider using ORM (Object-Relational Mapping) frameworks such as SQLAlchemy or Django ORM. * * *