GBase 8a MPP Cluster Development Interface: Python

Today, we'll introduce the Python driver interface for the GBase database. The GBase-connector-python interface is a driver program that allows Python to connect to the GBase database (hereafter referred to as "GBase Connector").

1. Overview

1.1 Introduction to GBase-connector-python

The GBase Connector interface is developed based on the Python Database API Specification 2.0. While it adheres to the standard, it also supports the following features:

Full support for GBase 8a and 8a clusters
Full support for GBase 8a data types
Full support for SQL standard syntax
Support for binary stream insertion and updates
Support for optimized batch inserts
Support for executing multiple SQL statements and retrieving multiple result sets
Support for TCP/IP protocol
Support for connection pool operations
Compatibility with popular frameworks like SQLAlchemy, Flask, and Django
Support for the Superset framework

1.2 Version Information

The current version of GBase Connector is 9.5.3, which is an upgrade from the original GBasePython3-9.5.2 version. The upgrade includes added support for the Django and Flask frameworks.

The following table shows the product versions and Python versions compatible with the GBase Connector interface:

Interface Version	GBase Version	Python Version	Compatibility
GBase-connector-python-9.5.3	GBase 8a Standalone GBase 8a Cluster	Python 3.8, Python 3.9, Python 3.10, Python 3.11	↑

The following table shows the framework versions compatible with the GBase Connector interface:

Interface Version	Framework Information	Supported Framework Versions
GBase-connector-python-9.5.3	SQLAlchemy	1.4.30—1.4.50
GBase-connector-python-9.5.3	Superset	3.0.0
GBase-connector-python-9.5.3	Flask	2.3.x, 2.2.5
GBase-connector-python-9.5.3	Django	4.2.x

1.3 Architecture Information

2. Basic Usage

2.1 Installation

The GBase Connector interface can be used without installation or it can be installed and then imported into Python scripts. If used with a framework, installation is required.

2.1.1 Unzip and Use

After obtaining the GBase Connector source code package, unzip it and copy the "lib/gbase" directory to your project. Then, create a Python script file in the same directory level as "gbase" to use the interface, as shown below:

2.1.2 Installation

To install, ensure that pip, setuptools, and wheel are installed in your Python environment (these packages are included by default after installing Python).

2.1.2.1 Installation on Windows

Navigate to the unzipped source directory, then execute the following command in the directory containing the setup.py file:

cd GBase-connector-python-9.5.3-src
python setup.py install

If the following information is displayed, the installation is complete:

Installed c:\users\zqh\.conda\envs\django-42611\lib\site-packages\gbase_connector_python-9.5.3-py3.11.egg
Processing dependencies for gbase-connector-python==9.5.3
Finished processing dependencies for gbase-connector-python==9.5.3

Note: This installation method is not recommended for Ubuntu environments.

2.1.2.2 Installation on Linux

Navigate to the unzipped source directory, then execute the following commands in the directory containing the setup.py file:

cd GBase-connector-python-9.5.3-src
# Generate a whl file (the current directory will create dist/gbase_connector_python-9.5.3-py3-none-any.whl)
python setup.py bdist_wheel
# Use the generated whl file to install
cd dist
pip install gbase_connector_python-9.5.3-py3-none-any.whl

If the following information is displayed, the installation is complete:

Processing ./gbase_connector_python-9.5.3-py3-none-any.whl
Installing collected packages: gbase-connector-python
Successfully installed gbase-connector-python-9.5.3

2.2 Creating a Database Connection

2.2.1 Connection Parameters

The table below describes the definitions and constraints of the parameters available for connecting to GBase. The asterisks (*) next to the parameter names indicate synonymous parameter names:

Parameter Name	Parameter Type	Default Value	Description
user (username*)	string		Username, the username for logging into the database
password (passwd*)	string		Password, the password for logging into the database
host	string	127.0.0.1	Host address, the address of the GBase service gcluster node
port	int	5258	Port number, the port used to connect to the GBase service
database (db*)	string		Database name, the default database to use after connecting
charset	string	utf8mb4	Character set used for the connection
use_unicode	bool	True	Whether to use Unicode
autocommit	bool	False	Whether to use autocommit; this parameter is effective when GBase transaction features are enabled
pool_name	string		Connection pool name; the name is limited to alphanumeric characters and special characters `.`, `_`, `*`, `$`, `#`, and cannot exceed 64 characters
pool_size	int	5	Connection pool size, must be greater than 0 and less than or equal to 32
pool_reset_session	bool	True	Whether to reset session variables when the connection is returned to the pool
auth_plugin	string		Specifies the authentication method when creating the connection

2.2.2 Creating/Closing Connections

The following operations demonstrate how to create and close connections:

from gbase.connector import connection

# Connecting to a compatibility mode cluster
conn = connection.GBaseConnection(user='gbase', password='gbase20110531',
                                  host='192.168.11.121',
                                  database='test')

# Connecting to a multi-VC mode cluster
conn = connection.GBaseConnection(user='gbase', password='gbase20110531',
                                  host='192.168.11.121',
                                  database='vc_name.test')

conn.close()

from gbase.connector import connect, Error

config = {
   'user': 'gbase',
   'passwd': 'gbase20110531',
   'host': '192.168.11.121',
   'db': 'test',  # For multi-VC mode: vc_name.test
   'port': 5258
}

try:
   conn = connect(**config)
except Error as e:
   print(e)
finally:
   conn.close()

import gbase.connector
from gbase.connector import errorcode, Error

try:
   conn = gbase.connector.connect(user='gbase',
                                  database='test', host='192.168.43.121')
except Error as err:
   if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
       print("Username or password is incorrect")
   elif err.errno == errorcode.ER_BAD_DB_ERROR:
       print("Database does not exist")
   else:
       print(err)
else:
   conn.close()

3. Third-Party Framework Support

3.1 SQLAlchemy

SQLAlchemy is a Python framework that allows database operations through ORM. Using SQLAlchemy, there is no need to write native SQL statements; you can operate on objects using Python syntax, which will automatically be mapped to SQL statements. The GBase Connector version 9.5.3 has developed a dialect package for GBase 8a, specifically for SQLAlchemy version 1.4.49. Users can operate the database directly using Python syntax via the GBase Connector driver.

3.2 Superset

The current version of GBase Connector and the GBase dialect package have been adapted based on Superset 3.0.0 and SQLAlchemy-1.4.49. To create a GBase connection in Superset, you need to install the GBase dialect package first. Refer to section 3.1.1 for the installation process.

3.3 Flask

In Flask, using the ORM framework requires the SQLAlchemy framework and the Flask-SQLAlchemy third-party package. In the supported Flask framework versions of the current GBase Connector, SQLAlchemy version 1.4.49 and Flask-SQLAlchemy version 2.5.1 are used.

3.4 Django

Since Django's built-in modules like admin, auth, contenttypes, and sessions require tables that depend on primary keys, unique keys, foreign keys, and index constraints in the database, and GBase 8a, as an analytical database, does not support these constraints, using GBase 8a in the Django framework requires abandoning the Django admin backend (see Usage Example 1). If development needs require the admin backend, you can leverage MySQL, PostgreSQL, or other transactional databases to create tables in the transactional database using Django's ability to connect to multiple databases (see Usage Example 2).

3.4.1 Usage Instructions

Before connecting to the GBase database using the ORM framework in the Django framework, you need to install the GBase Connector in the current Python development environment. Obtain the source code files of the GBase dialect package adapted to the Django framework, and navigate to the "gbase-dialect-Django_4.2.6" directory:

Copy the "gbase" directory to the Django dialect package directory of the current Python development environment: PYTHON_HOME/.../site-packages/django/db/backends.

Alternatively, download the Django-4.2.6 source code files and copy the "gbase" dialect package files to the Django dialect package directory: Django-4.2.6/django/db/backends, then manually install the Django framework.

Next, create a Django project and configure the database connection route in the settings.py file:

DATABASES = {
   'gbase_db': {
       'ENGINE': 'gbase.connector.django',
       'NAME': 'test',  # For multi-VC mode: vc_name.test
       'USER': 'gbase',
       'PASSWORD': 'gbase20110531',
       'HOST': '127.0.0.1',
       'PORT': '5258'
   }
}

3.4.2 Example Usage One

To disable the admin backend in the Django framework, you can leverage Django's ability to support multiple database connections and configure a custom database router. Detailed instructions can be found in the Django official documentation: Multiple Databases. The configuration steps are as follows:

Create an App

# Execute the command to create an app in the current Django project's Python environment
python manage.py startapp appName

settings.py Configuration

# Register the app
INSTALLED_APPS = [
   'django.contrib.admin',
   'django.contrib.auth',
   'django.contrib.contenttypes',
   'django.contrib.sessions',
   'django.contrib.messages',
   'django.contrib.staticfiles',
   'appName',
]

# Configure database connections
DATABASES = {
   # Default database connection is empty
   'default': {},

   # Add GBase 8a database connection configuration
   'other': {
       'ENGINE': 'gbase.connector.django',
       'NAME': 'test',
       'USER': 'gbase',
       'PASSWORD': 'gbase20110531',
       'HOST': '127.0.0.1',
       'charset': 'utf8',
       'PORT': '5258',
   }
}

# Configure app modules and database mapping
# The admin, auth, contenttypes, sessions modules are for Django's admin backend
# 'admin_db' is a non-existent database configuration to prevent generating tables for the admin backend
# 'appName' is the user-created module, specified to use the GBase 8a database configuration
# 'other' specifies the database configuration for the current module
DATABASE_APPS_MAPPING = {
   "admin": "admin_db",
   "auth": "admin_db",
   "contenttypes": "admin_db",
   "sessions": "admin_db",
   "appName": "other",
}

# Database router configuration
# GBaseDjangoDemo: Project name
# database_router: Router configuration Python file
# GBaseRouter: Database router configuration class
DATABASE_ROUTERS = ['GBaseDjangoDemo.database_router.GBaseRouter']

database_router.py This file is created in the same directory as the settings.py file.

# Custom database router configuration class
class GBaseRouter:
   """
   Database router that specifies the database for model operations
   """

   # List of admin backend modules
   admin_route_app_labels = {"admin", "auth", "contenttypes", "sessions"}

   # List of app names using GBase 8a as a repository
   other_route_app_labels = {"appName"}

   def db_for_read(self, model, **hints):
       """
       Specify the database for read operations on model objects
       """
       if model._meta.app_label in self.admin_route_app_labels:
           return "admin_db"
       if model._meta.app_label in self.other_route_app_labels:
           return "other_db"
       return None

   def db_for_write(self, model, **hints):
       """
       Specify the database for write operations on model objects
       """
       if model._meta.app_label in self.admin_route_app_labels:
           return "admin_db"
       if model._meta.app_label in self.other_route_app_labels:
           return "other_db"
       return None

   def allow_relation(self, obj1, obj2, **hints):
       """
       Allow relations between obj1 and obj2 if applicable; return None if undecidable
       """
       if (
           obj1._meta.app_label in self.other_route_app_labels
           or obj2._meta.app_label in self.other_route_app_labels
       ):
           return True
       return None

   def allow_migrate(self, db, app_label, model_name=None, **hints):
       """
       Define whether migration operations are allowed on the database alias 'db'
       This restricts the default Django-generated tables to the admin_db.
       If the migration command does not specify admin_db, tables will not be created even if the default database is configured.
       """
       if app_label in self.admin_route_app_labels:
           return False
       return None

models.py Custom table structure defined by the user.

from django.db import models

# Create your models here.
class Test(models.Model):
    test_id = models.AutoField(primary_key=True)
    test_small_int = models.SmallIntegerField(null=True)
    test_int = models.IntegerField(default=0, null=False)
    test_big_int = models.BigIntegerField(default=0)
    test_decimal = models.DecimalField(max_digits=5, decimal_places=2)
    test_float = models.FloatField(null=True)

    # Corresponds to the longblob type in GBase
    test_binary = models.BinaryField(max_length=1024)

    test_bool = models.BooleanField(default=False)

    # Corresponds to the varchar type in GBase
    test_str = models.CharField(max_length=255, default='', db_index=True)

    # Corresponds to the longtext type in GBase
    test_text = models.TextField(max_length=1000, null=True)

    test_date = models.DateField(auto_now=True)
    test_time = models.TimeField(auto_now=True)
    test_date_time = models.DateTimeField(auto_now=True)

    class Meta:
        app_label = "appName"
        db_table = "test"

Generate Database Table

# Generate migration files
python manage.py makemigrations

# Specify the database for table creation
python manage.py migrate --database=other

3.4.3 Example Usage Two

When using GBase 8a within the Django framework and retaining the admin backend, you can leverage Django’s ability to support multiple database connections. This is achieved by configuring a custom database router, storing the admin backend data in a transactional database such as MySQL or PostgreSQL. Detailed instructions can be found in the Django official documentation: Multiple Databases. The configuration steps are as follows:

Create an App

# Execute the command to create an app in the current Django project's Python environment
python manage.py startapp appName

settings.py Configuration

# Register the app
INSTALLED_APPS = [
   'django.contrib.admin',
   'django.contrib.auth',
   'django.contrib.contenttypes',
   'django.contrib.sessions',
   'django.contrib.messages',
   'django.contrib.staticfiles',
   'appName',
]

# Configure database connections
DATABASES = {
   # Default database connection is empty
   'default': {},

   # Add MySQL database connection configuration
   'admin_db': {
       'ENGINE': 'django.db.backends.mysql',
       'NAME': 'admin_info',
       'USER': 'root',
       'PASSWORD': 'pass',
       'HOST': '127.0.0.1',
       'PORT': '3306',
   },

   # Add GBase 8a database connection configuration
   'other_db': {
       'ENGINE': 'gbase.connector.django',
       'NAME': 'other_info',
       'USER': 'gbase',
       'PASSWORD': 'gbase20110531',
       'HOST': '127.0.0.1',
       'charset': 'utf8',
       'PORT': '5258',
   }
}

# Configure app modules and database mapping
# The admin, auth, contenttypes, sessions modules are for Django's admin backend
# 'admin_db' is used to store tables required for the admin backend
# 'appName' is the user-created module, specified to use the GBase 8a database configuration
# 'other_db' specifies the database configuration for the current module
DATABASE_APPS_MAPPING = {
   "admin": "admin_db",
   "auth": "admin_db",
   "contenttypes": "admin_db",
   "sessions": "admin_db",
   "appName": "other_db",
}

# Database router configuration
# GBaseDjangoDemo: Project name
# database_router: Router configuration Python file
# GBaseRouter: Database router configuration class
DATABASE_ROUTERS = ['GBaseDjangoDemo.database_router.GBaseRouter']

database_router.py This file is created in the same directory as the settings.py file.

# Custom database router configuration class
class GBaseRouter:
   """
   Database router that specifies the database for model operations
   """

   # List of admin backend modules
   admin_route_app_labels = {"admin", "auth", "contenttypes", "sessions"}

   # List of app names using GBase 8a as a repository
   other_route_app_labels = {"appName"}

   def db_for_read(self, model, **hints):
       """
       Specify the database for read operations on model objects
       """
       if model._meta.app_label in self.admin_route_app_labels:
           return "admin_db"
       if model._meta.app_label in self.other_route_app_labels:
           return "other_db"
       return None

   def db_for_write(self, model, **hints):
       """
       Specify the database for write operations on model objects
       """
       if model._meta.app_label in self.admin_route_app_labels:
           return "admin_db"
       if model._meta.app_label in self.other_route_app_labels:
           return "other_db"
       return None

   def allow_relation(self, obj1, obj2, **hints):
       """
       Allow relations between obj1 and obj2 if applicable; return None if undecidable
       """
       if (
           obj1._meta.app_label in self.admin_route_app_labels
           or obj2._meta.app_label in self.admin_route_app_labels
       ):
           return True
       if (
           obj1._meta.app_label in self.other_route_app_labels
           or obj2._meta.app_label in self.other_route_app_labels
       ):
           return True
       return None

   def allow_migrate(self, db, app_label, model_name=None, **hints):
       """
       Define whether migration operations are allowed on the database alias 'db'
       This restricts the default Django-generated tables to the admin_db.
       If the migration command does not specify admin_db, tables will not be created even if the default database is configured.
       """
       if app_label in self.admin_route_app_labels:
           return db == "admin_db"
       return None

models.py Custom table structure defined by the user.

from django.db import models

class Test(models.Model):
    test_id = models.IntegerField()
    test_name = models.CharField(max_length=50)
    text = models.TextField(max_length=1000, null=True)
    create_time = models.DateTimeField(auto_now_add=True)

    class Meta:
        app_label = "appName"
        db_table = "test"

admin.py Configure the table fields to be displayed in the admin backend.

from django.contrib import admin
from appName.models import Test

# Register your models here.
class TestAdmin(admin.ModelAdmin):
   # Set the table fields to be displayed in the admin backend
   list_display = ["test_id", "test_name", "text", "create_time"]

admin.site.register(Test, TestAdmin)

Generate Database Table

# Generate migration files
python manage.py makemigrations

# Specify the database for table creation
python manage.py migrate --database=admin_db
python manage.py migrate --database=other_db

# Create admin user
python manage.py createsuperuser --database=admin_db

Blog