Days-23-26.md

Day 23: Database Connection

Python can connect to various types of databases using different database libraries. One common library for working with databases in Python is sqlite3 for SQLite databases.

• Connecting to a Database:
  • Use the sqlite3.connect() method to connect to a database.
  • This method creates a new database if it doesn't exist or opens an existing one.

Example of connecting to an SQLite database:

import sqlite3

# Connect to a database (creates a new one if it doesn't exist)
conn = sqlite3.connect("my_database.db")

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Close the connection when done
conn.close()

Day 24: Creating Tables and Inserting Data

You can create tables in a database and insert data into them using SQL commands.

• Creating Tables:
  • Use the execute() method to run SQL CREATE TABLE queries.
• Inserting Data:
  • Use the execute() method to run SQL INSERT queries.

Example of creating a table and inserting data:

import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Create a table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
""")

# Insert data into the table
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Bob", 30))

# Commit the changes and close the connection
conn.commit()
conn.close()

Day 25: Querying Data

You can retrieve data from a database table using SQL SELECT queries.

• Querying Data:
  • Use the execute() method to run SQL SELECT queries.
  • Use the fetchall() method to retrieve all rows from a query.

Example of querying data from a table:

import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Query data from the table
cursor.execute("SELECT * FROM students")
students = cursor.fetchall()

# Print the results
for student in students:
    print(f"ID: {student[0]}, Name: {student[1]}, Age: {student[2]}")

conn.close()

Day 26: Updating and Deleting Data

You can use SQL UPDATE and DELETE queries to modify or remove data from a database table.

• Updating Data:
  • Use SQL UPDATE queries to modify existing data.
• Deleting Data:
  • Use SQL DELETE queries to remove data from the table.

Example of updating and deleting data:

import sqlite3

conn = sqlite3.connect("my_database.db")
cursor = conn.cursor()

# Update data
cursor.execute("UPDATE students SET age = 26 WHERE name = 'Alice'")

# Delete data
cursor.execute("DELETE FROM students WHERE name = 'Bob'")

# Commit the changes and close the connection
conn.commit()
conn.close()

Understanding how to work with databases and SQL in Python is crucial for many real-world applications where data storage and retrieval are required. The sqlite3 library is suitable for learning and small-scale projects, but for larger databases, you may consider other database systems like MySQL or PostgreSQL. Practice with these examples to become proficient in database operations in Python.