clinicaltrials.gov数据导入SQLite遇类型错误及数据解析需求

API 探路者

2026-8-18

clinicaltrials.gov数据导入SQLite错误解决及数据处理方案

问题概述

处理clinicaltrials.gov数据时，需完成以下操作：

移除字段的列表格式（括号）
解析数量可变的协作者名称
将Phase字段提取为纯数字
但执行导入SQLite代码时触发InterfaceError: Error binding parameter 0 - probably unsupported type错误。

错误原因

API返回的所有字段值均为列表类型（例如NCTId对应["NCT01745367"]），而SQLite的TEXT字段仅支持字符串类型，直接插入列表会触发类型不兼容错误。

数据处理逻辑及修正代码

以下是包含完整数据处理逻辑的修正代码，同时完成你需要的三个数据清洗操作：

import pandas as pd
import json
import urllib.request, urllib.parse, urllib.error
import sqlite3
import ssl
import re

conn = sqlite3.connect('ctrialsdb.sqlite')
cur = conn.cursor()

# 初始化数据库表
cur.executescript('''
DROP TABLE IF EXISTS ctdata;

CREATE TABLE ctdata (
    NCTId                 TEXT NOT NULL PRIMARY KEY,
    LeadSponsorName       TEXT,
    CollaboratorName      TEXT,
    InterventionName      TEXT,
    Phase                 TEXT,
    OverallStatus         TEXT,
    Condition             TEXT,
    LastUpdatePostDate    TEXT
)
''')

# 禁用SSL证书验证
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

# 构造API请求链接
serviceurl ='https://www.clinicaltrials.gov/api/query/study_fields?'
sponsor = input('Company name: ')
url=serviceurl + urllib.parse.urlencode({
    'expr' : sponsor, 
    'fields' : 'NCTId, LeadSponsorName,CollaboratorName,InterventionName,Phase,OverallStatus,Condition, LastUpdatePostDate',
    'min_rnk' :'1',
    'max_rnk':'1000',
    'fmt' :'json'
})

# 获取并解析API数据
urlo=urllib.request.urlopen(url, context = ctx)
urld = urlo.read().decode()
jdata = json.loads(urld)

# 定义通用字段处理函数：将列表转为字符串
def process_field(field_value):
    if not field_value:
        return ""
    return ", ".join(field_value) if len(field_value) > 1 else field_value[0]

# 定义Phase字段处理函数：提取纯数字
def extract_phase(phase_list):
    phase_str = process_field(phase_list)
    match = re.search(r'Phase (\d+)', phase_str)
    return match.group(1) if match else phase_str

# 处理每条数据并插入数据库
for child in jdata['StudyFieldsResponse']['StudyFields']:
    nct_id = process_field(child['NCTId'])
    lead_sponsor = process_field(child['LeadSponsorName'])
    collaborators = process_field(child['CollaboratorName'])
    interventions = process_field(child['InterventionName'])
    phase = extract_phase(child['Phase'])
    status = process_field(child['OverallStatus'])
    condition = process_field(child['Condition'])
    update_date = process_field(child['LastUpdatePostDate'])

    cur.execute('''INSERT OR IGNORE INTO ctdata
    (NCTId, LeadSponsorName, CollaboratorName, InterventionName, Phase, OverallStatus, Condition, LastUpdatePostDate) 
    VALUES (?,?,?,?,?,?,?,?)''',
                (nct_id, lead_sponsor, collaborators, interventions, phase, status, condition, update_date))
conn.commit()
print("数据导入完成")

关键处理说明

移除列表格式：通过process_field函数将列表转为字符串，空列表返回空字符串，多元素用逗号拼接
解析可变协作者：同样用process_field函数，将多个协作者名称拼接为逗号分隔的字符串
提取Phase纯数字：用正则表达式匹配"Phase X"格式，提取数字；如果是"Not Applicable"则直接保留原内容
解决SQLite类型错误：所有字段均转为字符串类型后再插入数据库，避免类型不兼容问题

原始相关信息

原始代码

import pandas as pd
import json
import urllib.request, urllib.parse, urllib.error
import sqlite3
import ssl
import ast

conn = sqlite3.connect('ctrialsdb.sqlite')
cur = conn.cursor()

# Setup DB
cur.executescript('''
DROP TABLE IF EXISTS ctdata;

CREATE TABLE ctdata (
    NCTId                 TEXT NOT NULL PRIMARY KEY,
    LeadSponsorName       TEXT,
    CollaboratorName      TEXT,
    InterventionName      TEXT,
    Phase                 TEXT,
    OverallStatus         TEXT,
    Condition             TEXT,
    LastUpdatePostDate    TEXT
)
''')

ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

serviceurl ='https://www.clinicaltrials.gov/api/query/study_fields?'
sponsor = input('Company name: ')
url=serviceurl + urllib.parse.urlencode({'expr' : sponsor, 'fields' :
    'NCTId, LeadSponsorName,CollaboratorName,InterventionName,Phase,OverallStatus,Condition, LastUpdatePostDate',
    'min_rnk' :'1','max_rnk':'1000','fmt' :'json'})

#print(url)

urlo=urllib.request.urlopen(url, context = ctx)
urld = urlo.read().decode()
jdata = json.loads(urld)

#print(json.dumps(jdata, indent=4))

df = pd.DataFrame(jdata['StudyFieldsResponse']['StudyFields'])
df

for child in jdata['StudyFieldsResponse']['StudyFields']:
    cur.execute('''INSERT OR IGNORE INTO ctdata
    (NCTId, LeadSponsorName, CollaboratorName, InterventionName, Phase, OverallStatus, Condition, LastUpdatePostDate) VALUES (?,?,?,?,?,?,?,?)''',
                (child['NCTId'],child['LeadSponsorName'],child['CollaboratorName'],child['InterventionName'],child['Phase'], child['OverallStatus'], child['Condition'], child['LastUpdatePostDate'], ) )
conn.commit()

原始数据输出

Rank    NCTId   LeadSponsorName CollaboratorName    InterventionName    Phase   OverallStatus   Condition   LastUpdatePostDate
0   1   [NCT01745367]   [AVEO Pharmaceuticals, Inc.]    [Astellas Pharma Inc]   [Tivozanib Hydrochloride, paclitaxel, Placebo]  [Phase 2]   [Terminated]    [Triple Negative Breast Cancer] [October 27, 2020]
1   2   [NCT01673386]   [AVEO Pharmaceuticals, Inc.]    [Astellas Pharma Inc]   [Tivozanib, Sunitinib]  [Phase 2]   [Terminated]    [Metastatic Renal Cell Carcinoma]   [October 27, 2020]
2   3   [NCT02318368]   [AVEO Pharmaceuticals, Inc.]    [Biodesix, Inc.]    [Ficlatuzumab, Erlotinib, placebo]  [Phase 2]   [Terminated]    [Non-small Cell Lung Cancer]    [October 22, 2020]
3   4   [NCT01369433]   [AVEO Pharmaceuticals, Inc.]    []  [Tivozanib + paclitaxel, Tivozanib + temsiroli...   [Not Applicable]    [Terminated]    [Solid Tumors]  [September 1, 2020]

原始错误信息

---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
Input In [1], in <cell line: 72>()
     54 #dfs = (jdata['StudyFieldsResponse']['StudyFields'])
     55 #dfs
     56 
   (...)
     69 #    Condition = child[7]
     70 #    LastUpdatePostDate = child[8]
     72 for child in jdata['StudyFieldsResponse']['StudyFields']:
---&gt; 73      cur.execute('''INSERT OR IGNORE INTO ctdata
     74      (NCTId, LeadSponsorName, CollaboratorName, InterventionName, Phase, OverallStatus, Condition, LastUpdatePostDate) VALUES (?,?,?,?,?,?,?,?)''',         (child['NCTId'],child['LeadSponsorName'],child['CollaboratorName'],child['InterventionName'],child['Phase'], child['OverallStatus'], child['Condition'], child['LastUpdatePostDate'], ) )
     75 conn.commit()

InterfaceError: Error binding parameter 0 - probably unsupported type.

原始JSON数据结构

{
    "StudyFieldsResponse": {
        "APIVrs": "1.01.05",
        "DataVrs": "2022:09:26 23:26:47.947",
        "Expression": "AVEO Pharmaceuticals, Inc.",
        "NStudiesAvail": 428928,
        "NStudiesFound": 42,
        "MinRank": 1,
        "MaxRank": 1000,
        "NStudiesReturned": 42,
        "FieldList": [
            "NCTId",
            "LeadSponsorName",
            "CollaboratorName",
            "InterventionName",
            "Phase",
            "OverallStatus",
            "Condition",
            "LastUpdatePostDate"
        ],
        "StudyFields": [
            {
                "Rank": 1,
                "NCTId": [
                    "NCT01745367"
                ],
                "LeadSponsorName": [
                    "AVEO Pharmaceuticals, Inc."
                ],
                "CollaboratorName": [
                    "Astellas Pharma Inc"
                ],
                "InterventionName": [
                    "Tivozanib Hydrochloride",
                    "paclitaxel",
                    "Placebo"
                ],
                "Phase": [
                    "Phase 2"
                ],
                "OverallStatus": [
                    "Terminated"
                ],
                "Condition": [
                    "Triple Negative Breast Cancer"
                ],
                "LastUpdatePostDate": [
                    "October 27, 2020"
                ]
            },
            {
                "Rank": 2,
                "NCTId": [
                    "NCT01673386"
                ],
                "LeadSponsorName": [
                    "AVEO Pharmaceuticals, Inc."
                ],
                "CollaboratorName": [
                    "Astellas Pharma Inc"
                ],
                "InterventionName": [
                    "Tivozanib",
                    "Sunitinib"
                ],
                "Phase": [
                    "Phase 2"
                ],
                "OverallStatus": [
                    "Terminated"
                ],
                "Condition": [
                    "Metastatic Renal Cell Carcinoma"
                ],
                "LastUpdatePostDate": [
                    "October 27, 2020"
                ]
            },
            {
                "Rank": 3,
                "NCTId": [
                    "NCT02318368"
                ],
                "LeadSponsorName": [
                    "AVEO Pharmaceuticals, Inc."
                ],
                "CollaboratorName": [
                    "Biodesix, Inc."
                ],
                "InterventionName": [
                    "Ficlatuzumab",
                    "Erlotinib",
                    "placebo"
                ],
                "Phase": [
                    "Phase 2"
                ],
                "OverallStatus": [
                    "Terminated"
                ],
                "Condition": [
                    "Non-small Cell Lung Cancer"
                ],
                "LastUpdatePostDate": [
                    "October 22, 2020"
                ]
            }
        ]
    }
}

内容的提问来源于stack exchange，提问作者MJR BBQ

clinicaltrials.gov数据导入SQLite遇类型错误及数据解析需求

问题概述 #

错误原因 #

数据处理逻辑及修正代码 #

关键处理说明 #

原始相关信息 #

原始代码 #

原始数据输出 #

原始错误信息 #

原始JSON数据结构 #