Connecting Python to MySQL: A Step-by-Step Developer’s Tutorial
MySQL remains one of the most widely used relational database management systems in the world, while Python continues to dominate as a preferred language for web development, data analysis, and automation. Connecting the two allows you to build robust, data-driven applications.
This tutorial provides a clear, step-by-step guide to connecting Python to a MySQL database, executing queries, and managing data safely. Prerequisites
Before beginning, ensure you have the following installed on your system: Python 3.x MySQL Server (running locally or hosted remotely) pip (Python package installer) Step 1: Install the MySQL Connector
Python requires a driver to communicate with MySQL. The official, Oracle-supported driver is mysql-connector-python. Open your terminal or command prompt and run: pip install mysql-connector-python Use code with caution. Step 2: Establish a Database Connection
To connect to your database, import the connector module and pass your database credentials—such as host, user, password, and database name—to the connect() method.
import mysql.connector from mysql.connector import Error try: # Establish the connection connection = mysql.connector.connect( host=‘localhost’, user=‘your_username’, password=‘your_password’, database=‘your_database_name’ ) if connection.is_connected(): db_info = connection.get_server_info() print(f”Successfully connected to MySQL Server version {db_info}“) except Error as e: print(f”Error while connecting to MySQL: {e}“) finally: # Ensure the connection closes when done if ‘connection’ in locals() and connection.is_connected(): connection.close() print(“MySQL connection is closed.”) Use code with caution. Step 3: Create a Table
Once connected, you interact with the database using a cursor object. The cursor allows you to execute SQL statements. Here is how to create a simple users table:
# (Inside your try block, after establishing connection) cursor = connection.cursor() create_table_query = “”” CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); “”” cursor.execute(create_table_query) print(“Table ‘users’ created successfully.”) cursor.close() Use code with caution. Step 4: Insert Data Safely (Preventing SQL Injection)
When inserting data provided by users, never use Python string formatting (like f-strings) to build your SQL queries. This exposes your application to SQL injection attacks. Instead, use placeholders (%s) and pass the data as a tuple.
cursor = connection.cursor() insert_query = “”” INSERT INTO users (name, email) VALUES (%s, %s); “”” user_data = (“John Doe”, “[email protected]”) cursor.execute(insert_query, user_data) # Commit changes to the database connection.commit() print(f”{cursor.rowcount} row(s) inserted successfully. Last Insert ID: {cursor.lastrowid}“) cursor.close() Use code with caution.
Note: MySQL requires you to explicitly call connection.commit() to save write operations (INSERT, UPDATE, DELETE) to the database. Step 5: Fetch and Read Data
To read data from the database, execute a SELECT query and use methods like fetchall() or fetchone() to retrieve the results.
cursor = connection.cursor() select_query = “SELECT id, name, email FROM users;” cursor.execute(select_query) # Fetch all rows from the executed query rows = cursor.fetchall() print(” — User Records —“) for row in rows: print(f”ID: {row[0]} | Name: {row[1]} | Email: {row[2]}“) cursor.close() Use code with caution. Step 6: Update and Delete Data
Updating and deleting records follows the same pattern as inserting data: use placeholders and commit the transaction. Updating Records
cursor = connection.cursor() update_query = “UPDATE users SET email = %s WHERE name = %s;” new_data = (“[email protected]”, “John Doe”) cursor.execute(update_query, new_data) connection.commit() print(f”{cursor.rowcount} row(s) updated.“) cursor.close() Use code with caution. Deleting Records
cursor = connection.cursor() delete_query = “DELETE FROM users WHERE name = %s;” target_user = (“John Doe”,) cursor.execute(delete_query, target_user) connection.commit() print(f”{cursor.rowcount} row(s) deleted.“) cursor.close() Use code with caution. Best Practices for Production
Use Context Managers (with statements): Context managers automatically handle closing cursors and connections, even if an error occurs.
Environment Variables: Never hardcode database passwords in your scripts. Use a library like python-dotenv to load credentials from a secure .env file.
Connection Pooling: For web applications (like Flask or Django), opening and closing connections for every user request is inefficient. Use mysql.connector.pooling to manage a pool of reusable connections. Conclusion
You now have a foundational workflow for integrating Python with MySQL. By mastering connecting, executing parameterized queries, and managing connections properly, you can confidently build secure and scalable database applications. To help refine this code for your project, let me know:
Leave a Reply