PHP PDO 操作 sqlite数据库 案例

                                                                                      PHP PDO 操作 sqlite数据库 案例

 

1、需求：
    已知：
        1)、一个json文件，里面是一个二维数组，数组解析出来为：
            array (
              0 =>
              array (
                'title' => '九十九',
              ),
              1 =>
              array (
                'title' => '电脑九十九',
              ),
              2 =>
              array (
                'title' => '手机九十九',
              ),
              3 =>
              array (
                'title' => '手机电脑九十九',
              ),
            );
            
        2)、一个sqlite数据库文件 20180824.db 新建一个sqlite数据库文件
            新建表 report
            表字段 id words time
    求：
        把从json中查到的数据，在sqlite中检索，判断是否存在；
        如果存在就给sqlite加上一个 word_sort字段，把title在文件中是第几个(一次递增，不是json文件数组的键值)写入到word_sort字段

    思路：
        0、获取jsonlist.json文件内容并json_decode($str,true)转为二维数组
        1、连接sqlite表
        2、try{}catch(){} 给表增加 word_sort字段
        3、把json文件中的数据数组化
        4、每次循环5000条json数据，用 IN 在report表中查询（title字段需要拼接）
        5、把查询出来的数据用 sql的批量跟新语句拼接
        6、try{}catch(){}批量更新report表数据
        7、echo输出运行结果

 

2、PHP代码(yaf框架)：

<?php
/**
 * @todo 组词
 * Class CommunityController
 */
class CombinwordController extends Rest{
	/**
	 * @todo 判断.json数据是否存在，存在把数据往前排
	 * @linux 212  /usr/local/php7/bin/php /var/www/web/shop/public/cli.php request_uri="/v1/combinword/index"
	 */
	public function indexAction(){
		set_time_limit ( 0 );	//设置时间不过时
		$data = $this->getjson();	//获取json数据
		
		$dbfile_path = APP_PATH.'/data/combinword/20180824.db';
		$db = new PDO("sqlite:{$dbfile_path}");
		//设置数据库句柄		属性 PDO::ATTR_ERRMODE：错误报告。 	PDO::ERRMODE_EXCEPTION: 抛出 exceptions 异常。
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		//加combinword字段 START
		$add_filed = 'word_sort';
		$add_filed_sql = "alter table report add {$add_filed} TEXT(32)";
		try {
			$db->beginTransaction();//启动事务
			$db->exec($add_filed_sql);	//加字段
			$db->commit();//提交事务
		}catch(PDOException  $e){
			//$e->getMessage();//获取错误信息。
			echo '字段已经存在'.PHP_EOL;
			$db->rollBack();//回滚，如果一个地方出现错误，回到总体操作之前。
		}
		//加combinword字段 END
		
		$addStep = 5000;	//每次操作的数据
		$word_cnt = 0;
		$succ_cnt = 0;
		$sort = 0;
		$total = count($data);
		for ( $x=0; $x<$total; $x += $addStep ){
			$temp_json = array_slice($data, $x, $addStep);	//批量操作 100条
			$temp_json = array_column( $temp_json, "title" );
			$temp_json =  array_unique($temp_json);
			
			$temp_str = $this->getStrByArr($temp_json);
			$temp_sql = "select * from report where words IN ({$temp_str})";
			$res = $db->query($temp_sql);
			$result = $res->fetchAll(PDO::FETCH_ASSOC);	//获取数组结果集
		
			$words_result = array_column($result, 'words'); //结果去重
			$unique_result = array_unique($words_result);
			//var_export($unique_result);die;
			//批量更新 START
			$update_sql = "UPDATE report SET {$add_filed} = CASE words ";
			foreach ($unique_result as $k => $v){
				$updateValue = $v;
				$update_sql .= " WHEN '{$updateValue}' THEN ".$sort++;
			}
			$sort += count($unique_result);	//加上排序字段
			$update_sql_str = $this->getStrByArr( $unique_result );
			$update_sql .= " END WHERE words IN ({$update_sql_str})";
				//var_export($update_sql);die;
			try {
				$db->beginTransaction();//启动事务
				$cnt = $db->exec($update_sql);	//加字段
				$db->commit();//提交事务
		
				$word_cnt += count($result);
				$succ_cnt += $cnt;
		
				echo "更新了[{".count($result)."}]个关键字，共影响了[{$cnt}]条数据  ".PHP_EOL;
			}catch(PDOException  $e){
				//$e->getMessage();//获取错误信息。
				echo "批量更新失败 ".PHP_EOL;
				$db->rollBack();//回滚，如果一个地方出现错误，回到总体操作之前。
			}
			//批量更新END
		}
		echo "一共更新了[{$word_cnt}]个关键字，共影响了[{$succ_cnt}]条数据  ".PHP_EOL;
		die;
	}
	
	/**
	 * @todo 根据数组返回拼接的字符串
	 * @param unknown $temp_json 数组
	 * @return string 字符串
	 */
	function getStrByArr($temp_json){
		$temp_str = '';
		$count = count($temp_json);
		$lastValue = end($temp_json);//var_export($lastValue);die;	//获取数组最后一个元素
		foreach ($temp_json as $k => $v){
			$next_str = '';
			if($v != $lastValue ){	//不是最后一个
				$next_str = ',';
			}else{
				$next_str = '';
			}
			$temp_str .= "'".$v."'{$next_str}";
		}
		return $temp_str;
	}
	
	/**
	 * @todo 获取json数据
	 */
	public function getjson(){
		$filename = APP_PATH.'/data/combinword/jsonlist.json';
		$json = file_get_contents($filename);
		$array = json_decode($json, true);
		return $array;
	}
}