Skip to content

Instantly share code, notes, and snippets.

@samredai
Created December 11, 2018 03:31
Show Gist options
  • Save samredai/8ab7f7b754d0cc0ad75aa706a462a7ca to your computer and use it in GitHub Desktop.
Save samredai/8ab7f7b754d0cc0ad75aa706a462a7ca to your computer and use it in GitHub Desktop.
Python: Function to call a MySQL stored procedure and return the results as JSON nested within "data"
import simplejson as json
import MySQLdb
#Better practice is to load and decrypt these values from a file
dbname = 'databaseName'
dbuser = 'username'
dbpass = 'password'
dbhost = 'host
def procedureToJSON(procedureName, inputs=''):
dbconn=MySQLdb.connect(
database=dbname, user=dbuser, password=dbpass, host=dbhost)
try:
with dbconn.cursor(MySQLdb.cursors.DictCursor) as cursor:
if inputs == '':
cursor.callproc(procedureName)
data = {'data': cursor.fetchall()}
else:
cursor.callproc(procedureName,inputs)
data = {'data': cursor.fetchall()}
except Exception as e:
error = "ERROR: " + str(e)
return error
return json.dumps(data,indent=4)
#Example usage
tableData = procedureToJSON('inventoryActiveInventory',[storeid_requested,])
#tableData will contain the results from the MySQL stored procedure in JSON format nested within "data"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment