Created
December 11, 2018 03:31
-
-
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"
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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