How do you properly pass a parameter in a proc call with MySQL, Pandas and SQLAlchemy?

With Python 2.7, when I use sqlalchemy and pandas to run a proc call from MySQL, I get this error:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, u'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'2018-03-10\'","end_date":"\'2018-03-16\'"

start_date = datetime.date(2018, 3, 10)
end_date = datetime.date(2018, 3, 16)

proc_call = text('''call schema.proc_call('{"start_date"\\:":start_date","end_date"\\:":end_date","foo":"1", "foo2":2}')''')

results = pd.read_sql_query(proc_call, con=connection, params={"start_date": start_date, "end_date": end_date})

It works when the dates are hard coded, but I need them to be parameters.

1 answer

  • answered 2018-03-20 17:23 Ilja Everilä

    Looks like you are trying to pass a JSON string to proc_call, so do just that. Create a suitable Python construct, encode as JSON, and pass it as the argument:

    import json
    
    start_date = datetime.date(2018, 3, 10)
    end_date = datetime.date(2018, 3, 16)
    arg0 = {"start_date": start_date, "end_date": end_date, "foo": "1", "foo2": 2}
    
    proc_call = text('call schema.proc_call(:arg0)')
    
    results = pd.read_sql_query(proc_call, con=connection,
                                params={"arg0": json.dumps(arg0)})
    

    The error is the result of trying to use placeholders inside a string literal. The end result is something along the lines of

    '{"start_date":"'2018-03-10'", ...}'
    

    which is the string literal '{"start_date":"' followed by 2018-03-10 and so on, which is not valid syntax.


    You could also take using bound parameters a step further and specify their type explicitly:

    from sqlalchemy import bindparam, JSON
    
    start_date = datetime.date(2018, 3, 10)
    end_date = datetime.date(2018, 3, 16)
    arg0 = {"start_date": start_date, "end_date": end_date, "foo": "1", "foo2": 2}
    
    proc_call = text('call schema.proc_call(:arg0)')
    proc_call = proc_call.bindparams(bindparam('arg0', type_=JSON))
    
    results = pd.read_sql_query(proc_call, con=connection, params={"arg0": arg0})