title: python and mysql connections in a class , DB open and close strange behaviour
body: <p>I have written a small app that uses mysql to get a list of products that need updating on our magento website.</p>
  
  <p>Python then actions these updates and marks the product in the db as complete.</p>
  
  <p>My Original code (pseudo to show the overview)</p>
  
  <pre><code>class Mysqltools:
      def get_products():
         db = pymysql.connect(host= .... )
         mysqlcursor = db.cursor(pymysql.cursors.DictCursor)
         sql = select * from x where y = z
         mysqlcursor.execute(sql % (z))
         rows = mysqlcursor.fetchall()
         mysqlcursor.close()
         db.close
         return rows
  
      def write_products(sku, name, id):
         db = pymysql.connect(host= .... )
         mysqlcursor = db.cursor(pymysql.cursors.DictCursor)
         sql = update table set sku = sku, name = name, id = id.....
         mysqlcursor.execute(sql % (sku, name, id))
         mysqlcursor.close()
         db.close
  </code></pre>
  
  <p>This was working ok, but on each db connection string we were getting a pause.</p>
  
  <p>I did a bit of research and did the following:</p>
  
  <pre><code>class Mysqltools:
      def __init__():
          self.db = pymysql.connect(host= .... )
  
      def get_products():
  
         mysqlcursor = self.db.cursor(pymysql.cursors.DictCursor)
         sql = select * from x where y = z
         mysqlcursor.execute(sql % (z))
         rows = mysqlcursor.fetchall()
         mysqlcursor.close()
  
      def write_products(sku, name, id):
         mysqlcursor = self.db.cursor(pymysql.cursors.DictCursor)
         sql = update table set sku = sku, name = name, id = id.....
         mysqlcursor.execute(sql % (sku, name, id))
         mysqlcursor.close()
         db.commit()
  </code></pre>
  
  <p>This has a MASSIVE speed improvement. However, it would only do a successful get_products on the first iteration, once it was called a second time, it was finding 0 products to update, even though performing the same SQL on the db would show a number of rows returned.</p>
  
  <p>Am I doing something wrong with the connections ?
  I have also tried moving the db = outside of the class and referencing it but that still gives the same issue.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Doing some testing, and if I remove the <code>DictCursor</code> from the cursor I can get the correct rows returned each time (I've just created a quick loop to keep checking for records)</p>
  
  <p>Is the DictCursor doing something I am unaware of ?</p>
  
  <p>** UPDATE 2 **</p>
  
  <p>I've removed the DictCursor, and tried the following.</p>
  
  <ol>
  <li>Create a while True loop which calls my get_product method.</li>
  <li>In MySQL change some rows so that they should be found.</li>
  </ol>
  
  <p>If I go from having 0 possible rows to find, then change some so they should be found, my code just shows 0 found, and loops stating this.</p>
  
  <p>If I got from having x possible rows to find, then change it to 0 in mysql, my code continues to loop showing the x possible rows.</p>