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 _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()
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)
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 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 connect(self):
# 从连接池中获取数据连接
self.conn = Connect(host=self.host,
port=self.port,
user=self.username,
password=self.password,
db=self.db,
charset=self.charset)
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 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 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()
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 open_spider(self, spider):
self.db = Connect(
host=self.host,
user=self.user,
password=self.password,
database=self.database,
port=self.port,
charset='utf8',
)
self.cursor = self.db.cursor()
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 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 connect(self):
SQLResource.connect(self)
self._connection = Connect(
host = self._host, port = self._port, connect_timeout = self._connect_timeout,
db = self._database, user = self._username, passwd = self._password,
sql_mode = self._sql_mode, charset = self._charset)
try:
self._connection.autocommit(False)
except:
self._connection.close()
raise
def __get_conn(self):
"""
获取连接
:return:
"""
con = None
try:
con = Connect(**self.__setttings)
dblog.info(u"获取数据库连接")
except BaseException as e:
dblog.err(u"数据库连接获取失败:%s" % e.message)
return con
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 connect_db(self,
user,
password,
database,
host='127.0.0.1',
port=3306,
charset="utf8"):
return Connect(host=host,
user=user,
password=password,
database=database,
port=port,
charset=charset)
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 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 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 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 _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 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()
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
def get_mysql_connect(conf):
for nu in range(1, 3):
try:
co = Connect(host=conf['host'],
port=conf['port'],
user=conf['user'],
password=str(conf['password']),
database=conf['database'],
charset='utf8')
if co is not None:
logger.info('got connect')
return co
except Exception as e:
# 连接获取失败后2分钟后重试
logger.error('can not get mysql connect ')
logger.error(e)
time.sleep(60 * 2)
return 0
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 '添加成功!'
def update(match):
path = './templates/update.html'
code = match.group(1)
with open(path, 'r') as f:
file_content = f.read()
conn = Connect(host='localhost',
port=3306,
database='stock_db',
user='******',
password='******',
charset='utf8')
cur = conn.cursor()
sql_str = '''select note_info from focus where info_id = (select id from info where code = %s); '''
cur.execute(sql_str, (code, ))
sql_result = cur.fetchone()
file_content = re.sub(r'\{%code%\}', code, file_content)
file_content = re.sub(r'\{%note_info%\}', sql_result[0], file_content)
return file_content
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'
