Intro to Object Relational Mappings with SQLAlchemy

CSCI 344: Advanced Web Technologies

Spring 2023

Announcements

1. Tutorial 10 (SQL Tutorial) due tonight
2. HW7 due next Wednesday (4/10)
3. This Friday: finishing one endpoint in HW7 where all the tests pass.
4. Next Friday: class cancelled (Sarah will be at a conference).

Outline

• SQL Review
• Brief Introduction to SQLAlchemy
• Download and Configure HW7 & HW8 starter files
• Activity

Outline

• SQL Review
• Brief Introduction to SQLAlchemy
• Download and Configure HW7 & HW8 starter files
• Activity

Database Basics

1. Accessing Your Databases�Access your database using the GUI tool (pgAdmin 4) or via the psql command line tool (demo).�
2. Table Schemas�Note that there are data types and constraints on all of the tables. If you insert the wrong datatype, or you forget to include a required piece of data, the database will reject your request (and this is a GOOD THING). You want data integrity.

More database basics...

• All of the tables in the photo app database have unique number that is autogenerated by the database using something called a “sequences.” Hence, each row has a unique identifier. This is called a PRIMARY KEY�
• Tables are linked together through these primary keys. For instance, if you want to track the user who created a post, you could create a field called user_id in the posts table that matches the id in the users table.
1. users.id is the primary key
2. posts.user_id is the foreign key

Quick Crash Course on Querying

• SELECT X from Y;
• SELECT functions (count, sum, etc.)
• INNER JOIN … (connect tables together)
• WHERE
1. Comparison operators: =, >, <, like
2. Logical operators: and, or, in, not
• GROUP BY
• ORDER BY

​

Review of Tutorial 10

Practice Queries

• Find all of the post_ids that have been bookmarked by user #5
• Feed for User # 15:
• Find all of the user_ids that user #15 follows.
• Find all of the usernames that user #15 follows.
• Find all of the posts_ids that have been created by people that user #15 is following
• Using a JOIN
• Using a nested query and an in clause
• Find all of the usernames that user #15 is not following.
• Hint: use “NOT IN” clause

SQL: INSERT

INSERT INTO table_name(column1, column2, …)

VALUES (value1, value2, …);

​

SQL: UPDATE

UPDATE table_name

SET column1 = value1,

column2 = value2,

column3 = value3,

...

WHERE condition;

​

SQL: DELETE

DELETE FROM table_name

WHERE condition;

​

Outline

• SQL Review
• Brief Introduction to SQLAlchemy
• Download and Configure HW7 & HW8 starter files
• Activity

What is SQLAlchemy?

SQLAlchemy is a python abstraction that makes communication with databases “easier.”

• It’s database agnostic
• You can interact with the DB using raw SQL or you can use something called Object Relational Mapping
• You create some classes (usually called “models”) that represent each of your tables
• You access the data in your tables using instances of the classes you create.

What is a Model?

• A Model class is a Python class that represents a database table or view. Think of it as the structure of the table, and how the table relates to other tables.
• A Model instance is a Python object that represents a single database record (or row).

This approach – known as “Object Relational Mapping” – allows a more convenient way to manipulate data via the Python language.

Outline

• SQL Review
• Brief Introduction to SQLAlchemy
• Download and Configure HW7 & HW8 starter files
• Activity

Download and Configure the HW7 Starter Files

1. Download the starter files and save them in your CSCI344 folder.
2. Navigate to the HW07 files in your terminal / command prompt
3. Create a virtual environment and install the python dependencies (see instructions)
4. Create a new database (see instructions)
5. Add a database connection string to your .env file (see instructions)
6. Populate your database (see instructions)
7. Run flask from your terminal / command prompt (see instructions)

Outline

• SQL Review
• Brief Introduction to SQLAlchemy
• Download and Configure HW7 & HW8 starter files
• SQLAlchemy Activity

SQLAlchemy Activity