数据操作

来自BDP个人版帮助中心
跳转至: 导航搜索

操作某张工作表中数据,支持写入、更新、行删除与条件删除;

特别注意

  • 数据操作结果是写入缓存,不会直接在BDP中生效,即在平台中不会看到数据变化,需要调用工作表的提交与更新,分别将数据写入工作表及其相关的合表。
  • 数据操作同样不支持并发,如不能并发向一张工作表写入数据,请顺序执行;

插入数据

向目标工作表的缓存文件中写入待插入的新增数据。

  • 插入内容须为新增数据,更新数据请使用data_update接口;
  • 写入数据须与对应字段一致,包括字段类型和数量;
  • 数据的结构代表与工作表字段的对应关系,可以使用字段名或字段ID,字段ID可通过字段查看获得;

字段名结构:

fields:["FIELD1_NAME","FIELD2_NAME","FIELD3_NAME"]

字段ID结构:

fields_id:["FIELD1_ID","FIELD2_ID","FIELD3_ID"]

请求URL:

https://open.bdp.cn/api/data/insert

已有数据为:

id name birth
1 John 1991-09-07 12:34:56
2 Anakin 1994-08-23 02:41:50
3 Raven 1993-05-12 13:02:13

需要插入的数据为

id name birth
4 Yoda 1990-12-03 11:45:20

请求参数:

{
	"access_token": "YOUR_TOKEN",   	//账户Token
	"tb_id": "TB_ID",   				//工作表ID
	"fields": [   						//使用字段名的数据结构
	    "id", 
	    "name", 
	    "birth"
	]
} 

请求数据:

[
	[
	    "4", 
	    "Yoda", 
	    "1990-12-03 11:45:20"
	]
]

错误代码说明:

状态码 说明
6 缺少fields name或fields id
6 工作表结构不全
11 "body"中数据JSON格式不正确
402 内部错误

示例代码(Python)

# Install the Python Requests library:
# `pip install requests`

import requests


def send_request():
    # data/insert
    # POST https://open.bdp.cn/api/data/insert

    try:
        response = requests.post(
            url="https://open.bdp.cn/api/data/insert",
            params={
                "access_token": "token",
                "tb_id": "tb_id",
                "fields": "[\"id\", \"name\", \"birth\"]",
            },
            data="[[4,\"Yoda\",\"1990-12-03 11:45:20\"]]"
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
        print('Response HTTP Response Body: {content}'.format(
            content=response.content))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')

示例代码(PHP)

<?php

// Get cURL resource
$ch = curl_init();

// Set url
curl_setopt($ch, CURLOPT_URL, 'https://open.bdp.cn:/api/data/insert?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D');

// Set method
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');

// Set options
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// Create body
$body = '[[4,\"Yoda\",\"1990-12-03 11:45:20\"]]';

// Set body
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $body);

// Send the request & save response to $resp
$resp = curl_exec($ch);

if(!$resp) {
  die('Error: "' . curl_error($ch) . '" - Code: ' . curl_errno($ch));
} else {
  echo "Response HTTP Status Code : " . curl_getinfo($ch, CURLINFO_HTTP_CODE);
  echo "\nResponse HTTP Body : " . $resp;
}

// Close request to clear up some resources
curl_close($ch);

示例代码(Java)

import java.io.IOException;
import org.apache.http.client.fluent.*;
import org.apache.http.entity.ContentType;

public class SendRequest
{
  public static void main(String[] args) {
    sendRequest();
  }
  
  private static void sendRequest() {
    
    // data/insert (POST )
    
    try {
      
      // Create request
      Content content = Request.Post("https://open.bdp.cn:/api/data/insert?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D")
      
      // Add body
      .bodyString("[[4,\"Yoda\",\"1990-12-03 11:45:20\"]]", ContentType.DEFAULT_TEXT)
      
      // Fetch request and return content
      .execute().returnContent();
      
      // Print content
      System.out.println(content);
    }
    catch (IOException e) { System.out.println(e); }
  }
}

请求成功时返回说明:

{
	"status": 0,
	"errstr": "",
	"result": "success"
}

更新数据

向目标工作表的缓存文件中写入被更新的数据。

  • 写入数据须与对应字段一致,包括字段类型和数量;
  • 更新数据须提供全部字段,缺少字段会默认置为空,更新根据主键替换旧数据;
  • 数据的结构代表与工作表字段的对应关系,可以使用字段名或字段ID,字段ID可通过字段查看获得;

字段名结构:

fields:["FIELD1_NAME","FIELD2_NAME","FIELD3_NAME"]

字段ID结构:

fields_id:["FIELD1_ID","FIELD2_ID","FIELD3_ID"]

请求URL:

https://open.bdp.cn/api/data/update

已有数据为(id为uniq_index):

id name birth
1 John 1991-09-07 12:34:56
2 Anakin 1994-08-23 02:41:50
3 Raven 1993-05-12 13:02:13

需要更新的数据为

id name birth
2 Anakin 1993-01-20 12:01:30

请求参数:

{
	"access_token": "YOUR_TOKEN",   	//账户Token
	"tb_id": "TB_ID",   				//工作表ID
	"fields": [   						//使用字段名的数据结构
	    "id", 
	    "name", 
	    "birth"
	]
} 

请求数据:

[
	[
	    "2", 
	    "Anakin", 
	    "1993-01-20 12:01:30"
	]
]

请求成功时返回说明:

{
	"status": 0,
	"errstr": "",
	"result": "success"
}

错误代码说明:

状态码 说明
6 缺少fields name或fields id
6 工作表结构不全
11 "body"中数据JSON格式不正确
402 内部错误


示例代码(Python)

# Install the Python Requests library:
# `pip install requests`

import requests


def send_request():
    # data/delete
    # POST https://open.bdp.cn/api/data/update

    try:
        response = requests.post(
            url="https://open.bdp.cn/api/data/update",
            params={
                "access_token": "token",
                "tb_id": "tb_id",
                "fields": "[\"id\", \"name\", \"birth\"]",
            },
            data="[[2,\"Anakin\",\"1993-01-20 12:01:30\"]]"
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
        print('Response HTTP Response Body: {content}'.format(
            content=response.content))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')

示例代码(PHP)

<?php

// Get cURL resource
$ch = curl_init();

// Set url
curl_setopt($ch, CURLOPT_URL, 'https://open.bdp.cn:/api/data/update?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D');

// Set method
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');

// Set options
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// Create body
$body = '[[2,\"Anakin\",\"1993-01-20 12:01:30\"]]';

// Set body
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $body);

// Send the request & save response to $resp
$resp = curl_exec($ch);

if(!$resp) {
  die('Error: "' . curl_error($ch) . '" - Code: ' . curl_errno($ch));
} else {
  echo "Response HTTP Status Code : " . curl_getinfo($ch, CURLINFO_HTTP_CODE);
  echo "\nResponse HTTP Body : " . $resp;
}

// Close request to clear up some resources
curl_close($ch);

示例代码(Java)

import java.io.IOException;
import org.apache.http.client.fluent.*;
import org.apache.http.entity.ContentType;

public class SendRequest
{
  public static void main(String[] args) {
    sendRequest();
  }
  
  private static void sendRequest() {
    
    // data/delete (POST )
    
    try {
      
      // Create request
      Content content = Request.Post("https://open.bdp.cn:/api/data/update?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D")
      
      // Add body
      .bodyString("[[2,\"Anakin\",\"1993-01-20 12:01:30\"]]", ContentType.DEFAULT_TEXT)
      
      // Fetch request and return content
      .execute().returnContent();
      
      // Print content
      System.out.println(content);
    }
    catch (IOException e) { System.out.println(e); }
  }
}

直接删除

根据主键向目标工作表的缓存文件中写入待删除的数据,支持批量删除。该接口性能高于条件删除,根据主键值删除数据时建议使用该接口。

  • 工作表中有主键时根据主键字段删除数据,如无主键将匹配整行数据,整行一致的数据将被删除;
  • 工作表中有主键时可以仅提供主键字段的字段结构与对应值,如果表中主键提供不全删除操作不生效将被忽略。
  • 写入数据须与对应字段一致,包括字段类型和数量;
  • 数据的结构代表与工作表字段的对应关系,可以使用字段名或字段ID,字段ID可通过字段查看获得;

字段名结构:

fields:["FIELD1_NAME","FIELD2_NAME","FIELD3_NAME"]

字段ID结构:

fields_id:["FIELD1_ID","FIELD2_ID","FIELD3_ID"]

请求URL:

https://open.bdp.cn/api/data/delete

已有数据为(该表无主键):

id name birth
1 John 1991-09-07 12:34:56
2 Anakin 1994-08-23 02:41:50
3 Raven 1993-05-12 13:02:13

需要删除的数据为

id name birth
2 Anakin 1994-08-23 02:41:50

请求参数:

{
	"access_token": "YOUR_TOKEN",   	//账户Token
	"tb_id": "TB_ID",   				//工作表ID
	"fields": [   						//使用字段名的数据结构
	    "id", 
	    "name", 
	    "birth"
	]
} 

请求数据:

[
	[
	    "2", 
	    "Anakin", 
	    "1994-08-23 02:41:50"
	]
]

请求成功时返回说明:

{
	"status": 0,
	"errstr": "",
	"result": "success"
}

错误代码说明:

状态码 说明
6 缺少fields name或fields id
6 工作表结构不全
11 "body"中数据JSON格式不正确
402 内部错误

示例代码(Python)

# Install the Python Requests library:
# `pip install requests`

import requests


def send_request():
    # data/delete
    # POST https://open.bdp.cn/api/data/delete

    try:
        response = requests.post(
            url="https://open.bdp.cn/api/data/delete",
            params={
                "access_token": "token",
                "tb_id": "tb_id",
                "fields": "[\"id\", \"name\", \"birth\"]",
            },
            data="[[2,\"Anakin\",\"1994-08-23 02:41:50\"]]"
        )
        print('Response HTTP Status Code: {status_code}'.format(
            status_code=response.status_code))
        print('Response HTTP Response Body: {content}'.format(
            content=response.content))
    except requests.exceptions.RequestException:
        print('HTTP Request failed')

示例代码(PHP)

<?php

// Get cURL resource
$ch = curl_init();

// Set url
curl_setopt($ch, CURLOPT_URL, 'https://open.bdp.cn:/api/data/delete?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D');

// Set method
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');

// Set options
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

// Create body
$body = '[[2,\"Anakin\",\"1994-08-23 02:41:50\"]]';

// Set body
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $body);

// Send the request & save response to $resp
$resp = curl_exec($ch);

if(!$resp) {
  die('Error: "' . curl_error($ch) . '" - Code: ' . curl_errno($ch));
} else {
  echo "Response HTTP Status Code : " . curl_getinfo($ch, CURLINFO_HTTP_CODE);
  echo "\nResponse HTTP Body : " . $resp;
}

// Close request to clear up some resources
curl_close($ch);

示例代码(Java)

import java.io.IOException;
import org.apache.http.client.fluent.*;
import org.apache.http.entity.ContentType;

public class SendRequest
{
  public static void main(String[] args) {
    sendRequest();
  }
  
  private static void sendRequest() {
    
    // data/delete (POST )
    
    try {
      
      // Create request
      Content content = Request.Post("https://open.bdp.cn:/api/data/delete?access_token=token&tb_id=tb_id&fields=%5B%22id%22%2C%20%22name%22%2C%20%22birth%22%5D")
      
      // Add body
      .bodyString("[[2,\"Anakin\",\"1994-08-23 02:41:50\"]]", ContentType.DEFAULT_TEXT)
      
      // Fetch request and return content
      .execute().returnContent();
      
      // Print content
      System.out.println(content);
    }
    catch (IOException e) { System.out.println(e); }
  }
}

条件删除

根据条件删除工作表中数据。删除操作将匹配所有符合条件的数据后进行删除,性能不如直接删除,如是根据主键值删除时建议采用直接删除。支持SQL;

字段名须用反撇号"`"包起来,值需要用单引号"'"包起来

请求URL:

https://open.bdp.cn/api/data/bulkdelete

请求参数:

{
	"access_token": "YOUR_TOKEN",   	//账户Token
	"tb_id": "TB_ID",   				//工作表ID
	"where": "`id`>'100'",   				//过滤条件
}

请求成功时返回说明:

{
	"status": 0,
	"errstr": "",
	"result": "success"
}

错误代码说明:

状态码 说明
4 无操作权限
6 过滤条件“where”中字段不存在
6 过滤条件“where”中语法错误
6 不能删除分享表
409 删除数据过程中异常