Database Operations

Database operations refer to the fundamental actions performed to interact with and manage data stored in a database system. These operations are crucial for nearly all applications, from simple websites to complex enterprise systems, as they allow for data persistence and dynamic content.

The core of database operations is often summarized by the acronym CRUD:

- C - Create (Ekleme): This involves adding new records or data entries into a database table. In SQL, the `INSERT INTO` statement is used for this purpose. For example, adding a new user to a `users` table.

- R - Read (Okuma/Sorgulama): This is the most common operation, focusing on retrieving existing data from a database. The `SELECT` statement in SQL is used, often combined with `WHERE` clauses to filter results, `JOIN`s to combine data from multiple tables, and `ORDER BY` clauses to sort the output.

- U - Update (Güncelleme): This operation modifies existing data records in a database. The `UPDATE` statement in SQL is used, specifying which columns to change and which rows to affect (typically using a `WHERE` clause to target specific records).

- D - Delete (Silme): This operation removes existing data records from a database. The `DELETE FROM` statement in SQL is used, again with a `WHERE` clause to specify precisely which rows to remove. Without a `WHERE` clause, `DELETE FROM` would remove all records from a table.

Key Concepts in Database Interaction:

- Database Connection: Before performing any operation, an application must establish a connection to the database. This typically involves providing credentials (username, password), the database host, port, and the name of the database to connect to.

- Cursor/Statement: Once connected, a 'cursor' or 'statement' object (depending on the programming language and database driver) is often used to execute SQL queries and fetch results from the database.

- Transaction Management: Operations are frequently grouped into transactions. A transaction is a single logical unit of work, which either completely succeeds or completely fails.
- Commit: Permanently saves all changes made within a transaction to the database, making them visible to other database users.
- Rollback: Undoes all changes made within a transaction, reverting the database to its state before the transaction began. This is vital for maintaining data integrity in case of errors, partial failures, or unexpected interruptions.

SQL (Structured Query Language): SQL is the standard language used to communicate with and manage relational databases. It provides the syntax for all CRUD operations and other database management tasks like schema definition (e.g., creating tables).

Importance: Database operations are fundamental for data persistence, enabling applications to store, retrieve, modify, and delete information reliably and efficiently. They form the backbone of dynamic and interactive software, allowing applications to manage user data, content, settings, and much more.

Security Considerations: Protecting against vulnerabilities like SQL injection is paramount. Always use parameterized queries or prepared statements when executing SQL with user-provided input. These methods ensure that input is treated as data, not as executable code, preventing malicious input from altering query logic or accessing unauthorized data.

import sqlite3

 1. Establish a connection to the database
 If the database file doesn't exist, it will be created.
try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

     2. Create Operation: Create a table
    print("Creating 'users' table...")
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE
        )
    ''')
    conn.commit()  Commit changes to save the table

     3. Create Operation: Insert data
    print("Inserting new users...")
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Charlie', 'charlie@example.com'))
    conn.commit()  Commit changes to save inserted data
    print("Users inserted successfully.")

     4. Read Operation: Select all data
    print("\nReading all users:")
    cursor.execute("SELECT id, name, email FROM users")
    users = cursor.fetchall()  Fetch all results
    for user in users:
        print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}")

     5. Read Operation: Select specific data with a WHERE clause
    print("\nReading user with ID = 2:")
    cursor.execute("SELECT id, name, email FROM users WHERE id = ?", (2,))
    bob = cursor.fetchone()  Fetch a single result
    if bob:
        print(f"ID: {bob[0]}, Name: {bob[1]}, Email: {bob[2]}")
    else:
        print("User with ID 2 not found.")

     6. Update Operation: Update data
    print("\nUpdating user 'Alice's email...")
    cursor.execute("UPDATE users SET email = ? WHERE name = ?", ('alice.smith@example.com', 'Alice'))
    conn.commit()
    print("User 'Alice' updated successfully.")

     Verify update
    print("Verifying 'Alice's updated email:")
    cursor.execute("SELECT id, name, email FROM users WHERE name = 'Alice'")
    alice_updated = cursor.fetchone()
    if alice_updated:
        print(f"ID: {alice_updated[0]}, Name: {alice_updated[1]}, Email: {alice_updated[2]}")

     7. Delete Operation: Delete data
    print("\nDeleting user 'Bob'...")
    cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',))
    conn.commit()
    print("User 'Bob' deleted successfully.")

     Verify deletion
    print("\nReading all users after deletion:")
    cursor.execute("SELECT id, name, email FROM users")
    remaining_users = cursor.fetchall()
    if remaining_users:
        for user in remaining_users:
            print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}")
    else:
        print("No users remaining.")

except sqlite3.Error as e:
    print(f"Database error: {e}")
    if 'conn' in locals() and conn:
        conn.rollback()  Rollback changes in case of error
finally:
     8. Close the connection
    if 'conn' in locals() and conn:
        conn.close()
        print("\nDatabase connection closed.")