Connecting to GBase 8s Database Using Python via ODBC

This article describes how to connect to a GBase 8s database using Python via ODBC. By combining the flexibility of Python with the universality of ODBC, we can create powerful and efficient database applications. ODBC allows Python to connect to various databases, including GBase 8s, execute queries, and perform complex data operations.

Configuring GBase 8s Database ODBC Data Source

To connect to a GBase 8s database via ODBC in Python, we first need to configure the ODBC data source. This section explains how to configure the GBase 8s database ODBC data source on a Linux environment.

1. Check if unixODBC is installed

[root@a]# odbcinst -j  
unixODBC 2.3.1  
DRIVERS............: /etc/odbcinst.ini  
SYSTEM DATA SOURCES: /etc/odbc.ini  
FILE DATA SOURCES..: /etc/ODBCDataSources  
USER DATA SOURCES..: /etc/odbc.ini  
SQLULEN Size.......: 8  
SQLLEN Size........: 8  
SQLSETPOSIROW Size.: 8  

2. Install unixODBC if not already installed

yum install unixODBC unixODBC-devel  

3. Install CSDK

tar -xf clientsdt_x.x.x_x86_64.tar -C csdk  
cd csdk
./installclientsdk -i silent -DLICENSE_ACCEPTED=TRUE -DUSER_INSTALL=/opt/gbasecsdk  

4. Write the sqlhosts file for the instance (filename: sqlhosts.odbc)

gbase01 onsoctcp 192.168.1.10 9088  

5. Set environment variables

export GBASEDBTDIR=/opt/gbasecsdk  
export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql  
export GBASEDBTSQLHOSTS=$GBASEDBTDIR/etc/sqlhosts.odbc  
export ODBCINI=/etc/odbc.ini  

6. Edit the /etc/odbcinst.ini file

# Example driver definitions  
[ODBC Drivers]  
GBase ODBC DRIVER=Installed  

[GBase ODBC Driver]  
Driver=/opt/gbasecsdk/lib/cli/iclit09b.so  
Setup=/opt/gbasecsdk/lib/cli/iclit09b.so  
APILevel=1  
ConnectFunctions=YYY  
DriverODBCVer=03.51  
FileUsage=0  
SQLLevel=1  
smProcessPerConnect=Y  

7. Edit the /etc/odbc.ini file

[ODBC Data Sources]  
gbase72=GBase ODBC DRIVER  

[gbaseserver1]  
Driver=/opt/gbasecsdk/lib/cli/iclit09b.so  
Description=GBase ODBC DRIVER  
Database=wwdb  
LogonID=gbasedbt  
pwd=111111  
Servername=gbase01  
CLIENT_LOCALE=en_US.819  
DB_LOCALE=en_US.819  

[ODBC]  
UNICODE=UCS-2  

Trace=0  
TraceFile=/tmp/odbctrace.out  
InstallDir=/extra/informix  
TRACEDLL=idmrs09a.so  

8. Use isql to verify the database connection (gbaseserver1 is the DSN)

[root@localhost FTS_V1.0]# isql gbaseserver1 -v  
+---------------------------------------+  
| Connected!                            |  
|                                       |  
| sql-statement                         |  
| help [tablename]                      |  
| quit                                  |  
|                                       |  
+---------------------------------------+  
SQL>  

Install Python Dependencies

pip install pyodbc  

Python Code Example

import pyodbc

dsn = "DSN=gbaseserver1"  
conn = pyodbc.connect(dsn)  
conn.setencoding(encoding='UTF-8')  
cursor = conn.cursor()  

# Execute a query
cursor.execute("SELECT * FROM emp")  

# Fetch and print the results
rows = cursor.fetchall()  
for row in rows:  
    print(row)  

Through this exploration, we've learned that using Python with ODBC drivers to connect and operate on GBase databases is not only feasible but also offers significant convenience and flexibility. This combination showcases Python's powerful capabilities in database management, enabling efficient completion of tasks such as data querying, updating, deletion, and complex data processing with simple code.