BJQA/insert.py

import pymysql
import uuid

# 连接 MySQL
conn = pymysql.connect(
    host='dujunlong',
    user='root',
    password='123456',
    database='knowledge_graph',
    charset='utf8mb4'
)
cursor = conn.cursor()

# 1. 获取 Block 类型节点
cursor.execute("SELECT node_guid FROM t_node WHERE node_type = 'Block'")
block_ids = [row[0] for row in cursor.fetchall()]

# 2.1 获取 Node(dd接口) 类型节点
cursor.execute("SELECT node_guid FROM t_node WHERE node_type = 'Node(dd接口)'")
node_ids = [row[0] for row in cursor.fetchall()]

# 2.2 获取 DD-DB 类型节点
cursor.execute("SELECT node_guid FROM t_node WHERE node_type = 'DD-DB'")
dd_ids = [row[0] for row in cursor.fetchall()]

# 3.1 获取关系 id（Block-Node）
cursor.execute("SELECT rela_guid FROM t_rela WHERE rela_name = 'Block-Node'")
relation_block_node = cursor.fetchone()
if not relation_block_node:
    raise Exception("关系 Block-Node 不存在")
rela_id_block_node = relation_block_node[0]

# 3.2 获取关系 id（Node-DDDB）
cursor.execute("SELECT rela_guid FROM t_rela WHERE rela_name = 'Node-DDDB'")
relation_node_dd = cursor.fetchone()
if not relation_node_dd:
    raise Exception("关系 Node-DDDB 不存在")
rela_id_node_dd = relation_node_dd[0]

# 4. 插入 Block → Node 的三元组
triples_block_node = []
for head_id, tail_id in zip(block_ids, node_ids):
    triple_id = uuid.uuid4().hex
    triples_block_node.append((triple_id, head_id, rela_id_block_node, tail_id))

cursor.executemany(
    "INSERT INTO t_triplet (tri_guid, head_guid, rela_guid, tail_guid) VALUES (%s, %s, %s, %s)",
    triples_block_node
)
print(f"成功插入 Block-Node 三元组：{len(triples_block_node)} 条")

# 5. 插入 Node → DD-DB 的三元组
triples_node_dd = []
for head_id, tail_id in zip(node_ids, dd_ids):
    triple_id = uuid.uuid4().hex
    triples_node_dd.append((triple_id, head_id, rela_id_node_dd, tail_id))

cursor.executemany(
    "INSERT INTO t_triplet (tri_guid, head_guid, rela_guid, tail_guid) VALUES (%s, %s, %s, %s)",
    triples_node_dd
)
print(f"成功插入 Node-DDDB 三元组：{len(triples_node_dd)} 条")

# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()

# import pymysql
# import uuid
# import re
#
# conn = pymysql.connect(
#     host='dujunlong',
#     user='root',
#     password='123456',
#     database='knowledge_graph',
#     charset='utf8mb4'
# )
# cursor = conn.cursor()
#
# # 获取 Node(dd接口) 节点（假设从 t_node 中 name 字段取出）
# cursor.execute("SELECT node_guid, node_name FROM t_node WHERE node_type = 'Node(dd接口)'")
# node_records = cursor.fetchall()
#
# # 获取关系 ID
# cursor.execute("SELECT rela_guid FROM t_rela WHERE rela_name = 'Node-DDDB'")
# rela_id = cursor.fetchone()
# if not rela_id:
#     raise Exception("关系 Node-DDDB 不存在")
# rela_guid = rela_id[0]
#
# triples = []
#
# for node_guid, node_name in node_records:
#     # 假设 Node 名称中关联了 DD-DB，比如：G1G2G3
#     dd_names = re.findall(r'G\d+', node_name)  # 正则提取 G+数字，例如 G1、G2、G10
#
#     for dd_name in dd_names:
#         # 查询 DD-DB 节点 GUID
#         cursor.execute("SELECT node_guid FROM t_node WHERE node_type = 'DD-DB' AND node_name = %s", (dd_name,))
#         row = cursor.fetchone()
#         if row:
#             dd_guid = row[0]
#             triple_id = uuid.uuid4().hex
#             triples.append((triple_id, node_guid, rela_guid, dd_guid))
#         else:
#             print(f"警告：未找到名称为 {dd_name} 的 DD-DB 节点")
#
# # 批量插入
# if triples:
#     cursor.executemany(
#         "INSERT INTO t_triplet (tri_guid, head_guid, rela_guid, tail_guid) VALUES (%s, %s, %s, %s)",
#         triples
#     )
#     print(f"成功插入 Node-DDDB 三元组：{len(triples)} 条")
# else:
#     print("没有可插入的三元组")
#
# conn.commit()
# cursor.close()
# conn.close()