MySQL Get Started in Python

You can connect to and work with a MySQL database in Python using the mysql-connector-python library.

1. Install MySQL Connector

First, install the MySQL connector if you haven’t already:

pip install mysql-connector-python

2. Connect to MySQL Server

Before performing any operations, establish a connection to the MySQL server.

import mysql.connector# Connect to MySQL serverconn = mysql.connector.connect(    host="localhost",   # Change this to your MySQL server    user="root",        # Your MySQL username    password="password" # Your MySQL password)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL successfully!")

3. Create a Database

Once connected, create a new database.

cursor.execute("CREATE DATABASE mydatabase")print("Database 'mydatabase' created successfully!")

To check all databases:

cursor.execute("SHOW DATABASES")for db in cursor:    print(db)

4. Connect to a Specific Database

Now, connect to the newly created mydatabase.

conn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")print("Connected to 'mydatabase' successfully!")

5. Create a Table

Inside the database, create a table.

cursor = conn.cursor()create_table_query = """CREATE TABLE employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))"""cursor.execute(create_table_query)print("Table 'employees' created successfully!")

To check all tables:

cursor.execute("SHOW TABLES")for table in cursor:    print(table)

6. Insert Data into the Table

Add data to the employees table.

insert_query = "INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)"values = ("Alice", 30, "HR")cursor.execute(insert_query, values)conn.commit()  # Save changesprint("Employee record inserted successfully!")

7. Fetch Data from the Table

Retrieve data from the database.

cursor.execute("SELECT * FROM employees")# Fetch all recordsrecords = cursor.fetchall()for row in records:    print(row)

8. Update Data in the Table

Modify a record in the table.

update_query = "UPDATE employees SET department = %s WHERE name = %s"values = ("Finance", "Alice")cursor.execute(update_query, values)conn.commit()print("Record updated successfully!")

9. Delete Data from the Table

Remove a record from the table.

delete_query = "DELETE FROM employees WHERE name = %s"values = ("Alice",)cursor.execute(delete_query, values)conn.commit()print("Record deleted successfully!")

10. Close the Connection

After performing all operations, close the connection.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create Tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    age INT,    department VARCHAR(50))""")# Insert Datainsert_query = "INSERT INTO employees (name, age, department) VALUES (%s, %s, %s)"values = ("Alice", 30, "HR")cursor.execute(insert_query, values)conn.commit()# Fetch Datacursor.execute("SELECT * FROM employees")records = cursor.fetchall()print("Employees List:")for row in records:    print(row)# Close Connectioncursor.close()conn.close()

Summary

This is how you get started with MySQL in Python! ?