Python Database Programming - SQL Alchemy ORM


Course Background, Python & Databases - An Introduction, Python Database API, What is ORM, An Introductory Example, Python Revisited, Data Structures, Lists & Tuples, Dictionaries, Sets

Python Revisited - OOP, OOP Concepts, Classes, Exceptions, Python DBAPI, Architecture, Connection & Cursor Objects, Bind arguments, Type Objects, Example - Movie Rating System, SQL Alchemy Introduction, Basic Concepts, SQL Alchemy Architecture, Engine, Metadata & DDL, SQL Expressions, ORM, Domain Model <-> Relational mapping, Exercises - Python Revisited

SQL Alchemy - Core Objects, Engine, DB dialect/driver, Connections, Result Proxy, Performing Inserts & Queries, Metadata, Creating tables, DDL, Reflection - Using existing tables/database, Working with columns, Constraints, Primary & Unique Keys, Foreign Key Constraint, Check Constraint, Creating Indexes, Creating Sequences, SQL Expressions, Inserts, Updates & Deletes, Selects, SQL Alchemy Type System, Core Types, Mapping with Python Types, Mapping with SQL Standard Types, Exercise - Extend the movie rating problem to include the following, tables., Movies - Movie data, Rating - Rating data, Genre - Movie type, Cast - Cast of the movie, Actor - Actors making up the cast, User - User rating the movie, Define tables for this using MetaData declarative schema with Foreign, Key constraints, indexes and required primary/unique key constraints, Write code to insert a given set of data into the tables and also perform queries to get a given set of data.

Exercise Review & Presentation - Advanced SQL Expressions, Insert-Select, Update-Select, Joins, Sub-queries, SQL Alchemy ORM Primer, A basic example, ORM concepts, Data Mapper, Unit of work, SQL Alchemy Sessions, Session maker & Session objects, Adding objects to session, Session methods, Commit, Rollback, Refresh, expunge etc, SQL Alchemy ORM Primer Revisited/Q&A, SQL Alchemy ORM - Basic Queries, Query, all, get, first, one, filter, filter_by Methods - asc, desc etc, Exercise, Design tables for the menu of a home delivery restaurant with the following objects, Food Item, Beverage Item, Order Menu, Write code to generate schema with the required FK & other constraints., Write code to perform a set of queries to fulfill a given customer's order and to calculate the final bill cost.

Querying with ORM - Advanced Querying, Joins, Select customization, Customizing Property Mapping, Eager vs Deferred, Synonyms, Properties, Composite Values, Standard Queries, Query+Inserts, Query+Updates, Types of Joins, Extend the Food/Beverage database exercise with new table(s) to generate a Bill to the customer plus add tables to implement a Pizza Delivery menu with customizations.

Advanced Topics - Relationships, 1:1, 1:N, M:N, Using brackets, Self references, Examples, Inheritance Mapping, Single, Concrete, Joined Table, Examples & Work on Exercises(Optional) Extend Food/Beverage ordering exercise to use Relationships.

Exercise Review - Python ORMs, Django ORM, Storm, SQL Object, Summary/Q & A & Preparation for Assessment