Introduction
Today we will discuss on everything you need to know about Python Boto3 RDS: Postgres, MySQL, Connect, List, Start, Stop, Delete in simple and easy to follow guide.
This is your ultimate source to quickly understand and get hands on, on how to implement Boto3 RDS and why it’s useful for your project.
More specifically we will discuss:
- Why Boto3 Sessions are useful
- Setting things up
- Creating, closing and mocking sessions
All the code and examples that will be provided below can be found in my git repo here.
I have been working in the Software industry for over 23 years now and I have been a software architect, manager, developer and engineer. I am a machine learning and crypto enthusiast with emphasis in security. I have experience in various industries such as entertainment, broadcasting, healthcare, security, education, retail and finance.
How to install Boto3 to connect to RDS
We are going to begin on setting up our environment in particular installing any dependencies and packages necessary. I assume you already have Python 3 installed and running in your system. If you haven’t you can check the official website of Python to get that installed before proceeding forward. Furthermore I’m assuming you already have access to an AWS EC2 account and own an SDK key. If you do not you can sign up for free with Amazon here to get started. I’m also making the assumption you have the Python Package manager pip. If you haven’t you can look into this guide to learn more about installing it.
Now that we have the basic requirements out of the way we can dive in and start setting up the system. Also I want to note that all of the code you will find in this guide can be found in github here.
How to Create a Python Virtual Environment for Boto3 RDS
- First install the virtual env using the python command: ‘pip install virtualenv’
- Then create a new virtual environment
- Finally you need to activate your virtual environment so we can start installing packages, please see below
main alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > virtualenv env
created virtual environment CPython3.8.6.final.0-64 in 3773ms
creator CPython3Posix(dest=/home/alex/code/unbiased-coder/python-boto3-ec2-guide/env, clear=False, no_vcs_ignore=False, global=False)
seeder FromAppData(download=False, pip=bundle, setuptools=bundle, wheel=bundle, via=copy, app_data_dir=/home/alex/.local/share/virtualenv)
added seed packages: pip==21.2.4, setuptools==57.4.0, wheel==0.37.0
activators BashActivator,CShellActivator,FishActivator,PowerShellActivator,PythonActivator
main alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > source env/bin/activateHow to Install pip dependencies for Boto3 RDS
Next we need to go ahead and install the Python dependencies to be able to use the boto3 library. You can do this by running the pip tool as shown below. Keep in mind make sure your virtual environment is activated before you run this step. If you wish to use it without having a virtual environment which I do not recommend you can go ahead and simply install it globally in your user account.
main (env) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > pip install boto3 python-dotenv
Collecting boto3
Downloading boto3-1.18.46-py3-none-any.whl (131 kB)
|████████████████████████████████| 131 kB 1.1 MB/s
Collecting s3transfer<0.6.0,>=0.5.0
Downloading s3transfer-0.5.0-py3-none-any.whl (79 kB)
|████████████████████████████████| 79 kB 2.8 MB/s
Collecting botocore<1.22.0,>=1.21.46
Downloading botocore-1.21.46.tar.gz (8.2 MB)
|████████████████████████████████| 8.2 MB 11.5 MB/s
Collecting jmespath<1.0.0,>=0.7.1
Downloading jmespath-0.10.0-py2.py3-none-any.whl (24 kB)
Collecting python-dateutil<3.0.0,>=2.1
Downloading python_dateutil-2.8.2-py2.py3-none-any.whl (247 kB)
|████████████████████████████████| 247 kB 10.1 MB/s
Collecting urllib3<1.27,>=1.25.4
Downloading urllib3-1.26.7-py2.py3-none-any.whl (138 kB)
|████████████████████████████████| 138 kB 9.9 MB/s
Collecting six>=1.5
Downloading six-1.16.0-py2.py3-none-any.whl (11 kB)
Building wheels for collected packages: botocore
Building wheel for botocore (setup.py) ... done
Created wheel for botocore: filename=botocore-1.21.46-py3-none-any.whl size=7933638 sha256=ee2d8a7f5bd91a7d2711b529706902a4a2a8fba97e69493757a8d1d461296d69
Stored in directory: /home/alex/.cache/pip/wheels/db/2a/b6/37624d07c0d7572bff3d08bd4bfd2c94b121f693278cd1ae77
Successfully built botocore
Collecting python-dotenv
Downloading python_dotenv-0.19.0-py2.py3-none-any.whl (17 kB)
Installing collected packages: six, urllib3, python-dateutil, jmespath, botocore, s3transfer, boto3, python-dotenv
Successfully installed boto3-1.18.46 botocore-1.21.46 jmespath-0.10.0 python-dateutil-2.8.2 s3transfer-0.5.0 six-1.16.0 urllib3-1.26.7 python-dotenv-0.19.0The two packages we installed are:
- boto3: This is the core Python AWS library we will be using in this guide
- dotenv: We will use this library to pass in sensitive information to it that we do not want to have hardcoded in our code such as the AWS credentials
Verifying it works
Now that we have setup our system we need to verify the library is installed properly and it works. You can do this by simply checking in a python shell using the following command shown below, if you encounter an error please delete your virtual environment and try again. If the problem still persists please drop me a line below and I will try to help you.
main ✚ (env) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python
Python 3.8.6 (default, Oct 23 2020, 14:59:35)
[GCC 9.3.0] on msys
Type "help", "copyright", "credits" or "license" for more information.
>>> import boto3
>>> boto3.__version__
'1.18.46'
>>> import dotenv
>>> quit()
As you can see above the boto3 library got loaded successfully and the version is 1.18.46. This is as of Late 2021 so this may be different in your system based on when you install it.
How to add an AWS user for Boto3 RDS
Before we start writing code and communicating with our API we need to get the API and Secret key that we will use alongside with the boto3 library that we previously setup. This is basically setting up permissions for the user to be able to manipulate and connect to the AWS infrastructure. First navigate to the IAM section and click add user. This will invoke the dialog as shown below.
The important part we need to select is the Access key – Programmatic access. This will allow us to programmatically control things using our code. The next important part is setting the correct permissions in order to access AWS.
How to select an AWS policy for RDS
You can go ahead and search for the RDS policy you would want to control using Boto3. In this example we will choose the RDS AWS service with full permissions as shown in the picture below. If you want to know more about controlling other AWS services and setting them up I have written a lot of articles that you can find the in my AWS section here.
So you can go ahead and click next and on the next dialog select the Attach existing policies directly. From this tab we need to select full permissions for RDS as discussed earlier.
How to get Boto3 RDS credentials
Now that we have selected the permissions to attach to our profile we can go ahead and add the user.
Once the user is created you should get a confirmation screen as seen below with two important parts:
- API Key: This is similar to your username which your code will be using to authenticate itself to the AWS service (in this case the IAM service)
- Secret Key: This is similar to the password which will be used to give you the permissions necessary to access the AWS service
The confirmation screen below shows how this would look like.
Once important thing to note here is that the Secret key is not shown but you will need to expand and save it now as we will be using it later in the document when we talk on how to setup an AWS Session.
How to Create an RDS MySQL Database
Before we proceed into writing any code we need to go through the process to explain how to create an RDS MySQL database. To do this we need to logon to our AWS Console and navigate to the RDS section. Once you land that you should see something called Create database as seen below.
Go ahead and click on it so we can invoke the process of creating our Database that we will be using in this Guide. The first thing we need to do is select between a standard process of creating it or an easy way, for now select the standard create because we will be fine tuning some things.
When prompted with the Dialog shown above you need to select the MySQL backend along with the version shown below. This tutorial has been tested with that specific version of the MySQL backend but it will likely work with any other version of MySQL.
If you are following this tutorial you can use the same names for the databases, if you already have instances feel free to adjust accordingly but know that some of the code in the github repo of this tutorial may need adjustments. Next you need to setup a username and password for the database, once you select that please keep a note for it so we can use it later when necessary in order to be able to connect to it.
Once this is done the rest of the options are all default which means you don’t need to adjust anything else. If this is done scroll all the way to the bottom and click on Create database.
Provisioning your new database instance may take a few minutes to complete. Once this is done the status will change from Creating to Available. As it can be seen in the screen below.
How to Create an RDS Postgres Database
Now that we have created a MySQL RDIS database instance we need to go ahead and create another one for a Postgress RDS instance so we can demonstrate with our code some specific RDS features and functionalities which require more than one.
In order to do this you need to repeat the process we did above for MySQL RDS but this time you need to select a RDS Postgres instance in the image selection list. Something things to notes before you proceed are listed below (if you already have one setup ignore the following items):
- Select Dev account so billing can remain low
- Select a Micro instance
- Use Magnetic disks (this will keep cost to bare minimum)
- Use the lowest disk space possible that the platform allows you
Off course if your requirements are different and want to go straight for a production build feel free to adjust accordingly and bump up the settings. The above configuration is for you to learn and practice at a minimal to no cost.
How Setup a Boto3 RDS Profile to establish a session
For setting up our Boto3 session profile we will be using the DOTENV Python library that we discussed earlier on how to install. For this we can create an env file that looks similar to the one below.
AWS_ACCESS_KEY=ABCDE
AWS_SECRET_KEY=abcde
AWS_REGION=us-east-1As shown above you can see three variables. The first two we already have from the setup we did earlier. So for the AWS_ACCESS_KEY and AWS_SECRET_KEY you can just fill in the information from the previous section.
Finally for the region it will be the default region you used when setting up the key. If you do not know what that is you can log in the AWS console and see it in the top right (unless you changed it). This looks something like this:
In my case it’s using Frankfurt which equates to eu-central-1. This can be easily seen if you expand the menu at the top where it shows the country.
Now that you have the region name you can fill it in your ENV file. The next step is to look into the code that lets us establish this connection and initialize the session. This concludes the infra side of things in terms of work, now we can focus on Python Boto3 RDS: Postgres, MySQL, Connect, List, Start, Stop, Delete on the programmatic side of things.
How to list RDS MySQL/Postgres Instances
The first thing we need to do is list the RDS MySQL and Postgres Instances. The reason we added instances earlier is to demonstrate this functionality here. In order to do that we will be leveraging the code of our Boto3 boiler plate connector. I have described how this works in detail in another article which you can find here if you need more context.
Once we initialize the session the next step is to get an RDS object which we will be using across this article to perform operations in the RDS instances we have previously created. The code below iterates through all the RDS instances in the region specified in our ENV file as described earlier. There’s a way to iterate across regions but in this article we will be focusing in one specific region that we have defined. The function that gives us all the instances is describe_db_instances. Once we call that we can simply print out some basic information about each instance. This can be seen in the code below.
import boto3_helper
session = boto3_helper.init_aws_session()
rds = session.client('rds')
db_instances = rds.describe_db_instances()['DBInstances']
for db_instance in db_instances:
print ('DB Name: ', db_instance['DBInstanceIdentifier'])
print ('Engine: ', db_instance['Engine'])
print ('Status: ', db_instance['DBInstanceStatus'])
print ('')The expected output if we were to execute the code above is to see the instances we created earlier in this guide, in particular the MySQL and the Postgres RDS Instances. Executing the code will have the output shown below.
main ● (venv) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python ./boto3_rds_list_instances.py
DB Name: unbiased-coder-mysql-db
Engine: mysql
Status: stopping
DB Name: unbiased-coder-postgres-db
Engine: postgres
Status: stopping
main ● (venv) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python ./boto3_rds_list_instances.py
DB Name: unbiased-coder-mysql-db
Engine: mysql
Status: Available
DB Name: unbiased-coder-postgres-db
Engine: postgres
Status: AvailableThe first time we execute we issued a stop command from the RDS AWS console to see if the status is reflected properly and the second execution we started them again. As it can be seen the instances and the database names we previously created is reflected properly in our output so we know the command listing of the RDS Instances works.
How to Stop RDS MySQL/Postgres Instances
As a next step we will proceed into simply stopping our RDS Instances. In order to do that we need to follow a similar code path but in this case we will be using the stop_db_instance function which is part of the RDS client.
import boto3_helper
session = boto3_helper.init_aws_session()
rds = session.client('rds')
db_instances = rds.describe_db_instances()['DBInstances']
for db_instance in db_instances:
db_identifier = db_instance['DBInstanceIdentifier']
print ('Stopping instance with identifier: %s'%db_identifier)
rds.stop_db_instance(DBInstanceIdentifier=db_identifier)Once this is invoked we will be connecting to the RDS service and issuing the command for each of the instances we have.
main ● (venv) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python ./boto3_rds_stop_instances.py
Stopping instance with identifier: unbiased-coder-mysql-db
Stopping instance with identifier: unbiased-coder-postgres-dbThe initial output we will is is that the instances change from Available to Stopping state. The process of starting and stopping RDS instances takes some time so we need to wait until they are fully stopped.
If we keep refreshing we will notice that the Status successfully is changed to Stopped as it can be seen below.
How to Start RDS MySQL/Postgres Instances
Starting the RDS Instances is similar to stopping them. The only difference in this case is that we will be using the start_db_instance function instead of the stop_db_instance.
import boto3_helper
session = boto3_helper.init_aws_session()
rds = session.client('rds')
db_instances = rds.describe_db_instances()['DBInstances']
for db_instance in db_instances:
db_identifier = db_instance['DBInstanceIdentifier']
print ('Starting instance with identifier: %s'%db_identifier)
rds.start_db_instance(DBInstanceIdentifier=db_identifier)Quickly executing the code should have a similar output when we dispatch the Start RDS instance command.
main ● (venv) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python ./boto3_rds_start_instances.py
Starting instance with identifier: unbiased-coder-mysql-db
Starting instance with identifier: unbiased-coder-postgres-dbOne thing to note is that both the Start and Stop RDS Instance functions take as a parameter the InstanceIdentifier. This unique identifier is used across all the RDS Instance commands when using the Boto3 library.
Similar to stopping the Starting state takes a few moments to change to Available as it can be seen below.
How to Delete/Remove RDS Instances
The final command we need to test out is Deleting the RDS instances we previously created. Again the process of doing that is very similar to starting and stopping RDS instances with the only difference that we will be invoking the delete_db_instance command. We need to pass to it two additional parameters:
- SkipFinalSnapshot: Since we used these instances for testing we don’t want to wait for a final snapshot of our instances
- DeleteAutomatedBackups: Similar to above we don’t care to keep any backups of the test RDS instances
One thing to note here is that if you obviously need those do not issue that command at all.
import boto3_helper
session = boto3_helper.init_aws_session()
rds = session.client('rds')
db_instances = rds.describe_db_instances()['DBInstances']
for db_instance in db_instances:
db_identifier = db_instance['DBInstanceIdentifier']
print ('Deleting instance with identifier: %s'%db_identifier)
rds.delete_db_instance( DBInstanceIdentifier=db_identifier,
SkipFinalSnapshot=True,
DeleteAutomatedBackups=True)
Once we execute that code the deletion will be dispatched and the instances will start being deleted from the RDS system.
main ● (venv) alex@DYNAMH ~/code/unbiased-coder/python-boto3-rds-guide > python ./boto3_rds_delete_instances.py
Deleting instance with identifier: unbiased-coder-mysql-db
Deleting instance with identifier: unbiased-coder-postgres-dbThe initial state will be deleting as we have seen before with Starting and Stopping RDS instances.
After a few minutes the instances would disappear from your list and you will no longer be able to access them. Along with that any backups and snapshots will also be deleted.
Overall we covered the basic functionality of programmatically managing RDS instances. This should cover most of your use cases and get you started with having automated code to execute such tasks. If you would like to read further and get into more details of RDS you can read the RDS Boto3 Documentation here on Amazon’s website.
Conclusion
If you found Python Boto3 RDS: Postgres, MySQL, Connect, List, Start, Stop, Delete useful and you think it may have helped you please drop me a cheer below I would appreciate it.
If you have any questions, comments please post them below I check periodically and try to answer them in the priority they come in.
Also if you have any corrections please do let me know and I’ll update the article with new updates or mistakes I did.
Which RDS database do you prefer?
If you would like to learn more about AWS Boto3 interfaces please take a look at the articles below: