MSSQL Cursor to Numpy Array/Dataframe - TypeError: expected a readable buffer object

Question

I'm trying to fetch the results of a cursor into a numpy array. I have tried to create a record. But I keep getting the following error : TypeError: expected a readable buffer object.

Basically my goal is to populate a numpy array or a pandas dataframe with the results from a database query, but have the datatypes kept intact (hence the record object specified by the dtype. ).

I'm using MSSQL 2008 R2 on a Windows 7 box. Python 2.7.

Here's my table and data:

CREATE TABLE dbo.records
(
    AID        int       NOT NULL,
    ID        int       NOT NULL,
    EID        int       NOT NULL,
    PCODE      char(2)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    PVALUE     float(53) NOT NULL,
    SI         float(53) NOT NULL,
    SC         float(53) NOT NULL,
    EE         float(53) NOT NULL,
    CONSTRAINT PK_records
    PRIMARY KEY CLUSTERED (AID,ID,EID,PCODE)
)

INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005901, 'GU', 12.886827900148048, 29.206025325689056, 7.301506331422264, 15956.1633 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005902, 'GU', 4.73038822711228, 10.77588154791872, 2.69397038697968, 15922.6067 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005904, 'GU', 22.645755728195648, 50.992083564652032, 12.748020891163008, 15645.0021 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005906, 'GU', 8.925904127986528, 20.274646568966784, 5.068661642241696, 15263.6771 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005913, 'GU', 56.382898744630784, 124.279775860660096, 31.069943965165024, 15985.144 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005914, 'GU', 12.09781233128632, 27.431428572167264, 6.857857143041816, 15950.0621 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005915, 'GU', 44.406190758700864, 98.59373165158272, 24.64843291289568, 15722.7924 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005917, 'GU', 7.133941786197224, 16.22555112649744, 4.05638778162436, 15696.8623 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005918, 'GU', 24.426820608145728, 54.920898223398144, 13.730224555849536, 15326.2144 ) 
go
INSERT INTO records ( AID, ID, EID, PCODE, PVALUE, SI, SC, EE ) 
         VALUES ( 1, 1, 2005919, 'GU', 16.629517313694592, 37.591771677943552, 9.397942919485888, 15664.831 ) 
go

Here's my python script.

import numpy as np
import pandas as pd
import pandas.io.sql as psql
import pyodbc as pyodbc
from pandas import DataFrame
from pandasql import sqldf
from pandasql import load_meat
from types import *
from StringIO import StringIO
from itertools import chain

driver_name = "{SQL Server Native Client 10.0}" #"{SQL Server}"
server_name = "(local)" 
db_name = "test_db"

analysis_qry = """
SELECT p.AID,
       p.EID,
       p.PCODE,
       p.PVALUE,
       p.SI,
       p.SC,
       p.EE
FROM """+db_name+"""..records p
"""

cnxn = pyodbc.connect('Trusted_connection=yes',driver='{SQL Server Native Client 10.0}',server=server_name,database=db_name)
cursor = cnxn.cursor()

#fetch all the rows back into arrays
cursor.execute(analysis_qry)
results = cursor.fetchall()
D = np.fromiter(chain.from_iterable(results), dtype=('i4, i4, a2, f8, f8, f8, f8'), count=-1)

WalkingDeadFan · Accepted Answer · 2014-08-12 03:52:28Z

2

There are two ways of doing this. Both of them perform roughly the same.

Method 1 - to numpy array:

cursor = cnxn.cursor()
cursor.execute(analysis_qry)
results = cursor.fetchall()
num_rows = int(cursor.rowcount)
arr_analysis = np.fromiter((tuple (row) for row in results), dtype='i4,i4,a4,f8,f8,f8,f8', count=num_rows)

Method 2 - to pandas dataframe:

analysis_df = psql.read_frame(analysis_qry, cnxn)

answered Aug 12, 2014 at 3:52

WalkingDeadFan

6472 gold badges8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MSSQL Cursor to Numpy Array/Dataframe - TypeError: expected a readable buffer object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related