def _getMaxSOfAProductTypeParallel(self, productTypeId, processCount=None):
getUserIdsSql = 'select reviewUserId from review \
where productTypeid = %s;'
connection = Connect(**pymysqlConfig)
processCount = (processCount
if processCount is not None else cpu_count())
try:
maxS = float('-inf')
with connection as cursor:
cursor.execute(getUserIdsSql, (productTypeId, ))
userIds = [row[0] for row in cursor.fetchall()]
perCount = len(userIds) // processCount
userIdParts = [
userIds[i * perCount:(i + 1) * perCount]
for i in range(processCount - 1)
]
userIdParts.append(userIds[(processCount - 1) * perCount:])
with futures.ProcessPoolExecutor(
max_workers=processCount) as pool:
results = [
pool.submit(self._computeMaxSOfSomeUserInAProductType,
userIds, productTypeId)
for userIds in userIdParts
]
for future in futures.as_completed(results):
maxS = max(maxS, future.result())
return maxS if maxS != float('-inf') else None
finally:
connection.close()
class mysqlPipeLine(object):
conn = None
cursor = None
def open_spider(self, spider):
print('开始爬虫并连接数据库')
self.conn = Connect(host='127.0.0.1',
port=3306,
user='******',
password='******',
db='my_django_project_2',
charset='utf8')
self.cursor = self.conn.cursor()
def process_item(self, item, spider):
img_src = item['img_src']
img_path = img_src.split('/')[-1]
sql = 'insert into pic_4k(img_name,img_size,img_path,img_cls) values("%s","%s","%s","%s")' % (
item['img_name'], item['img_size'], img_path, '4k动物')
try:
self.cursor.execute(sql)
self.conn.commit()
except Exception as e:
print('向数据库中插入数据异常:', e)
self.conn.rollback()
return item
def close_spider(self, spider):
print('结束爬虫并关闭数据库连接')
self.cursor.close()
self.conn.close()
def send_center(ret):
with open('./templates/center.html', encoding='utf8') as f:
content = f.read()
custom_msg = ''
conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8')
cursor = conn.cursor()
cursor.execute('select f.id, i.code, i.short, i.chg, i.turnover, i.price, i.highs, f.note_info from info as i inner join focus as f on i.id = f.info_id;')
data = cursor.fetchall()
cursor.close()
conn.close()
html_template = """
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>
<a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a>
</td>
<td>
<input type="button" value="删除" id="toDel" name="toDel" systemIdVaule="%s">
</td>
</tr>
"""
for item in data:
custom_msg += html_template % (item[1:] + (item[0], item[0]))
return re.sub(r'\{%content%\}', custom_msg, content)
class DB:
def __init__(self, **kwargs):
# host, port ,user, pwd, db, charset
self.host = kwargs.get('host', '127.0.0.1')
self.port = kwargs.get('port', 3306) # mysql
self.username = kwargs.get('user', 'root')
self.password = kwargs.get('pwd', 'root')
self.db = kwargs.get('db', 'mysql')
self.charset = kwargs.get('charset', 'utf8')
def connect(self):
# 从连接池中获取数据连接
self.conn = Connect(host=self.host,
port=self.port,
user=self.username,
password=self.password,
db=self.db,
charset=self.charset)
def close(self):
self.conn.close()
def __enter__(self):
self.connect() # 连接数据库
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
def _saveData(self):
"""
保存数据
"""
getExistIdSql = 'select productTypeId from textAnalysisMaxSRecord;'
insertSql = 'insert into textAnalysisMaxSRecord(productTypeId, maxS) \
values(%s, %s);'
updateSql = 'update textAnalysisMaxSRecord set maxS = %s\
where id = %s;'
connection = Connect(**pymysqlConfig)
try:
with connection as cursor:
cursor.execute(getExistIdSql)
existsIds = set(row[0] for row in cursor.fetchall())
insertPairs = []
changePairs = []
for productTypeId, maxS in self._data.items():
if productTypeId in existsIds:
changePairs.append((maxS, productTypeId))
else:
insertPairs.append((productTypeId, maxS))
cursor.executemany(insertSql, insertPairs)
cursor.executemany(updateSql, changePairs)
finally:
connection.close()
def send_index(ret):
with open('./templates/index.html', encoding='utf8') as f:
""""open打开的路径是相对web_server来确定的"""
content = f.read()
custom_msg = ''
conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8')
cursor = conn.cursor()
cursor.execute('select * from info;')
data = cursor.fetchall()
cursor.close()
conn.close()
html_template = """
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>
<input type="button" value="添加" id="toAdd" name="toAdd" systemIdVaule="%s">
</td>
</tr>"""
for item in data:
custom_msg += html_template % (item + (item[1], ))
return re.sub(r'\{%content%\}', custom_msg, content)
def stock(t, c, v=400, z=0.0):
stk = PluginStock()
stk.set_param(t, c, v, z)
results = stk.anyl_dadan_zhanbi()
connection = Connect(host='103.235.232.114',
port=3306,
user='******',
password='******',
db='stock',
charset='utf8mb4',
cursorclass=cursors.DictCursor)
cursor = connection.cursor()
sql = "INSERT INTO rizhanbi(code,zhanbi,timedate) values(%s,%s,%s)"
cursor.executemany(sql, results)
connection.commit()
connection.close()
# if result and (not len(result)==0):
# r = redis.Redis(host='103.235.232.114', port=6379, decode_responses=True,password='******')
# r.lpush(t,c)
# @app.task(base=CallbackTask)
# def multiply(x,y):
# return x * y
def add(match):
code = match.group(1)
print('code:', code)
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
cur = conn.cursor()
sql_str = ''' select * from focus where info_id in (select id from info where code = %s); '''
cur.execute(sql_str, (code, ))
ret = cur.fetchone()
print('ret:', ret)
if ret:
body = '添加过'
else:
sql_str = '''insert into focus (info_id) select id from info where code = %s; '''
cur.execute(sql_str, (code, ))
conn.commit()
body = '成功'
cur.connection
conn.close()
return body
def center(match):
# 接着模板文件地址
path = './templates/center.html'
# 读取模板文件
with open(path, 'r') as f:
content = f.read()
# 设置显示的数据,将原来的固定数据替换成占位符
row_str = """ <tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>
<a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a>
</td>
<td>
<input type="button" value="删除" id="toDel" name="toDel" systemidvaule="%s">
</td>
</tr> """
# 连接数据库
# 1.连接数据
# 创建Connection连接
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# 2 准备执行的 sql 语句字符串
sql_str = """ select info.code,info.short,info.chg,info.turnover,info.price,info.highs,focus.note_info from info inner join focus where info.id = focus.info_id;"""
# 执行sql
cur.execute(sql_str)
# 获取所有的结果
sql_relust = cur.fetchall()
# 遍历结果并拼接数据
all_data = ""
for t in sql_relust:
#根据格式字符串中的每项空白,将数据从元组中取出并添加到格式字符串中
all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[0],
t[0])
# 3. 关闭游标和数据库
cur.close()
conn.close()
# 替换模板中的占位符
content = re.sub(r'\{%content%\}', all_data, content)
return content
def db_cursor(commit=False):
con = None
try:
con = Connect(host=DBHOST, user=DBUSER, password=DBPASS, db=DB)
with con.cursor() as cur:
yield cur
if commit:
con.commit()
finally:
if con:
con.close()
def index(match):
# 接着模板文件地址
path = './templates/index.html'
# 读取模板文件
with open(path, 'r') as f:
content = f.read()
# 设置显示的数据,将原来的固定数据替换成占位符
row_str = """ <tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>
<input type="button" value="添加" id="toAdd" name="toAdd" systemidvaule="%s">
</td>
</tr> """
# 连接数据库
# 1.连接数据
# 创建Connection连接
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
# 获得Cursor对象
cur = conn.cursor()
# 2 准备执行的 sql 语句字符串
sql_str = """ select * from info;"""
# 执行sql
cur.execute(sql_str)
# 获取所有的结果
sql_relust = cur.fetchall()
# 遍历结果并拼接数据
all_data = ""
for t in sql_relust:
#根据格式字符串中的每项空白,将数据从元组中取出并添加到格式字符串中
all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[7],
t[1])
# 3. 关闭游标和数据库
cur.close()
conn.close()
# 替换模板中的占位符
content = re.sub(r'\{%content%\}', all_data, content)
return content
def getData(sql):
conn = Connect(host="localhost",
port=3306,
user="******",
password="******",
database="visual_db_2017",
charset="utf8")
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
conn.close()
return data
def index():
# 读取模板文件
with open('templates/index.html', 'r', encoding='utf8') as file:
content = file.read()
# 下面的开发思想使用的是 前后端不分离思想
# 首页固定数据
# 先将 标签中的固定数据变成占位符
row_str = """
<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>
<input type="button" value="添加" id="toAdd" name="toAdd" systemidvaule="%s">
</td>
</tr>
"""
# 连接数据库
db_connect = Connect(host='localhost',
port=3306,
user='******',
password='******',
database='stock_db',
charset='utf8')
cur = db_connect.cursor()
sql_str = ''' select * from info '''
cur.execute(sql_str)
result = cur.fetchall()
cur.close()
db_connect.close()
# 遍历结果,并将结果添加到格式字符串中
all_data = ''
for t in result:
all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[7],
t[1])
# 字符串是一个不可变对象,所以要接收一下替换后的数据并返回
content = content.replace('{%content%}', all_data)
# 将模板文件中的内容返回
return content
def send_update(ret):
with open('./templates/update.html', encoding='utf8') as f:
""""open打开的路径是相对web_server来确定的"""
stock_id = int(ret.group(1))
content = f.read()
conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8')
cursor = conn.cursor()
cursor.execute('select * from focus where id = %d;' % stock_id)
data = cursor.fetchone()
cursor.close()
conn.close()
content = re.sub(r'\{%code%\}', str(data[0]), content) # sub中用作替换的必须是字符串，否则会报错
content = re.sub(r'\{%note_info%\}', str(data[1]), content)
return content
def del_foucs(ret):
"""添加关注 """
focus_id = ret.group(1) # 关注id
conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8')
cursor = conn.cursor()
res = cursor.execute('delete from focus where id = %d' % int(focus_id)) # execute 只能对字符串进行拼接
if res:
msg = '删除成功!'
else:
msg = '删除失败!'
conn.commit()
cursor.close()
conn.close()
return msg
def _computeMaxSOfSomeUserInAProductType(self, userIds, productTypeId):
connection = Connect(**pymysqlConfig)
try:
maxS = float('-inf')
count = 0
for userId in userIds:
S = self._computeSOfAUserInAProductType(
connection, userId, productTypeId)
maxS = max(maxS, S)
count += 1
if count % 5 == 0:
gc.collect()
return maxS
finally:
connection.close()
def delete(match):
code = match.group(1)
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
cur = conn.cursor()
sql_str = ''' delete from focus where info_id = (select id from info where code = %s)'''
cur.execute(sql_str, (code, ))
conn.commit()
cur.close()
conn.close()
return 'OK'
def connection():
from helpers import abort
cnx = None
try:
cnx = Connect(host='timbess.net',
user='******',
password='******',
database='test')
curr = cnx.cursor(cursor=DictCursor)
return cnx, curr
except MySQLError as e:
if cnx is not None:
cnx.close()
current_app.logger.exception(e)
abort(500, message='Failed to connect to database')
def _getMaxSOfAProductTypeSingle(self, productTypeId):
getUserIdsSql = 'select reviewUserId from review \
where productTypeId = %s;'
connection = Connect(**pymysqlConfig)
try:
maxS = float('-inf')
with connection as cursor:
cursor.execute(getUserIdsSql, (productTypeId, ))
for userId in (row[0] for row in cursor.fetchall()):
S = self._computeSOfAUserInAProductType(
connection, userId, productTypeId)
maxS = max(maxS, S)
return maxS if maxS != float('-inf') else None
finally:
connection.close()
class QcwyMysqlPipeline(object):
def __init__(self, host, database, port, user, password):
self.host = host
self.database = database
self.table_name = '51job'
self.port = port
self.user = user
self.password = password
@classmethod
def from_crawler(cls, crawler):
return cls(
host=crawler.settings.get('MYSQL_HOST'),
database=crawler.settings.get('MYSQL_DATABASE'),
port=crawler.settings.get('MYSQL_PORT'),
user=crawler.settings.get('MYSQL_USER'),
password=crawler.settings.get('MYSQL_PASSWORD'),
)
def open_spider(self, spider):
# print(self.table_name)
self.db = Connect(
host=self.host,
database=self.database,
port=self.port,
user=self.user,
password=self.password,
charset='utf8',
)
self.cursor = self.db.cursor()
def process_item(self, item, spider):
data = dict(item)
keys = ','.join(data.keys())
values = ','.join(['\"%s\"' % i for i in data.values()])
sql = 'insert into %s(%s) values (%s)' % (self.table_name, keys,
values)
try:
self.cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
print('异常错误是：', e)
def close_spider(self, spider):
self.cursor.close()
self.db.close()
def update(ret): stock_id = ret.group(1) """由于浏览器会将url中的特殊字符进行url编码，因此在解析时需要对其进行解码码""" note = urllib.parse.unquote(ret.group(2), encoding='utf8') conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() sql = 'update focus set note_info = "%s" where id = %s;' % (note, stock_id) print(sql) res = cursor.execute(sql) if res: msg = '修改成功!' else: msg = '修改失败！' cursor.close() conn.commit() conn.close() return msg
class db(object):
def __init__(self):
self.conn = Connect(host='mysql',
user='******',
password='******',
database='visual_db_2017',
port=3306)
def get_cur(self, sql):
"""
向数据库查询sql语句
:param sql: 需要查询的sql语法 ：string
:return: 返回结果游标
"""
cur = self.conn.cursor()
cur.execute(sql)
self.conn.close()
return cur
class ConnMysql(object):
# ===================数据库连接===================
def __init__(self,
host='localhost',
port=3306,
user='******',
password='******',
database="allip",
charset='utf8'):
self.lock = Lock()
try:
self.conn = Connect(host=host,
port=port,
user=user,
password=password,
database=database,
charset=charset)
self.cursor = self.conn.cursor()
except Exception as e:
print("数据库连接异常:{}".format(e))
print("请检查您是否使用了默认的数据库参数...")
else:
pass
finally:
pass
# ===================保存数据===================
def exe(self, sql_language):
try:
# 数据库插入数据
self.lock.acquire() # 获取锁,多线程应用.
self.cursor.execute(sql_language)
self.conn.commit()
except Exception as e:
self.conn.rollback()
print("执行sql语句失败...{}".format(e))
else:
pass
finally:
self.lock.release() # 释放锁
def close(self):
self.cursor.close()
self.conn.close()
def update_commit(match):
code = match.group(1)
note_info = match.group(2)
note_info = unquote(note_info)
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
cur = conn.cursor()
sql_str = ''' update focus set note_info = %s where info_id = (select id from info where code = %s);'''
cur.execute(sql_str, (note_info, code))
conn.commit()
cur.close()
conn.close()
return 'OK'
class SaveSqlPipeline(object):
def process_item(self, item, spider):
self.save_pics_info(item)
self.save_pics_detail(item)
self.conn.commit()
return item
def save_pics_info(self, item):
url = item['image_path'][0]
title = item['title']
tags = item['tags']
date = item['date']
params = [url, title, tags, date]
sql = 'insert into pics_info (url,title,tags,date) values (%s,%s,%s,%s)'
logging.info(sql % tuple(params))
self.cursor.execute(sql, params)
def save_pics_detail(self, item):
id = self.cursor.lastrowid
image_urls = item['image_path'][1:]
for url in image_urls:
params = [id, url]
sql = 'insert into pic_detail (pic_info_id,url) values (%s,%s)'
logging.info(sql % tuple(params))
self.cursor.execute(sql, params)
def open_spider(self, spider):
self.conn = Connect(
host=spider.settings.get('MYSQL_HOST'),
port=spider.settings.get('MYSQL_PORT'),
database=spider.settings.get('MYSQL_DATABASE'),
user=spider.settings.get('MYSQL_USER'),
password=spider.settings.get('MYSQL_PASSWORD'),
charset=spider.settings.get('MYSQL_CHARSET'),
)
self.cursor = self.conn.cursor()
# spider (Spider 对象) – 被开启的spider
# 可选实现，当spider被开启时，这个方法被调用。
def close_spider(self, spider):
self.cursor.close()
self.conn.close()
def add_foucs(ret):
"""添加关注 """
stock_code = ret.group(1) # 股票代号
conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8')
cursor = conn.cursor()
# 判断是否存在此股票
cursor.execute('select 1 from info where code = %s limit 1;', (stock_code,))
if not cursor.fetchone():
return '不存在对应的股票信息!'
# 判断是否已添加关注
cursor.execute('select * from focus inner join info on focus.info_id = info.id having code = %s;', (stock_code,))
if cursor.fetchone():
return '此股票已在关注列表，请勿重复添加!'
# 若未关注，则添加关注
cursor.execute('insert into focus (info_id) select id from info where code = %s;', (stock_code,))
conn.commit()
cursor.close()
conn.close()
return '添加成功!'
class PythonJobMySqlPipeline(object):
def open_spider(self, spider):
print("爬虫开始了mysql------------------------------------------")
self.client = Connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PASSWORD,
database=MYSQL_DBNAME,
port=MYSQL_PORT,
charset='utf8')
self.sursor = self.client.cursor()
def close_spider(self, spider):
#关闭数据库，释放资源
self.sursor.close()
self.client.close()
def process_item(self, item, spider):
s = dict(item)
parms = [
s["url"],
s["title"],
s["location"],
s["company_name"],
s["salary"],
s["company_info"],
s["experience"],
s["job_info"],
s["address"],
s["crawled"],
s["spider"],
]
sql = "INSERT INTO job_items(url,title,location,company_name,salary,company_info,experience,job_info,address,crawled,spider) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
self.sursor.execute(sql, parms)
#事务提交
self.client.commit()
#字典
return item
def do_sql(self, query, flag=1):
'''query:查询语句
flag：标记，1：查询第一条数据，2：查询获取多条的数据'''
# 读取配置文件的数据库信息，连接数据库，
db_config = ReadConfig().get_data('DB', 'db_config') # 数据库基本信息
# cnn=connector.connect(**db_config) #建立连接
cnn1 = Connect(**db_config)
# 获取游标
# cursor=cnn.cursor()
cursor = cnn1.cursor()
# 操作数据表， 执行查询语句
cursor.execute(query)
# 判断flag，要获取哪些数据
if flag == 1:
res = cursor.fetchone() #查询第一条数据，返回的是元祖类型
else:
res = cursor.fetchall() #查询所有的数据，返回的是列表嵌套元祖类型
cnn1.close()
cursor.close()
return res
def _initMaxSData(self):
getMaxSRecordsSql = 'select productTypeId, maxS from\
textAnalysisMaxSRecord;'
getProductTypeIdsSql = 'select id from productType where id not in \
(select productTypeId from textAnalysisMaxSRecord);'
connection = Connect(**pymysqlConfig)
try:
with connection as cursor:
# 载入已经计算好了的maxS
cursor.execute(getMaxSRecordsSql)
res = cursor.fetchall()
self._data = {row[0]: row[1] for row in res}
# 初始化为计算的
cursor.execute(getProductTypeIdsSql)
res = cursor.fetchall()
for productTypeId in (row[0] for row in res):
maxS = self._getMaxSOfAProductType(productTypeId)
if maxS is not None:
self._data[productTypeId] = maxS
finally:
connection.close()
rai.name as area_name
FROM rs_product_base_info as rpbi,
rs_product_sales_info as rpsi,
rs_shop_info as rsi,
rs_area_info as rai
WHERE
rpbi.id = rpsi.product_id AND
rpbi.shop_id = rsi.id AND
rsi.area_code = rai.area_code AND
rpbi.id = %s
"""
pool = [i for i in range(100000)]
items = random.choices(pool, k=1)
items = [str(i).zfill(11) for i in items]
start_time = time.time()
try:
for i in items:
cursor.execute(sql, i)
for k, v in cursor.fetchall()[0].items():
if type(v) not in (float, int, str):
print(v, type(v))
except Exception:
print("err")
finally:
cursor.close()
db.close()
print(time.time() - start_time)