6. Database Access - jcmwright80/work-experience GitHub Wiki

The goal of this tutorial is to create a table called todos in your database which has the following simple structure:

Table - todos Columns - id - The unique identifier for each todo entry this should just be an auto incrementing number task_description - String description of the task thats got to be done due_date - Timestamp column representing the date the task needs to be completed by complete - Boolean field to capture if a task has been completed or not.

Step 1. Learn how to create the required table above using MySQL Workbench connected to your docker container database. Either read some mysql quick start guides or ask chatgpt for some guidance but make sure you understand the different column types available and what data they are for.

Step 2. Using MySQL Workbench insert a couple of rows into your new table. This can be done either using the UI after selecting all rows from the table or by running SQL insert statements to create them, again chatgpt can assist you with this if necessary but you should try and understand what an insert statement looks like.

Step 3. Write an SQL statement that updates one of your test rows changing one of the column values to something different e.g. setting complete from 0 to 1.

Step 4. Connect to the db from your python app.

1. Install MySQL client ORM

   pip install flask flask-mysqldb

2. In your todos python project create a new file called db.py with the following content:

import MySQLdb

def get_connection():
    return MySQLdb.connect(
        host="localhost",
        user="root",
        password="root",
        db="todo"
    )

3. Create a new python file called db_utils.py, import the get_connection function from db.py

4. Next create a function called add_todo(task, due_date, complete) which will use the get_connection function to execute a sql command to add a row to the db which will look something like:

from db import get_connection

def add_todo(task, due_date, complete):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("INSERT INTO todos (task, due_date, complete) VALUES (%s, %s, %s)", (task, due_date, complete))
    conn.commit()
    conn.close()

5. Write a unit test that tests your add_todo works and validate by using MySQL Workbench to view the new row in the database.