File size: 8,651 Bytes
6bc1e38 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
openapi: 3.0.1
info:
title: SQL Executor for Dual Databases
description: |
Execute read-only SQL queries on two specialized databases and return structured JSON results:
- pubchemlite: Compound basic information database (core table: pubchemlite_exposomics_20251226)
- invitrodb_v4_3: In vitro toxicity experiment database (core tables: assay, chemical, mc0, assay_component)
(Adapted for local/server Flask API, only SELECT queries supported, write/modify operations prohibited)
version: 1.0.0
servers:
- url: http://192.168.0.179:5000
description: Main API Server
paths:
/execute_sql:
post:
summary: Execute SELECT query on specified database
operationId: executeSqlPost
description: |
Execute read-only SELECT queries on pubchemlite or invitrodb_v4_3 database.
Notes:
1. Only SELECT operation is allowed (INSERT/UPDATE/DELETE/DROP are prohibited);
2. For fields with special characters (e.g. pred_CCS_A2_[M+H]+ in pubchemlite), escape []/+ with backslash (\) or use backticks (`);
3. Avoid Python format characters (%, A, s, d) in SQL to prevent parsing errors;
4. Add LIMIT clause to avoid large result sets (recommended LIMIT 100).
requestBody:
required: true
content:
application/json:
schema:
type: object
properties:
db_identifier:
type: string
description: Target database identifier (case-sensitive)
enum: [pubchemlite, invitrodb_v4_3]
example: "pubchemlite"
x-validation: "Must be one of the specified enum values"
sql:
type: string
description: |
Valid MySQL SELECT query (only):
## pubchemlite (core table: pubchemlite_exposomics_20251226)
Key fields:
- Basic info: Identifier, CompoundName, MolecularFormula, SMILES, XLogP
- Literature/Patent: PubMed_Count, Patent_Count
- Toxicity/Property: SafetyInfo, ToxicityInfo, MonoisotopicMass
- Predicted CCS: pred_CCS_A2_[M+H]+, pred_CCS_A2_[M+Na]+, pred_CCS_A2_[M-H]- (escape special chars!)
## invitrodb_v4_3 (core tables)
- assay: aid, assay_name, organism, tissue, ncbi_taxon_id
- chemical: chid, casn, chnm (chemical name), dsstox_substance_id
- mc0: m0id, acid, spid, conc (concentration μM), rval (response value)
- assay_component: acid, assay_component_name (toxicity type)
examples:
pubchemlite_example:
summary: Query compound basic info (with escaped special field)
value: "SELECT Identifier, CompoundName, `pred_CCS_A2_[M+H]+` FROM pubchemlite_exposomics_20251226 WHERE PubMed_Count > 5 LIMIT 10"
invitrodb_example:
summary: Query human in vitro toxicity data
value: "SELECT c.chnm, a.assay_name, m.conc, m.rval FROM chemical c JOIN mc0 m ON c.dsstox_substance_id = m.dsstox_substance_id JOIN assay a ON m.aid = a.aid WHERE a.ncbi_taxon_id = 9606 LIMIT 10"
required:
- db_identifier
- sql
x-error-tips: |
1. Missing required parameters: Ensure both db_identifier and sql are provided;
2. Invalid db_identifier: Only pubchemlite/invitrodb_v4_3 are allowed;
3. SQL syntax error: Check field/table names and special character escaping.
responses:
'200':
description: Query executed successfully (returns array of records)
content:
application/json:
schema:
type: object
properties:
status:
type: string
enum: [success, error]
description: Execution status
data:
type: array
items:
type: object
description: Query results (key = field name, value = field value)
additionalProperties: true
message:
type: string
description: Error message (empty if success)
examples:
pubchemlite_response:
summary: pubchemlite query success
value:
status: "success"
data: [
{
"Identifier": "CID12345",
"CompoundName": "Aspirin",
"MolecularFormula": "C9H8O4",
"pred_CCS_A2_[M+H]+": 181.05
}
]
message: ""
invitrodb_response:
summary: invitrodb_v4_3 query success
value:
status: "success"
data: [
{
"chnm": "Aspirin",
"assay_name": "Cell Viability Assay",
"conc": 10.0,
"rval": 25.8
}
]
message: ""
'400':
description: Bad Request (client-side error)
content:
application/json:
schema:
type: object
properties:
status:
type: string
default: "error"
message:
type: string
examples:
invalid_db_identifier:
summary: Incorrect database identifier
value:
status: "error"
message: "Parameter error: db_identifier only supports pubchemlite/invitrodb_v4_3"
special_char_error:
summary: Unescaped special characters in SQL
value:
status: "error"
message: "SQL parsing error: pred_CCS_A2_[M+H]+ needs escaping (recommended: `pred_CCS_A2_[M+H]+` or pred_CCS_A2_\\[M+H\\]+)"
format_char_conflict:
summary: Python format character conflict
value:
status: "error"
message: "Request processing failed: unsupported format character 'A' (please avoid %/A/s/d format characters in SQL)"
cross_db_field:
summary: Cross-database field query
value:
status: "error"
message: "SQL syntax error: CompoundName field does not exist in invitrodb_v4_3 database (this field is only supported in pubchemlite)"
'500':
description: Server Error (database/server-side error)
content:
application/json:
schema:
type: object
properties:
status:
type: string
default: "error"
message:
type: string
examples:
db_connection_failed:
summary: Database connection timeout
value:
status: "error"
message: "Database connection failed: pubchemlite database connection timeout, please check database configuration"
table_not_exist:
summary: Table does not exist in target database
value:
status: "error"
message: "Database execution error: assay table does not exist in pubchemlite database (this table is only supported in invitrodb_v4_3)"
sql_timeout:
summary: SQL query timeout
value:
status: "error"
message: "SQL execution timeout: please add LIMIT to restrict returned rows (recommended LIMIT 100)"
parameters: []
components:
schemas: {}
|