When to close cursors using MySQLdb

迈不过友情╰ 2021-12-17 15:04 363阅读 0赞

http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <p>I'm building a WSGI web app and I have a MySQL database. I'm using MySQLdb, which provides cursors for executing statements and getting results. <strong>What is the standard practice for getting and closing cursors?</strong> In particular, how long should my cursors last? Should I get a new cursor for each transaction?</p> 
     <p>I believe you need to close the cursor before committing the connection. Is there any significant advantage to finding sets of transactions that don't require intermediate commits so that you don't have to get new cursors for each transaction? Is there a lot of overhead for getting new cursors, or is it just not a big deal?</p> 
    </div> 
    <div> 
     <a title="" href="http://stackoverflow.com/questions/tagged/python" rel="nofollow">python</a> 
     <a title="show questions tagged 'mysql'" href="http://stackoverflow.com/questions/tagged/mysql" rel="nofollow">mysql</a> 
     <a title="show questions tagged 'mysql-python'" href="http://stackoverflow.com/questions/tagged/mysql-python" rel="nofollow">mysql-pytho</a> 
    </div> </td> 
  </tr> 
  <tr> 
   <td>&nbsp;<span style="font-size:1.5em;font-family:verdana, Arial, Helvetica, sans-serif;line-height:1.5;">4 Answers </span><span style="font-size:1.5em;font-family:verdana, Arial, Helvetica, sans-serif;line-height:1.5;">4</span></td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

[ active][active] [ oldest][oldest] [ votes][votes]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>29<a title="This answer is not useful" rel="nofollow">down vote</a><span title="loading when this answer was accepted...">accepted</span></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>Instead of asking what is standard practice, since that's often unclear and subjective, you might try looking to the module itself for guidance. In general, using the <code>with</code> keyword as another user suggested is a great idea, but in this specific circumstance it may not give you quite the functionality you expect.</p> 
     <p>As of version 1.2.5 of the module, <code>MySQLdb.Connection</code> implements the <a href="http://docs.python.org/2/library/stdtypes.html#context-manager-types" rel="nofollow">context manager protocol</a> with the following code (<a href="https://github.com/farcepest/MySQLdb1/blob/2204283605e8c450223965eda8d8f357d5fe4c90/MySQLdb/connections.py" rel="nofollow">github</a>):</p> 
     <pre><code><span>def<span> __enter__<span>(<span>self<span>):<span> <span>if<span> <span>self<span>.<span>get_autocommit<span>():<span> <span>self<span>.<span>query<span>(<span>"BEGIN"<span>)<span> <span>return<span> <span>self<span>.<span>cursor<span>()<span> <span>def<span> __exit__<span>(<span>self<span>,<span> exc<span>,<span> value<span>,<span> tb<span>):<span> <span>if<span> exc<span>:<span> <span>self<span>.<span>rollback<span>()<span> <span>else<span>:<span> <span>self<span>.<span>commit<span>()</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
     <p>There are several existing Q&amp;A about <code>with</code> already, or you can read <a href="http://effbot.org/zone/python-with-statement.htm" rel="nofollow">Understanding Python's "with" statement</a>, but essentially what happens is that <code>__enter__</code> executes at the start of the <code>with</code> block, and <code>__exit__</code> executes upon leaving the <code>with</code> block. You can use the optional syntax <code>with EXPR as VAR</code> to bind the object returned by <code>__enter__</code> to a name if you intend to reference that object later. So, given the above implementation, here's a simple way to query your database:</p> 
     <pre><code><span>connection <span>=<span> <span>MySQLdb<span>.<span>connect<span>(...)<span> <span>with<span> connection <span>as<span> cursor<span>:<span> <span># connection.__enter__ executes at this line<span> cursor<span>.<span>execute<span>(<span>'select 1;'<span>)<span> result <span>=<span> cursor<span>.<span>fetchall<span>()<span> <span># connection.__exit__ executes after this line<span> <span>print<span> result <span># prints "((1L,),)"</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
     <p>The question now is, what are the states of the connection and the cursor after exiting the <code>with</code> block? The <code>__exit__</code> method shown above calls only <code>self.rollback()</code> or <code>self.commit()</code>, and neither of those methods go on to call the <code>close()</code> method. The cursor itself has no <code>__exit__</code> method defined – and wouldn't matter if it did, because <code>with</code> is only managing the connection. Therefore, both the connection and the cursor remain open after exiting the <code>with</code> block. This is easily confirmed by adding the following code to the above example:</p> 
     <pre><code><span>try<span>:<span>
    cursor<span>.<span>execute<span>(<span>'select 1;'<span>)<span> <span>print<span> <span>'cursor is open;'<span>,<span> <span>except<span> <span>MySQLdb<span>.<span>ProgrammingError<span>:<span> <span>print<span> <span>'cursor is closed;'<span>,<span> <span>if<span> connection<span>.<span>open<span>:<span> <span>print<span> <span>'connection is open'<span> <span>else<span>:<span> <span>print<span> <span>'connection is closed'</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
     <p>You should see the output "cursor is open; connection is open" printed to stdout.</p> 
     <blockquote> 
      <p>I believe you need to close the cursor before committing the connection.</p> 
     </blockquote> 
     <p>Why? The <a href="https://dev.mysql.com/doc/refman/5.6/en/c-api-function-overview.html" rel="nofollow">MySQL C API</a>, which is the basis for <code>MySQLdb</code>, does not implement any cursor object, as implied in the module documentation: <a href="http://mysql-python.sourceforge.net/MySQLdb.html#connection-objects" rel="nofollow">"MySQL does not support cursors; however, cursors are easily emulated."</a> Indeed, the <code>MySQLdb.cursors.BaseCursor</code> class inherits directly from <code>object</code> and imposes no such restriction on cursors with regard to commit/rollback. An Oracle developer <a href="http://forums.mysql.com/read.php?50,578823,578941#msg-578941" rel="nofollow">had this to say</a>:</p> 
     <blockquote> 
      <p>cnx.commit() before cur.close() sounds most logical to me. Maybe you can go by the rule: "Close the cursor if you do not need it anymore." Thus commit() before closing the cursor. In the end, for Connector/Python, it does not make much difference, but or other databases it might.</p> 
     </blockquote> 
     <p>I expect that's as close as you're going to get to "standard practice" on this subject.</p> 
     <blockquote> 
      <p>Is there any significant advantage to finding sets of transactions that don't require intermediate commits so that you don't have to get new cursors for each transaction?</p> 
     </blockquote> 
     <p>I very much doubt it, and in trying to do so, you may introduce additional human error. Better to decide on a convention and stick with it.</p> 
     <blockquote> 
      <p>Is there a lot of overhead for getting new cursors, or is it just not a big deal?</p> 
     </blockquote> 
     <p>The overhead is negligible, and doesn't touch the database server at all; it's entirely within the implementation of MySQLdb. You can <a href="https://github.com/farcepest/MySQLdb1/blob/master/MySQLdb/cursors.py" rel="nofollow">look at <code>BaseCursor.__init__</code> on github</a> if you're really curious to know what's happening when you create a new cursor.</p> 
     <p>Going back to earlier when we were discussing <code>with</code>, perhaps now you can understand why the <code>MySQLdb.Connection</code> class <code>__enter__</code> and <code>__exit__</code> methods give you a brand new cursor object in every <code>with</code> block and don't bother keeping track of it or closing it at the end of the block. It's fairly lightweight and exists purely for your convenience.</p> 
     <p>If it's really that important to you to micromanage the cursor object, you can use <a href="http://docs.python.org/2/library/contextlib.html#contextlib.closing" rel="nofollow">contextlib.closing</a> to make up for the fact that the cursor object has no defined <code>__exit__</code> method. For that matter, you can also use it to force the connection object to close itself upon exiting a <code>with</code> block. This should output "my_curs is closed; my_conn is closed":</p> 
     <pre><code><span>from<span> contextlib <span>import<span> closing
<span>import<span> <span>MySQLdb<span> <span>with<span> closing<span>(<span>MySQLdb<span>.<span>connect<span>(...))<span> <span>as<span> my_conn<span>:<span> <span>with<span> closing<span>(<span>my_conn<span>.<span>cursor<span>())<span> <span>as<span> my_curs<span>:<span> my_curs<span>.<span>execute<span>(<span>'select 1;'<span>)<span> result <span>=<span> my_curs<span>.<span>fetchall<span>()<span> <span>try<span>:<span> my_curs<span>.<span>execute<span>(<span>'select 1;'<span>)<span> <span>print<span> <span>'my_curs is open;'<span>,<span> <span>except<span> <span>MySQLdb<span>.<span>ProgrammingError<span>:<span> <span>print<span> <span>'my_curs is closed;'<span>,<span> <span>if<span> my_conn<span>.<span>open<span>:<span> <span>print<span> <span>'my_conn is open'<span> <span>else<span>:<span> <span>print<span> <span>'my_conn is closed'</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
     <p>Note that <code>with closing(arg_obj)</code> will not call the argument object's <code>__enter__</code> and <code>__exit__</code> methods; it will <em>only</em> call the argument object's <code>close</code> method at the end of the <code>with</code> block. (To see this in action, simply define a class <code>Foo</code> with <code>__enter__</code>, <code>__exit__</code>, and <code>close</code> methods containing simple <code>print</code> statements, and compare what happens when you do <code>with Foo(): pass</code> to what happens when you do <code>with closing(Foo()): pass</code>.) This has two significant implications:</p> 
     <p>First, if autocommit mode is enabled, MySQLdb will <code>BEGIN</code> an explicit transaction on the server when you use <code>with connection</code> and commit or rollback the transaction at the end of the block. These are default behaviors of MySQLdb, intended to protect you from MySQL's default behavior of immediately committing any and all DML statements. MySQLdb assumes that when you use a context manager, you want a transaction, and uses the explicit <code>BEGIN</code> to bypass the autocommit setting on the server. If you're used to using <code>with connection</code>, you might think autocommit is disabled when actually it was only being bypassed. You might get an unpleasant surprise if you add <code>closing</code> to your code and lose transactional integrity; you won't be able to rollback changes, you may start seeing concurrency bugs and it may not be immediately obvious why.</p> 
     <p>Second, <code>with closing(MySQLdb.connect(user, pass)) as VAR</code> binds the <em>connection object</em> to <code>VAR</code>, in contrast to <code>with MySQLdb.connect(user, pass) as VAR</code>, which binds <em>a new cursor object</em> to <code>VAR</code>. In the latter case you would have no direct access to the connection object! Instead, you would have to use the cursor's <code>connection</code> attribute, which provides proxy access to the original connection. When the cursor is closed, its <code>connection</code> attribute is set to <code>None</code>. This results in an abandoned connection that will stick around until one of the following happens:</p> 
     <ul> 
      <li>All references to the cursor are removed</li> 
      <li>The cursor goes out of scope</li> 
      <li>The connection times out</li> 
      <li>The connection is closed manually via server administration tools</li> 
     </ul> 
     <p>You can test this by monitoring open connections (in Workbench or by <a href="http://dev.mysql.com/doc/refman/5.6/en/show-processlist.html" rel="nofollow">using <code>SHOW PROCESSLIST</code></a>) while executing the following lines one by one:</p> 
     <pre><code><span>with<span> <span>MySQLdb<span>.<span>connect<span>(...)<span> <span>as<span> my_curs<span>:<span> <span>pass<span> my_curs<span>.<span>close<span>()<span> my_curs<span>.<span>connection <span># None<span> my_curs<span>.<span>connection<span>.<span>close<span>()<span> <span># throws AttributeError, but connection still open<span> <span>del<span> my_curs <span># connection will close here</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
    </div> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <a title="short permalink to this answer" href="http://stackoverflow.com/a/22618781" rel="nofollow">share</a> 
         <span>|<a title="" href="http://stackoverflow.com/posts/22618781/edit" rel="nofollow">improve this answer</a></span> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div> 
          <a title="show all edits to this post" href="http://stackoverflow.com/posts/22618781/revisions" rel="nofollow">edited <span title="2016-06-16 01:04:34Z">Jun 16 at 1:04</span></a> 
         </div> 
         <div> 
          <div> 
           <img src="https://i.stack.imgur.com/USV9e.jpg?s=32&amp;g=1" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/4594973/ray" rel="nofollow">ray</a> 
          <div> 
           <span title="reputation score">3,100<span title="6 silver badges"><span><span>6<span title="28 bronze badges"><span><span>28 </span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div>
           answered 
          <span title="2014-03-24 19:26:39Z">Mar 24 '14 at 19:26 </span> 
         </div> 
         <div> 
          <div> 
           <img src="https://i.stack.imgur.com/y1Mud.gif?s=32&amp;g=1" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/2359271/air" rel="nofollow">Air</a> 
          <div> 
           <span title="reputation score">3,753<span title="1 gold badge"><span><span>1<span title="24 silver badges"><span><span>24<span title="46 bronze badges"><span><span>46 </span></span></span></span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td><span title="number of 'useful comment' votes received">2 </span></td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>your post was most exhaustive, but even after re-reading it a few times, I find myself still puzzled regarding closing cursors. Judging from the numerous post on the subject, it seems to be a common point of confusion. My takeaway is that cursors seemingly DO NOT require .close() to be called -- ever. So why even have a .close() method? –&nbsp;<a title="518 reputation" href="http://stackoverflow.com/users/1499690/smgreenfield" rel="nofollow">SMGreenfield</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment50560257_22618781" rel="nofollow"><span title="2015-07-08 05:02:29Z">Jul 8 '15 at 5:02</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td><span title="number of 'useful comment' votes received">2 </span></td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>The short answer is that <code>cursor.close()</code> is part of <a href="https://www.python.org/dev/peps/pep-0249/#cursor-methods" rel="nofollow">the Python DB API</a>, which was not written specifically with MySQL in mind. –&nbsp;<a title="3,753 reputation" href="http://stackoverflow.com/users/2359271/air" rel="nofollow">Air</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment50581658_22618781" rel="nofollow"><span title="2015-07-08 14:22:53Z">Jul 8 '15 at 14:22</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Why connection will close after del my_curs? –&nbsp;<a title="880 reputation" href="http://stackoverflow.com/users/3837778/bae" rel="nofollow">BAE</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment53778335_22618781" rel="nofollow"><span title="2015-10-06 18:04:03Z">Oct 6 '15 at 18:04</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>@ChengchengPei <code>my_curs</code> holds the last reference to the <code>connection</code> object. Once that reference no longer exists, the <code>connection</code> object <em>should</em> be garbage collected. –&nbsp;<a title="3,753 reputation" href="http://stackoverflow.com/users/2359271/air" rel="nofollow">Air</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment53779513_22618781" rel="nofollow"><span title="2015-10-06 18:35:54Z">Oct 6 '15 at 18:35</span></a> </span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid comments like “+1” or “thanks”." rel="nofollow">add a comment</a> 
     <span>&nbsp;|&nbsp; </span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

No problem. We won't show you that ad again. Why didn't you like it?

*  Uninteresting
 *  Misleading
 *  Offensive
 *  Repetitive
 *  Other

[Oops! I didn't mean to do this.][Oops_ I didn_t mean to do this.]

![i.gif_e_eyJhdiI6MTMwMDAxLCJhdCI6NCwiYnQiOjAsImNtIjo0NjcwODksImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjEzNzAxLCJkaSI6IjYyMzg4NTY2ZDliMjQ5NTFiYzViZDU1YTc3NDBhNjc3IiwiZG0iOjEsImZjIjoxOTA3NjAwLCJmbCI6MjA5NzIwNywiaXAiOiIyMDguMTQ2LjQzLjUiLCJrdyI6InB5dGhvbixteXNxbCxteXNxbC1weXRob24iLCJtayI6InB5dGhvbiIsIm53IjoyMiwicGMiOjYuMzYsImVjIjo2LjM2LCJwciI6NzY1LCJydCI6Miwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3Mzg3ODIxMjczMCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrMjE0MDcwMjc0NCIsImZxIjowfQ_s_xKuQydwDvr2DMa8amVVPJ4oAkUY][]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>20<a title="This answer is not useful" rel="nofollow">down vote</a></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>It's better to rewrite it using 'with' keyword. 'With' will take care about closing cursor (it's important because it's unmanaged resource) automatically. The benefit is it will close cursor in case of exception too.</p> 
     <pre><code><span>from<span> contextlib <span>import<span> closing
<span>import<span> <span>MySQLdb<span> <span>''' At the beginning you open a DB connection. Particular moment when you open connection depends from your approach: - it can be inside the same function where you work with cursors - in the class constructor - etc '''<span> db <span>=<span> <span>MySQLdb<span>.<span>connect<span>(<span>"host"<span>,<span> <span>"user"<span>,<span> <span>"pass"<span>,<span> <span>"database"<span>)<span> <span>with<span> closing<span>(<span>db<span>.<span>cursor<span>())<span> <span>as<span> cur<span>:<span> cur<span>.<span>execute<span>(<span>"somestuff"<span>)<span> results <span>=<span> cur<span>.<span>fetchall<span>()<span> <span># do stuff with results<span> cur<span>.<span>execute<span>(<span>"insert operation"<span>)<span> <span># call commit if you do INSERT, UPDATE or DELETE operations<span> db<span>.<span>commit<span>()<span> cur<span>.<span>execute<span>(<span>"someotherstuff"<span>)<span> results2 <span>=<span> cur<span>.<span>fetchone<span>()<span> <span># do stuff with results2<span> <span># at some point when you decided that you do not need<span> <span># the open connection anymore you close it<span> db<span>.<span>close<span>()</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
    </div> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <a title="short permalink to this answer" href="http://stackoverflow.com/a/16718647" rel="nofollow">share</a> 
         <span>|<a title="" href="http://stackoverflow.com/posts/16718647/edit" rel="nofollow">improve this answer</a></span> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div> 
          <a title="show all edits to this post" href="http://stackoverflow.com/posts/16718647/revisions" rel="nofollow">edited <span title="2014-07-26 18:14:33Z">Jul 26 '14 at 18:14</span></a> 
         </div> 
         <div> 
          <div> 
           <img src="https://www.gravatar.com/avatar/e86681e49622d52817b36fd2a4c936b7?s=32&amp;d=identicon&amp;r=PG" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/562769/martin-thoma" rel="nofollow">Martin Thoma</a> 
          <div> 
           <span title="reputation score 14,161">14.2k<span title="17 gold badges"><span><span>17<span title="115 silver badges"><span><span>115<span title="229 bronze badges"><span><span>229 </span></span></span></span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div>
           answered 
          <span title="2013-05-23 15:59:30Z">May 23 '13 at 15:59 </span> 
         </div> 
         <div> 
          <div> 
           <img src="https://i.stack.imgur.com/Iwfoc.jpg?s=32&amp;g=1" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/427457/roman-podlinov" rel="nofollow">Roman Podlinov</a> 
          <div> 
           <span title="reputation score">5,754<span title="5 gold badges"><span><span>5<span title="24 silver badges"><span><span>24<span title="44 bronze badges"><span><span>44 </span></span></span></span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>+1 for using <code>with</code> –&nbsp;<a title="2,563 reputation" href="http://stackoverflow.com/users/1994863/snooze92" rel="nofollow">snooze92</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment31983302_16718647" rel="nofollow"><span title="2014-01-20 12:01:26Z">Jan 20 '14 at 12:01</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>I dont think the <code>with</code> is a good option if you want to use it in Flask or other web framework. If the situation is <code>http://flask.pocoo.org/docs/patterns/sqlite3/#sqlite3</code> then there will be problems. –&nbsp;<a title="431 reputation" href="http://stackoverflow.com/users/2930744/james-king" rel="nofollow">James King</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment32194509_16718647" rel="nofollow"><span title="2014-01-25 16:04:10Z">Jan 25 '14 at 16:04</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>@james-king I didn't work with Flask, but in your example Flask will close db connection itself. Actually in my code I use slightly different approach- I use <strong>with</strong> for close cursors <code>with closing(self.db.cursor()) as cur: cur.execute("UPDATE table1 SET status = %s WHERE id = %s",(self.INTEGR_STATUS_PROCESSING, id)) self.db.commit()</code> –&nbsp;<a title="5,754 reputation" href="http://stackoverflow.com/users/427457/roman-podlinov" rel="nofollow">Roman Podlinov</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment32245937_16718647" rel="nofollow"><span title="2014-01-27 12:59:43Z">Jan 27 '14 at 12:59</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>@RomanPodlinov Yeah, If you use it with cursor then things would be fine. –&nbsp;<a title="431 reputation" href="http://stackoverflow.com/users/2930744/james-king" rel="nofollow">James King</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment32288694_16718647" rel="nofollow"><span title="2014-01-28 12:32:48Z">Jan 28 '14 at 12:32</span></a> </span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid comments like “+1” or “thanks”." rel="nofollow">add a comment</a> 
     <span>&nbsp;|&nbsp; </span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>4<a title="This answer is not useful" rel="nofollow">down vote</a></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>I think you'll be better off trying to use one cursor for all of your executions, and close it at the end of your code. It's easier to work with, and it might have efficiency benefits as well (don't quote me on that one).</p> 
     <pre><code><span>conn <span>=<span> <span>MySQLdb<span>.<span>connect<span>(<span>"host"<span>,<span>"user"<span>,<span>"pass"<span>,<span>"database"<span>)<span> cursor <span>=<span> conn<span>.<span>cursor<span>()<span> cursor<span>.<span>execute<span>(<span>"somestuff"<span>)<span> results <span>=<span> cursor<span>.<span>fetchall<span>()<span> <span>..<span>do<span> stuff <span>with<span> results cursor<span>.<span>execute<span>(<span>"someotherstuff"<span>)<span> results2 <span>=<span> cursor<span>.<span>fetchall<span>()<span> <span>..<span>do<span> stuff <span>with<span> results2 cursor<span>.<span>close<span>()</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code></pre> 
     <p>The point is that you can store the results of a cursor's execution in another variable, thereby freeing your cursor to make a second execution. You run into problems this way only if you're using fetchone(), and need to make a second cursor execution before you've iterated through all results from the first query.</p> 
     <p>Otherwise, I'd say just close your cursors as soon as you're done getting all of the data out of them. That way you don't have to worry about tying up loose ends later in your code.</p> 
    </div> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <a title="short permalink to this answer" href="http://stackoverflow.com/a/6885390" rel="nofollow">share</a> 
         <span>|<a title="" href="http://stackoverflow.com/posts/6885390/edit" rel="nofollow">improve this answer</a></span> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div>
           answered 
          <span title="2011-07-30 19:06:12Z">Jul 30 '11 at 19:06 </span> 
         </div> 
         <div> 
          <div> 
           <img src="https://www.gravatar.com/avatar/cce7fbdea21c66e27bf31ac0d491c79d?s=32&amp;d=identicon&amp;r=PG" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/870906/nct25" rel="nofollow">nct25</a> 
          <div> 
           <span title="reputation score">125<span title="1 silver badge"><span><span>1<span title="10 bronze badges"><span><span>10 </span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Thanks - Considering that you have to close the cursor to commit an update/insert, I guess one easy way to do it for updates/inserts would be to get one cursor for each daemon, close the cursor to commit and immediately get a new cursor so your ready the next time. Does that sound reasonable? –&nbsp;<a title="2,814 reputation" href="http://stackoverflow.com/users/484488/jmilloy" rel="nofollow">jmilloy</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment8201959_6885390" rel="nofollow"><span title="2011-07-31 16:21:57Z">Jul 31 '11 at 16:21</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td><span title="number of 'useful comment' votes received">1 </span></td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Hey, no problem. I didn't actually know about committing the update/insert by closing your cursors, but a quick search online shows this: conn = MySQLdb.connect(arguments_go_here) cursor = MySQLdb.cursor() cursor.execute(mysql_insert_statement_here) try: conn.commit() except: conn.rollback() # undo changes made if error occurs. This way, the database itself commits the changes, and you don't have to worry about the cursors themselves. Then you can just have 1 cursor open at all times. Have a look here: <a href="http://www.tutorialspoint.com/python/python_database_access.htm" rel="nofollow">tutorialspoint.com/python/python_database_access.htm</a> –&nbsp;<a title="125 reputation" href="http://stackoverflow.com/users/870906/nct25" rel="nofollow">nct25</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment8204593_6885390" rel="nofollow"><span title="2011-07-31 21:43:03Z">Jul 31 '11 at 21:43</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Yeah if that works then I'm just wrong and there was some other reason that caused me to think I had to close the cursor in order to commit the connection. –&nbsp;<a title="2,814 reputation" href="http://stackoverflow.com/users/484488/jmilloy" rel="nofollow">jmilloy</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment8206135_6885390" rel="nofollow"><span title="2011-08-01 01:39:35Z">Aug 1 '11 at 1:39</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>Yea I dunno, that link I posted makes me think that that works. I guess a little more research would tell you if it definitely works or not, but I think you could probably just go with it. Hope I was of help to you! –&nbsp;<a title="125 reputation" href="http://stackoverflow.com/users/870906/nct25" rel="nofollow">nct25</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment8206860_6885390" rel="nofollow"><span title="2011-08-01 03:48:12Z">Aug 1 '11 at 3:48</span></a> </span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid comments like “+1” or “thanks”." rel="nofollow">add a comment</a> 
     <span>&nbsp;|&nbsp; </span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <a title="This answer is useful" rel="nofollow">up vote</a> 
     <span>-3<a title="This answer is not useful" rel="nofollow">down vote</a></span> 
    </div> </td> 
   <td> 
    <div> 
     <p>I suggest to do it like php and mysql. Start i at the beginning of your code before printing of the first data. So if you get a connect error you can display a <code>50x</code>(Don't remember what internal error is) error message. And keep it open for the whole session and close it when you know you wont need it anymore.</p> 
    </div> 
    <table> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <a title="short permalink to this answer" href="http://stackoverflow.com/a/5670056" rel="nofollow">share</a> 
         <span>|<a title="" href="http://stackoverflow.com/posts/5670056/edit" rel="nofollow">improve this answer</a></span> 
        </div> </td> 
       <td align="right"> 
        <div> 
         <div>
           answered 
          <span title="2011-04-14 21:40:40Z">Apr 14 '11 at 21:40 </span> 
         </div> 
         <div> 
          <div> 
           <img src="https://www.gravatar.com/avatar/f05d597cc06068b0f1d745d1a11cd6df?s=32&amp;d=identicon&amp;r=PG" alt="" width="32" height="32"> 
          </div> 
         </div> 
         <div> 
          <a href="http://stackoverflow.com/users/671006/killedkenny" rel="nofollow">KilledKenny</a> 
          <div> 
           <span title="reputation score">220<span title="4 silver badges"><span><span>4<span title="16 bronze badges"><span><span>16 </span></span></span></span></span></span></span> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td> 
    <div> 
     <table> 
      <tbody> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>In MySQLdb, there is a difference between a connection and a cursor. I connect once per request (for now) and can detect connection errors early. But what about cursors? –&nbsp;<a title="2,814 reputation" href="http://stackoverflow.com/users/484488/jmilloy" rel="nofollow">jmilloy</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment6471269_5670056" rel="nofollow"><span title="2011-04-14 21:47:14Z">Apr 14 '11 at 21:47</span></a> </span></span> 
         </div> </td> 
       </tr> 
       <tr> 
        <td> 
         <table> 
          <tbody> 
           <tr> 
            <td>&nbsp;&nbsp;</td> 
            <td>&nbsp;</td> 
           </tr> 
          </tbody> 
         </table></td> 
        <td> 
         <div> 
          <span>IMHO it's not accurate advice. It dependes. If your code will keep connection for long time (e.g. it takes some data from DB and then for 1-5-10 mins it does something on the server and keep connection) and it's multy thread application it will create a problem pretty soon (you will exceed max allowed connections). –&nbsp;<a title="5,754 reputation" href="http://stackoverflow.com/users/427457/roman-podlinov" rel="nofollow">Roman Podlinov</a> <span><a href="http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#comment24070396_5670056" rel="nofollow"><span title="2013-05-23 16:10:28Z">May 23 '13 at 16:10</span></a> </span></span> 
         </div> </td> 
       </tr> 
      </tbody> 
     </table> 
    </div> 
    <div> 
     <a title="Use comments to ask for more information or suggest improvements. Avoid comments like “+1” or “thanks”." rel="nofollow">add a comment</a> 
     <span>&nbsp;|&nbsp;</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/kungfupanda/p/5874191.html

[active]: http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb?answertab=active#tab-top
[oldest]: http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb?answertab=oldest#tab-top
[votes]: http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb?answertab=votes#tab-top
[Oops_ I didn_t mean to do this.]: http://stackoverflow.com/questions/5669878/when-to-close-cursors-using-mysqldb#
[i.gif_e_eyJhdiI6MTMwMDAxLCJhdCI6NCwiYnQiOjAsImNtIjo0NjcwODksImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjEzNzAxLCJkaSI6IjYyMzg4NTY2ZDliMjQ5NTFiYzViZDU1YTc3NDBhNjc3IiwiZG0iOjEsImZjIjoxOTA3NjAwLCJmbCI6MjA5NzIwNywiaXAiOiIyMDguMTQ2LjQzLjUiLCJrdyI6InB5dGhvbixteXNxbCxteXNxbC1weXRob24iLCJtayI6InB5dGhvbiIsIm53IjoyMiwicGMiOjYuMzYsImVjIjo2LjM2LCJwciI6NzY1LCJydCI6Miwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3Mzg3ODIxMjczMCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrMjE0MDcwMjc0NCIsImZxIjowfQ_s_xKuQydwDvr2DMa8amVVPJ4oAkUY]: http://engine.adzerk.net/i.gif?e=eyJhdiI6MTMwMDAxLCJhdCI6NCwiYnQiOjAsImNtIjo0NjcwODksImNoIjoxMTc4LCJjayI6e30sImNyIjoxNjEzNzAxLCJkaSI6IjYyMzg4NTY2ZDliMjQ5NTFiYzViZDU1YTc3NDBhNjc3IiwiZG0iOjEsImZjIjoxOTA3NjAwLCJmbCI6MjA5NzIwNywiaXAiOiIyMDguMTQ2LjQzLjUiLCJrdyI6InB5dGhvbixteXNxbCxteXNxbC1weXRob24iLCJtayI6InB5dGhvbiIsIm53IjoyMiwicGMiOjYuMzYsImVjIjo2LjM2LCJwciI6NzY1LCJydCI6Miwic3QiOjgyNzcsInVrIjoidWUxLTVjMjc0YjQ0NWIzMjQ3MzRhYjIyY2UwODM0NmNjOWU3Iiwiem4iOjQ0LCJ0cyI6MTQ3Mzg3ODIxMjczMCwiYmYiOnRydWUsInBuIjoiYWR6ZXJrMjE0MDcwMjc0NCIsImZxIjowfQ&s=xKuQydwDvr2DMa8amVVPJ4oAkUY