Esercizi Lettura MySQL Python

Approfondisci la tua capacità di recuperare dati da MySQL con questi esercizi che utilizzano due delle librerie Python più comuni.

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

Recuperare un singolo record da una tabella utilizzando mysql-connector-python.

import mysql.connector

def fetch_single_data():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "SELECT name, position FROM employees WHERE employee_id = 1"
    cursor.execute(query)
    result = cursor.fetchone()
    print("Dati recuperati:", result)
    cursor.close()
    connection.close()

fetch_single_data()

Esercizio 2: Lettura Multipla con PyMySQL (OOP)

Recuperare più record contemporaneamente da 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 fetch_multiple_data(self):
        with self.connection.cursor() as cursor:
            query = "SELECT name, position FROM employees"
            cursor.execute(query)
            results = cursor.fetchall()
            for row in results:
                print("Dipendente:", row)

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

db = Database()
db.fetch_multiple_data()
db.close()

Esercizio 3: Lettura Condizionata con PyMySQL (Sequenziale)

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

import pymysql

def fetch_conditional_records():
    connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "SELECT name, position FROM employees WHERE position = 'Data Scientist'"
    cursor.execute(query)
    results = cursor.fetchall()
    for row in results:
        print("Dipendente:", row)
    cursor.close()
    connection.close()

fetch_conditional_records()

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

Utilizzare mysql-connector-python per recuperare tutti i record da 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 fetch_all_employees(self):
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM employees")
        results = cursor.fetchall()
        for row in results:
            print("Dipendente:", row)
        cursor.close()

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

db = EmployeeDatabase()
db.fetch_all_employees()
db.close()

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

Recuperare una specifica colonna da una tabella utilizzando mysql-connector-python.

import mysql.connector

def fetch_specific_column():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "SELECT position FROM employees"
    cursor.execute(query)
    positions = cursor.fetchall()
    print("Posizioni disponibili:")
    for position in positions:
        print(position)
    cursor.close()
    connection.close()

fetch_specific_column()

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

Utilizzare mysql-connector-python per recuperare i record da più tabelle 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 fetch_products_and_suppliers(self):
        cursor = self.connection.cursor()
        query = """
        SELECT p.name, s.name
        FROM products p
        JOIN suppliers s ON p.supplier_id = s.supplier_id
        """
        cursor.execute(query)
        results = cursor.fetchall()
        for row in results:
            print("Prodotto e fornitore:", row)
        cursor.close()

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

db = ProductDatabase()
db.fetch_products_and_suppliers()
db.close()

Esercizio 7: Lettura Aggregata con PyMySQL (Sequenziale)

Scrivere uno script per eseguire una query aggregata (es. COUNT, MAX) usando PyMySQL.

import pymysql

def fetch_aggregate_data():
    connection = pymysql.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "SELECT COUNT(*) FROM employees WHERE position = 'Developer'"
    cursor.execute(query)
    count = cursor.fetchone()
    print("Numero di sviluppatori:", count[0])
    cursor.close()
    connection.close()

fetch_aggregate_data()

Esercizio 8: Lettura Singola con PyMySQL (OOP)

Creare una classe per recuperare un singolo record da una tabella usando PyMySQL.

import pymysql

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

    def fetch_user(self, user_id):
        with self.connection.cursor() as cursor:
            cursor.execute("SELECT name, email FROM users WHERE user_id = %s", (user_id,))
            result = cursor.fetchone()
            if result:
                print("Utente trovato:", result)
            else:
                print("Nessun utente trovato con l'ID specificato.")

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

db = UserDatabase()
db.fetch_user(1)
db.close()

Esercizio 9: Lettura Dettagliata con mysql-connector-python (Sequenziale)

Recuperare dettagli da una tabella filtrando i risultati in base a una condizione specifica usando mysql-connector-python.

import mysql.connector

def fetch_detailed_records():
    connection = mysql.connector.connect(host='localhost', user='user', password='password', database='testdb')
    cursor = connection.cursor()
    query = "SELECT name, hire_date FROM employees WHERE year(hire_date) > 2010"
    cursor.execute(query)
    results = cursor.fetchall()
    for result in results:
        print("Dipendente:", result)
    cursor.close()
    connection.close()

fetch_detailed_records()

Esercizio 10: Lettura Completa con PyMySQL (OOP)

Creare una classe che recupera tutte le informazioni da una tabella specifica utilizzando PyMySQL.

import pymysql

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

    def fetch_all(self, table_name):


 with self.connection.cursor() as cursor:
            query = f"SELECT * FROM {table_name}"
            cursor.execute(query)
            results = cursor.fetchall()
            for row in results:
                print("Record:", row)

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

db = CompleteFetchDatabase()
db.fetch_all('employees')
db.close()