CODEWITHSUNDEEP
pythonmysqlarraysnumpy
wpns
wpns

Reputation: 203

MySQLdb query to Numpy array

So I've tried to follow What's the most efficient way to convert a MySQL result set to a NumPy array? but am still having problems.

My database rows are 57 unsigned integers (Unix epoch plus byte counts for each of 28 switch ports, in and out).

My code looks like:

import MySQLdb as mdb
import numpy

# get the database connector
DBconn = mdb.connect('localhost', 'root', '<Password>', 'Monitoring')

with DBconn:

    # prepare a cursor object using cursor() method
    cursor = DBconn.cursor()

    # now get the data for the last 10 minutes
    sql = "select * from LowerSwitchBytes where ComputerTime >= (unix_timestamp(now())-(60*10))"
    cursor.execute(sql)

    results = cursor.fetchall()
    for row in results:
        print row

So that prints out 10 lines like:

(1378151928L, 615983307L, 517980853L, 25355784L, 117110102L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 267680651L, 288368872L, 84761960L, 337403085L, 224270992L, 335381466L, 27238950843L, 549910918625L, 240002569249L, 11167210734L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 222575491L, 335850213L, 223669465L, 339800088L, 310004136202L, 16635727254L, 0L, 0L, 16590672L, 147102083L, 0L, 0L, 0L, 0L)

But when I change the:

    results = cursor.fetchall()
    for row in results:
        print row

to

    A = numpy.fromiter(cursor.fetchall(), count=-1, dtype=numpy.uint32)
    print A

I get:

Traceback (most recent call last):
  File "min.py", line 23, in <module>
    A = numpy.fromiter(cursor.fetchall(), count=-1, dtype=numpy.uint32)
ValueError: setting an array element with a sequence.

Any idea what I'm doing wrong?

Upvotes: 4

Views: 3602

Answers (1)

Jaime
Jaime

Reputation: 67437

np.fromiter is complaining because it is trying to write a full row of inputs into a single item of the new array. You can work around this using record arrays:

A = numpy.fromiter(cursor.fetchall(), count=-1,
                   dtype=[('', numpy.uint8)]*57)

If all your records are of the same type, you can then get an array view as follows:

A = A.view(numpy.uint8).reshape(-1, 57)

Upvotes: 2

Related Questions