Databases and SQL with Python

Designing relational databases, writing SQL and using databases from Python with drivers and an ORM.

PYTH-215 Year 2 3 credits 12 lessons · 6 h 55 min ▶ Video lessons coming soon

Offered by HSM University · Python Programming

What you'll learn

Explain what a database management system provides that files do not, and when to choose an embedded or a client-server database.
Describe tables, rows, columns, types, NULL, primary keys, foreign keys and constraints, and inspect a schema from Python.
Write SELECT queries with chosen columns, expressions, aliases, DISTINCT and LIMIT, and pass values safely as parameters.
Filter, sort, aggregate and group data with WHERE, ORDER BY, aggregate functions, GROUP BY and HAVING.
Combine tables with inner, left and self joins, and avoid the common counting and filtering mistakes in joins.
Design tables with suitable types, keys, constraints and junction tables, and choose foreign key delete behaviour.
Recognize update, insertion and deletion anomalies and normalize tables to third normal form, using views to present joined data.
Change data with INSERT, UPDATE, DELETE and upserts, and group related changes into transactions that commit or roll back together.
Use the Python DB-API with sqlite3 and psycopg: cursors, row factories, type conversion, connection handling and database exceptions.
Explain object-relational mapping, recognize and fix N+1 queries, and read SQLAlchemy models, sessions and migrations.
Read query plans, create effective single-column and composite indexes, and write sargable queries and keyset pagination.
Build, migrate and test a database-backed Python package that keeps all SQL behind a clear interface.

12 lessons · 6 h 55 min total · final exam

Recommended background. You can start any course at any time.

Designing relational databases, writing SQL and using databases from Python with drivers and an ORM.

Part of the Information Technology stream, typically taken in year 2.