0

I have a Python application, in which I'm calling a MySQL stored procedure from my view, like so:

import mysql.connector
proc = 'audit_report'
parms = [data['schoolid'], dateToISO(data['startdatedefault'],'from'), dateToISO(data['enddatedefault'],'to'), joinIntList(data['studypgms'], joinWith), joinIntList(data['fedpgms'], joinWith), joinIntList(data['statuses'], joinWith), data['fullssndefault']]
conn = mysql.connector.connect(user='usr', database='db', password='pwd')
cursor = conn.cursor(dictionary=True)
cursor.callproc(proc, parms)
for result in cursor.stored_results():
    print(result.fetchall())

I am getting the data returned as a list of tuples, the standard output. Since I'm using connector version 2.1.7, the docs say adding

dictionary=True

to the cursor declaration should cause the rowset to be returned as a list of dictionaries, with column name as the key of each dictionary. Main difference between my application and the example in the docs is that I'm using cursor.callproc(), whereas the examples use cursor.execute() with actual sql code.

I tried 

print(cursor.column_names)

to see if I could get the column names that way, but all I get is

('@_audit_report_arg1', '@_audit_report_arg2', '@_audit_report_arg3', '@_audit_report_arg4', '@_audit_report_arg5', '@_audit_report_arg6', '@_audit_report_arg7')

which looks more like the input parameters to the stored procedure.

Is there any way to actually get the column names of the returned data? The procedure is somewhat complex and contains crosstab-type manipulation, but calling the same stored procedure from MySQL Workbench happily supplies the column names.

Normally, knowing what the output is supposed to be, I could hard-code column names, except this procedure crosstabs the data for the last few columns, and it is unpredictable what they will be until after the query runs. Thanks...

RMittelman
  • 319
  • 3
  • 16
  • https://stackoverflow.com/a/5058950/7383995 this might help you !!! – legend-is-back Aug 23 '17 at 07:43
  • Thanks @legend-is-back, but didn't help. That article is for MySqlDb, which doesn't support Python 3. I'm using mysql connector/Python. When I print cursor.description, I get: [('@_check_register_report_arg1', 250, None, None, None, None, 1, 0), ('@_check_register_report_arg2', 250, None, None, None, None, 1, 0), etc. No column names, just info on parameters. – RMittelman Aug 23 '17 at 13:37
  • if you are using python 3 I would suggest using pymysql, i use oymsyql for python 3 and mysqldb for 2,7 and the solution it works in both – legend-is-back Aug 23 '17 at 19:41

1 Answers1

1

You can use pymysql in python3 and it should work fine !!

import pymysql.cursors
connection = pymysql.connect(host='',
                             user='',
                             password='',
                             db='test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "query"
        cursor.execute(sql)
        result = cursor.fetchone()
        num_fields = len(cursor.description)
        field_names = [i[0] for i in cursor.description]
        print (field_names)
finally:
    connection.close()
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
legend-is-back
  • 443
  • 2
  • 12
  • Well, that worked like a charm. Now I have records like: {'EnrollmentStatus': 'Continuing', 'Amount': Decimal('1732.00'), 'CkNo': 71515, 'CheckTo': 'School', 'CkDate': datetime.datetime(2015, 7, 15, 0, 0), 'FedPgmName': 'DL Subsidized', 'AwardYear': '1516', 'StudentName': 'xxx redacted xxx': 'Diagnostic Medical Sonography I', 'FedPgmID': 8, 'formattedSSN': 'xxxx-xx-xxxx', 'PayPeriod': '1', 'SSN': 'xxxxxxxxx', 'SchedDate': datetime.datetime(2015, 7, 15, 0, 0), 'CampusName': 'Main'}. Perfect! – RMittelman Aug 23 '17 at 21:51
  • Slight strangeness: I get this in my server console now: /Library/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/pymysql/cursors.py:99: Warning: (1292, "Incorrect date value: '2015-07-16 23:59:59' for column '_toDate' at row 1") self._do_get_result() Don't know what that's all about, but it's only a warning so should be ok, right? – RMittelman Aug 23 '17 at 21:54