本文介紹如何通過Python串連池DBUtils串連Lindorm寬表引擎。
前提條件
準備工作
安裝phoenixdb 1.2.0版本和DBUtils 3.0.2版本,範例程式碼如下:
pip install phoenixdb==1.2.0
pip install DBUtils==3.0.2
使用樣本
#!/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")
# 基於DBUtils的串連池
self.pooled = PooledDB(db_creator,
maxcached=10,
# 串連池的最大空閑串連數,可以根據實際需要調整
maxconnections=50,
# 串連池的最大串連數, 可以根據實際需要調整
blocking=True,
# 如果串連池沒有閒置串連,是否等待。True:等待空閑串連;False:不等待並報錯。
ping=1,
# 檢查服務端是否可用
**config)
# 從串連池擷取串連
def _connect(self):
try:
r = self.pooled.connection()
return r
except Exception as e:
print("Failed to connect:" + str(e))
# 歸還串連到串連池
def _close(self, conn, stmt):
if stmt:
stmt.close()
if conn:
conn.close()
# 查詢單條記錄
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)
# 查詢多條記錄
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)
# 更新與插入
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)
# 更新與插入帶參數
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__':
# Lindorm 寬表SQL串連地址。
url = 'http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060'
# 使用者名稱,根據實際情況做替換。您可以通過Lindorm叢集管理系統查看使用者名稱和密碼。
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)
重要
Lindorm寬表SQL地址的擷取方式,請參見查看寬表引擎串連地址。
url參數中填寫的串連地址為Lindorm寬表SQL地址刪除
jdbc:lindorm:table:url=
部分後的HTTP地址。例如,控制台擷取到的串連地址為jdbc:lindorm:table:url=http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060
,則url參數中填寫的地址為http://ld-bp1p7e07ohamf****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060
。DBUtils建立的串連支援使用ping方法探測,phoenixdb建立的串連不支援使用ping方法探測。
在長時間不使用串連時(目前時間為10分鐘),會出現NoSuchConnection異常,您需要重新從串連池擷取串連。