title: Python MySQL data import
body: <p>I am using the following script to pull data from a third party tool, create a table in a MySQL database and populate it with the resulting data. The script runs through and I can see the print out of all of the requested data in the Python Shell window. However, when I open the database the table is created with the column names but there are no rows and no data. I have searched around and read that I do not need to use 'conn.commit' for a script that is just retrieving data. Is that the case here? If not does anyone see another reason why the data is not populating the table?</p>
  
  <pre><code>import httplib2, urllib, json, pprint, getpass, string, time, MySQLdb
  
  def usage():
      print "Usage: python26 mysql.py or ./mysql.py"
      sys.exit(1)
  
  if len(sys.argv) != 1:
      usage()
  
  # Connect to the database and create the tables
  conn = MySQLdb.connect (host = "localhost",
                     user = "XXXXXXXXX",
                     passwd = "XXXXXXXX")
  cursor = conn.cursor ()
  cursor.execute ("DROP DATABASE IF EXISTS tenable")
  cursor.execute ("CREATE DATABASE tenable")
  cursor.execute ("USE tenable")
  cursor.execute ("""
  CREATE TABLE cumvulndata
  (
    offset         BIGINT(10),
    pluginName     TEXT,
    repositoryID   SMALLINT(3),
    severity       TINYINT(2),
    pluginID       MEDIUMINT(8),
    hasBeenMitigated   TINYINT(1),
    dnsName        VARCHAR(255),
    macAddress     VARCHAR(40),
    familyID       INT(4),
    recastRisk     TINYINT(1),
    firstSeen      DATETIME,
    ip             VARCHAR(15),
    acceptRisk     TINYINT(1),
    lastSeen       DATETIME,
    netbiosName    VARCHAR(255),
    port           MEDIUMINT(5),
    pluginText     MEDIUMTEXT,
    protocol       TINYINT(3)
    )
    """)
  #
  # Security Center organizational user creds
  user = 'XXXXXXXXX'
  passwd = 'XXXXXXXX'
  url = 'https://Security Center Server/request.php'
  
  def SendRequest(url, headers, data):
      http = httplib2.Http()
      response, content = http.request(url, 
                                      'POST', 
                                       headers=headers, 
                                       body=urllib.urlencode(data))
      if 'set-cookie' in response:
         headers['Cookie'] = response['set-cookie']
      return response, content
  
  headers = {"Content-type": "application/x-www-form-urlencoded"}
  
  input = {'password': passwd,
       'username': user}
  
  # Convert input to login JSON
  inputjson = json.dumps(input)
  
  data = {"request_id": "8",
      "module": "auth",
      "action": "login",
      "input": inputjson}
  
  # Send Login Request
  response, content = SendRequest(url, headers, data)
  
  # Decode JSON to python data structure
  result = json.loads(content)
  
  if result["error_code"] == 0:
      print "SC4 Login Successful"
      token = result['response']['token']
      print "Session Token:",token
  
  # Construct the cumulative vuln query JSON 
  cuminput = {'tool':'vulndetails',
       'startOffset':'0',
       'endOffset':sys.maxint,
       'sortField':'ip',
       'sortDir':'asc',
       'sourceType':'cumulative',
       'filters': [
                              {'filterName':'lastSeen',
                              'value':'31',
                              'operator':'&lt;='},
                              {"filterName":"severity",
                              "value":"1,2,3",
                              "operator":"="}
  
              ]}
  cuminputjson = json.dumps(cuminput)
  
   #
  cumdata = {"request_id": "1",
          "module": "vuln",
      "action": "query",
      "input":cuminputjson,
          "token": token}
  
  # Send the cumulative JSON and then populate the table
  cumresponse, content = SendRequest(url, headers, cumdata)
  resultc = json.loads(content)
  off = 0
      print "\nFilling cumvulndata table with vulnerabilities from the cumulative    database.     Please wait..."
  for result in resultc['response']['results']:
      off += 1
      cursor.execute ("""INSERT INTO cumvulndata   (offset,pluginName,repositoryID,severity,pluginID,hasBeenMitigated,dnsName,macAddress,familyID,recastRisk,firstSeen,ip,acceptRisk,lastSeen,netbiosName,port,pluginText,protocol)
      VALUES
      (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,(FROM_UNIXTIME(%s)),%s,%s,(FROM_UNIXTIME(%s)),%s,%s,%s,%s)""",   (off,result["pluginName"],result["repositoryID"],result["severity"],result["pluginID"],result["hasBeenMitigated"],result["dnsName"],result["macAddress"],result["familyID"],result["recastRisk"],result["firstSeen"],result["ip"],result["acceptRisk"],result["lastSeen"],result["netbiosName"],result["port"],result["pluginText"],result["protocol"]))
  
  
  # Close the cursor and connection
  cursor.close ()
  conn.close ()
  
  print "Done!!"
  </code></pre>