Python import Column Data from MySQL as Array
Question: I have this code which retrieves data from a mysql table. I am using Python's
MySQLdb module. I want EACH column's data based on the SELECT WHERE condition
to be retrieved under an array. For instance, in the code below, I want all
the data where location field is 'NY, US' to be retrieved under different
arrays - with each array representing different columns values.

    
    
    import numpy
    import MySQLdb
    
    db = MySQLdb.connect("localhost", "root", "", "test")
    cursor = db.cursor()
    
    sql = "SELECT * FROM usa_new WHERE location = 'NY, US'"
    try:
       cursor.execute(sql)
       results = cursor.fetchall()
       discresults = {}
       for row in results:
    
          id = row[0]
          location = row[1]
          temp_f = row[2]
          pressure_mb = row[3]
          wind_dir = row[4]
          wind_mph = row[5]
          relative_humidity = row[6]
          timestamp = row[7]
    
    except:
       print "Error: unable to fecth data"
    
    db.close()
    

Is there something going wrong?


Answer: There is a data structure called 'list' in python which you can use as array.
If your question's semantic what I understood is "Get the result in arrays
categorized by columns, to be stored in local lists", so here is simple
implementation you can do: remember I have fetched rows one by one matching
the given criteria; as its a good practice;

    
    
    import MySQLdb
    
    db = MySQLdb.connect("localhost", "root", "", "test")
    cursor = db.cursor()
    id, location, temp_fm, pressure_mb, .. = [],[],[],[],...
    //for the number of lists you want to create, just add their names and a empty list
    sql = "SELECT * FROM usa_new WHERE location = 'NY, US'"
    
    try:
       cursor.execute(sql)
    
       rcount = int(cursor.rowcount)
    
       for r in rcount:
          row = cursor.fetchone()
    
          id.append(row[0])
          location.append(row[1])
          temp_f.append(row[2])
          pressure_mb.append(row[3])
          wind_dir.append(row[4])
          wind_mph.append(row[5])
          relative_humidity.append(row[6])
          timestamp.append(row[7])
    
    except:
       print "Error: unable to fecth data"
    
    db.close()
    

