Connecting python to aws rds db in the cloud

In my last post I established a python connection to a MySQL database running on in AWS/RDS. In this follow-up I want to do essentially the same thing but I’d like to do it through a python script.

There are 3 parts to this:

  1. write the python scripts that will connect to the database
  2. upload those scripts to my cloud-based Ubuntu server running in AWS/EC2
  3. run the scripts

Step 1: Write the python scripts

This step is somewhat trivial as I’m just using some recycled open source code. Working from the outside in I have:

  1. a python script that connects to a database and prints out each row in one of the database tables

  2. a script that contains a user-defined function for connecting to a MySQL database

  3. config.ini: the function in will read configuration parameters from a file called config.ini.

Here is the file


from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config

def query_with_fetchone():
        dbconfig = read_db_config()
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        cursor.execute("Select * from USERS")
        row = cursor.fetchone()
        while row is not None:
            row = cursor.fetchone()
    except Error as e:

if __name__ == '__main__':

You can see that imports the method read_db_config from the module python_mysql_dbconfig. Here is the file that contains the needed method. This module was lifted more-or-less verbatim from this MySQL Tutorial.

from configparser import ConfigParser

def read_db_config(filename='config.ini', section='mysql'):
    """ Read database configuration file and return a dictionary object
    :param filename: name of the configuration file
    :param section: section of database configuration
    :return: a dictionary of database parameters
    # create parser and read ini configuration file
    parser = ConfigParser()

    # get section, default to mysql
    db = {}
    if parser.has_section(section):
        items = parser.items(section)
        for item in items:
            db[item[0]] = item[1]
        raise Exception('{0} not found in the {1} file'.format(section, filename))

    return db

You can also see that python_mysql_dbconfig is looking for a file called ‘config.ini’. So, finally, here is the config.ini file

database= mintyhippo
user = ******
password = *******

Step 2: Send these files to the AWS server

I have created a directory in my AWS Ubuntu server called rdstest. This is where I will be storing the aforementioned files.

Moving .py files to the AWS server. The first argument is the key-pair file allowing a connection to the remote server, the second argument is the file I want to me to the sever, the third argument is the dns or server endpoint.

(base) aarons-MacBook-Air-2:~ aaronmamula$ scp -i ~/Desktop/*****.pem ~/Desktop/AWSpython/

After the file transfer I log back into my AWS server and check that the files I want are where I want them:

(base) aarons-MacBook-Air-2:~ aaronmamula$ cd ~/Desktop
(base) aarons-MacBook-Air-2:Desktop aaronmamula$ ssh -i "*******.pem"
Welcome to Ubuntu 16.04.6 LTS (GNU/Linux 4.4.0-1101-aws x86_64)

 * Documentation:
 * Management:
 * Support:

 * Ubuntu 20.04 LTS is out, raising the bar on performance, security,
   and optimisation for Intel, AMD, Nvidia, ARM64 and Z15 as well as
   AWS, Azure and Google Cloud.

28 packages can be updated.
7 updates are security updates.

*** System restart required ***
Last login: Thu May 14 03:51:58 2020 from
ubuntu@ip-192-168-2-247:~/rdstest$ ls -al
total 24
drwxrwxr-x 2 ubuntu ubuntu 4096 May 14 03:27 .
drwxr-xr-x 6 ubuntu ubuntu 4096 May 13 04:59 ..
-rw-r--r-- 1 ubuntu ubuntu  129 Apr 23 04:51 config.ini
-rwxr-xr-x 1 ubuntu ubuntu  622 Apr 24 15:18
-rw-r--r-- 1 ubuntu ubuntu   19 Apr 12 01:35
-rw-r--r-- 1 ubuntu ubuntu  732 May 14 03:27

Step 3: Run the script

Step 3A: Install the configparser library

The python scripts outlined in Step 2 above have two external library dependencies: mysql-connector and configparser. I previously installed the mysql-connector module on the AWS server so that should be ready to go. I don’t believe I have the configparser library so I will need to pip install that.

Step 3B: Test the read_db_config() method

Test my python_mysql_dbconfig module. The read_db_config() method in is pretty simple. It reads database parameters from a file called ‘config.ini’ and, as long as that file has a ‘mysql’ section, the method returns the configuration parameters from the config.ini file. So, running this through the python interpreter on my EC2 server, I should just get back a list of connection parameters for my RDS MySQL database:

ubuntu@ip-192-168-2-247:~$ cd ~/rdstest
ubuntu@ip-192-168-2-247:~/rdstest$ python2
Python 2.7.12 (default, Apr 15 2020, 17:07:12) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from python_mysql_dbconfig import read_db_config
>>> read_db_config()
{u'host': u'', u'password': u'*********', u'user': u'*****', u'database': u'mintyhippo'}

Step 3C: Run the script

I’m going to throw in a little bonus step here just because. I’m going to inspect the script just to double-double confirm that it’s what I want to run. I open the file with the VIM text editor that comes pre-installed on the Ubuntu 16.04 AMI that I’m working with.

ubuntu@ip-192-168-2-247:~$ cd ~/rdstest
ubuntu@ip-192-168-2-247:~/rdstest$ vim


Upon inspection everything looks cool so finally, I’ll execute the file and, if everything is cool, I should get each row of the USERS tables from the mintyhippo database printed to the screen.

ubuntu@ip-192-168-2-247:~/rdstest$ python2
(1, u'aaron', u'mamula', u'')
(2, u'aaron', u'mamula', u'')
(3, u'aaron', u'mamula', u'')


Written on May 14, 2020