Photo by Caspar Camille Rubin on Unsplash
Hi All!! Hope you had a wonderful weekend! I have been working on importing data frames to an Amazon RDS (Postgres SQL) DB instance. Last time, we saw how to make a connection to your Amazon RDS DB instance using Python script here
Today, we can see how to connect DB to Postgres SQL server and create a table into the DB for storing our data frame values.
Connecting pgAdmin and RDS database instance:
1. Open pgAdmin, you should see a sidebar that shows “Servers” on the left-hand side. Right-hand click “Servers” to create a new server.
2. Give the server a name. For example, let's name the server “example”.
3. Next, complete some information within the “Connection” tab. Hostname/address can be found by clicking on your database instance within the RDS dashboard. You’ll specifically find it under “Endpoint” within “Connectivity & security”.
4. At last, enter the username and password you created for the database instance in RDS. Once you’ve entered each of those items, click "save" to save your details.
5. To create a database within the server, right-click on “Databases” and select “Create” and then “Database”
6. Give the database a name and save it when done.
You will now be able to see your database instance connected under the server that has been mapped.
Image by Author
Create a Table in Postgres DB using Python:
Now let's define the create function inorder to create a new table into the DB that was just created using the steps above.
Below is the code for create table function:
#create table function - Make sure to check the schema as per the dataframe retrieved before
def create_table(curr):
create_table_command = ("""CREATE TABLE IF NOT EXISTS videos_new (
videoId VARCHAR(255) PRIMARY KEY,
title TEXT NOT NULL,
publishedDt DATE NOT NULL DEFAULT CURRENT_DATE,
views INTEGER NOT NULL,
likes INTEGER NOT NULL,
dislikes INTEGER NOT NULL,
comments INTEGER NOT NULL
)""")
curr.execute(create_table_command)
#define a cursor to execute the create_table function
curr = conn.cursor()
create_table(curr)
#commit the session
conn.commit()Once the above set of code is executed, you can cross-check the pgAdmin platform to see if an empty table is created similar to the snapshot below:
Image by Author
After having created the table, next up is to update and insert the data frame to the table using python script. We can see the next steps in the following weeks. That's all for now and Happy Monday!
If you like this post, please feel free to like, share, and subscribe!!
Comments