Snippet Code Writer For Database Crud Op

Here’s a code snippet demonstrating basic CRUD operations on a MySQL database in Python, using the `mysql-connector-python` library. This code adheres to best practices by: 1. Handling exceptions. 2. Using prepared statements for security (prevents SQL injection). 3. Organizing CRUD operations into separate, reusable functions. 4. Managing transactions properly. ### Setup To use this code, install `mysql-connector-python` if you haven't already: ```bash pip install mysql-connector-python ``` ### Python Code ```python import mysql.connector from mysql.connector import Error # Database connection parameters DB_CONFIG = { 'host': 'localhost', 'user': 'your_username', 'password': 'your_password', 'database': 'your_database' } # Function to create a database connection def create_connection(): try: conn = mysql.connector.connect(**DB_CONFIG) if conn.is_connected(): print("Connected to MySQL database") return conn except Error as e: print(f"Error: {e}") return None # Close database connection def close_connection(conn): if conn and conn.is_connected(): conn.close() print("Connection closed.") # Create Operation def create_record(name, age, email): conn = create_connection() if conn: try: cursor = conn.cursor() query = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)" cursor.execute(query, (name, age, email)) conn.commit() print("Record created successfully.") except Error as e: print(f"Failed to create record: {e}") finally: cursor.close() close_connection(conn) # Read Operation def read_records(): conn = create_connection() if conn: try: cursor = conn.cursor(dictionary=True) query = "SELECT * FROM users" cursor.execute(query) records = cursor.fetchall() print("Records retrieved successfully.") for record in records: print(record) return records except Error as e: print(f"Failed to read records: {e}") return None finally: cursor.close() close_connection(conn) # Update Operation def update_record(user_id, name=None, age=None, email=None): conn = create_connection() if conn: try: cursor = conn.cursor() fields = [] values = [] # Dynamically construct the update query based on provided fields if name: fields.append("name = %s") values.append(name) if age: fields.append("age = %s") values.append(age) if email: fields.append("email = %s") values.append(email) values.append(user_id) query = f"UPDATE users SET {', '.join(fields)} WHERE id = %s" cursor.execute(query, tuple(values)) conn.commit() print("Record updated successfully.") except Error as e: print(f"Failed to update record: {e}") finally: cursor.close() close_connection(conn) # Delete Operation def delete_record(user_id): conn = create_connection() if conn: try: cursor = conn.cursor() query = "DELETE FROM users WHERE id = %s" cursor.execute(query, (user_id,)) conn.commit() print("Record deleted successfully.") except Error as e: print(f"Failed to delete record: {e}") finally: cursor.close() close_connection(conn) # Usage examples (uncomment to test) # create_record("John Doe", 30, "john@example.com") # read_records() # update_record(1, name="Jane Doe", age=29) # delete_record(1) ``` ### Explanation of Each Function 1. **create_connection()**: Establishes a connection to the MySQL database using provided credentials. Handles connection errors. 2. **close_connection(conn)**: Closes the provided database connection. 3. **create_record(name, age, email)**: Inserts a new record into the `users` table. Uses a prepared statement to avoid SQL injection. 4. **read_records()**: Retrieves all records from the `users` table. Uses a dictionary cursor for easy field access and formats output as a list of dictionaries. 5. **update_record(user_id, name, age, email)**: Updates specified fields of a user’s record by constructing the query dynamically. Only provided fields are updated. 6. **delete_record(user_id)**: Deletes a record with the specified `user_id`. ### Error Handling Each function is wrapped in `try-except` blocks to handle potential errors, printing clear error messages and ensuring connections are closed after operations complete. ### Transactions Each operation involving data modification (`INSERT`, `UPDATE`, `DELETE`) uses `conn.commit()` to finalize changes.