0

I need to access spectroscopic data (x,y arrays) that are stored in a SQL Server 2008 database.

A while ago - with your help - I was able to get the data into a format I know how to work with.

Python 3 - decode spectroscopy data (Base64, IEEE754)

At the moment I use the SQLCMD tool to access the database via:

p = Popen(["sqlcmd", "-S", host, "-U",...], stdin=PIPE, stdout=PIPE, stderr=PIPE)
output = p.stdout.read()

This is the only way I get the output I know how to deal with:

0xA79DFD6F14DCC23F242D1E1DFF50C43FAE9C624BD174...

Now I am not allowed to use the SQLCMD tool anymore and need to get the data via pyodbc. I use pandas as well.

I try the following and it works well for all columns of the database but the important one.

I'm lost here - can you please advise how to proceed? The output looks like:

[(b'\xa7\x9d\xfdo\x14\xdc\xc2?$-\x1e\x1d\xffP\xc4?\xae\x9cbK\xd1t\xc4?\x8a\xfc}a

and the settings I use are as follows:

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

query1 =  '''SELECT TOP 1 [DATA] FROM [ABC].[dbo].[DATABASE]'''

df_q1 = pd.read_sql(query1, sql_conn)

I messed with encoding settings and failed.

enter image description here

EDIT: array.array('d', df_q1.Data[0]) gives the expected numbers.

Chris
  • 13
  • 3
  • 1
    FYI, the `image` data type has been deprecated for 15 years (iirc); you should really have "updated" to use the `varbinary(MAX)` data type by now. SQL Server 2008 also reached End of Life over a year ago, and you should be looking at upgrade paths ASAP there as well. – Thom A Oct 12 '20 at 15:44
  • Regarding the text representation of binary data in Python see [this answer](https://stackoverflow.com/a/62053947/2144390). – Gord Thompson Oct 12 '20 at 16:36
  • What's your goal here, are you trying to get an array of floats so you can do something interesting with it? Have you tried using `array.array('f', data)` to turn the Python buffer in the dataframe into an array of floats? – AlwaysLearning Oct 13 '20 at 01:25
  • Thank's a lot for the promt help! @Larnu - You're right with your comments and it's a commercial package coming prepacked with that `SQL Server` version and using that deprecated `image` data type. @Gord - Thanks for forwarding - it is exactly the issue I was facing! @AlwaysLearing - Thanks for your comment, I could make it work the way you wrote. `array.array('d', df_q1.Data[0])` gives the expected numbers. How can I mark this as the accepted aswer? – Chris Oct 13 '20 at 07:15

0 Answers0