DEV Community

Cong Li
Cong Li

Posted on

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  
Enter fullscreen mode Exit fullscreen mode

2. Install unixODBC if not already installed

yum install unixODBC unixODBC-devel  
Enter fullscreen mode Exit fullscreen mode

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  
Enter fullscreen mode Exit fullscreen mode

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

gbase01 onsoctcp 192.168.1.10 9088  
Enter fullscreen mode Exit fullscreen mode

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  
Enter fullscreen mode Exit fullscreen mode

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  
Enter fullscreen mode Exit fullscreen mode

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  
Enter fullscreen mode Exit fullscreen mode

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>  
Enter fullscreen mode Exit fullscreen mode

Install Python Dependencies

pip install pyodbc  
Enter fullscreen mode Exit fullscreen mode

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)  
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)