Python way to query a database and execute a shell command

The following example shows how to connect to a database, query the database and use the result values to perform some operation using python.

In this script, there's a local database which has some employee inventory, We will query some record and send the result as an email notification.


# Including shebang and title 

#!/usr/bin/python
# Python script to query a database and send mail alert using shell command

# Importing modules and Setup mysql connection

# Import Modules

import MySQLdb
import sys
import subprocess

# Setting up DB Connection

connection = MySQLdb.connect (host = "localhost", user = "bingo", passwd = "kurkure", db = "emp_db")

# Cursor Object
cursor = connection.cursor ()

cursor.execute ("select ID,emp_email,manager_email where keyvalue='somevalue';")

# Reading rows and storing to variables 

# Fetching results
data = cursor.fetchall ()
for row in data :
        ID = row[0]
        emp_email = row[1]
        manager_email = row[2]
        print hash,user_email,manager_email;

# Sending email notification by running a shell script

        runn = subprocess.check_call("/<path-to-script>/mail %s %s %s" % (str(row[0]), str(row[1]), str(row[2])), shell=True)

cursor.close ()
connection.close ()

sys.exit()

######## /<path-to-script>/mail script will look like -

'''
echo -e "Hello, \n\n\n Employee notification for - $1) $2  \n\n\n Thanks" | mail -r "do-not-reply@247-inc.net"  -s $"New Record - $2" $3

'''