MySQL Connectors

Available Connectors

🔹 Python Connector

MySQL Connector/Python is the official MySQL driver for Python. Install it using pip and use it to connect, execute queries, and manage database operations. It supports both traditional and modern Python programming styles with full support for prepared statements and transactions.

# Install
# pip install mysql-connector-python

import mysql.connector

# Connect to database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="mydb"
)

cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM users WHERE age > %s", (18,))

# Fetch results
results = cursor.fetchall()
for row in results:
    print(row)

# Insert data
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("John Doe", "[email protected]")
cursor.execute(sql, values)
conn.commit()

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

🔹 Node.js Connector

The mysql2 package is a popular MySQL client for Node.js with promise support and improved performance. Install via npm and use async/await for clean, modern JavaScript code. It supports connection pooling, prepared statements, and both callback and promise-based APIs.

// Install
// npm install mysql2

const mysql = require('mysql2/promise');

// Create connection
async function connectDB() {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'your_password',
        database: 'mydb'
    });
    
    return connection;
}

// Query database
async function getUsers() {
    const conn = await connectDB();
    
    // Execute query
    const [rows] = await conn.execute(
        'SELECT * FROM users WHERE age > ?',
        [18]
    );
    
    console.log(rows);
    
    // Insert data
    await conn.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ['Jane Doe', '[email protected]']
    );
    
    await conn.end();
}

getUsers();

🔹 PHP Connector

PHP offers two main ways to connect to MySQL: MySQLi (MySQL Improved) and PDO (PHP Data Objects). Both support prepared statements for security against SQL injection. MySQLi is MySQL-specific while PDO works with multiple databases, making it more portable for multi-database applications.

<?php
// Using MySQLi
$conn = new mysqli("localhost", "root", "password", "mydb");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE age > ?");
$stmt->bind_param("i", $age);
$age = 18;
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "
";
}

// Insert data
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$name = "John Doe";
$email = "[email protected]";
$stmt->execute();

$stmt->close();
$conn->close();
?>

🔹 Java Connector (JDBC)

MySQL Connector/J is the official JDBC driver for MySQL. Add it to your project dependencies and use standard JDBC APIs to interact with MySQL. It provides connection pooling, prepared statements, and full transaction support for enterprise Java applications with robust error handling.

// Add to pom.xml (Maven)
// <dependency>
//   <groupId>mysql</groupId>
//   <artifactId>mysql-connector-java</artifactId>
//   <version>8.0.33</version>
// </dependency>

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "your_password";
        
        try {
            // Connect to database
            Connection conn = DriverManager.getConnection(url, user, password);
            
            // Prepared statement
            String sql = "SELECT * FROM users WHERE age > ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 18);
            
            // Execute query
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
            
            // Insert data
            String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";
            PreparedStatement insertStmt = conn.prepareStatement(insertSQL);
            insertStmt.setString(1, "John Doe");
            insertStmt.setString(2, "[email protected]");
            insertStmt.executeUpdate();
            
            // Close connections
            rs.close();
            stmt.close();
            conn.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

🔹 C# Connector (.NET)

MySQL Connector/NET enables .NET applications to connect to MySQL databases. Install via NuGet package manager and use familiar ADO.NET patterns. It integrates seamlessly with Entity Framework and supports async operations, connection pooling, and all modern .NET features for building robust applications.

// Install via NuGet
// Install-Package MySql.Data

using MySql.Data.MySqlClient;

class Program {
    static void Main() {
        string connStr = "server=localhost;user=root;database=mydb;password=your_password;";
        
        using (MySqlConnection conn = new MySqlConnection(connStr)) {
            conn.Open();
            
            // Query with parameters
            string sql = "SELECT * FROM users WHERE age > @age";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@age", 18);
            
            using (MySqlDataReader reader = cmd.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine(reader["name"]);
                }
            }
            
            // Insert data
            string insertSQL = "INSERT INTO users (name, email) VALUES (@name, @email)";
            MySqlCommand insertCmd = new MySqlCommand(insertSQL, conn);
            insertCmd.Parameters.AddWithValue("@name", "John Doe");
            insertCmd.Parameters.AddWithValue("@email", "[email protected]");
            insertCmd.ExecuteNonQuery();
        }
    }
}

🔹 Connection Best Practices

Follow these essential practices when using MySQL connectors: always use prepared statements to prevent SQL injection, implement connection pooling for better performance, handle errors gracefully, close connections properly, use transactions for data integrity, and never store credentials in code.

# Example: Connection pooling in Python
from mysql.connector import pooling

# Create connection pool
pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    user="root",
    password="password",
    database="mydb"
)

# Get connection from pool
conn = pool.get_connection()
cursor = conn.cursor()

# Use connection
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

# Return connection to pool
cursor.close()
conn.close()