If you’re working with data that is in close proximity with a front-facing app, or if your data team is very mature in terms of technical implementations, you’ll likely be using an Object-relational mapping (ORM) tool.

In short, ORM obfuscates the need to write out entire SQL statements generally unless for edge-cases, and it reduces the potential of human-error by providing functions and parameters to work with instead.

It is typically rare for business intelligence (BI) or data analysts to use ORMs, particularly because the reports they need to generate are too complex to leverage an ORM’s value. However, if an app is using an ORM to collect data, then you might need to know how it works.

Before we start, take a deep breathe.

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_BasicSQL_Connection folder to get started.

We are getting to a point where we are diving into operational processes when using SQL.

Introduction to Object Relational Mapping (ORM)?

SQL is very powerful because of the CRUD process, where data can be lost or corrupted by a human error.

Thus, ORM provides a framework where operational processes are less error-prone, and if you follow a certain template, you should not have problems inserting, updating or deleting records.

  • Because it applies object-oriented programming (OOP), and has the power to obfuscate the complexity of creating/updating/deleting records.

Introduction to SQLite

SQLite is a lightweight database where it doesn’t need processing power to run. Rather, it writes the data into files itself.

  • This is very useful especially when you’re coding mobile apps, and you want to store a local database within the app itself.
    • However, as it is a file, you might want to be careful when you scale the database if you’re using your work commercially, especially when you’re working with apps on a small physical footprint.
    • Though you may not be responsible for coding an app in a mobile device, you might be responsible for the telemetry data that exist in a mobile device.

Students Do: Ice Cream Connection

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

SQLAlchemy and Pandas

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

Students Do: Read All the SQL

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

Preview SQLAlchemy with Classes

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

This is the part where we obfuscate the SQL statements with OOP and Python. As the SQL statements are hidden, we should expect lesser human errors with our SQL statements.

  • This is especially true for application engineers who don’t dive deep into the world of data.

However, data analysts who uses a business intelligence (BI) tool such as Tableau will often use raw SQL statements instead.

A Schooling on Classes

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

We are covering the basics of OOP, where it is the default standard of how we should design our code within a repo.

The fundamental outcome of OOP is the “don’t repeat yourself” (DRY) principle. In essence, if we keep repeating our code by copying and paste, we are forced to:

  • Update all the different blocks of code if there are errors or changes.
  • Waste a lot of time and energy doing the same thing when others had done the same piece of work.
  • It is not good enough if your code works. Can we scale the work if others are contributing code, or with other areas of improvement?

Students Do: Surfer Class

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

Everyone Do: Designing an ERD, Part 1

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

Back when I was in school, I was confused between the terms “method” and “function” because there were times they were mentioned interchangeably. They are basically the same except:

  • Function is a piece of code that exist by itself for a certain objective.
  • Method is a piece of code that is associated with an object.

In both cases, they are a block of code that performs one main objective. It is not wise to compound your function with more than 1 objective as it becomes difficult to maintain, and thus we break it into smaller objects for better maintainability.

Students Do: Surfer Class Extended

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

Everyone Do: Back to the SQL

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

Students Do: Surfing SQL

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