Skip to Content

How to Use SQLAlchemy with SAP HANA, express edition

test
0 %
How to Use SQLAlchemy with SAP HANA, express edition
Details

How to Use SQLAlchemy with SAP HANA, express edition

2020-01-05
Integrate SQLAlchemy with the SAP HANA, express edition by identifying SAP HANA-specific changes needed to complete the SQLAlchemy users tutorial.

You will learn

This is a standalone How-To on basic integration of the SQLAlchemy Object Relational Mapper (ORM) with HANA, express edition.

Prerequisites

  • SAP HANA, express edition must be running.

The available HANA, express edition versions (1.0 SP12 and 2.0 SP00) have different default instance numbers. The published Tutorials and How-Tos refer to the default HANA 2.0 SP00 instance numbers. When using the SP12 version please use the old default instance number and port (3<instance number>15):

HANA Express Version Default Instance ID Port
1.0 SP12 00 30013
2.0 SP00 90 39013

Experimental Packages

This tutorial relies on the experimental packages pyhdb and sqlalchemy-hana.
pyhdb is a pure python database client for HANA.
sqlalchemy-hana is a SQLAlchemy dialect for HANA. A SQLAlchemy dialect is the system SQLAlchemy uses to communicate with a particular database.

Background

SQLAlchemy is an Object Relational Mapper (ORM) that maps python objects to SQL database management system entities. This how-to tutorial will show you how to:

This tutorial assumes that you are installing SQLAlchemy and SQLAlchemy HANA packages on your client operating system and not on the HANA, express edition virtual machine itself. If you intend to install SQLAlchemy on your HANA, express edition virtual machine, you should review the tutorial “Installing Python Modules on HANA, express edition” to prepare the virtual machine for installing Python packages from pypi.


Step 1: Install `SQLAlchemy` and `SQLALchemy` HANA modules
  1. Install sqlalchemy-hana by following the Install SQLAlchemy by following the SQLAlchemy installation instructions. The sqlalchemy-hana installation will also install SQLAlchemy if it is not already installed in your environment.

  2. The sql-alchemy-hana implementation relies on the pyhdb driver, a pure python HANA database driver. Run the pip installer to install pyhdb: pip install pyhdb. If successful, this pip install command should indicate a successful installation and the version number (e.g. Successfully installed pyhdb-0.3.3).

  3. Verify that you can connect to the HXE database by creating a small test script.

    • Create a file called test_hxe_conn.py and add the following python code to it. Modify the value based on your needs, using the hints in the comments. At a minimum, you will need to change the host ip address and the password.

      import pyhdb
      
      # Define the connection to the HXE database
      connection = pyhdb.connect(
          # replace with the ip address of your HXE Host (This may be a virtual machine)
          host="10.172.91.122",
          # 39013 is the systemDB port for HXE on the default instance of 90.
          # Replace 90 with your instance number as needed (e.g. 30013 for instance 00)
          port=39013,
          #Replace user and password with your user and password.
          user="system",
          password="mypassword"
          )
      
      #Connect to the database and issue a dummy query to verify connectivity
      cursor = connection.cursor()
      cursor.execute("SELECT 'Hello HANA SQLAlchemists' FROM DUMMY")
      #This should return "Hello HANA SQLAlchemists"
      myString = cursor.fetchone()[0]
      print myString
      #Close the cursor
      connection.close()
      
    • Run your test script in the python shell: python test_hxe_conn.py. If you are able to connect, the following string should be returned: Hello HANA SQLAlchemists. If you are unable to connect, make sure that the connectivity setting–host, user, port and password–are correct for your database.

Log on to answer question
Step 2: Run tutorial

After making the changes below, you will be able to follow the SQLAlchemy tutorial using HANA Express as the backend database.

  1. In the Connecting section of the tutorial, you will need to replace the following code for connecting to sqlite:

    from sqlalchemy import create_engine
    engine = create_engine('sqlite:///:memory:', echo=True)
    

    with the following to connect to HANA, express edition:

    from sqlalchemy import create_engine
    engine = create_engine('hana+pyhdb://{}:{}@{}:{}'.format('system', 'myPassword', '10.125.20.57', '39013'))
    

    The order of the format arguments is user , password, IP address or host name, port.

  2. In the Declare a Mapping section of the tutorial you will need to add a line defining the __table_args__ to the User class.

    from sqlalchemy import Column, Integer, String
    class User(Base):
        __tablename__ = 'users'
        #This line was added for HANA express
        __table_args__ = {'hana_table_type': 'COLUMN'}        
    
        id = Column(Integer, primary_key=True)
        name = Column(String)
        fullname = Column(String)
        password = Column(String)
    
        def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)
    
    
Log on to answer question

Next Steps

Back to top