Hi,
I have created a stored procedure on a MySQL database to create a "pivot table". To reduce network traffic the procedure generates some SQL and then executes a statement. It has no return value. I have had trouble calling the procedure from sqlachemy using the mysq-connector driver. From the searches I've done it seems that there is no standard or easy way of doing this because of the different, if any, ways .callproc() is implemented. I'm therefore looking for the best or most convenient way of integrating the procedure in SQLAlchemy code.
The procedure is:
CREATE PROCEDURE `http`.`cdn_pivot` ()
BEGIN
SET <at> <at> group_concat_max_len = 32000;
SET <at> sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
' sum(IF(cdn = ''',
cdn,
''', sites,NULL)) "'
,cdn, '"'
)
)
INTO <at> sql
FROM cdn_trend;
SET <at> stmt = CONCAT('SELECT labelDate, ',
<at> sql,
' from cdn_trend
group by labelDate');
prepare stmt from <at> stmt;
execute stmt;
deallocate prepare stmt;
SET <at> <at> group_concat_max_len = 1024;
END
And this *has* to be called and read like this:
c = con.cursor()
c.callproc("cdn_pivot")
for r in c.stored_results():
print r.fetchall()
So .execute() cannot be used. I can think of two solutions: do the first part of the procedure in Python and simply execute the generated statement. This adds a network call but also increases the testability of the code. Secondly, access the cursor directly with something likte:
c = session.connection.cursor()
c.callproc()
for r in c.stored_results():
pass
print r.fetchall()
Is this a reasonable summary of the situation?
Charlie
--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe <at> googlegroups.com.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
Visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit
https://groups.google.com/groups/opt_out.