Skip to Content
Author's profile photo Former Member

HANA connectivity with ODBC on Windows using Python


As part of the Big Data group,  my assignment was to learn and document HANA learnings, and possible extend/find some of the uses that have not quite been documented yet. My first task was to find a way to connect with HANA from a Windows machine using an ODBC/JDBC connection. It was tricky at first to find a way to connect with ODBC, but digging into HANA online resources (corporate and community web portals) helped in the end. I also got to learn a lot about HANA technology and associated tools.

HANA ODBC connection through Windows (step 1)

This part was tricky because you will not find the proper driver using the regular ODBC connection wizard on Control Panel -> Administrative Tools -> Data Sources (ODBC) . The procedure is described here: .

Testing the ODBC connection using HANA Command Line Interface (step 2)

It is also easy to open up a HANA CLI once you have the ODBC connection installed and you have a working installation of the HANA Client on your system. All you need to do is execute: C:\Program Files (x86)\SAP\hdbclient\hdbsql.exe from the Windows Command Line;

Microsoft Windows [Version 6.1.7601]

Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\SomeUser>cd C:\Program Files (x86)\SAP\hdbclient

C:\Program Files (x86)\SAP\hdbclient>hdbsql.exe

Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands

       \q to quit


Loading the ODBC Driver using Python (step 3)

You should be able to use any scripting programming language to do this part. Since we were using Python extensively in our project I did this part using Python. I used the pyodbc module which can be found here: . So if you do:

import pyodbc

conn = pyodbc.connect(‘DSN=<the_name_of_the_connection_you_created_in_step_1>;UID=<user_id>;PWD=<some_password>’)

cursor = conn.cursor()

this should create an ODBC connection, along with a cursor, the later of which can be used to execute SQL Scripts (HANA queries) and fetch the results, such as:


print cursor.fetchall()


Thanks to a post by Ethan:

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Vinoth Kumar Thirusangu
      Vinoth Kumar Thirusangu

      Hi Parvez,

      Is there a way to cofigure the connection without goin to DSN..I have installed the HDBODBC32  driver... like the one below for HANA...

      DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

      In the above connection striing i tried with our driver name HDBODBC32 and other required information...but it doesnt getting the error "Data source name not found and no default driver specified"

      It works fine when i configure DSN...but it would be great if i can set it from python code...



      Author's profile photo Vinoth Kumar Thirusangu
      Vinoth Kumar Thirusangu

      Hi Parvez,

      I got the solution, below is it,




      Author's profile photo Ronald Konijnenburg
      Ronald Konijnenburg

      Hi what is "database" in this case?

      Author's profile photo Ronald Konijnenburg
      Ronald Konijnenburg

      Hi, step 1 points to a death URL. What needs to be done in step 1?

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Unfortunately the link points to a SAP internal WIKI.

      This is tricky to check from within SAP network as you will always find the WIKI ...

      However, all you've to do is to setup a ODBC data source on your system via the ODBC Data Source Administration panel from your Windows control panel (just type odbc into the search bar).

      (if your Windows is 64-Bit then you should use the 64-Bit version of R and the 64-Bit version of the HANA ODBC driver. In case you use the 32-Bit version of R the 32-Bit HANA ODBC driver is required. To setup a data source for this one on a 64-Bit Windows you need to start the 32-Bit version of the Data Source Administration via program C:\Windows\SysWOW64\odbcad32.exe )

      Once you are there, you simply create a system data source.

      The connection test can also be done from this dialogue box - simply click on "Connect" and type in the logon data.

      The connection test via HDBSQL however, does not check the ODBC connectivity as HDBSQL doesn't use ODBC at all. The HSQL call above actually does only open the tool and does not connect to anything... 😉

      Finally, if you want to avoid creating the DSN beforehand and want to declare the connection parameters dynamically, the only way I found it to be working is by using the SERVERNODE parameter like this:

      conn = pyodbc.connect('driver={HDBODBC};SERVERNODE={<hostname>:<port>}', uid='<username>', pwd='<password>')

      For example:

      conn = pyodbc.connect('driver={HDBODBC};SERVERNODE={vml3012:30015}', uid='LARS', pwd='***')

      would connect me to my HANA toy system.

      Cheers, Lars

      Author's profile photo Former Member
      Former Member

      I created the DSN (32-Bit) and was able to test it successfully using the dialogue box. However, when I execute a few lines of Python code similar to what is described in this blog I see no results. What could be going wrong?

      This is what I am trying to run:

      import pyodbc

      cnxn = pyodbc.connect('DSN=HDB;UID=******;PWD=******'')

      cursor = cnxn.cursor()

      cursor.execute("SELECT COUNT(*) from *****.******")

      print cursor.fetchall()