MySQLdb, problem with query with user-defined variables

[buran]

I have SQL query I try to run using MySQL-python.
My code

import MySQLdb as mdb

   
def mysql_query(sql, schema='my_schema', do_commit=False):
    server, username, password = get_db_credentials('mysql')
    try:
        with mdb.connect(host=server, user=username, passwd=password, db='my_schema', charset='utf8') as cursor:
            cursor.execute(sql)
            # cursor.callproc('irr', ())
            if do_commit:
                conn.commit()
                return True
            else:
                res = cursor.fetchall()
                return (res,'')
    except mdb.Error, e:
        return([],'Error {} : {}'.format(e.args[0],e.args[1]))
        
# sql = 'call my_schema.irr()'
sql = '''
SET @runtot:=0.0;
SET @runtot_bv:=0.0;
SET @interest_exp:=0.0;
SET @daily_interest_expense:=0.0;
SELECT
   q1.value_date AS 'Value Date',
   q1.cash_flow AS 'Cash Flow',
   @runtot := @runtot + q1.cash_flow as 'Cash Flow Running Total',
   @daily_interest_expense := (@runtot_bv + q1.cash_flow)*(q1.daily_irr) AS 'Daily Interest Expense',
   (CASE WHEN DAYOFYEAR(q1.value_date) = 1 then @interest_exp := @daily_interest_expense
    ELSE @interest_exp := @interest_exp + @daily_interest_expense end) as 'Yearly Interest Expense',
   @runtot_bv := (@runtot_bv + q1.cash_flow)*(1+q1.daily_irr) as 'Balance Sheet Value'
FROM
   cash_flow AS q1;
'''
res = mysql_query(sql)
for row in res:
    print row

I got MySQL error
Error 2014: Commands out of sync; you can't run this command now.

I have no problem to run queries without user-defined variables. Tried to put the sql in stored procedure and call it two different ways (see commented lines), but get the same error.

Both sql query and the stored procedure work as expected in MySQL workbench.

Any ideas greatly appreciated. I'm reading different solutions re this error in the last couple of hours, but without success.

[nilamo]

I don't know a whole lot about the mysql drivers, but maybe you can't combine multiple queries into a single query?

Why calling the udf doesn't work, I don't know, but that might be a different issue.

I'm a little surprised you get any error at all, though, since .connect() returns a connection object, not a Cursor.

[buran]

I'm a little surprised you get any error at all, though, since .connect() returns a connection object, not a Cursor.

when using with MySQL-python returns cursor. Old-fashioned way - returns connection.

import MySQLdb as mdb

with mdb.connect(host='server', user='user', passwd='password', db='schema', charset='utf8') as cursor:
   print 'Using with context manager -> {}'.format(type(cursor))
   
conn = mdb.connect(host='server', user='user', passwd='password', db='schema', charset='utf8')
print 'Without context manager -> {}'.format(type(conn))
conn.close()

Output:
Using with context manager -> <class 'MySQLdb.cursors.Cursor'>
Without context manager -> <class 'MySQLdb.connections.Connection'>

In the same time when using with pymssql returns connection and I have to get cursor myself.
I know because I have to work simultaneously with MySQL and MSSQL

For the time being I decided to store the calculated values in the DB, not doing calculations on the fly in the SQL query. It works just fine.flask/

[nilamo]

In the same time when using with pymssql returns connection and I have to get cursor myself.
I know because I have to work simultaneously with MySQL and MSSQL

Gross. I checked the pep for what db drivers are supposed to do as a context manager, and it didn't mention them at all, before I made my last comment.

ANYWAY. So you CAN make other queries just fine, right? It's just this one particular one isn't working? Is there a special way to do multi-statements?

What happens if you do something much simpler, like a double-select?

select curdate();
select curdate();

Do you get two result rows, or an error parsing the query?

[buran]

Do you get two result rows, or an error parsing the query?

Well, I didn't do that so far. Now I checked and I get the same error. So it's not the user defined variables, but the multiple SQLs...

EDIT: I haven't tried that because when I tried with stored procedure, the sql statement is single one. So I decided it's the user variables.

---

There is cursor.executemany() but I'm not able to make it run. It seems to expect params, e.g. for multiple INSERTs.
Now I will look more into that direction. thanks for the idea and the "AHA-A-A!" :-)

[nilamo]

Ok, so it looks like you're only using those variables as sort of config values, since you never modify them. Why not eliminate them entirely, define the values in python, and pass them to .execute()?

OR, you can try passing multi=True to .execute():
https://dev.mysql.com/doc/connector-pyth...ecute.html

[buran]

Ok, so it looks like you're only using those variables as sort of config values, since you never modify them

No, scroll down, I use them to calculate accrued interest, sort of running total.
I will read further over the weekend. :-)

---

OK. Solved! :-)
I converted the multiple statements to list of singe statements which the execute in a loop before fetch the result and it worked.

Note that multi=True works only for mysql.connector module, but not wit MySQL-python.