Try:
import MySQLdb
conn.MySQLdb.connect(db=db,
user=user,
passwd=passwd,
host=host,
port=port,
charset=charset)
curr = conn.cursor()
curr.execute('CALL sp_recursive_start(1);')
print curr.fetchall() # (1)
conn.rollback()
(1) Try with and without this line.
On Monday, 25 February 2013 12:06:09 UTC-6, __pyslan__ wrote:
>
> I have not the faintest idea how to do that, and this feature is essential
> for the project. I can not build a recursive function due to performance.
> With great respect I ask, should I give up web2py in this case?
>
>
>
>
> On Mon, Feb 25, 2013 at 2:28 PM, Niphlod <[email protected]
> <javascript:>>wrote:
>
>> well, I doubt that workbench uses a python dbapi.
>> From where I stand, you should try to make that work on the adapter (i.e.
>> without web2py) and see if there it works....
>>
>> in my pov, mysql returns different resultsets for that kind of stored
>> proc: one is the result of the line inserted in a loop, and the other one
>> is the resultset that is fetched back from the temporary table. I'm no
>> dbapi expert but I'd say that this is not supported and has to be managed
>> on the adapter side.
>>
>>
>> http://geert.vanderkelen.org/multiple-result-sets-in-mysql-connectorpython/
>>
>>
>> On Monday, February 25, 2013 5:55:16 PM UTC+1, __pyslan__ wrote:
>>
>>> But when I make these calls in MySQL Workbench, the result is correct.
>>>
>>> Look the attached image, please...
>>>
>>>
>>>
>>>
>>> On Mon, Feb 25, 2013 at 1:24 PM, Massimo Di Pierro <
>>> [email protected]> wrote:
>>>
>>>> The code below is equivalent to:
>>>>
>>>> sql> CALL sp_recursive_start(1);
>>>> sql> ROLLBACK;
>>>>
>>>> web2py is not adding anything to it.
>>>> This demonstrates that the problem is completely with the code in
>>>> "sp_recursive_start".
>>>> You are getting an OperationError from the database on rollback.
>>>>
>>>> My guess is that there is one of these two problems or both:
>>>>
>>>> 1) you cannot declare the same stored procedure at every request. You
>>>> should declare it once outside of web2py.
>>>> 2) you need to explicitly commit after you declare a stored procedure.
>>>> You cannot roll it back.
>>>>
>>>> Let us know if this helps.
>>>>
>>>> Massimo
>>>>
>>>>
>>>> On Monday, 25 February 2013 08:38:30 UTC-6, __pyslan__ wrote:
>>>>
>>>>> I got it:
>>>>>
>>>>> In [1]: fields = [db.auth_user.generation, db.auth_user.id,
>>>>> db.auth_user.first_name]
>>>>>
>>>>> In [2]: raw_rows = db.executesql('CALL sp_recursive_start(1);',
>>>>> fields=fields)
>>>>>
>>>>> In [3]: db.rollback()
>>>>> ------------------------------****------------------------------****
>>>>> ---------------
>>>>> ProgrammingError Traceback (most recent call
>>>>> last)
>>>>>
>>>>> /home/ctx/PROJECTS/WEB/web2py/****applications/myapp/models/006_****menu.py
>>>>>
>>>>> in <module>()
>>>>> ----> 1 db.rollback()
>>>>>
>>>>> /home/ctx/PROJECTS/WEB/web2py/****gluon/dal.pyc in rollback(self)
>>>>> 7286
>>>>> 7287 def rollback(self):
>>>>> -> 7288 self._adapter.rollback()
>>>>> 7289
>>>>> 7290 def close(self):
>>>>>
>>>>> /home/ctx/PROJECTS/WEB/web2py/****gluon/dal.pyc in rollback(self)
>>>>> 1668
>>>>> 1669 def rollback(self):
>>>>> -> 1670 if self.connection: return self.connection.rollback()
>>>>> 1671
>>>>> 1672 def close_connection(self):
>>>>>
>>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this
>>>>> command now")
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Feb 25, 2013 at 11:24 AM, Massimo Di Pierro <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> This is a database issue more than a web2py issue but I am interested
>>>>>> to get to the bottom of it.
>>>>>>
>>>>>> Can you ty open a web2py shell
>>>>>>
>>>>>> $ python web2py.py -S yourapp -M
>>>>>> >>> fields = [db.auth_user.generation, db.auth_user.id, db.auth_user.
>>>>>> first_name]
>>>>>>
>>>>>> >>> raw_rows = db.executesql('CALL sp_recursive_start(1);',
>>>>>> >>> fields=fields)
>>>>>>
>>>>>>
>>>>>> >>> db.rollback()
>>>>>>
>>>>>> What do you get?
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Monday, 25 February 2013 07:39:24 UTC-6, __pyslan__ wrote:
>>>>>>
>>>>>>> No, Massimo, is not ... With or without the try... except... the
>>>>>>> error is the same ... I've tried this several times.
>>>>>>>
>>>>>>> The code of gist:
>>>>>>> https://gist.github.com/**pyslan****/5007364<https://gist.github.com/pyslan/5007364>
>>>>>>> (without
>>>>>>> try... except...) print it on the terminal server:
>>>>>>>
>>>>>>> <Row {'generation': 1, 'first_name': 'Root', 'id': 1}>
>>>>>>> <Row {'generation': 2, 'first_name': 'Teste', 'id': 2}>
>>>>>>> ERROR:web2py:Traceback (most recent call last):
>>>>>>> File "/home/ctx/PROJECTS/WEB/**web2py****/gluon/main.py", line
>>>>>>> 632, in wsgibase
>>>>>>> BaseAdapter.close_all_**instance****s('rollback')
>>>>>>> File "/home/ctx/PROJECTS/WEB/**web2py****/gluon/dal.py", line
>>>>>>> 543, in close_all_instances
>>>>>>> db._adapter.close(action)
>>>>>>> File "/home/ctx/PROJECTS/WEB/**web2py****/gluon/dal.py", line
>>>>>>> 523, in close
>>>>>>> getattr(self, action)()
>>>>>>> File "/home/ctx/PROJECTS/WEB/**web2py****/gluon/dal.py", line
>>>>>>> 1670, in rollback
>>>>>>> if self.connection: return self.connection.rollback()
>>>>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this
>>>>>>> command now")
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Sat, Feb 23, 2013 at 5:58 PM, Massimo Di Pierro <
>>>>>>> [email protected]> wrote:
>>>>>>>
>>>>>>>> This is causing the problem. What if you remove the try... except?
>>>>>>>> What ticket do you get?
>>>>>>>>
>>>>>>>> try:
>>>>>>>>
>>>>>>>>
>>>>>>>> raw_rows = db.executesql('CALL sp_recursive_start(1);',
>>>>>>>> fields=fields)
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> except Exception, e:
>>>>>>>> print 'ERROR NOW:', e
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> db.rollback()
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Saturday, 23 February 2013 12:58:44 UTC-6, __pyslan__ wrote:
>>>>>>>>>
>>>>>>>>> After reading this
>>>>>>>>> thread<http://en.usenet.digipedia.org/thread/16137/52728/>,
>>>>>>>>> as Anthony suggested, I tried this:
>>>>>>>>>
>>>>>>>>> import MySQLdb
>>>>>>>>> from gluon.dal import MySQLAdapter
>>>>>>>>> MySQLAdapter.driver = MySQLdb
>>>>>>>>>
>>>>>>>>> After that, even commenting this block of code I get the result
>>>>>>>>> correctly because the print rows, but the error below is thrown
>>>>>>>>> anyway:
>>>>>>>>>
>>>>>>>>> Traceback (most recent call last):
>>>>>>>>> File "/media/sda3/pyslan/PROJECTS/**w******eb2py/gluon/main.py",
>>>>>>>>> line 632, in wsgibase
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> BaseAdapter.close_all_**instance******s('rollback')
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> File "/media/sda3/pyslan/PROJECTS/**w******eb2py/gluon/dal.py",
>>>>>>>>> line 543, in close_all_instances
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> db._adapter.close(action)
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> File "/media/sda3/pyslan/PROJECTS/**w******eb2py/gluon/dal.py",
>>>>>>>>> line 523, in close
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> getattr(self, action)()
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> File "/media/sda3/pyslan/PROJECTS/**w******eb2py/gluon/dal.py",
>>>>>>>>> line 1670, in rollback
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> if self.connection: return self.connection.rollback()
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this
>>>>>>>>> command now")
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> The code:
>>>>>>>>> https://gist.github.com/**pyslan******/5007364<https://gist.github.com/pyslan/5007364>
>>>>>>>>>
>>>>>>>>> Something related with this?
>>>>>>>>> https://groups.google.com/**foru******m/#!msg/web2py/**
>>>>>>>>> 1s7vDix8QC8/**mgv****ot4PEvBgJ<https://groups.google.com/forum/#!msg/web2py/1s7vDix8QC8/mgvot4PEvBgJ>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>
>>>>>>>> ---
>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>> Groups "web2py-users" group.
>>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>>> send an email to web2py+un...@**googlegroups.com.
>>>>>>>>
>>>>>>>> For more options, visit https://groups.google.com/**grou****
>>>>>>>> ps/opt_out <https://groups.google.com/groups/opt_out>.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>
>>>>>> ---
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "web2py-users" group.
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to web2py+un...@**googlegroups.com.
>>>>>> For more options, visit
>>>>>> https://groups.google.com/**grou**ps/opt_out<https://groups.google.com/groups/opt_out>
>>>>>> .
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>
>>>> ---
>>>> You received this message because you are subscribed to the Google
>>>> Groups "web2py-users" group.
>>>> To unsubscribe from this group and stop receiving emails from it, send
>>>> an email to web2py+un...@**googlegroups.com.
>>>> For more options, visit
>>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>>>> .
>>>>
>>>>
>>>>
>>>
>>> --
>>
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "web2py-users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
