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?

• noel: What is discresults{} doing there?
• noel: I think you need to use the commit() method to complete the query.
• saifuddin778: @shakabra acutallyy i edited this code a bit..previously it was taking out single values from each column. that is why discresults is here.
• Pedro Romano: Your question is not very clear. I can't understand what your problem is. What are you exactly trying to achieve with the code above that you can't?

Answer 1

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()

Answer 2

Once you have your results from cursor.fetchall(), you can attempt to map the results into a numpy array:-

cols = zip( *results ) # return a list of each column
 # ( the * unpacks the 1st level of the tuple )
outlist = []
for col in cols:
 arr = numpy.asarray( col )
 type = arr.dtype
 if str(type)[0:2] == '|S':
 # it's a string array!
 outlist.append( arr )
 else:
 outlist.append( numpy.asarray(arr, numpy.float32) )