Esercizi Modifica MySQL Python

Codegrind Team•Jul 10 2024

Esercizi sulla modifica dei dati in un database MySQL utilizzando le librerie Python mysql-connector-python e pymysql. Gli esercizi includono sia approcci sequenziali che orientati agli oggetti (OOP).

Esercizio 1: Modifica Singola con mysql-connector-python (Sequenziale)

Modificare un singolo record in una tabella utilizzando mysql-connector-python.

import mysql.connector

def update_single_record():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "UPDATE employees SET position = %s WHERE employee_id = %s"
    data = ("Senior Developer", 1)
    cursor.execute(query, data)
    connection.commit()
    print("Record aggiornato con successo")
    cursor.close()
    connection.close()

update_single_record()

Esercizio 2: Modifica Multipla con PyMySQL (OOP)

Modificare più record contemporaneamente in una tabella utilizzando PyMySQL e OOP.

import pymysql

class Database:
    def __init__(self):
        self.connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')

    def update_multiple_records(self):
        with self.connection.cursor() as cursor:
            query = "UPDATE employees SET position = %s WHERE employee_id = %s"
            data = [
                ("Manager", 2),
                ("Director", 3)
            ]
            cursor.executemany(query, data)
            self.connection.commit()
            print(f"{cursor.rowcount} record aggiornati con successo")

    def close(self):
        self.connection.close()

db = Database()
db.update_multiple_records()
db.close()

Esercizio 3: Modifica Condizionata con PyMySQL (Sequenziale)

Scrivere uno script per modificare record con condizioni specifiche usando PyMySQL.

import pymysql

def update_conditional_records():
    connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "UPDATE employees SET salary = salary + 5000 WHERE position = 'Developer'"
    cursor.execute(query)
    connection.commit()
    print(f"{cursor.rowcount} record aggiornati con successo")
    cursor.close()
    connection.close()

update_conditional_records()

Esercizio 4: Modifica Tutto con mysql-connector-python (OOP)

Utilizzare mysql-connector-python per modificare tutti i record in una tabella in modalità OOP.

import mysql.connector

class EmployeeDatabase:
    def __init__(self):
        self.connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')

    def update_all_employees(self):
        cursor = self.connection.cursor()
        cursor.execute("UPDATE employees SET active = %s", (0,))
        self.connection.commit()
        print(f"{cursor.rowcount} record aggiornati con successo")
        cursor.close()

    def close(self):
        self.connection.close()

db = EmployeeDatabase()
db.update_all_employees()
db.close()

Esercizio 5: Modifica Specifica con mysql-connector-python (Sequenziale)

Modificare una specifica colonna di un record utilizzando mysql-connector-python.

import mysql.connector

def update_specific_column():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "UPDATE employees SET position = %s WHERE name = %s"
    data = ("Team Lead", "John Doe")
    cursor.execute(query, data)
    connection.commit()
    print("Record aggiornato con successo")
    cursor.close()
    connection.close()

update_specific_column()

Esercizio 6: Modifica Multipla con mysql-connector-python (OOP)

Utilizzare mysql-connector-python per modificare più record in modalità OOP.

import mysql.connector

class ProductDatabase:
    def __init__(self):
        self.connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')

    def update_product_prices(self):
        products = [
            (19.99, "Coffee"),
            (25.00, "Tea")
        ]
        cursor = self.connection.cursor()
        cursor.executemany("UPDATE products SET price = %s WHERE name = %s", products)
        self.connection.commit()
        print(f"{cursor.rowcount} prezzi aggiornati con successo")
        cursor.close()

    def close(self):
        self.connection.close()

db = ProductDatabase()
db.update_product_prices()
db.close()

Esercizio 7: Modifica Condizionata con PyMySQL (OOP)

Creare una classe per modificare record condizionati con PyMySQL.

import pymysql

class DepartmentDatabase:
    def __init__(self):
        self.connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')

    def update_department_name(self, old_name, new_name):
        with self.connection.cursor() as cursor:
            query = "UPDATE departments SET name = %s WHERE name = %s"
            cursor.execute(query, (new_name, old_name))
            self.connection.commit()
            print(f"{cursor.rowcount} record aggiornati con successo")

    def close(self):
        self.connection.close()

db = DepartmentDatabase()
db.update_department_name("Sales", "Global Sales")
db.close()

Esercizio 8: Modifica Completa con mysql-connector-python (Sequenziale)

Modificare tutti i record di una tabella utilizzando mysql-connector-python.

import mysql.connector

def update_all_records():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    cursor.execute("UPDATE employees SET bonus = bonus * 1.1")
    connection.commit()
    print(f"{cursor.rowcount} record aggiornati con successo")
    cursor.close()
    connection.close()

update_all_records()

Esercizio 9: Modifica Singola con PyMySQL (OOP)

Creare una classe per modificare un singolo record in una tabella usando PyMySQL.

import pymysql

class UserDatabase:
    def __init__(self):
        self.connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')

    def update_user_email(self, user_id, new_email):
        with self.connection.cursor() as cursor:
            query = "UPDATE users SET email = %s WHERE user_id = %s"
            cursor.execute(query, (new_email, user_id))
            self.connection.commit()
            print("Email aggiornata con successo")

    def close(self):
        self.connection.close()

db = UserDatabase()
db.update_user_email(1, "newemail@example.com")
db.close()

Esercizio 10: Modifica Complessa con mysql-connector-python (OOP)

Utilizzare mysql-connector-python per modificare record in più tabelle in modalità OOP.

import mysql.connector

class MultiTableDatabase:
    def __init__(self):
        self.connection = mysql.connector.connect(host='localhost', user='user', password='password', database='

testdb')

    def update_employee_and_department(self, employee_id, new_position, department_id, new_department_name):
        cursor = self.connection.cursor()
        try:
            cursor.execute("UPDATE employees SET position = %s WHERE employee_id = %s", (new_position, employee_id))
            cursor.execute("UPDATE departments SET name = %s WHERE department_id = %s", (new_department_name, department_id))
            self.connection.commit()
            print("Record aggiornati con successo")
        except mysql.connector.Error as err:
            print(f"Errore: {err}")
            self.connection.rollback()
        finally:
            cursor.close()

    def close(self):
        self.connection.close()

db = MultiTableDatabase()
db.update_employee_and_department(1, "Senior Manager", 2, "Marketing and Sales")
db.close()