We are covering aspects of object-oriented programming (OOP) with SQL statements.

  • At work, you might not need as much OOP knowledge and skill, but this is dependent on the company you work for.
  • Yet, you will see that we are creating records within a database with OOP skills. Thus, it is important for you to learn and try to understand.
  • Week 9 is still the more important week for learning databases and data. This week (week 10), it is more about how you can leverage Python with SQL in a more intimate manner.

Don’t feel as if you have to understand all in one sitting. This takes some time and practice to get used to it.

Introduction to SQLAlchemy

Let’s open up the file(s) in the 01-Ins_Basic_Querying folder to get started.

What is a connection?

This is the establishment of a network connection between a client application and a database server, and it can be physical or network-level.

What is a session?

A session represents a specific instance of interaction between the client application and the database server.

How I think of these 2 differences in layman terms?

  • A connection is liken to be a pipe, or a wire. A session is the liquid, or an electric current within it.
  • A single connection can have multiple sessions with it.
  • A session usually has limitations to it, such as time and capacity. A connection isn’t as transcient, because you’re either connected or you’re not.

Students Do: Sunny Hours Search

Let’s open up the file(s) in the 02-Stu_Sunny_Hours folder to get started.

Updating and Deleting Rows

Let’s open up the file in the 03-Ins_Basic_Updating folder to get started.

The strength of using SQLAlchemy is where you can update your Python variables, and perform changes to the databases accordingly.

  • session.dirty is to check if the variable within the session has experienced changes or not.
  • If the changes are intended, you can directly commit without adding. Adding means that the element isn’t part of the database.
  • session.deleted is to check if the variable within the session has a delete flag or not.
  • If the deletion is intended, you can commit the deletion and it will be permanent.

Students Do: CRUD Database

Let’s open up the file in the 04-Par_CRUD_DB folder to get started.

Reflection with SQL

Let’s open up the file(s) in the 05-Ins_Reflection folder to get started.

We have been creating new databases and tables from scratch, and we define them using the declarative Base class.

What if we are dealing with an existing data source?

Reflection is the process where we extract data from existing tables and databases, and it mirrors them within SQLAlchemy objects.

Students Do: Demographics Reflection with SQL

Let’s open up the file(s) in the 06-Stu_Reflecting_On_SQL folder to get started.

SQLAlchemy Inspection

Let’s open up the file(s) in the 07-Ins_Inspection folder to get started.

I taught the class on how to use sqlite3 on your Git Bash/Terminal to see the schemas of tables and the database.

The inspect module does the same thing, without the use of SQL statements.

Students Do: Salary Exploration

Let’s open up the file(s) in the 08-Stu_Salary_Exploration folder to get started.