Daniel Daniel - 6 months ago 26
Bash Question

How to Include a Variable as part of Subprocess Call in Python

I am having trouble inserting a variable as part of a database query, when calling the query with Python's

subprocess.call
method.

Here is my code:

import subprocess
date = '2015-08-01'
subprocess.call(['/usr/local/mysql/bin/mysql -h localhost -u root -p -D my_db -N -e "SELECT SUBDATE("'" + date + "'", INTERVAL -1 DAY);"'], shell=True)


This code returns
NULL
, when it should return a date.

What is the correct way to include the variable
date
as part of the subprocess call?

Answer

Here are two ways to include this variable:

subprocess

Use an escape character \ along with the quotation marks ' and string formatting:

import subprocess
date = '2015-08-01'
subprocess.call(['/usr/local/mysql/bin/mysql -h localhost -u root -p -D my_db -N -e "SELECT 
SUBDATE(\'%s\', INTERVAL -1 DAY);"' % (date)], shell=True)

MySQLdb

The preferred way to work with MySQL queries in Python, though, is to use a binding like MySQLdb:

import MySQLdb
conn = MySQLdb.connect(db="my_db", user="root", host="localhost")
curs = conn.cursor()
curs.execute("""SELECT SUBDATE(%s, INTERVAL -1 DAY)""", [date])
results = curs.fetchall()
Comments