Course: SQL Databases using SQLAlchemy

There are many advantages to using SQLAlchemy, including using native Python language constructs for queries, transactions, and stored procedures and easily porting code between database servers.

You begin this course by installing MySQL on both Windows and Macintosh platforms. You'll then learn how to access a database from SQLAlchemy using the create_engine method, and how to specify the driver, dialect, and server information correctly.

You move on to creating database tables as well as associated constraints from Python using SQLAlchemy's Core API and the Table abstraction. You'll then query that table using a fluent-style API from SQLAlchemy's SQL Expression Language. You'll also learn how to specify primary key, unique, and not null constraints.

At the end of the course, you'll have a solid handle on basic functionalities of the core APIs of SQLAlchemy.

When you need access to data in a relational database while coding in Python, SQLAlchemy provides an ideal abstraction layer. SQLAlchemy supports several database types. In this course, you'll work with MySQL.

You'll start by learning how to construct and invoke select-from-where queries from Python using the SQLAlchemy core API. You'll then use the and_, or_, and not_ functions from the Expressions API, before learning how to alter tables by adding and dropping columns and constraints.

Following on, you'll practice deleting data and drop tables from SQLAlchemy. You'll then specify foreign key constraints to define parent-child relationships.

To finish off, you'll perform simple select-from-where queries with logical and relational operators from SQLAlchemy, add and drop columns and constraints, and define foreign-key constraints and parent-child relationships.

SQLAlchemy, an object-relational mapper (ORM) library available in Python, provides application developers with the same might and capability of SQL. In this course, you'll learn how to take advantage of some of the more complex queries available in this library.

You'll start by constructing queries using dates, the current date, order by, and limit clauses. You'll then learn how to leverage the fetch methods from the cursor provided via the ResultProxy object. You'll perform joins using the .join method on the column object. You'll also specify group by and having clauses in your SQL queries

By the end of this course, you'll be able to implement SQL order by and limit operations using SQLAlchemy Core APIs, work with dates, customize iteration over cursor results, perform joins, and implement group by and having clauses.

With a good grip on the use of SQLAlchemy in querying data in SQL tables, you are now ready to perform multi-table joins and use more powerful SQL abstractions.

In this course, you'll perform multi-table joins by chaining the .join methods on different tables. You'll also simplify join syntax using aliases. You'll then create and query views and learn how insertions into views are propagated from the view to the underlying tables.

Moving on, you'll create indexes and examine their metadata. You'll then differentiate between simple and composite indexes.

By the end of the course, you'll be able to perform multi-table joins by chaining .join methods, create and query views, correctly insert data into underlying tables using views, create indexes, and interpret index metadata.

Triggers are used for asynchronous execution and react to specific conditions in your database. Stored procedures can be used to achieve code reuse and code composition of SQL commands. In ths course, you'll learn about some of these procedural constructs of SQL, including triggers, stored procedures, and transactions.

You'll start by learn how to define triggers to perform specific operations before or after insert, update, or delete operations on specific tables. You'll also define stored procedures and invoke them using raw DBAPI connection objects.

Moving forward, you'll define and invoke stored procedures that take in input arguments. You'll also create, commit, and rollback transactions to enforce all-or-nothing execution of a group of SQL commands.

To finish up, you'll learn how DDL operations, such as create, drop, and alter operations, are not reversed by transaction rollback.

By the end of this course, you'll be able to create and test trigger executions, correctly invoke stored procedures using DBAPI connections, achieve code re-use with parameterized stored procedures, and work with transactions from Python using with blocks and transaction objects.

The SQLAlchemy Object Relational Mapping (ORM) module treats each database table as a class, and each row in that table as an instance or object of that class. These classes are very easy to define and use via base classes provided in the ORM API.

In this course, you'll start by defining check constraints and using them to enforce generic conditions on data in a column. You'll then use cascading update and delete with foreign keys to propagate changes from the parent table to child table.

Next, you'll illustrate how the SQLAlchemy ORM treats each table in a database as a class, and rows in that table as objects of that class. You'll identify how the declarative_base class is suitable for creating new tables, while the automap_base is ideal for reading in pre-existing tables from a database.

Upon finishing this course, you'll be able to create and enforce check constraints, enable cascading delete and update on foreign key constraints, use the ORM API to model tables as classes, correctly extend the declarative_base and automap_base classes from the ORM API and use them to perform SQL commands.