title: Python sharing DB connection with Multiple threads
body: <p>I am creating one database connection and I am sharing it across multiple threads (Around 100 to 1000 threads), I am getting the error as below,</p>
  
  <blockquote>
    <p>1) What is wrong with my code ?</p>
  </blockquote>
  
  <p><em>This is My Sample code, I will change this code for below scenario:</em>
  Based on some SQL result, It will create maximum of 1000 threads. 
  <strong>If I create database connection for each thread, It will create more database connections. In order to avoid this, I used the same database connection across all the threads, I am having only insert and update operations.</strong></p>
  
  <blockquote>
    <p>2) Is there any better approach on this kind of multithreading
    scenario ?</p>
  </blockquote>
  
  <p>Error:</p>
  
  <pre><code>Worker: 0
  Create Notification called for userid abcxyz and message is Test Message : Worker: 0 Attempt: 0
  Sleeping ... 
  
  Worker: 1
  Create Notification called for userid abcxyz and message is Test Message : Worker: 1 Attempt: 0
  
  Partial insertedID 43
  Traceback (most recent call last):
    File "T:\work\Testing\src\notification_data_feeder.py", line 58, in createNotification
      db.commit()
  OperationalError: (2006, 'MySQL server has gone away')
  
  Error on createNotification 
  Traceback (most recent call last):
    File "T:\work\Testing\src\notification_data_feeder.py", line 54, in createNotification
      cursor.execute("INSERT INTO push_notification_data (user_id, trig_time, msg, msg_prio, type, trig_ref, lang) VALUES (%s, %s, %s, %s, %s, %s, %s)", (str(userid), current_gmt_datetime, msg, msgPriority, '2', trigRef, lang))
    File "N:\Installation\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
      self.errorhandler(self, exc, value)
    File "N:\Installation\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
      raise errorclass, errorvalue
  OperationalError: (2013, 'Lost connection to MySQL server during query')
  
  Error on createNotification
  </code></pre>
  
  <p>Code :</p>
  
  <pre><code>import json
  import os
  from random import randint
  import threading
  from time import sleep
  import time, datetime
  from urllib2 import Request, urlopen, HTTPError
  import traceback
  import MySQLdb
  import socket
  from collections import defaultdict
  
  import CONST_LOCAL
  from Notification import Notification
  from Notification import UserDevice
  
  def convertQueryResultToDevices(userDevicesRows, cursor): 
      userDevices = []
      if userDevicesRows is not None:
          for userDevicesRow in userDevicesRows:
              userDevice = UserDevice()
              userDevice.userId = userDevicesRow[0]
              userDevice.deviceId = userDevicesRow[1]
              userDevice.deviceType = userDevicesRow[2]
              userDevices.append(userDevice);
      return userDevices;  
  
  def getDeviceIdsByUser(userId, cursor):
      userDevices = None;
      try :
          cursor.execute("SELECT uuid, registration_id, device_type  FROM userid_data WHERE userid = %s", (userId));
          userDevicesRows = cursor.fetchall()
          # Convert SQL Row to model object
          userDevices = convertQueryResultToDevices(userDevicesRows, cursor)   
      except:
          print(traceback.format_exc())
  
      return userDevices;
  
  def createNotification(userid, msg, msgPriority, type, trigRef, lang, db, cursor):
      print("Create Notification called for userid " + str(userid) + " and message is " + msg)    
      try:
          # Insert the notification message
          current_gmt_datetime = datetime.datetime.fromtimestamp(time.mktime(time.gmtime()));
          cursor.execute("INSERT INTO notification_data (user_id, triger_time, msg, msg_prio, type, trig_ref, lang) VALUES (%s, %s, %s, %s, %s, %s, %s)", (str(userid), current_gmt_datetime, msg, msgPriority, '2', trigRef, lang))
  
          insertedID = cursor.lastrowid
          print("Partial insertedID " + str(insertedID))
  
          # Commit this insertion
          if db:
              db.commit()
  
          # Get Devices of the user
          userDevices = getDeviceIdsByUser(userid, cursor)
          if userDevices is not None:
              for userDevice in userDevices:
                  deviceId = userDevice.deviceId
                  deviceType = userDevice.deviceType
                  # If the value exists
                  if deviceId:
                      # Default value
                      deviceTypeInt = 1;
  
                      cursor.execute("INSERT INTO notification_status (notification_id, device_id, device_type, status, attempts, delivery_status, delivery_err_reason) VALUES (%s, %s, %s, %s, %s, %s, %s)", (insertedID, deviceId, deviceTypeInt, 0, 0, 0, 0))
  
              # Commit this status row of the previous inserted data row
              if db:
                  db.commit()
  
          print("Notification created , notification id " + str(insertedID))  
      except MySQLdb.Error, e:
          print(traceback.format_exc())
          print("Error on createNotification ")
  
  def worker(num, attempt, db, cursor):
      """thread worker function"""
      for i in range(attempt):
          print 'Worker: %s' % num
          message = "Test Message : Worker: " + str(num) + " Attempt: " + str(i)
          createNotification("abcxyz", message, 1, 1, 1, 1, db, cursor)
          # Sleep for testing purpose
          sleep(1)
  
  if __name__ == '__main__':
      threads = []
      attempt = 1
  
      try:
          # Create DB connection, It will be shared across all threads
          db = MySQLdb.connect(CONST_LOCAL.DATABASE_SERVER, CONST_LOCAL.DATABASE_USER, CONST_LOCAL.DATABASE_PASSWORD, CONST_LOCAL.DATABASE_NAME)
          cursor = db.cursor()
  
          # Create Two threads, Each thread will insert one entry
          for i in range(2):
              t = threading.Thread(target=worker, args=(i, attempt, db, cursor))
              threads.append(t)
              t.start()
      except:
          print(traceback.format_exc())
  
      print("Sleeping ... ")
      # Testing purpose
      sleep(50000)
      print("Completed ... ")
  </code></pre>