Rauf: AI and ML

Key Concepts in Database Queries

SQL Queries: Structured Query Language (SQL) is used to interact with databases.
CRUD Operations: Create, Read, Update, Delete operations are fundamental for manipulating data in a database.
Connecting to Databases: Python can be used to connect to databases using libraries like `sqlite3`, `mysql-connector`, or `psycopg2` for different types of databases.

Connecting to a Database with Python

To interact with databases in Python, we can use the `sqlite3` library for SQLite databases. Here’s an example of how to create a connection and execute a query:

import sqlite3

# Connect to a SQLite database (it will create the database if it doesn't exist) 
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

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

 # Insert data 
cursor.execute('''INSERT INTO users (name, age) 
                  VALUES ('Alice', 30), ('Bob', 25), ('Charlie', 35)''')

                  # Commit changes
conn.commit()

# Query data 
cursor.execute('SELECT * FROM users')

 # Fetch all rows 
rows = cursor.fetchall()
for row in rows:
    print(row)

 # Close the connection 
conn.close()

Performing SQL Queries

Basic SQL Query:

Here’s how to query the database and retrieve data based on certain conditions. For example, fetching all users older than 30:

 
         # Query users older than 30 
cursor.execute('SELECT * FROM users WHERE age > 30')
result = cursor.fetchall()
for user in result:
    print(user)

Updating Records:

To update data in the database, use the following query:

cursor.execute('UPDATE users SET age = 40 WHERE name = "Alice"')
conn.commit()

Deleting Records:

To delete a record, use:

cursor.execute('DELETE FROM users WHERE name = "Bob"')
conn.commit()

Understanding SQL Joins

SQL joins allow you to combine rows from two or more tables based on a related column. Here’s how to perform an inner join:

cursor.execute('''SELECT users.name, orders.amount FROM users 
                      INNER JOIN orders ON users.id = orders.user_id''')
result = cursor.fetchall()
for row in result:
    print(row)