from cmdbox.app import common, client
from cmdbox.app.commons import convert, limiter, redis_client, resdata, validator
from cmdbox.app.features.cli.datasource import datasource_base
from cmdbox.app.options import Options
from pathlib import Path
from typing import Any, Dict, List, Tuple, Union
import argparse
import json
import logging
import pydantic
[docs]
class DatasourceDataInsert(datasource_base.DatasourceBase, validator.Validator, limiter.LimitedFeature):
[docs]
def get_mode(self) -> Union[str, List[str]]:
return 'datasource'
[docs]
def get_cmd(self) -> str:
return 'data_insert'
[docs]
def get_option(self) -> Dict[str, Any]:
return dict(
use_redis=self.USE_REDIS_TRUE, nouse_webmode=False, use_agent=False,
description_ja="指定したデータソースのテーブルにレコードを挿入します。",
description_en="Inserts a record into a table of the specified datasource.",
choice=[
dict(opt="host", type=Options.T_STR, default=self.default_host, required=True, multi=False, hide=True, choice=None, web="mask",
description_ja="Redisサーバーのサービスホストを指定します。",
description_en="Specify the service host of the Redis server."),
dict(opt="port", type=Options.T_INT, default=self.default_port, required=True, multi=False, hide=True, choice=None, web="mask",
description_ja="Redisサーバーのサービスポートを指定します。",
description_en="Specify the service port of the Redis server."),
dict(opt="password", type=Options.T_PASSWD, default=self.default_pass, required=True, multi=False, hide=True, choice=None, web="mask",
description_ja=f"Redisサーバーのアクセスパスワード(任意)を指定します。省略時は `{self.default_pass}` を使用します。",
description_en=f"Specify the access password of the Redis server (optional). If omitted, `{self.default_pass}` is used."),
dict(opt="svname", type=Options.T_STR, default=self.default_svname, required=True, multi=False, hide=True, choice=None, web="readonly",
description_ja="サーバーのサービス名を指定します。省略時は `server` を使用します。",
description_en="Specify the service name of the inference server. If omitted, `server` is used."),
dict(opt="retry_count", type=Options.T_INT, default=3, required=False, multi=False, hide=True, choice=None,
description_ja="Redisサーバーへの再接続回数を指定します。0以下を指定すると永遠に再接続を行います。",
description_en="Specifies the number of reconnections to the Redis server. If less than 0 is specified, reconnection is forever."),
dict(opt="retry_interval", type=Options.T_INT, default=5, required=False, multi=False, hide=True, choice=None,
description_ja="Redisサーバーに再接続までの秒数を指定します。",
description_en="Specifies the number of seconds before reconnecting to the Redis server."),
dict(opt="timeout", type=Options.T_INT, default="60", required=False, multi=False, hide=True, choice=None,
description_ja="サーバーの応答が返ってくるまでの最大待ち時間を指定。",
description_en="Specify the maximum waiting time until the server responds."),
dict(opt="dsname", type=Options.T_STR, default=None, required=True, multi=False, hide=False, choice=None,
description_ja="接続先データソースの識別名を指定します。",
description_en="Specify the identifier name of the target datasource."),
dict(opt="schema", type=Options.T_STR, default=None, required=False, multi=False, hide=False, choice=None,
description_ja="スキーマ名を指定します。省略時はスキーマなしで操作します。",
description_en="Specify the schema name. If omitted, the operation is performed without a schema."),
dict(opt="tblname", type=Options.T_STR, default=None, required=True, multi=False, hide=False, choice=None,
description_ja="挿入先テーブル名を指定します。",
description_en="Specify the target table name."),
dict(opt="insert_data", type=Options.T_DICT, default=None, required=True, multi=True, hide=False, choice=None,
description_ja="挿入するレコードのカラム名と値を key=value 形式で指定します。複数のカラムを指定する場合は繰り返し指定します。",
description_en="Specify the column names and values of the record to insert in key=value format. Repeat for multiple columns."),
]
)
def _do_run(self, data_dir: Path, payload: Dict[str, Any], logger: logging.Logger) -> Dict[str, Any]:
conn = None
try:
schema = payload.get('schema') or None
tblname = self.validate_identifier(payload['tblname'])
data: Dict[str, Any] = payload['insert_data']
with self.get_context(payload) as (conn, dbtype):
qualified_tbl = self.qualified_name(schema, tblname, dbtype)
cols = ', '.join(self.validate_identifier(k) for k in data)
placeholder = '%s' if dbtype == self.DBTYPE_PG else '?'
placeholders = ', '.join([placeholder] * len(data))
sql = f"INSERT INTO {qualified_tbl} ({cols}) VALUES ({placeholders})"
with conn.cursor() as cur:
cur.execute(sql, list(data.values()))
conn.commit()
return dict(success=dict(data=f"1 row inserted into '{qualified_tbl}'."))
except Exception as e:
logger.warning(f"{self.get_mode()}_{self.get_cmd()}: {e}", exc_info=True)
return dict(warn=f"{self.get_mode()}_{self.get_cmd()}: {e}")
finally:
if conn is not None:
try:
conn.close()
except Exception:
pass
[docs]
@limiter.async_apprun_check_limit
@validator.async_apprun_check
async def apprun(self, logger: logging.Logger, args: argparse.Namespace, tm: float, pf: List[Dict[str, float]] = []) -> Tuple[int, Dict[str, Any], Any]:
payload = dict(dsname=args.dsname, schema=args.schema if hasattr(args, 'schema') else None, tblname=args.tblname, insert_data=args.insert_data)
try:
return await self.route_apprun(args, payload, logger, tm, pf)
except Exception as e:
logger.warning(f"{self.get_mode()}_{self.get_cmd()}: {e}", exc_info=True)
ret = dict(warn=f"{self.get_mode()}_{self.get_cmd()}: {e}")
return self.RESP_WARN, ret, None
[docs]
def output_schema(self) -> type:
class Data(resdata.Data):
data: Union[str, None] = pydantic.Field(default=None, description="処理結果のデータ")
class Result(resdata.Result):
success: Union[Data, None] = pydantic.Field(default=None, description="成功した場合の結果")
return Result
[docs]
def is_cluster_redirect(self):
return False
@limiter.svrun_check_limit
def svrun(self, data_dir: Path, logger: logging.Logger, redis_cli: redis_client.RedisClient,
msg: List[str], sessions: Dict[str, Dict[str, Any]]) -> int:
reskey = msg[1]
payload = json.loads(convert.b64str2str(msg[2]))
ret = self._do_run(data_dir, payload, logger)
redis_cli.rpush(reskey, ret)
return self.RESP_SUCCESS if 'success' in ret else self.RESP_WARN
[docs]
def svrun_registrations(self, data_dir, logger, payload, msg):
conn = None
try:
schema = payload.get('schema') or None
tblname = self.validate_identifier(payload['tblname'])
with self.get_context(payload) as (conn, dbtype):
qualified_tbl = self.qualified_name(schema, tblname, dbtype)
sql = f"SELECT COUNT(*) FROM {qualified_tbl}"
with conn.cursor() as cur:
cur.execute(sql)
count = cur.fetchone()[0]
conn.commit()
return count
except Exception as e:
logger.warning(f"{self.get_mode()}_{self.get_cmd()}: {e}", exc_info=True)
return 0
finally:
if conn is not None:
try:
conn.close()
except Exception:
pass