Python: Connect to Sybase SAP Database (Linux Based OS ONLY)

Connection to Sybase DB using python & sqlanydb module

Motivation

Most of the times, connection to databases using python are usually straight forward.

Though connection to Sybase SAP database what quite difficult to achieve when relying on free/opensource software only. I took me some time and research to get this relatively simple & free way to connect to Sybase SAP databases using python.

Note that this could have been done easily if you have access to a Sybase SAP account to get the needed connectors from the official site.

In this tutorial we’ll use SQL Anywhere connection modules

Resources:

SAP SQL Anywhere Database Client Download – SAP SQL Anywhere – Community Wiki

Procedure

We’ll first install SQL Anywhere Database Client download from the above link.

Below are details of each step

• Install the Client:

# Download the Client:
wget http://d5d4ifzqzkhwt.cloudfront.net/sqla17client/sqla17_client_linux_x86x64.tar.gz

# After the download is completed, extract the tar file
tar -xzf sqla17_client_linux_x86x64.tar.gz

# Enter the extracted directory & launch the installation
cd client17010/
## run the setup
./setup

The Installation goes as below below:

• Choose Location where you are installing the software
  
• Then you have to read the LICENSE AGREEMENT & Agree
  
• Select the components to be installed (By default all is selected)
  
• Begin the installation by typing “S” & hit Enter
  
• Choose the installation directory (Ensure that your user has the needed permission on the folder) & hit Enter
  
• Validate the settings by Typing “Y” & hit Enter
  
• Initiate installation by hitting Enter
  

After completed installation of the Client

• Add the Client Environmental Variables

# Add the below line in **.profile** or **.bashrc**
## This will load all the needed environmental variables for SQLanywhere17
source "/opt/sqlanywhere17/bin64/sa_config.sh"

• Verify that Environmental Variables are set

# Check the libs paths are loaded
echo $LD_LIBRARY_PATH
## Expected content:
# **/opt/sqlanywhere17/lib64:/opt/sqlanywhere17/lib32**

# Ensure that bin paths are loaded
echo $PATH
## Expected content:
# **/opt/sqlanywhere17/bin64:/opt/sqlanywhere17/bin32**

Next, we install needed python modules:

python -m pip install sqlanydb

Then, we test the connection to the database:

import sqlanydb
# Initiate connection to the database
DB_PARAMS = {"HOST": "localhost",
	     "USER": "username",
	     "PASSWORD","password",
	     "DB","db_name"}
conn = sqlanydb.connect(host=DB_PARAMS['HOST'], uid=DB_PARAMS['USER'], pwd=DB_PARAMS['PASSWORD'], dbn=DB_PARAMS['DB'])
# Instantiate cursor
curs = conn.cursor()
# Execute a query
curs.execute("SELECT 'success' as connection_status FROM SYS.DUMMY")
result = curs.fetchall()
print(result)

Thanks, I hope it helped you.

You can email me if you need assistance or more.

Don’t forget to like and share.🥳