# 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. fetchone.py: a python script that connects to a database and prints out each row in one of the database tables

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

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

Here is the fetchone.py file

#!/usr/bin/python

from mysql.connector import MySQLConnection, Error

def query_with_fetchone():
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("Select * from USERS")

row = cursor.fetchone()

while row is not None:
print(row)
row = cursor.fetchone()

except Error as e:
print(e)

finally:
cursor.close()
conn.close()

if __name__ == '__main__':
query_with_fetchone()


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


from configparser import ConfigParser

""" 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]
else:

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

[mysql]
host= mams-california.c65i4tmttvql.us-west-1.rds.amazonaws.com
database= mintyhippo
user = ******


## 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/python_mysql_dbconfig.py ubuntu@ec2-54-153-106-13.us-west-1.compute.amazonaws.com:~/rdstest  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" ubuntu@ec2-54-153-106-13.us-west-1.compute.amazonaws.com Welcome to Ubuntu 16.04.6 LTS (GNU/Linux 4.4.0-1101-aws x86_64) * Documentation: https://help.ubuntu.com * Management: https://landscape.canonical.com * Support: https://ubuntu.com/advantage * 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. https://ubuntu.com/blog/ubuntu-20-04-lts-arrives 28 packages can be updated. 7 updates are security updates. *** System restart required *** Last login: Thu May 14 03:51:58 2020 from 67.180.160.152 ubuntu@ip-192-168-2-247:~$
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'mams-california.c65i4tmttvql.us-west-1.rds.amazonaws.com', u'password': u'*********', u'user': u'*****', u'database': u'mintyhippo'} >>>  ### Step 3C: Run the fetchone.py script I’m going to throw in a little bonus step here just because. I’m going to inspect the fetchone.py 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 fetchone.py  Upon inspection everything looks cool so finally, I’ll execute the fetchone.py 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 fetchone.py
(1, u'aaron', u'mamula', u'aaron.mamula@noaa.gov')
(2, u'aaron', u'mamula', u'aaron.mams@gmail.com')
(3, u'aaron', u'mamula', u'aamamula@ucsc.edu')



Yatzee!

Written on May 14, 2020