Python with SAP Databases

How can we leverage Python programming language in SAP infrastructure? – in multiple ways I must say.

In this blog we will try to understand how we can establish connection with the database/s and how can we execute some basic queries.

But why Python?

Python is easy to learn, flexible, supports connectivity for almost all databases and OS platforms, supports connectivity to SAP using rfc modules, strong community support and has a lot of potential in automation.

How can we setup Python?

Simple,  download python package – https://www.python.org/downloads/  for your OS and follow the blog: https://realpython.com/installing-python/

IDLE – Python’s Integrated Development and Learning Environment, will be used for writing and executing code.

Before discussing about the Database connectivity using Python programs let us first understand some basics of Python programming.

What is function is Python?

Lets say you have a small set of instructions (e.g. addition of two numbers) that you have to repeat in your program body often, each time with a different values.

Instead of writing those instructions each time (and for each set of numbers) what we can do is define a Python function and the function can then be called every time with new set of values for addition.

A simple example below for understanding:

function is named ‘add’ and is called twice in two print commands

What is a module in Python?

A module is more complex in nature, it may contain multiple code instructions and multiple functions.

Unlike function, a module doesn’t have to be a part of the program body but it can be imported into the program from outside.

Why are we discussing Python modules?

Because some of them are efficiently designed to be used to make Database connectivity simple and easy.

Python module – ‘pyodbc’

‘pyodbc’ is an open source Python module that provides access to Open Database Connectivity (ODBC) databases.

pyodbc uses database drivers to establish connection with the database hence the ODBC driver must be installed on the database server.

Installing a Python module:

Python has an extensive library, most of the modules come along with the installation package.

A utility called ‘pip’  is used for installation. As long as you have added the Python installation path to variable ‘PATH’ you can just open the command prompt and execute ‘pip install <module name>’

Installing pyodbc in the screen shot below:

pip install pyodbc

Note – sometimes you may be prompted that pip is outdated. The command prompt will inform accordingly and an update will have to be performed.

Now that we are ready with IDLE, python libraries & modules, database drivers and the database (of-course), we can try setting up a connection.

Remember the Procedure: Open connection -> Attach Database -> Execute query/queries -> Detach database -> Close connection.

> Connecting to MsSql server:

Python program to connect to MsSQL server

If you observe in the example program above, you will notice a few things

1. Python module ‘pyodbc’ is imported in the program. That’s important because it is needed for the connectivity.

2. The variable ‘connection_string’ holds the specifics of your database (driver, Db server, database name, and logon credentials – if not trusted connection you can pass user and password values somewhat like below)

3. The python functions within pyodbc are called, example – ‘connect’, ‘cursor’, ‘execute’.

4. A select query (select @@version) is passed and the output is printed.

5. It is important that the connection is then closed.

Its that simple.

Output of the above program:

> Connecting to Oracle Database

The program logic remains the same, the same module is used and the query is passed in the similar manner.. except the values you pass in the connection string change and the correct driver is called with correct database name, the port and the query changes.

Python program for Oracle DB connection

Output:

> Connecting to SAP HANA database

Steps:

1. Install the Pyhton driver – HDBCLI:

pip install “C:\<path>\hdbclient\hdbcli-N.N.N.zip”

—

2. The module used here is called ‘dbapi’ and is available in hdbcli

from hdbcli import dbapi

—

3. Connection string –

dbapi.connect(address=’localhost’, port=3xxxx, user=’system’, password=’xxxxxx’)

example connection string:

from hdbcli import dbapi

conn = dbapi.connect(

address=”<hostname>”,

port=3<NN>MM,

user=”<username>”,

password=”<password>”

)

–End

There are other Python modules available as well:

cx_Oracle for Oracle

cdata for SAP HANA

ibm_db for DB2 and many more.