在入职的时候得到了这样一个练手任务,可以增强对数据库的理解以及各种编程语言接入达梦数据库时的掌握
用C、JAVA、PYTHON、GO、Node 完成命令行工具,最少完成三种语言。工具定义如下:
工具定义:工具包解压后是一个命令行工具(JAVA、PYTHON、GO 编译好的程序;同时有一个配置文件conf.conf),toolDM文件夹下包括conf.conf和工具
conf.conf:该文件每一行都是一个语法完整、正确的SQL,以/r/n结尾
工具:该工具具有三个入参,第一个是连接串,后两个均为数字,如java -jar SYSDBA/SYSDBA@127.0.0.1:5236 1 3,表示和数据库实例SYSDBA/SYSDBA@127.0.0.1:5236 交互,每3秒执行一次,conf.conf里的第1行的sql,并展示结果。
说明:
1) sql的行和列是不固定的;
2) 展示结果时,是计算本地运行的结果和上次的增量值。
所以这个工具是一个简单的定时SQL轮询小工具:从conf.conf里按行号取一条 SQL,每隔 N 秒对达梦执行一次,把结果打成表格,并且每次都和上一次的结果比,展示增量。典型用途是盯一个会变化的指标(比如会话数),不用反复手敲 SQL。
这里我选择了比较熟悉的Python、Java和GO
| Python (dmPython) | Java (JDBC) | Go (database/sql) | |
|---|---|---|---|
| 驱动 | dmPython 2.5.38 | DmJdbcDriver8.jar | 本地驱动 /Work/go/dm,注册名是 dm |
| 连接串写法 | dmPython.connect(user, password, server, port) | jdbc:dm://host:port + DriverManager | sql.Open(“dm”, “dm://user:pwd@host:port”) |
| 取结果 | cursor.description + fetchall() | ResultSet + getMetaData() | rows.Columns() + rows.Scan() |
做法是逐单元格、按位置和上一轮比较:
代码如附录所示
先进行编译
# Java
cd /Work/DMDBCliTool/java
/dm9/jdk/bin/javac -encoding UTF-8 -cp DmJdbcDriver8.jar ToolDM.java
printf "Main-Class: ToolDM\r\nClass-Path: DmJdbcDriver8.jar\r\n" > manifest.txt
/dm9/jdk/bin/jar cfm toolDM.jar manifest.txt *.class
# Go
cd /Work/DMDBCliTool/go
cp /Work/dameng_exporter/go.sum . # 借用已缓存的依赖校验
PATH=$PATH:/usr/local/go/bin \
GOFLAGS=-mod=mod GOPROXY=off GOSUMDB=off go build -o toolDM .
Python 是解释型语言,无需编译,python310 tool_dm.py 直接跑即可
cd /Work/DMDBCliTool/toolDM
# 连 SYSDBA/Dameng123@localhost:5237,每 3 秒执行 conf.conf 第 1 行
python310 tool_dm.py SYSDBA/Dameng123@localhost:5237 1 3 # python
java -jar toolDM.jar SYSDBA/Dameng123@localhost:5237 1 3 # java
./toolDM SYSDBA/Dameng123@localhost:5237 1 3 # go
conf.conf 内容
SELECT COUNT(*) AS SESSION_CNT FROM V$SESSIONS;
SELECT TABLE_NAME FROM USER_TABLES WHERE ROWNUM <= 5;
SELECT SYSDATE AS NOW_TIME FROM DUAL;
三条分别用来演示:数字增量、多行结果、非数字类型(日期不参与增量比较)。
另开一个窗口 disql SYSDBA/Dameng123@localhost:5237 连上库(每多一个连接会话数 +1),观察工具输出:
[2026-09-18 01:06:01] 执行第 1 行 SQL: SELECT COUNT(*) AS SESSION_CNT FROM V$SESSIONS;
SESSION_CNT
-----------
2 (Δ—)
共 1 行
耗时: 1 ms
[2026-09-18 01:06:04] 执行第 1 行 SQL: SELECT COUNT(*) AS SESSION_CNT FROM V$SESSIONS;
SESSION_CNT
-----------
1 (Δ-1)
共 1 行
Δ-1 / Δ+1 就是相对上一轮的增量;断开 disql 会看到 Δ-1。退出工具:Ctrl+C。
同一个 conf.conf,把 ./toolDM 换成 java -jar toolDM.jar 或 python310 tool_dm.py,输出格式完全一致——说明三种接入方式只是"连接层"不同。
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""toolDM: 每 N 秒执行 conf.conf 中指定行的 SQL, 展示结果并计算与上次的增量"""
import sys
import time
import datetime
import os
import unicodedata
from decimal import Decimal, InvalidOperation
import dmPython
USAGE = "用法: python tool_dm.py user/password@host:port 行号 间隔秒"
def die(msg):
print(msg, file=sys.stderr)
sys.exit(1)
def parse_dsn(dsn):
"""解析 SYSDBA/Dameng123@localhost:5237 形式的连接串"""
try:
left, right = dsn.rsplit('@', 1)
user, password = left.split('/', 1)
host, port = right.rsplit(':', 1)
return user, password, host, int(port)
except ValueError:
die("连接串格式错误, 应为 user/password@host:port")
def load_sqls(path):
"""conf.conf 每行一条 SQL, 以 \\r\\n 结尾"""
with open(path, 'rb') as f:
data = f.read().decode('utf-8')
return [x for x in data.split('\r\n') if x != '']
def to_number(v):
if v is None or isinstance(v, (datetime.date, datetime.time)):
return None
try:
return Decimal(str(v))
except (InvalidOperation, ValueError):
return None
def disp_width(s):
"""显示宽度: 全角字符按 2 计"""
return sum(2 if unicodedata.east_asian_width(ch) in ('W', 'F') else 1 for ch in s)
def pad(s, w):
return s + ' ' * (w - disp_width(s))
def fmt(v):
return 'NULL' if v is None else str(v)
def print_result(columns, rows, last):
display = []
for r, row in enumerate(rows):
line = []
for c, cur in enumerate(row):
cur_str = fmt(cur)
cur_num = to_number(cur)
show = cur_str
if last is not None and r < len(last) and c < len(last[r]):
old_num = to_number(last[r][c])
if cur_num is not None and old_num is not None:
diff = cur_num - old_num
sign = '+' if diff >= 0 else ''
show = "%s (Δ%s%s)" % (cur_str, sign, diff)
elif cur_num is not None:
show = "%s (Δ—)" % cur_str
line.append(show)
display.append(line)
widths = [disp_width(col) for col in columns]
for line in display:
for i, cell in enumerate(line):
widths[i] = max(widths[i], disp_width(cell))
def join_row(cells):
return ' | '.join(pad(cells[i], widths[i]) for i in range(len(cells)))
print(join_row(columns))
print('-+-'.join('-' * w for w in widths))
for line in display:
print(join_row(line))
print("共 %d 行" % len(rows))
def main():
# 管道/重定向下也即时输出(默认全缓冲)
if hasattr(sys.stdout, 'reconfigure'):
sys.stdout.reconfigure(line_buffering=True)
if len(sys.argv) != 4:
die(USAGE)
dsn = sys.argv[1]
try:
line_no = int(sys.argv[2])
interval = int(sys.argv[3])
except ValueError:
die("行号和间隔秒必须是正整数")
if line_no < 1 or interval < 1:
die("行号和间隔秒必须是正整数")
conf_path = os.path.join(os.getcwd(), 'conf.conf')
if not os.path.exists(conf_path):
die("当前目录下找不到 conf.conf: %s" % conf_path)
sqls = load_sqls(conf_path)
if line_no > len(sqls):
die("行号超出范围: conf.conf 共 %d 行" % len(sqls))
sql = sqls[line_no - 1]
user, password, host, port = parse_dsn(dsn)
try:
conn = dmPython.connect(user=user, password=password, server=host, port=port)
except Exception as e:
die("连接数据库失败: %s" % e)
cursor = conn.cursor()
last = None
try:
while True:
start = time.time()
print("[%s] 执行第 %d 行 SQL: %s" % (
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), line_no, sql))
try:
cursor.execute(sql)
if cursor.description:
columns = [d[0] for d in cursor.description]
rows = cursor.fetchall()
else:
columns, rows = [], []
print_result(columns, rows, last)
last = rows
except Exception as e:
print("执行 SQL 失败: %s" % e, file=sys.stderr)
print("耗时: %d ms" % ((time.time() - start) * 1000))
print()
time.sleep(interval)
except KeyboardInterrupt:
pass
finally:
cursor.close()
conn.close()
if __name__ == '__main__':
main()
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* toolDM: 每 N 秒执行 conf.conf 中指定行的 SQL, 展示结果并计算与上次的增量
*/
public class ToolDM {
static final String USAGE = "用法: java -jar toolDM.jar user/password@host:port 行号 间隔秒";
public static void main(String[] args) {
if (args.length != 3) {
System.err.println(USAGE);
System.exit(1);
}
String dsn = args[0];
int lineNo;
int interval;
try {
lineNo = Integer.parseInt(args[1]);
interval = Integer.parseInt(args[2]);
} catch (NumberFormatException e) {
System.err.println("行号和间隔秒必须是正整数");
System.exit(1);
return;
}
if (lineNo < 1 || interval < 1) {
System.err.println("行号和间隔秒必须是正整数");
System.exit(1);
}
String[] sqls;
try {
sqls = loadSqls("conf.conf");
} catch (IOException e) {
System.err.println("读取 conf.conf 失败: " + e.getMessage());
System.exit(1);
return;
}
if (lineNo > sqls.length) {
System.err.println("行号超出范围: conf.conf 共 " + sqls.length + " 行");
System.exit(1);
}
String sql = sqls[lineNo - 1];
ConnInfo ci = parseDsn(dsn);
try {
Class.forName("dm.jdbc.driver.DmDriver");
} catch (ClassNotFoundException e) {
System.err.println("找不到达梦 JDBC 驱动, 请确认 DmJdbcDriver8.jar 与 toolDM.jar 在同一目录: " + e);
System.exit(1);
return;
}
try (Connection conn = DriverManager.getConnection(
"jdbc:dm://" + ci.host + ":" + ci.port, ci.user, ci.password)) {
List<List<Object>> last = null;
while (true) {
long start = System.currentTimeMillis();
System.out.println("[" + now() + "] 执行第 " + lineNo + " 行 SQL: " + sql);
try {
QueryResult qr = execute(conn, sql);
printResult(qr.columns, qr.rows, last);
last = qr.rows;
} catch (SQLException e) {
System.err.println("执行 SQL 失败: " + e.getMessage());
}
System.out.println("耗时: " + (System.currentTimeMillis() - start) + " ms");
System.out.println();
Thread.sleep(interval * 1000L);
}
} catch (InterruptedException e) {
// Ctrl+C 退出
} catch (SQLException e) {
System.err.println("连接数据库失败: " + e.getMessage());
System.exit(1);
}
}
/** conf.conf 每行一条 SQL, 以 \r\n 结尾 */
static String[] loadSqls(String path) throws IOException {
String content = new String(Files.readAllBytes(Paths.get(path)), StandardCharsets.UTF_8);
String[] parts = content.split("\r\n", -1);
List<String> list = new ArrayList<String>();
for (String p : parts) {
if (!p.isEmpty()) {
list.add(p);
}
}
return list.toArray(new String[0]);
}
/** 解析 SYSDBA/Dameng123@localhost:5237 形式的连接串 */
static ConnInfo parseDsn(String dsn) {
int at = dsn.lastIndexOf('@');
if (at < 0) {
System.err.println("连接串格式错误, 应为 user/password@host:port");
System.exit(1);
}
String left = dsn.substring(0, at);
String right = dsn.substring(at + 1);
int slash = left.indexOf('/');
if (slash < 0) {
System.err.println("连接串格式错误, 应为 user/password@host:port");
System.exit(1);
}
String user = left.substring(0, slash);
String password = left.substring(slash + 1);
int colon = right.lastIndexOf(':');
if (colon < 0) {
System.err.println("连接串格式错误, 应为 user/password@host:port");
System.exit(1);
}
String host = right.substring(0, colon);
String port = right.substring(colon + 1);
return new ConnInfo(user, password, host, port);
}
static QueryResult execute(Connection conn, String sql) throws SQLException {
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
ResultSetMetaData md = rs.getMetaData();
int n = md.getColumnCount();
List<String> columns = new ArrayList<String>();
for (int i = 1; i <= n; i++) {
columns.add(md.getColumnLabel(i));
}
List<List<Object>> rows = new ArrayList<List<Object>>();
while (rs.next()) {
List<Object> row = new ArrayList<Object>();
for (int i = 1; i <= n; i++) {
row.add(rs.getObject(i));
}
rows.add(row);
}
return new QueryResult(columns, rows);
}
}
static void printResult(List<String> columns, List<List<Object>> rows, List<List<Object>> last) {
List<List<String>> display = new ArrayList<List<String>>();
for (int r = 0; r < rows.size(); r++) {
List<String> line = new ArrayList<String>();
for (int c = 0; c < columns.size(); c++) {
Object cur = rows.get(r).get(c);
String curStr = fmt(cur);
BigDecimal curNum = toNumber(cur);
String show = curStr;
if (last != null && r < last.size() && c < last.get(r).size()) {
BigDecimal oldNum = toNumber(last.get(r).get(c));
if (curNum != null && oldNum != null) {
BigDecimal diff = curNum.subtract(oldNum);
String sign = diff.signum() >= 0 ? "+" : "";
show = curStr + " (Δ" + sign + diff.stripTrailingZeros().toPlainString() + ")";
}
} else if (curNum != null) {
show = curStr + " (Δ—)";
}
line.add(show);
}
display.add(line);
}
int[] widths = new int[columns.size()];
for (int i = 0; i < columns.size(); i++) {
widths[i] = dispWidth(columns.get(i));
}
for (List<String> line : display) {
for (int i = 0; i < line.size(); i++) {
widths[i] = Math.max(widths[i], dispWidth(line.get(i)));
}
}
StringBuilder sep = new StringBuilder();
for (int i = 0; i < columns.size(); i++) {
if (i > 0) {
sep.append("-+-");
}
for (int j = 0; j < widths[i]; j++) {
sep.append('-');
}
}
System.out.println(joinRow(columns, widths));
System.out.println(sep);
for (List<String> line : display) {
System.out.println(joinRow(line, widths));
}
System.out.println("共 " + rows.size() + " 行");
}
static String joinRow(List<String> cells, int[] widths) {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < cells.size(); i++) {
if (i > 0) {
sb.append(" | ");
}
sb.append(cells.get(i));
for (int w = dispWidth(cells.get(i)); w < widths[i]; w++) {
sb.append(' ');
}
}
return sb.toString();
}
/** 显示宽度: 全角字符按 2 计 */
static int dispWidth(String s) {
int w = 0;
for (int i = 0; i < s.length(); i++) {
w += isWide(s.charAt(i)) ? 2 : 1;
}
return w;
}
static boolean isWide(char c) {
return (c >= 0x1100 && c <= 0x115F) // Hangul Jamo
|| (c >= 0x2E80 && c <= 0xA4CF) // CJK 部首/符号/汉字
|| (c >= 0xAC00 && c <= 0xD7A3) // Hangul 音节
|| (c >= 0xF900 && c <= 0xFAFF) // CJK 兼容表意
|| (c >= 0xFE30 && c <= 0xFE4F) // CJK 兼容形式
|| (c >= 0xFF00 && c <= 0xFF60) // 全角形式
|| (c >= 0xFFE0 && c <= 0xFFE6);
}
static String fmt(Object o) {
return o == null ? "NULL" : String.valueOf(o);
}
static BigDecimal toNumber(Object o) {
if (o == null || o instanceof java.util.Date) {
return null;
}
if (o instanceof BigDecimal) {
return (BigDecimal) o;
}
if (o instanceof Number) {
return new BigDecimal(o.toString());
}
try {
return new BigDecimal(o.toString());
} catch (Exception e) {
return null;
}
}
static String now() {
return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
}
static class QueryResult {
List<String> columns;
List<List<Object>> rows;
QueryResult(List<String> columns, List<List<Object>> rows) {
this.columns = columns;
this.rows = rows;
}
}
static class ConnInfo {
String user;
String password;
String host;
String port;
ConnInfo(String user, String password, String host, String port) {
this.user = user;
this.password = password;
this.host = host;
this.port = port;
}
}
}
package main
// toolDM: 每 N 秒执行 conf.conf 中指定行的 SQL, 展示结果并计算与上次的增量
import (
"database/sql"
"fmt"
"net/url"
"os"
"strconv"
"strings"
"time"
_ "github.com/gaoyuan98/dm"
)
const usage = "用法: ./toolDM user/password@host:port 行号 间隔秒"
func die(format string, a ...interface{}) {
fmt.Fprintf(os.Stderr, format+"\n", a...)
os.Exit(1)
}
type connInfo struct {
user, password, host, port string
}
// 解析 SYSDBA/Dameng123@localhost:5237 形式的连接串
func parseDSN(dsn string) connInfo {
badConn := func() connInfo {
die("连接串格式错误, 应为 user/password@host:port")
return connInfo{}
}
at := strings.LastIndex(dsn, "@")
if at < 0 {
return badConn()
}
left, right := dsn[:at], dsn[at+1:]
slash := strings.Index(left, "/")
if slash < 0 {
return badConn()
}
user, password := left[:slash], left[slash+1:]
colon := strings.LastIndex(right, ":")
if colon < 0 {
return badConn()
}
host, port := right[:colon], right[colon+1:]
return connInfo{user, password, host, port}
}
// conf.conf 每行一条 SQL, 以 \r\n 结尾
func loadSQLs(path string) []string {
data, err := os.ReadFile(path)
if err != nil {
die("读取 conf.conf 失败: %v", err)
}
var lines []string
for _, p := range strings.Split(string(data), "\r\n") {
if p != "" {
lines = append(lines, p)
}
}
return lines
}
type queryResult struct {
columns []string
rows [][]interface{}
}
func query(db *sql.DB, sqlText string) (*queryResult, error) {
rows, err := db.Query(sqlText)
if err != nil {
return nil, err
}
defer rows.Close()
cols, err := rows.Columns()
if err != nil {
return nil, err
}
var result [][]interface{}
for rows.Next() {
vals := make([]interface{}, len(cols))
ptrs := make([]interface{}, len(cols))
for i := range vals {
ptrs[i] = &vals[i]
}
if err := rows.Scan(ptrs...); err != nil {
return nil, err
}
result = append(result, vals)
}
if err := rows.Err(); err != nil {
return nil, err
}
return &queryResult{columns: cols, rows: result}, nil
}
func toNumber(v interface{}) (float64, bool) {
if v == nil {
return 0, false
}
switch x := v.(type) {
case int64:
return float64(x), true
case int32:
return float64(x), true
case int:
return float64(x), true
case float64:
return x, true
case float32:
return float64(x), true
case []byte:
f, err := strconv.ParseFloat(string(x), 64)
return f, err == nil
case string:
f, err := strconv.ParseFloat(x, 64)
return f, err == nil
default:
return 0, false
}
}
func fmtVal(v interface{}) string {
if v == nil {
return "NULL"
}
if b, ok := v.([]byte); ok {
return string(b)
}
if t, ok := v.(time.Time); ok {
return t.Format("2006-01-02 15:04:05")
}
return fmt.Sprint(v)
}
// 显示宽度: 全角字符按 2 计
func dispWidth(s string) int {
w := 0
for _, r := range s {
w += runeWidth(r)
}
return w
}
func runeWidth(r rune) int {
switch {
case r >= 0x1100 && r <= 0x115F, // Hangul Jamo
r >= 0x2E80 && r <= 0xA4CF, // CJK 部首/符号/汉字
r >= 0xAC00 && r <= 0xD7A3, // Hangul 音节
r >= 0xF900 && r <= 0xFAFF, // CJK 兼容表意
r >= 0xFE30 && r <= 0xFE4F, // CJK 兼容形式
r >= 0xFF00 && r <= 0xFF60, // 全角形式
r >= 0xFFE0 && r <= 0xFFE6:
return 2
}
return 1
}
func pad(s string, w int) string {
return s + strings.Repeat(" ", w-dispWidth(s))
}
func printResult(columns []string, rows [][]interface{}, last [][]interface{}) {
display := make([][]string, len(rows))
for r, row := range rows {
display[r] = make([]string, len(row))
for c, cur := range row {
curStr := fmtVal(cur)
curNum, curOk := toNumber(cur)
show := curStr
if last != nil && r < len(last) && c < len(last[r]) {
oldNum, oldOk := toNumber(last[r][c])
if curOk && oldOk {
diff := curNum - oldNum
sign := ""
if diff >= 0 {
sign = "+"
}
show = fmt.Sprintf("%s (Δ%s%g)", curStr, sign, diff)
}
} else if curOk {
show = fmt.Sprintf("%s (Δ—)", curStr)
}
display[r][c] = show
}
}
widths := make([]int, len(columns))
for i, col := range columns {
widths[i] = dispWidth(col)
}
for _, line := range display {
for i, cell := range line {
if w := dispWidth(cell); w > widths[i] {
widths[i] = w
}
}
}
printRow := func(cells []string) {
for i, cell := range cells {
if i > 0 {
fmt.Print(" | ")
}
fmt.Print(pad(cell, widths[i]))
}
fmt.Println()
}
printRow(columns)
seps := make([]string, len(widths))
for i, w := range widths {
seps[i] = strings.Repeat("-", w)
}
fmt.Println(strings.Join(seps, "-+-"))
for _, line := range display {
printRow(line)
}
fmt.Printf("共 %d 行\n", len(rows))
}
func main() {
if len(os.Args) != 4 {
die(usage)
}
dsnArg := os.Args[1]
lineNo, err := strconv.Atoi(os.Args[2])
if err != nil || lineNo < 1 {
die("行号必须是正整数")
}
interval, err := strconv.Atoi(os.Args[3])
if err != nil || interval < 1 {
die("间隔秒必须是正整数")
}
sqls := loadSQLs("conf.conf")
if lineNo > len(sqls) {
die("行号超出范围: conf.conf 共 %d 行", len(sqls))
}
sqlText := sqls[lineNo-1]
ci := parseDSN(dsnArg)
dsn := fmt.Sprintf("dm://%s:%s@%s:%s",
url.QueryEscape(ci.user), url.QueryEscape(ci.password), ci.host, ci.port)
db, err := sql.Open("dm", dsn)
if err != nil {
die("打开数据库失败: %v", err)
}
defer db.Close()
if err := db.Ping(); err != nil {
die("连接数据库失败: %v", err)
}
var last [][]interface{}
for {
start := time.Now()
fmt.Printf("[%s] 执行第 %d 行 SQL: %s\n",
start.Format("2006-01-02 15:04:05"), lineNo, sqlText)
qr, err := query(db, sqlText)
if err != nil {
fmt.Fprintln(os.Stderr, "执行 SQL 失败:", err)
} else {
printResult(qr.columns, qr.rows, last)
last = qr.rows
}
fmt.Printf("耗时: %d ms\n\n", time.Since(start).Milliseconds())
time.Sleep(time.Duration(interval) * time.Second)
}
}
文章
阅读量
获赞
