php版
<?php
/*
Model
对数据进行操作
*/
//数据库相关配置
// define('HOST','127.0.0.1');
define('USER','root');
define('PASS','1234@Hao');
define('DBNAME','txt');
define('CHARSET','utf8');
class Model{
//成员属性
public $link;//数据库链接标识
public $tableName;//表名
public $pk;//主键
public $fields;//字段
public $where;//where条件
public $limit;//limit条件
public $order;//order条件
public $field;//字段条件
//构造方法
public function __construct($tableName,$host = '127.0.0.1')
{
//初始化
$this->tableName = $tableName;
//链接数据库
$this->link = mysqli_connect($host,USER,PASS) or die('数据库链接失败');
// $this->link = mysqli_connect(HOST,USER,PASS) or die('数据库链接失败');
// 选择库
mysqli_select_db($this->link,DBNAME) or die('选择数据库失败');
//设置字符集
mysqli_set_charset($this->link,CHARSET) or die('设置字符集失败');
//调用方法 获取表中字段
$this->getTableFields();
}
public function query($sql,$resultType = MYSQLI_BOTH)
{
//保存SQL语句
$this->sql = $sql;
//每次执行完SQL语句都要初始化条件
// $this->options = $this->initOptions();
$result = mysqli_query($this->link,$sql);
// $result = mysql_query($sql,$this->link);
if($result){
if($result === false || $result === true){
return $result;
}
return mysqli_fetch_all($result,$resultType);
}
return $result;
}
//获取当前表中的所有字段信息 desc user
public function getTableFields()
{
//拼接sql
$sql = 'desc '.$this->tableName;
//执行sql
$res = mysqli_query($this->link,$sql);
//声明变量和数组
$pk = '';//主键
$arr = [];//普通字段
//判断 提取结果
if($res){
while($row = mysqli_fetch_assoc($res))
{
//判断是否为主键
if($row['Key'] == 'PRI'){
$pk = $row['Field'];
}else{
$arr[] = $row['Field'];
}
}
//将字段和主键存入属性中
$this->pk = $pk;
$this->fields = $arr;
}else{
return false;
}
}
//析构方法
public function __destruct()
{
//关闭数据库
mysqli_close($this->link);
}
}
?>
python异步版
使用twist库完成异步操作
# -*- coding: utf-8 -*-
from mininova.settings import settings
from twisted.enterprise import adbapi
import pymysql
import pymysql.cursors
from scrapy import log
class Bookdb():
def __init__(self):
dbparams = dict(
host=settings['mysql_host'],
db=settings['mysql_db'],
user=settings['mysql_user'],
passwd=settings['mysql_password'],
charset=settings['mysql_charset'],
cursorclass=pymysql.cursors.DictCursor,
use_unicode=False,
)
dbpool = adbapi.ConnectionPool('pymysql', **dbparams)
self.dbpool = dbpool
def insert(self, item):
chapter_table = int(item['book_id'])%100 + 1
sql = "insert into chapter_"+str(chapter_table)+"(book_id,chp_name,sort,chp_url,chp_script_url) values(%s,%s,%s,%s,'')"
query = self.dbpool.runInteraction(self._conditional_insert, sql, item)
query.addErrback(self._handle_error)
return item
def _conditional_insert(self, tx, sql, item):
chapter_table =int(item['book_id'])%100 + 1
params = (item['book_id'],item['chp_name'], item['chp_sort'],item['url'])
exist_sql = "select id from chapter_"+str(chapter_table)+" where book_id = %s and chp_name = %s and sort= %s and chp_url = %s"
exist = tx.execute(exist_sql, params)
# print(exist)
if exist == 0:
res = tx.execute(sql, params)
log.msg("Item not stored in db:\n%s" % item,level=log.DEBUG)
else:
log.msg("Item already stored in db:\n%s" % item,level=log.DEBUG)
def update(self,item):
print('book_id :'+str(item['book_id']))
chapter_table =int(item['book_id'])%100 + 1
sql = "update chapter_"+str(chapter_table)+" set chp_script_url = %s where book_id = %s and sort= %s"
query = self.dbpool.runInteraction(self._conditional_update, sql, item)
query.addErrback(self._handle_error)
return item
def _conditional_update(self, tx, sql, item):
chapter_table =int(item['book_id'])%100 + 1
print('_conditional_update')
print(sql)
params = (item['chp_script_url'], item['book_id'], item['chp_sort'])
print(params)
res = tx.execute(sql,params)
params1 = (item['book_id'], item['chp_sort'])
print(params1)
sql = "select * from chapter_"+str(chapter_table)+" where book_id = %s and sort= %s"
result = tx.execute(sql,params1)
result = tx.fetchall()
log.msg("Item updated in db:\n%s" % item,level=log.DEBUG)
log.msg("Item result in db:\n%s" % result,level=log.DEBUG)
log.msg("Item affect rows in db:%s" % res,level=log.DEBUG)
def _handle_error(self, failue):
print('--------------database operation exception!!-----------------')
print(failue)