Course: Pythonista to Python Master - Deel 3 Dynamic Data Handling with Python

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.

Extract, Transform, and Load (ETL) tasks help in collecting and manipulating data from diverse sources to fit the user's requirements. In this course, you'll explore different interfaces available in the petl library and perform basic ETL tasks using petl. You will begin by examining how to import data from various data sources, including delimited text files, Microsoft Excel, and structured JSON data. You'll also recognize how to load and save data in these formats. Next, you'll outline how to integrate petl with a relational database using SQLAlchemy and SQLite3. Finally, you'll perform transform operations on data using different petl features to filter specific data needed by you. Once you have completed this course, you'll have a clear understanding of the role played by petl in simplifying ETL tasks.

Software development often requires manipulation of data that has been extracted from different data sources to make it compatible with the user's specifications and requirements. petl's data transformation features can help achieve this. In this course, you'll investigate fundamental data transformations that can be performed using the petl library. You'll demonstrate how to load data into a petl table, filter columns, and combine multiple tables using different forms of concatenation operations. Next, you'll outline how to convert data in a petl table into a form that is compatible with your requirements. This includes transforming strings to numbers, applying calculations to numeric fields, and replacing specific values in the table. Lastly, you'll explore ways to filter content in petl tables using the facet() function and different select operations.

Petl facilitates and streamlines tasks related to data extraction and manipulation, often required by software developers to make data fit for actionable business intelligence (BI). In this course, you'll work with complex operations in petl and outline how to extract data from a source and convert it to a format that complies with your requirements. You'll begin by investigating the use of regular expressions to analyze, search, and extract specific rows and columns in a petl table. You'll then create transform functions and apply them to your data. These include operations on numeric as well as string fields. Moving on, you'll implement sort operations to organize data in a petl table and arrange it in a sequence that suits your purposes. Finally, you'll investigate how to perform joins and set operations on data tables and meaningfully reduce the data in them using aggregation functions.

Experience making and processing HTTP requests using HTTPX will help you integrate such requests into your web applications. In this entirely hands-on course, you'll dive into the mechanics of HTTP requests and responses using the HTTPX library. You'll begin by invoking basic GET requests to request data from a remote server. You'll then explore the different possible forms of responses - ranging from HTML content and structured JSON data to binary data, such as images and videos. Moving on, you'll learn to use POST requests to submit information to a remote server. Lastly, you'll cover a host of other HTTP requests that can be invoked with HTTPX, namely HEAD requests to retrieve headers, OPTIONS requests to get communication information, PUT requests to create or update resources, and DELETE requests to remove them.

Various HTTPX features are used to optimize the processing of HTTP requests. This course introduces you to some advanced techniques that use HTTPX to send and process HTTP requests. You'll start by looking at HTTPX streams, which allow the processing of large responses in chunks to reduce the load on memory resources. You'll then examine the HTTP response content that can determine how our application's subsequent actions are defined, including HTTP status codes, HTTP headers, and cookie usage. Next, you'll configure requests to handle redirects on the server side, set timeout values to prevent long waits due to network or server issues, and touch upon the compatibility of the Python Requests library with HTTPX. Finally, you'll focus on an essential feature of HTTPX - sending and asynchronously processing HTTP requests, which can significantly improve app performance.

In this practice lab, learners will be presented with a series of exercises to practice developing in Python. Exercises include tasks such as connecting to MySQL using SQLAlchemy, managing data in a table, and running basic and advanced queries in a table. Learners will also practice using Petl to read data from a CSV file, querying data using SQL or MySQL, installing HTTPX and examining HTTP headers. Learners can also use the environment as an open sandbox. No installation or configuration is required, so you can gain immediate hands-on experience. Create new files or upload your own from a storage location of your choice, such as GitHub, and you can practice coding right away! You can even download a copy of your work when you're done. Whether you're looking to dive into the code presented within our courses or you want to work on your own coding projects, this lab environment will provide you with everything you need. So, go ahead and start coding today! See below for a complete list of available software. - MySQL 8.0 - phpMyAdmin: - Python 3 libraries: - sqlalchemy - pymsql - mysqlclient - beautifulsoup4 - selenium - Python 2 and 3 - Pip 2 and 3 - Node.js - npm - Git - Vim editor - Standard Python libraries This lab is part of the Dynamic Data Handling with Python track of the Skillsoft Aspire Pythonista to Python Master Journey.

Final Exam: Dynamic Data Handling with Python will test your knowledge and application of the topics presented throughout the Dynamic Data Handling with Python track of the Skillsoft Aspire Pythonista to Python Master Journey.