In this lecture, we will explore how to connect to the Postgres database that will store the data extracted
from the API when we are using airflow.
In order to set up a connection to a service, which in our case is the Postgres database, we use what
are known as hooks.
And since we are dealing with Postgres connection, we will use the Postgres hook.
I will link the documentation that you see here for Postgres hook in the appendix of the section.
Now before we start coding, I will explain two concepts that we will go over when we introduce Postgres
hook, these being the connection and the cursor.
The connection is used to establish the connection to the Postgres instance, while the cursor is an
object that allows you to execute SQL commands.
For the connection in question.
Now that we have a basic understanding of what connections and cursors are, let's create the first
Python script relating to how we will interact with the data warehouse.
We will need to build a number of functions to get the end result, and many of the functions will be
helper functions when we talk about helper functions, these are smaller functions where their purpose
is to assist the main functions in a program.
So let's first define a sub folder called data warehouse in our DAGs directory.
And inside the sub folder let's first create the helper function script that will be used repeatedly
in the section of the course.
For these type of functions we usually put them in a utils script.
So let's define a data underscore utils of the script.
Here we will have most functions relating to database connections and operations like creating tables
and schemas.
Now we can start coding.
And the first step is to import the Postgres hook which can be done with the following import statement.
So let's go back to here and copy all of this.
From here we import the Postgres hook.
To interact with our Postgres database using Python.
We will use a very common adapter called Psycopg2.
And the first import we will need is a package from the Psycopg2 that is called the real data set.
This will allow retrieval of reports using Python dictionaries and not the default tuples.
This will be needed in our case as it gives more flexibility with how we structure our code.
Now that we have our imports ready, let's define a function called get on set.
And here we set up the Postgres hook by defining a variable hook.
So hook equals to the Postgres hook.
And here we define two arguments.
The first one is the Postgres one id.
And if you recall in Docker compose we had defined this connection already in the environment part where
we said that the connection URI can be written as airflow underscore underscore one connection ID.
Let's go back to here and I'll showcase this to you.
I am referring to this environment variable.
So the actual Postgres connection ID would be this part here.
The Postgres DB YouTube thing.
Let's go back to the utils and write this down.
The second argument which we need to define is the database.
And we had defined this as elt underscore db.
Also just to remind you I defined this in dot env.
So let's go to the DMV and we will find that the database name is in fact the underscore db.
Now that we have the hook variable set up we can define the connection using the get underscore method.
So we define a connection variable using the hook dot get underscore method.
And we also define the cursor which we can define by specifying the cursor methods with the argument
cursor factory set to cursor.
This is it right now, so the cursor variable takes the connection.
We use the cursor method and we set an argument for cursor factory equal to the input that we have here.
We are using the cursor because it changes how the data is returned.
When we execute a query using the cursor, and the cursor in our case will return the data from the
SQL query as a Python dictionary instead of the default tuple.
Once we have defined both the connection and cursor objects, we can then use the cursor object to execute
some SQL, such as a simple select star from a particular table.
So this would look something like this.
Execute.
And then we have the actual query.
So select star from and the particular table.
So this is how it would look.
But we don't need this right now.
So we remove this part of the code.
Finally we should always close connection objects when done to release resources.
And this would look like this.
So characters close and connection dot close.
So this is the main function that we will use to connect to the Postgres database.
As a final step, we will make setting up and closing connections more modular.
So I will split this function into two by defining another function.
And we can call this function close.
One.
And they will take the conn and care arguments.
Also in the first function I will return the canonical variables.
So what we have effectively done here is the first function.
We set up the connection and cursor object.
And the second function will close the cursor and connection.
So that's it for the connection and cursor functions.
Next we will create the schemas and tables.