Connecting to Mysql using Python

by | Jul 5, 2024 | All, Dev | 0 comments

MySQL is a widely-used relational database management system that is especially popular for web applications. Python, being a versatile programming language, offers several libraries to interact with MySQL databases. One of the most commonly used libraries is mysql.connector, which allows you to connect to a MySQL database, execute queries, and handle database operations.

In this blog post, we’ll walk you through the steps to connect to a MySQL database using Python and demonstrate how to modify table columns with SQL commands.

 

Prerequisites

Before we begin, make sure you have the following:

  • Python installed on your machine
  • MySQL server installed and running
  • mysql-connector-python library installed (you can install it using pip install mysql-connector-python)

Step-by-Step Guide

  1. Import the MySQL Connector Library

    import mysql.connector

  2. Establish a Connection to the MySQL Database

    Use the connect method to establish a connection to your MySQL database. You need to provide the host, user, password, and database name.

    conn = mysql.connector.connect(

    host="localhost",

    user="root",

    password="password",

    database="your_database"

    )

    cursor = conn.cursor()

  3. Define the SQL Commands to Modify Table Columns

    Create a list of SQL commands that you want to execute. In this example, we’re modifying columns in different tables.

    alter_commands = [

    "ALTER TABLE employees MODIFY COLUMN employee_id INT;",

    "ALTER TABLE employees MODIFY COLUMN join_date DATE;",

    "ALTER TABLE sales MODIFY COLUMN sale_amount DECIMAL(10,2);",

    "ALTER TABLE products MODIFY COLUMN product_info JSON;"

    ]

  4. Execute Each Command

    Loop through the list of commands and execute each one using the cursor object.

    for command in alter_commands:

    cursor.execute(command)

  5. Commit Changes and Close the Connection

    After executing the commands, commit the changes to the database and close the cursor and connection.

    conn.commit()

    cursor.close()

    conn.close()

Full Code Example

Here’s the complete code:

import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
host=“localhost”,
user=“root”,
password=“password”,
database=“your_database”
)
cursor = conn.cursor()

# List of tables and columns to modify
alter_commands = [
“ALTER TABLE employees MODIFY COLUMN employee_id INT;”,
“ALTER TABLE employees MODIFY COLUMN join_date DATE;”,
“ALTER TABLE sales MODIFY COLUMN sale_amount DECIMAL(10,2);”,
“ALTER TABLE products MODIFY COLUMN product_info JSON;”
]

# Execute each command
for command in alter_commands:
cursor.execute(command)

# Commit changes and close connection
conn.commit()
cursor.close()
conn.close()

Explanation

  • Connecting to MySQL: The mysql.connector.connect method establishes a connection to the MySQL server.
  • Cursor Object: The cursor object is used to execute SQL commands.
  • Executing Commands: The cursor.execute method runs each SQL command in the alter_commands list.
  • Committing Changes: conn.commit saves all changes made to the database during the session.
  • Closing Connections: It’s crucial to close the cursor and connection using cursor.close and conn.close to free up resources.

This basic example demonstrates how to connect to a MySQL database using Python and execute multiple SQL commands. You can expand upon this foundation by adding error handling, logging, and more complex queries as needed for your specific application.

Submit a Comment