On May 19, 2013, at 8:53 AM, Charlie Clark <charlie.cl...@clark-consulting.eu>
wrote:
> 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 @@group_concat_max_len = 32000;
> SET @sql = NULL;
> SELECT GROUP_CONCAT(DISTINCT CONCAT( ' sum(IF(cdn = ''', cdn, ''',
> sites,NULL)) "' ,cdn, '"' ) )
> INTO @sql
> FROM cdn_trend;
> SET @stmt = CONCAT('SELECT labelDate, ', @sql, ' from cdn_trend group by
> labelDate'); prepare stmt from @stmt; execute stmt; deallocate prepare stmt;
> SET @@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?
you want to pull a DBAPI cursor from SQLAlchemy's notion of the DBAPI
connection, like this:
dbapi_conn = session.connection().connection
cursor = dbapi_conn.cursor()
# etc.
this is:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#sqlalchemy.orm.session.Session.connection
then
http://docs.sqlalchemy.org/en/rel_0_8/core/connections.html#sqlalchemy.engine.Connection.connection
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@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.
