Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

How to Connect Python with Tibero

Document Type | Technical Information

Category | Interface/Integration

Applicable Product Version | Tibero6 FS07

Document Number | TIITI050

 

Overview

This article explains how to connect a Python application to a Tibero database.

It uses an ODBC-based integration method, and includes the full procedure from Python environment setup, Tibero Client and ODBC configuration, connection testing, to executing Python sample code.

The configuration environment described in this article is as follows.

  • OS: CentOS 7

  • Python: 3.6 (IP: 192.168.41.149)

  • Tibero: Tibero6 FS07 (IP: 192.168.41.148)

 

Method

1. Install Python

Install Python 3

Run the command below as the root account.

  • yum install python3-devel

 

Change Python Settings (Optional)

This applies if you have installed Python 3.6 in an environment where Python 2.7 is already installed.

  • Check the current Python version and path

    • python -V

    • which python

  • Change the OS default Python setting

    • update-alternatives --install /bin/python python /bin/python2.7 1-translate

    • update-alternatives --install /bin/python python /bin/python3.6 2

    • update-alternatives --config python (Select option 2)

    • python -V

    • ln -s /bin/pip3.6 /bin/pip

    • pip -V

  • If a yum error occurs due to changing the default Python version, edit the first line of the following files.

    File to edit: /usr/bin/yum

    Change the first line #!/usr/bin/python to .7
    File to edit: /usr/libexec/urlgrabber-ext-down

    Change the first line #!/usr/bin/python to #!/usr/bin/python2.7

 

2. Install ODBC

Install unixODBC

  • yum install unixODBC-devel

 

Install pyodbc

  • yum install gcc-c++

  • pip3 install pyodbc
    (Depending on your Python settings, you may use the pip command)

 

Configure odbc.ini

Create and configure the /etc/odbc.ini or .odbc.ini file in your user home directory.

1) If using tbdsn.tbr

[ODBC Data Sources]
tibero6 = Tibero6 ODBC driver    => DSN name in isql, python

[ODBC]
Trace     = 1                   => Log level
TraceFile = /home/python/odbc.trace
                             => ODBC log path and file name

[tibero6]                       => Must match the red name above
Driver      = /home/python/tibero6/client/lib/libtbodbc.so
                             => Absolute path to Tibero's libtbodbc.so
Description = Tibero6 ODBC Datasource
SID         = tibero          gt; Alias in tbdsn.tbr
User        = tibero            => Tibero user to connect
Password    = tmax              => Password for the Tibero user
 

2) If not using tbdsn.tbr

[ODBC Data Sources]
tibero6 = Tibero6 ODBC driver        => DSN name in isql, python

[ODBC]
Trace     = 1                       => Log level
TraceFile = /home/python/.odbc.trace
                                   => ODBC log path and file name

[tibero6]                           => Must match the red name above
Driver      = /home/python/lib/libtbodbc.so
                                   => Absolute path to Tibero's libtbodbc.so
Description = Tibero6 ODBC Datasource
Server      = 192.168.41.148         => Tibero IP to connect
Port        = 8629                   => Tibero Port to connect
User        = tibero                 => Tibero user to connect
Password    = tmax                   => Password for the Tibero user
Database;= tibero                 => Tibero DB Name to connect
 

 

3. Install Tibero Client

Configure tbdsn.tbr

Set Tibero connection information as follows.

tibero=(
  (INSTANCE=(HOST=192.168.41.148)
    (PORT=8629)
    (DB_NAME=tibero)
  )
)
 

tbsql Connection Test (Available only when Client is installed)

Add the following environment variables to the user profile.

/python/tibero6/
export PATH=.:$TB_HOME/client/bin:$PATH
export LD_LIBRARY_PATH=.:$TB_HOME/client/lib:$LD_LIBRARY_PATH

Then test the connection with the command below.

  • tbsql tibero/tmax@tibero

 

4. ODBC Connection Test

  • isql -v tibero6

 

5. Python and Tibero Integration

Verify pyodbc Import

Create a test.py file with the content below and run python test.py. There should be no errors.

  • import pyodbc

 

Integration Test Using Python Sample Code

import pyodbc

db = pyodbc.connect('DSN=tibero6;UID=tibero;PWD=tmax')

db.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
db.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
db.setdecoding(pyodbc.SQL_WMETADATA, encoding='utf-32le')
db.setencoding(encoding='utf-8')

cursor = db.cursor()

cursor.execute('select * from python_test;')
data = cursor.fetchall()

for x in data:
   print(x[0])    # The statement following the python for loop must be indented.

cursor.close()
db.close()

The code in red needs to be added if an encoding-related error occurs.

 

 

Related to