How to Create a Database on AWS RDS using Sagemaker with Mysql-connector in python

Create a Database/Table on AWS RDS using Sagemaker with python mysql-connector library

In this post I’ll explain how you can create a RDS database using Sagemaker with python and mysql-connector library, and also show you how you can create a table and populate with records on the rds db using a sample csv.

First thing first, you need to create a database instance with a Mysql engine, for people new to AWS analytics tools, Amazon RDS is a managed relational database service for MySQL, PostgreSQL, MariaDB, Oracle BYOL, or SQL Server.

I wont be going into the step by step details of creating a RDS database you can visit here to see how you can create a RDS database using your desired engine, a sample snapshot of a new database with mysql engine can be seen below. Note the database identifier and the engine.

The next step is for us to copy the host (endpoint) of the database we created as we’ll be making use of it in our code. Click on the database identifier to show you the details of your db. An example can be seen below

Now lets lunch Sagemaker to access a notebook instance. A notebook instance is a machine learning (ML) compute instance that helps you run Jupyter Notebook App and run your python codes. You can learn how to create a notebook instance here. A snap shot of an already created instance can be seen below. Click on open jupyter to lunch your Jupyter notebook.

Now lets get to creating a database

To start with we need to install mysql connector library. MySQL-Connector enables Python programs to access MySQL databases. Click on one of the code cell block type the following code

Next we import the library and insert the credentials. This is where we make use of the database host(endpoint) url we copied initially. Make sure it matches with your RDS instance URL, Username and passwords. Run the cell below to create the connection

Now let's create a database. Run the cursor.execute method which executes the given database operation (query or command)

We have successfully created the inventory_database

Now let create a table on the database using the create table statement and specifying the column datatypes and execute the command.

We have successfully created a table product_table_qs on the inventory_database. Now we can populate the table with a sample record using a csv file. For this article I’ve uploaded a sample csv file to my jupyter notebook rootfolder called lab2_inventory

Run the code cell block below to populate the table product_table_qs with records from the csv file.

Now you have successfully populated the table product_table_qs with some records

Now let's read data from the table created on the inventory_database and store into a pandas dataframe.

Run the code cell block below

Your output should resemble below

Cool stuff right!!

In summary, this article has explained how you can can create an RDS database/table with sagemaker using mysql-connector, as well as populating the table with a sample records from a CSV file and reading the table into a pandas dataframe.

Happy reading. Don't forget to clap and share comments!!!

Connect with me on Twitter

Connect with me on linkedin