GBase 8a MPP Cluster Development Interface: Python

Cong Li - Aug 19 - - Dev Community

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

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:

Image description

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

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

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

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

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

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

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
Enter fullscreen mode Exit fullscreen mode
  • 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']
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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"
Enter fullscreen mode Exit fullscreen mode
  • Generate Database Table 
# Generate migration files
python manage.py makemigrations

# Specify the database for table creation
python manage.py migrate --database=other
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • 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']
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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"
Enter fullscreen mode Exit fullscreen mode
  • 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)
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
Image
Utilizing JUNEO-JS to do cross chain transaction
Image
The lucky sort
Image
Top 5 Java Bugs (and Their Solutions) Every Developer Should Know
Image
Best open-source tools for DevOps and microservices
Image
Inspirasi Dapur Berwarna Pink: Memadukan Gaya dan Keceriaan di Ruang Memasak

jasaarsitekmedan, arsitekmedan, jasakontraktormedan, kontraktormedan
Image
Deploy a lightweight BI solution with your first dashboard in 5 steps
Image
Connect Namecheap Domain to KnownHost
Image
I'm trying to obfuscate an .exe file obtained after compiling my .NET 8 project with the "produce a single file" option.
Image
WHAT DO PEOPLE THINK OF SCAMMED BTC RECOVERY - ULTIMATE HACKER JERRY
Image
Travel Booking Using Next.js 14, Prisma, TailwindCSS & Next Auth
Image
Understanding call, apply, and bind in JavaScript with Simple Examples
Image
Day 3:From Strings to Numbers: Demystifying JavaScript Type Conversions
Image
Unlocking WhatsApp Secrets: Analyzing Chat Data with Next.js 14 Web App
Image
Day 5: State and Lifecycle Methods - ReactJS

react, webdev, javascript, statemanagement
Image
U.S. CTOs Flocking to LATAM IT Talent
Image
5 Simple Steps to Create a Stunning UI Element Card
Image
Streamline Your Projects: A Cutting-Edge Neon Task Management Starter Kit
Image
SQLAlchemy: The Powerhouse of ORMs
Image
Diary of Software Developer #5 - Learning Report
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player