querydb-skill — 查询db-技能

查询DB 技能 - 数据库查询 & 测试用例生成 功能概述 模块 用途 Database命令行工具ent 通用数据库连接和查询（MySQL / PostgreSQL） DbFixture db_fixture 模式，为接口测试注入真实数据 TestCase生成器 从数据库按场景自动生成接口测试用例 测试环境数据库信息 参数 值 host 10.115.96.247 port 3306 database jxindependent0 user jxindependent password Xj2zCkLJXTkEJ5j 购方税号 91440606MA4WHN8C8X 核心表：bw_jms_mAIn1 字段 说明 INV_TYPE 票种代码（01/31/32/85等，2位字符） INV_KIND 发票代码（税控票/数电纸票，12位） INV_NUM 发票号码（税控票8位，数电纸票8位） E_INV_NUM 数电号码（数电票/数电纸票，20位） BUYER_TAXNO 购方税号 SELLER_TAXNO 销方税号 SELLER_NAME 销方名称 INV_DATE 开票日期（date类型） 创建_DATE 入库时间（datetime类型） 更新_DATE 更新时间（datetime类型） INV_状态 发票状态 RED_LOCK_FLAG 红字标识 INV_DEDU_结果 认证/合规结果 IS_COLLECT_ALL 是否全量采集 NOT_DEDUCTIBLE 是否不可抵扣 发票类型规则（重要）

查询发票时，根据票种（INV_TYPE）决定使用哪个字段作为接口入参：

票种大类 INV_TYPE 代码 invoiceCode（接口入参） invoiceNumber（接口入参） 数电票 31/32/51/59/61/83/84 ❌ 不传 E_INV_NUM（20位数电号码） 税控票 01/03/04/08/10/11/14/15 INV_KIND INV_NUM 数电纸票 85/86/87/88 INV_KIND INV_NUM 安装依赖 pip 安装 pymysql # MySQL pip 安装 psycopg2-binary # PostgreSQL（可选）

使用方法

• Database命令行工具ent - 基本查询

db = Database命令行工具ent( host="10.115.96.247", port=3306, user="jxindependent", password="Xj2zCkLJXTkEJ5j", database="jxindependent0", char设置="utf8mb4" )

# 查多条 rows = db.查询("SELECT INV_TYPE, INV_NUM, E_INV_NUM FROM bw_jms_mAIn1 LIMIT 5")

# 查单条 row = db.查询_one( "SELECT INV_TYPE, INV_KIND, INV_NUM, E_INV_NUM, BUYER_TAXNO " "FROM bw_jms_mAIn1 WHERE INV_TYPE='31' AND E_INV_NUM IS NOT NULL LIMIT 1" )

# 计数 total = db.count("SELECT COUNT(*) FROM bw_jms_mAIn1 WHERE INV_TYPE='01'")

db.close()

• DbFixture - db_fixture 模式

将真实数据注入到测试用例的 {{占位符}} 中：

from db_查询 导入 DbFixture

# 查税控票样本 fixture = DbFixture( connection={ "host": "10.115.96.247", "port": 3306, "user": "jxindependent", "password": "Xj2zCkLJXTkEJ5j", "database": "jxindependent0", "char设置": "utf8mb4" }, queries=[ { "name": "tax_invoice", "sql": "SELECT INV_KIND, INV_NUM, BUYER_TAXNO FROM bw_jms_mAIn1 " "WHERE INV_TYPE='01' AND INV_NUM IS NOT NULL LIMIT 1", "m应用ing": { "invoiceCode": "INV_KIND", "invoiceNumber": "INV_NUM", "taxNo": "BUYER_TAXNO" } }, { "name": "digital_invoice", "sql": "SELECT E_INV_NUM, BUYER_TAXNO FROM bw_jms_mAIn1 " "WHERE INV_TYPE='31' AND E_INV_NUM IS NOT NULL LIMIT 1", "m应用ing": { "invoiceNumber": "E_INV_NUM", "taxNo": "BUYER_TAXNO" } } ] ) data = fixture.获取_data() # 返回: {"invoiceCode": "044031900101", "invoiceNumber": "12345678", "taxNo": "xxx"}

• TestCase生成器 - 自动生成测试用例

支持三种场景模式：

模式 方法 适用场景 single_row 生成_single_row_case() 按单条记录字段映射到接口入参 multi_row_safe 生成_multi_row_safe_case() 多条记录但 < 500 条时，映射查询条件到入参 invoice_by_taxno 生成_invoice_by_taxno_case() 按购方税号查发票，自动加时间范围 示例：按票种查各类型发票样本 导入 sys sys.path.insert(0, r"C:\Users\PC\.workbuddy\技能s\查询DB-技能\scripts") from db_查询 导入 TestCase生成器

conn = { "host": "10.115.96.247", "port": 3306, "user": "jxindependent", "password": "Xj2zCkLJXTkEJ5j", "database": "jxindependent0", "char设置": "utf8mb4" }

with TestCase生成器(connection=conn, buyer_tax_no="91440606MA4WHN8C8X") as gen:

# 税控票（01）：invoiceCode=INV_KIND, invoiceNumber=INV_NUM case = gen.生成_single_row_case( case_id="TC_001", case_name="01-增值税专用发票", case_group="票种覆盖-税控票", sql="SELECT INV_KIND, INV_NUM, BUYER_TAXNO FROM bw_jms_mAIn1 " "WHERE INV_TYPE='01' AND INV_KIND IS NOT NULL AND INV_NUM IS NOT NULL LIMIT 1", m应用ing={ "taxNo": "BUYER_TAXNO", "invoiceCode": "INV_KIND", "invoiceNumber": "INV_NUM", }, )

# 数电票（31）：invoiceCode 不传，invoiceNumber=E_INV_NUM（20位） case = gen.生成_single_row_case( case_id="TC_009", case_name="31-数电票（增值税专用发票）", case_group="票种覆盖-数电票", sql="SELECT E_INV_NUM, BUYER_TAXNO FROM bw_jms_mAIn1 " "WHERE INV_TYPE='31' AND E_INV_NUM IS NOT NULL " "AND BUYER_TAXNO IS NOT NULL LIMIT 1", m应用ing={ "taxNo": "BUYER_TAXNO", "invoiceNumber": "E_INV_NUM", # 20位数电号码 # 注意：不映射 invoiceCode！数电票没有发票代码 }, )

cases = gen.获取_cases() print("生成 {} 个用例".格式化(len(cases)))

批量生成：生成_from_scenarios scenarios = [ { "mode": "single_row", "case_id": "TC_001", "case_name": "01-增值税专用发票", "case_group": "票种覆盖-税控票", "sql": "SELECT INV_KIND, INV_NUM, BUYER_TAXNO FROM bw_jms_mAIn1 " "WHERE INV_TYPE='01' AND INV_KIND IS NOT NULL LIMIT 1", "m应用ing": {"taxNo":"BUYER_TAXNO","invoiceCode":"INV_KIND","invoiceNumber":