Python and SAP: Part 2 - Getting data from SAP

So now you're connected to SAP (see part 1 here), but what do you do with it?

Well, as the name implies, PyRFC allows you to perform Remote Function Calls from Python. In theory, any valid Function Module in SAP should work. Use Transaction SE37 to view your function module and check the inputs. 

In my case, I wanted to read data from SAP. Therefore, the function module that interested me was RFC_READ_TABLE. In SE37, you can see the inputs for this function module (but I've done the work for you below).

Also, worth noting: You can do a search and see the general fear this solution produces. People get quite alarmist about anything that "queries" SAP. However, let's remember that there are serious controls that you can put in place (see this link). With a good basis team, calling functions and reading data from SAP can be locked down tight. It's clearly a concern, but completely manageable. 

On to the fun part! Let's create a little class for getting data out of SAP. We're going to use a class, rather than a script, so that we can build on it in the next lesson.

The connection class

First, we need to initialize our connection again. 

from pyrfc import Connection
import re

class main():
    def __init__(self):
        ASHOST='my.server.name'
        CLIENT='000'
        SYSNR='00'
        USER='mouser'
        PASSWD='mypassword'
        self.conn = Connection(ashost=ASHOST, sysnr=SYSNR, client=CLIENT, user=USER, passwd=PASSWD)

Ok - pretty simple When we initialize the class, we connect to SAP. You could pass the login parameters to the class - might even be a good idea.

Now we want to create a function to call RFC_READ_TABLE. To do this, we need to pass:

• Fields: the fields we want to return
• SQLTable: The table we want to query
• Where: Any WHERE conditions
• MaxRows: The maximum number of rows to return
• FromRow: The starting row of the result set to use

Therefore, our query function should look like this:

    def qry(self, Fields, SQLTable, Where = '', MaxRows=50, FromRow=0):
        """A function to query SAP with RFC_READ_TABLE"""

        # By default, if you send a blank value for fields, you get all of them
        # Therefore, we add a select all option, to better mimic SQL.
        if Fields[0] == '*':
            Fields = ''
        else:
            Fields = [{'FIELDNAME':x} for x in Fields] # Notice the format

        # the WHERE part of the query is called "options"
        options = [{'TEXT': x} for x in Where] # again, notice the format

        # we set a maximum number of rows to return, because it's easy to do and
        # greatly speeds up testing queries.
        rowcount = MaxRows

        # Here is the call to SAP's RFC_READ_TABLE
        tables = self.conn.call("RFC_READ_TABLE", QUERY_TABLE=SQLTable, DELIMITER='|', FIELDS = Fields, \ 
                                OPTIONS=options, ROWCOUNT = MaxRows, ROWSKIPS=FromRow)

        # We split out fields and fields_name to hold the data and the column names
        fields = []
        fields_name = []

        data_fields = tables["DATA"] # pull the data part of the result set
        data_names = tables["FIELDS"] # pull the field name part of the result set

        headers = [x['FIELDNAME'] for x in data_names] # headers extraction
        long_fields = len(data_fields) # data extraction
        long_names = len(data_names) # full headers extraction if you want it

        # now parse the data fields into a list
        for line in range(0, long_fields):
            fields.append(data_fields[line]["WA"].strip())

        # for each line, split the list by the '|' separator
        fields = [x.strip().split('|') for x in fields ]

        # return the 2D list and the headers
        return fields, headers

Easy right? The comments should give you an idea what is happening in each step. The result from SAP is in text, so much of the function is just parsing the data.

So if you wanted to use your new class, you could do something like this:

# Init the class and connect
# I find this can be very slow to do... 
s = main() 

# Choose your fields and table
fields = ['MATNR', 'EAN11']
table = 'MEAN'
# you need to put a where condition in there... could be anything
where = ['MATNR <> 0']

# max number of rows to return
maxrows = 10

# starting row to return
fromrow = 0

# query SAP
results, headers = s.qry(fields, table, where, maxrows, fromrow)

print headers
print results

You can download the full class from here. I suggest you modify it and learn what each portion does before you do any testing on your own system. I've left out error checking and assertions for simplicity.

That's it for now. In the next segment, I'll show you how to write standard SQL and have it converted to the RFC format. In the meantime, ping me if you have questions in the comments.

-ab