topic Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python in Architecture

Connecting to SPDS (SAS Scalable Performance Data Server) from Python

burchill — Wed, 04 May 2022 21:15:09 GMT

We have been asked to add Python to the mix of software we are using. SPDS has been a central Database server for a long time and it works well with Base SAS but we have had a request to allow python connections.

I am wondering about the best route and connection strings to support our python users in a Base SAS environment.

Thanks.

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

LinusH — Thu, 05 May 2022 09:53:01 GMT

If it's enought to access data throgh SQL, you should be able to connect to SPDS own ODBC driver connection.

If you want to submit SAS code, you need to conenct to a SAS session that has SPDS libnames available. Here check the documentation for SASPy.

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

burchill — Thu, 05 May 2022 12:11:24 GMT

Thanks for the suggestion - yes we are using ODBC for connecting with other applications and those work (R, STATA for example). When we attempt to connect with pyodbc I get the following response:

import pyodbc
cnxn = pyodbc.connect('dsn=spds.common; uid=XXXX; pwd=XXXX')

Traceback (most recent call last) : File "
cnxn = pyodbc.connect('dsn=spds.common; uid=XXXX; pwd=XXXX')
pyodbc.Error: ('S1C00', '[S1C00] [SAS] [SAS ODBC Driver not capable (-1) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')

I apologize I should have put this in the first post.

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

Patrick — Thu, 05 May 2022 12:38:16 GMT

Did you follow all these steps? https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/spdsug/p0d2bbuq1jkfnun1ksjw86cy5kfn.htm

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

burchill — Thu, 05 May 2022 13:00:24 GMT

Thank you - yes. We have ODBC connections to SPDS working from other applications (Access, R, STATA). There are some hiccups writing to the SAS SPDS but reading works fine.

The other suggestion about using saspy is being explored as well.

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

Patrick — Thu, 05 May 2022 13:20:17 GMT

Hmmm.... If this works out of R etc. but the message is "SAS ODBC Driver not capable" then it's may-be also worth to contact SAS TechSupport.

SASPy should work as well - also for writing data to SPDS. Method sasdata2dataframe should allow to load SPDS tables into a dataframe.

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

AllanBowe — Thu, 05 May 2022 22:32:00 GMT

We've built an open-source server for Base SAS - which comes with a full REST API.

You could easily spin this up and trigger a Stored Program (SAS code that queries your SPD tables) using the `/SASjsApi/stp/execute` endpoint.

Happy to jump on a call to help you get set up - we haven't documented the multi-user capability yet: https://github.com/sasjs/server

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

burchill — Tue, 17 May 2022 14:08:53 GMT

Thank you for all of your responses so far - the community is great.

We have pyodbc working now for read access. It required the autocommit=True option to be set. It spins out a number of errors but the connection works. There was a suggestion to use sqlAlchemy but there is no "dialect" information (that we could find) for SPDS.

We have also been looking at saspy as that seemed like a good solution but getting it to work in our environment has worked out (yet).

import pyodbc

import os

cnxn = pyodbc.connect('dsn=spds.DOMAIN; uid=XXXXXX)'; pwd=XXXXX'), autocommit=True)

Output:

05/12/22 16:42:33:(5480) SPDS- Message file spdsmsg.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file wglobal.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file engmsg.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file sqlmsg.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file supmsg.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file sqlmsob.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file xffile.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file zsem.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file dates.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file wxmsg.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file attrbm.m open failed: errno=2

05/12/22 16:42:33:(5480) SPDS- Message file hdpmsg.m open failed: errno=2

***MESSAGE NOT FOUND***

SPDS_NOTE: User SPDSUSER(ACL Group EXTERNAL) connected to SPD(NT) 5.4 Server at XXX.XXX.XXX.X.

SPDS_NOTE: Libref X0000001 was successfully assigned as follows:

Engine: SPDSENG

Physical Name: :5420m:\meta\XXXXX\

Re: Connecting to SPDS (SAS Scalable Performance Data Server) from Python

burchill — Tue, 17 May 2022 14:11:43 GMT

I should add that we have read access working, write is still being checked. Write from other applications works but it is very slow, still exploring.