A parameterized query keeps values separate from SQL structure by using placeholders such as %s. This reduces SQL injection risk and supports safer database operations. section 9.2
Use parameterized queries as part of a careful database workflow:
- Write the SQL with placeholders rather than concatenating user-provided values into the query string. section 9.2
- Pass the values separately when executing the statement, as in cursor.execute(sql_query, (name, age)). section 9.2
- Review AI-generated SQL for string concatenation, missing filters, inefficient queries, and absent error handling. section 5.3.6
Parameterized insert in Python
sql_query = "INSERT INTO users (name, age) VALUES (%s, %s)" cursor.execute(sql_query, (name, age)) connection.commit() The SQL structure remains fixed while the name and age values are supplied separately. The surrounding operation catches database errors and commits the insert. section 9.2
#### Create (Insert) ```python # Insert a new record into the table def insert_record(connection, name, age): try: cursor = connection.cursor() sql_query = """INSERT INTO users (name, age) VALUES (%s, %s)""" cursor.execute(sql_query, (name, age)) connection.commit() print(f"Record inserted successfully into users table") except mysql.connector.Error as error: print(f"Failed to insert record: {error}") # Usage insert_record(connection, "John Doe", 30) ``` #### Read (Select) ```python # Fetch all records from the table def fetch_records(connection): try: cursor = connection.cursor() cursor.execute("SELECT * FROM users") records = cursor.fetchall() for row in records: print(f"Id: {row[0]}, Name: {row[1]}, Age: {row[2]}") except mysql.connector.Error as error: print(f"Failed to fetch records: {error}") # Usage fetch_records(connection) ``` #### Update ```python # Update a record in the table def update_record(connection, user_id, new_age): try: cursor = connection.cursor() sql_query = """UPDATE users SET age = %s WHERE id = %s""" cursor.execute(sql_query, (new_age, user_id)) connection.commit() print(f"Record with id {user_id} updated successfully") except mysql.connector.Error as error: print(f"Failed to update record: {error}") # Usage update_record(connection, 1, 35) ``` #### Delete ```python # Delete a record from the table def delete_record(connection, user_id): try: cursor = connection.cursor() sql_query = """DELETE FROM users WHERE id = %s""" cursor.execute(sql_query, (user_id,)) connection.commit() print(f"Record with id {user_id} deleted successfully") except mysql.connector.Error as error: print(f"Failed to delete record: {error}") # Usage delete_record(connection, 1) ```
Inspect a complete CRUD listing showing parameterized insertion alongside database cursor use and transaction commit. section 9.2
