Databases: Databases

Database Readings & References

PostgreSQL Reference

SQLAlchemy Reference

SQL Cheatsheet

Examples use the dvdrental database from Lab 8.

Task Example Learn more
Select columns SELECT title, release_year FROM film; Select
Filter rows SELECT * FROM customer WHERE last_name LIKE 'S%'; Where, Like
Sort rows SELECT title FROM film ORDER BY rental_duration DESC, title; Order By
Limit rows SELECT title FROM film LIMIT 5; Limit
Join tables SELECT f.title, c.name FROM film f JOIN film_category fc ON f.film_id = fc.film_id JOIN category c ON fc.category_id = c.category_id; Inner Join, Aliases
Count / group SELECT rating, COUNT(*) FROM film GROUP BY rating; Group By, Count
Insert a row INSERT INTO category (name) VALUES ('Mystery'); Insert
Update rows UPDATE film SET rental_rate = rental_rate * 1.1 WHERE film_id = 1; Update
Delete rows DELETE FROM rental WHERE rental_id = 1; Delete

SQLAlchemy (Async) Cheatsheet

Every call that talks to the database needs await. See orm_samples.py in Lab 8 for working examples.

SQL idea SQLAlchemy Learn more
Select columns result = await session.execute(select(Film.title, Film.release_year)) then for title, year in result: Simple select
Filter rows select(Customer).where(Customer.last_name.istartswith("s")) istartswith
Sort rows .order_by(desc(Film.rental_duration), Film.title) Order by
Join tables select(Film.title, Category.name).join(FilmCategory, ...).join(Category, ...) Select with join
Count rows result = await session.execute(select(func.count(Film.film_id))) then result.scalar() Aggregate functions
Insert a row session.add(obj), then await session.commit(), then await session.refresh(obj) to read generated ids Adding items
Get by primary key obj = await session.get(Customer, customer_id) (returns None if not found) Session.get
Update a row obj.email = "new@example.com", then await session.commit() Updating objects
Delete a row await session.delete(obj), then await session.commit() Deleting objects
See the SQL print(query.compile(compile_kwargs={"literal_binds": True}))

PostgreSQL Administrative Commands

To enter the postgreSQL shell, type: psql -U postgres (connecting as the postgres superuser). Once you’re in the psql shell, try using the following commands:

Command Explanation Example
\q Exits the psql shell
\l Lists all the available databases
\c <dbname> <username> Connect to specific database \c photo_app_tutorial postgres
\dt Lists all of the tables in the database you’re connected to
\d <table_name> Describes the structure (i.e., “schema”) of a table \d posts
\du List all users and their roles
space bar If you query data in a table that has multiple pages, the space bar will show you the next set of records.
q If you query data in a table that has multiple pages, and you want to go back to the psql prompt.

UNC Asheville Department of Computer Science