{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# dbfaker\n",
    "\n",
    "#### 介绍\n",
    "基于数据库层面批量生成有逻辑关联的数据\n",
    "\n",
    "#### 软件架构\n",
    "对于要造大量数据来讲，有几种方式，一是通过开发写的接口来模拟真实用户场景来产生数据，这种方式在某些方面来讲是比较合适的，比如因为是模拟用户场景，业务数据完整，不会丢．但不好的地方就是要处理的地方太多，接口定义，签名，加密等等，还有扩展性也很不好，一个业务场景要写一套代码．另一种方式，是已知业务产生的数据之间的依赖关系后，直接在数据库中插入相关数据，本项目就是通过这种方式来实现，好处就是生成规则通过配置文件来描述即可（yaml文件），不需要额外添加代码（对于某些字段生成规则有可能需要单独编写方法），与测试库testrunner比较类似．现在已知有些库也支持直接在数据库内造数据，但对库表之间的关联关系的处理都做得不太好．\n",
    "\n",
    "另外对于测试人员进行自动化接口测试时，前置数据是个问题，是通过业务接口一条条跑完作为前置数据产生条件还是直接在数据库里面插入需要的数据呢？前面一种方式在业务场景复杂的时候用例的维护可能会变得极其麻烦。比如：\n",
    "> 你要测试一个用户加入商品到购物车的一个接口；前置数据有哪些？\n",
    ">1. 用户账号\n",
    ">2. 商品\n",
    ">\n",
    ">通过接口来造数据的流程如下：\n",
    ">1. 通过用户端注册接口来创建账号\n",
    ">2. 通过管理端接口登录，拿到token（预设管理端账号是已经存在了，如果不存在你还得注册管理端账号）\n",
    ">3. 登陆后通过管理端接口创建商品信息\n",
    ">\n",
    ">这三个步骤看起来简单，但实际上针对某些业务场景还有接口加密、签名、而且几乎每一条测试用例都需要用到N个前置条件，这些通过接口来调用的前置条件只要有一个失败就会影响你真正要测试的接口。做过接口测试的同学应该深有体会；\n",
    ">\n",
    "实际上实现上述接口测试，真正要测的只是商品是否能正常加入到购物车；真正依赖的就只有两个数据，用户账号和商品信息。我们只需要在用户表和商品表里面各插入一条数据就可以了。通过本工具，只需要描述两个表的每个字段的生成规则就可以直接在数据库中插入两条“十分真实”的数据。用这种方式来解决测试用例前置条件的问题不说是最好的方式，但肯定是比上面的前置用例的方式好很多。\n",
    "\n",
    "本项目数据处理流程如下：\n",
    "![处理流程](https://images.gitee.com/uploads/images/2020/0915/183724_40e0141c_1021400.png \"屏幕截图.png\")\n",
    "\n",
    "#### 安装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!git clone https://gitee.com/guojongg/dbfaker.git\n",
    "!cd dbfaker\n",
    "# 可先创建虚拟环境后再安装\n",
    "!python3 setup.py install\n",
    "# 卸载\n",
    "!pip uninstall dbfaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 使用pip直接安装\n",
    "!pip3 install dbfaker"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### 使用说明\n",
    "\n",
    "１、生成meta.yml文件，可通过内置工具table2yml.py来一键生成模板，生成后需完善模板中表字段的定义\n",
    "    \n",
    "table2yml.py文件使用说明："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!table2yml --help"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!table2yml table_statement --sql_file test.sql --output test.yml"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "2、编辑meta.yml文件，文件格式如下\n",
    "```yaml\n",
    "package:\n",
    "  - datetime  #　导入额外的包，在jinja2模板中使用（下面有用到datetime包，所以要先导入）\n",
    "env:\n",
    "  id:\n",
    "    engine: faker.uuid\n",
    "    rule: null\n",
    "  time_format:\n",
    "    engine: faker.eq\n",
    "    rule:\n",
    "      value: \"%Y-%m-%d %H:%M:%S\"\n",
    "tables:\n",
    "- columns:\n",
    "  - column: id\n",
    "    comment: 数据主键id\n",
    "    engine: faker.eq\n",
    "    rule:\n",
    "      value: '{{ env.id }}'  # 通过引用环境变量中的值\n",
    "  - column: name\n",
    "    comment: 姓名\n",
    "    engine: faker.name\n",
    "    rule: null\n",
    "  - column: idcard\n",
    "    comment: 身份证号\n",
    "    engine: faker.ssn\n",
    "    rule: null\n",
    "  - column: age\n",
    "    comment: 年龄\n",
    "    engine: faker.eq\n",
    "    rule:\n",
    "      value: '{{ datetime.datetime.now().year - int(stu.idcard[6:10]) }}'  #　通过jinja２模板直接计算\n",
    "  - column: sex\n",
    "    comment: 性别\n",
    "    engine: faker.eq\n",
    "    rule:\n",
    "      value: '{{ \"man\" if int(stu.idcard[-2]) % 2==1 else \"female\" }}'  #　通过jinja２模板直接计算\n",
    "  comment: ''\n",
    "  table: stu\n",
    "- columns:\n",
    "  - column: id\n",
    "    comment: 数据主键id\n",
    "    engine: faker.uuid\n",
    "    rule: null\n",
    "  - column: stu_id\n",
    "    comment: 数据主键id\n",
    "    engine: faker.eq\n",
    "    rule:\n",
    "      value: '{{ stu.id }}'  # 通过其他表中的值\n",
    "  - column: course_name\n",
    "    comment: 课程名称\n",
    "    engine: faker.choice # 通过内置方法从列表中随机取一个值\n",
    "    rule:\n",
    "      value: [数学,语文,英语,化学,地理]\n",
    "  - column: course_time\n",
    "    comment: 上课时间\n",
    "    engine: faker.now  # 通过内置方法获取当前时间，并按照指定格式返回\n",
    "    rule:\n",
    "      format: \"{{ env.time_format }}\"\n",
    "  comment: '课程信息 '\n",
    "  table: course\n",
    "\n",
    "```\n",
    "３，创建ｓｑｌ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!dbfaker -h\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 打印输出\n",
    "!dbfaker ../data/test.yml --number 10 -p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 保存到文件\n",
    "!dbfaker ../data/test.yml --number 10 -o out.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# 插入到数据库\n",
    "# !dbfaker ../data/test.yml --number 10 -i --connect mysql+mysqldb://pdmsadmin:system001@cpcs.homelabs.in/pdms_hospital"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### 更多例子\n",
    "[usage example(使用举例)](docs/使用举例.md)\n",
    "\n",
    "#### 内置方法一览\n",
    "[dbfaker内置方法速览](docs/dbfaker内置方法.md)\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}