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?

I think you need to use the commit() method to complete the query. — noel
– noel, Commented Nov 17, 2012 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. — saifuddin778
– saifuddin778, Commented Nov 17, 2012 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
– Pedro Romano, Commented Nov 17, 2012 at 9:29

Navid · Accepted Answer · 2014-08-19 18:23:06Z

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

Calvin Cheng · Accepted Answer · 2012-11-18 12:03:14Z

0

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

answered Nov 18, 2012 at 12:03

Calvin Cheng

36.7k40 gold badges121 silver badges171 bronze badges

Collectives™ on Stack Overflow

Python import Column Data from MySQL as Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related