Working with stored procedures

[Charlie Clark]

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?

Charlie

[Michael Bayer]

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

[Charlie Clark]

Am 19.05.2013, 17:09 Uhr, schrieb Michael Bayer <mik...@zzzcomputing.com>:

> 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()

Thanks for explaining the nested connection stuff. So, you recommend
completely bypassing SQLa for this sort of thing? I found I was doing that
anyway because of the inability to map the results to any models.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Michael Bayer]

On May 19, 2013, at 12:57 PM, "Charlie Clark" <charli...@clark-consulting.eu> wrote:

> Am 19.05.2013, 17:09 Uhr, schrieb Michael Bayer <mik...@zzzcomputing.com>:
>
>> 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()
>
> Thanks for explaining the nested connection stuff. So, you recommend completely bypassing SQLa for this sort of thing? I found I was doing that anyway because of the inability to map the results to any models.

Yes, SQLA actually doesn't get into callproc() or any of that at all right now. Not that we can't someday, but it hasn't been a priority to build around that. (It is something I'm curious about, stored procedure support, but I'd need a reason to go there).

[Charlie Clark]

Am 20.05.2013, 00:53 Uhr, schrieb Michael Bayer <mik...@zzzcomputing.com>:

> Yes, SQLA actually doesn't get into callproc() or any of that at all
> right now. Not that we can't someday, but it hasn't been a priority to
> build around that. (It is something I'm curious about, stored
> procedure support, but I'd need a reason to go there).

Hi Mike,

thanks for the help and the explanation. I can understand why support
isn't there - there is so much that can be done with SPs. FWIW and for
future reference as I didn't find much myself when I searched, I've put
the code into practice:

https://bitbucket.org/charlie_x/python-httparchive/src/a9a2c30c8ffb4e1d39720ad44368284922e0e94f/httparchive/httparchive/views/trends.py?at=default#cl-341

I do think it would be useful to support something like pivots - whether
these are done as stored procedures or "table functions" like Postgres
http://www.postgresql.org/docs/9.1/static/tablefunc.html ie. a view on a
normalised table which transposes rows into columns, the dynamic nature of
which causes I problems for the ORM, I think.