Database Readings & References
PostgreSQL Reference
- About PostgreSQL
- psql commands (command line)
- Querying a Table
- Joins Between Tables
- Aggregate Functions
- Inserts
- Updates
- Deletions
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. |