db.py¶
The db module handles database connectivity. The application usese MySQL for data storage, and the mysql.connector library to interface between Python and MySQL.
Examples from the documentation are located here.
The basic flow:
Connect to the database
Create a cursor from the connection
Define SQL statement
Execute the query (response is stored in the cursor)
Optionally commit the transaction (if it is an insert or update)
Close the connection
Return the results
-
db.dbconnect()¶ Creates database connection
- Returns
mysql.connector connection object
-
db.delete_recipe(rid)¶ Deletes recipe from database
- Parameters
rid – recipe id (int)
- Returns
None
-
db.get_all_recipes()¶ Retrieves a list (ordered by title) of all recipes in the database, whether they are associated with a book or not.
- Returns
list of tuples (ID (int), title (str), ingr (str), dir (str), user_id (int), cat_id (int), book_id (int))
-
db.get_book_info(book_id)¶ Gets book information including name, description and author
- Parameters
book_id – book id (int)
- Returns
tuple (id (int), name (str), description (str), first_name (str), last_name (str))
-
db.get_book_recipes(book_id)¶ Gets a list of recipes contained in a single book
- Parameters
book_id – book id (int)
- Returns
list of tuples (id (int), title (str))
-
db.get_books()¶ Get all non-empty books
- Returns
list of tuples (id (int), name (str))
-
db.get_books_by_user(uid)¶ Gets books owned by a single user
- Parameters
uid – user id (int)
- Returns
list of tuples (id (int), name (str))
-
db.get_recipe(rid)¶ Gets recipe given a recipe id
- Parameters
rid (int) – recipe id
- Returns
single recipe information
- Return type
tuple (id (int), title (str), ingredients (str), directions (str), firstname (str), lastname (str), email (str), category (str), category_id (str), book_id (str))
-
db.get_recipe_user_id(rid)¶ Returns the user_id of a recipe
- Parameters
rid (int) – recipe id
- Returns
user id
- Return type
int
-
db.get_reset_token_timestamp(email)¶ Retrieves the timestamp of the current token used for resetting a user password
- Parameters
email – user email (str)
- Returns
timestamp if found, else None
- Return type
str (%Y%m%d%H%M)
-
db.get_user(email)¶ Retrieves user information based on email
- Parameters
email – user email (str)
- Returns
user info if found, else None
- Return type
tuple (email (str), passwd (str), first_name (str), last_name (str), verified (int), ID (int))
-
db.insert_book(book_name, book_desc, user_id)¶ Inserts a new book into the database
- Parameters
book_name – name of book (str)
book_desc – description (str)
user_id – user id (int)
- Returns
book id of the new entry (int)
-
db.insert_recipe(title, ingr, dirs, user_id, cat_id, book_id)¶ Inserts a new recipe into the database
- Parameters
title – recipe title (str)
ingr – ingredients (str)
dirs – directions (str)
user_id – user id (int)
cat_id – category id (int)
book_id – book id (int)
- Returns
record id of the recipe just inserted
- Return type
int
-
db.register_user(email, cipher, first_name, last_name)¶ Registers a new user
- Parameters
email – user email (str)
cipher – encrypted password (str)
first_name – user first name (str)
last_name – user last name (str)
- Returns
None
-
db.reset_token_timestamp(time_stamp, email)¶ Updates the timestamp for a reset password token.
- Parameters
time_stamp – new timestamp (str)
email – user email (str)
- Returns
True
-
db.update_password(cipher, email)¶ Updates the password field in the users table
- Parameters
cipher – encrypted password (str)
email – user email (str)
- Returns
True
-
db.update_recipe(title, ingr, dirs, rid, cat_id, book_id)¶ Updates an existing recipe
- Parameters
title – recipe title (str)
ingr – ingredients (str)
dirs – directions (str)
rid – recipe id (int)
cat_id – category id (int)
book_id – book id (int)
- Returns
True
- Return type
bool
-
db.update_user_verified(email)¶ Updates the verified field in the users table to 1, meaning they have responded to the email verification process.
- Parameters
email – user email (str)
- Returns
True