Lindorm:Use DBUtils to develop applications

Prerequisites

Before you begin, make sure you have:

• Python 3.8 or later installed

• Your client IP address added to the Lindorm instance whitelist. For details, see Configure whitelists

• LindormTable 2.3.1 or later. To check or upgrade your version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance

Install dependencies

Install phoenixdb and DBUtils:

pip install phoenixdb==1.2.0
pip install DBUtils==3.0.2

Connect to LindormTable using a connection pool

The following example creates a PooledDB connection pool and demonstrates upsert and query operations against LindormTable.

#!/usr/bin/python3

from dbutils.pooled_db import PooledDB
import importlib


class DBUtilsDemo:

    def __init__(self, url, user, password, database):
        config = {
            'url': url,
            'lindorm_user': user,
            'lindorm_password': password,
            'database': database,
            'autocommit': True
        }
        db_creator = importlib.import_module("phoenixdb")
        # Create a connection pool based on DBUtils.
        self.pooled = PooledDB(db_creator,
                               maxcached=10,
                               maxconnections=50,
                               blocking=True,
                               ping=1,
                               **config)

    # Obtain a connection from the connection pool.
    def _connect(self):
        try:
            r = self.pooled.connection()
            return r
        except Exception as e:
            print("Failed to connect:" + str(e))

    # Return a connection to the connection pool.
    def _close(self, conn, stmt):
        if stmt:
            stmt.close()
        if conn:
            conn.close()

    # Query a single row of data.
    def select_row(self, sql):
        connection = self._connect()
        statement = None
        try:
            statement = connection.cursor()
            statement.execute(sql)
            row = statement.fetchone()
            return row
        except Exception as e:
            print(e)
        finally:
            self._close(connection, statement)

    # Query multiple rows of data.
    def select_rows(self, sql):
        connection = self._connect()
        statement = None
        try:
            statement = connection.cursor()
            print(sql)
            statement.execute(sql)
            rows = statement.fetchall()
            return rows
        except Exception as e:
            print(e)
        finally:
            self._close(connection, statement)

    # Update and insert data to a table.
    def upsert_data(self, sql_upsert):
        connection = self._connect()
        statement = None
        try:
            statement = connection.cursor()
            statement.execute(sql_upsert)
            connection.commit()
        except Exception as e:
            print(e)
        finally:
            self._close(connection, statement)

    # Update and insert data to a table by specifying parameters.
    def upsert_data_prams(self, sql_upsert, prams):
        connection = self._connect()
        statement = None
        try:
            statement = connection.cursor()
            statement.execute(sql_upsert, prams)
            connection.commit()
        except Exception as e:
            print(e)
        finally:
            self._close(connection, statement)

if __name__ == '__main__':
    url = 'http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060'
    user = 'root'
    password = 'root'
    database = 'test'

    poolUtils = DBUtilsDemo(url, user, password, database)
    poolUtils.upsert_data("upsert into tb(id,name,address) values ('i001','n001','a001')")

    params = ['i002', 'n002', 'a002']
    poolUtils.upsert_data_prams("upsert into tb(id,name,address) values (?,?,?)", params)

    rows = poolUtils.select_rows("select * from tb")
    print(rows)

    row = poolUtils.select_row("select * from tb limit 1")
    print(row)

    row = poolUtils.select_row("select * from tb where id = 'i001' limit 1")
    print(row)

Usage notes:

1. Set url to the HTTP address from the LindormTable SQL endpoint. Remove the jdbc:lindorm:table:url= prefix. For example, if the console shows jdbc:lindorm:table:url=http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060, set url to http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060. To find the endpoint, see View the endpoints of LindormTable.

2. Set user and password to the credentials for LindormTable. You can find them in the LindormTable cluster management system.

3. Set database to the name of the database to connect to.

Connection pool parameters

Tune maxcached and maxconnections based on your workload.

Limitations

• ping does not apply to phoenixdb connections. You can run the ping command to test connectivity of connections established by DBUtils, but this method is not applicable to connections established by phoenixdb.

• Connections idle for more than 10 minutes raise NoSuchConnection. If a connection has not been used for more than 10 minutes, call _connect() to get a fresh connection from the pool.

What's next

• View the endpoints of LindormTable

• Configure whitelists

• Release notes of LindormTable