# Database Interaction with Python

## Introduction to relational databases

A **relational database** is a type of database that stores and allows access to data. These types of databases are referred to as “relational” because the data items within them have pre-determined relationships with one another. [Data in a relational database is stored in tables ](https://www.coursera.org/articles/relational-database)[1](https://www.coursera.org/articles/relational-database).

1. **Youtube video link**: Here is a link to a video tutorial on the basics of relational databases by freeCodeCamp.org.

### **Examples**

```python
# Example 1: Creating a table in SQLite
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a table
conn.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert data into the table
conn.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Commit the changes
conn.commit()

# Close the connection
conn.close()
```

```python
# Example 2: Querying a table in MySQL
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

# Query the database
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Print the results
for (first_name, last_name, hire_date) in cursor:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

# Close the connection
cursor.close()
cnx.close()
```

### **Exercises**

* Write a Python program to create a table in a relational database.
* Write a Python program to insert data into a table in a relational database.
* Write a Python program to query a table in a relational database.
* Write a Python program to update data in a table in a relational database.
* Write a Python program to delete data from a table in a relational database.

## Connecting to databases (SQLite, MySQL, PostgreSQL)

Databases are an essential part of modern software development. They allow you to store and retrieve data in a structured and efficient way. In this topic, we will explore how to connect to three popular relational databases: SQLite, MySQL, and PostgreSQL. We will cover the basics of connecting to a database, executing SQL queries, and handling errors.

1. **Youtube video link**: Here is a [link](https://www.youtube.com/watch?v=HXV3zeQKqGY) to a video tutorial on connecting to a MySQL database by freeCodeCamp.org.

### **Examples**

```python
# Example 1: Connecting to a SQLite database
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a table
conn.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert data into the table
conn.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Commit the changes
conn.commit()

# Close the connection
conn.close()
```

```python
# Example 2: Connecting to a MySQL database
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

# Query the database
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Print the results
for (first_name, last_name, hire_date) in cursor:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

# Close the connection
cursor.close()
cnx.close()
```

```python
# Example 3: Connecting to a PostgreSQL database
import psycopg2

# Connect to the database
conn = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myusername",
    password="mypassword"
)

# Query the database
cur = conn.cursor()
cur.execute("SELECT * FROM mytable")

# Print the results
rows = cur.fetchall()
for row in rows:
    print(row)

# Close the connection
cur.close()
conn.close()
```

### **Exercises**

* Write a Python program to connect to a SQLite database and create a table.
* Write a Python program to connect to a MySQL database and query a table.
* Write a Python program to connect to a PostgreSQL database and insert data into a table.
* Write a Python program to connect to a SQLite database and update data in a table.
* Write a Python program to connect to a MySQL database and delete data from a table.

## Executing SQL queries with Python

Executing SQL queries with Python is a critical component of working with data in a Python environment. To accomplish this, various libraries can be utilized. In this topic, we will explore how to execute SQL queries in Python using two popular libraries, namely `sqlite3` and `SQLAlchemy`.

1. **Youtube video link**: Here is a link to a video tutorial on executing SQL queries with Python by freeCodeCamp.org.

### **Examples**

```python
# Example 1: Connecting to a SQLite database and executing a query
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a cursor object
cursor = conn.cursor()

# Execute a query
cursor.execute('SELECT * FROM employees')

# Fetch the results
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
conn.close()
```

```python
# Example 2: Connecting to a MySQL database and executing a query
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

# Create a cursor object
cursor = cnx.cursor()

# Execute a query
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Fetch the results
results = cursor.fetchall()

# Print the results
for (first_name, last_name, hire_date) in results:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

# Close the connection
cursor.close()
cnx.close()
```

### **Exercises**

* Write a Python program to connect to a SQLite database and execute a query.
* Write a Python program to connect to a MySQL database and execute a query.
* Write a Python program to connect to a PostgreSQL database and execute a query.
* Write a Python program to connect to a SQLite database and insert data into a table.
* Write a Python program to connect to a MySQL database and update data in a table.

Both `sqlite3` and `SQLAlchemy` are Python libraries that allow you to interact with relational databases. However, they serve different purposes.

`sqlite3` is a built-in Python library that provides a simple way to work with SQLite databases. It is a lightweight database engine that is easy to use and requires no additional installation or configuration. You can use `sqlite3` to create, read, update, and delete data in an SQLite database.

On the other hand, `SQLAlchemy` is a Python library that provides a set of tools for working with relational databases. It includes an object-relational mapper (ORM) that allows you to map database tables to Python classes, making it easier to work with data in an object-oriented way. `SQLAlchemy` supports multiple database engines, including SQLite, MySQL, and PostgreSQL.

If you need to work with a simple SQLite database, `sqlite3` is a good choice. However, if you need to work with more complex databases or multiple database engines, `SQLAlchemy` is a better option.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://doc.questfolio.io/python/intermediate-level/database-interaction-with-python.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.