Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
What is discresults{} doing there? – shakabra Nov 17 '12 at 6:51
I think you need to use the commit() method to complete the query. – shakabra Nov 17 '12 at 6:53
@shakabra acutallyy i edited this code a bit..previously it was taking out single values from each column. that is why discresults is here. – khan Nov 17 '12 at 7:07
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? – Pedro Romano Nov 17 '12 at 9:29
add comment (requires an account with 50 reputation)

2 Answers

up vote 0 down vote

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) )
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 0 down vote

Their 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()
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.