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 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 _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 _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 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 insert_whmcs_ipv4(insert: [(IPv4Address, str, IPv4Address, int)],
interface: str,
db: Connect,
debug: bool = False,
verbose: bool = False):
"""
This function insert given IPs and MACs to WHMCS
:param insert: The list of IPs, MACs to insert
:type insert: [(IPv4Address, str, IPv4Address, int)]
:param interface: The interface of IPs
:type interface: str
:param db: The database connection of WHMCS
:type db: pymysql.Connect
:param debug: Disable commit on database
:type debug: bool
:param verbose: Print actions on database
:type verbose: bool
"""
cursor = db.cursor()
# Get gateway
gateway = insert[0][0]
del insert[0]
# Get vlan if given
if interface[:4] == "vlan":
try:
vlan = int(interface[4:])
except ValueError:
raise ValueError("Invalid vlan !")
else:
vlan = "null"
# For every IP to insert
for i in insert:
if i[1]:
cmd = f"INSERT INTO mg_proxmox_addon_ip (ip, type, mac_address, subnet_mask, cidr, sid, gateway, tag) " \
f"VALUES ('{i[0]}', 'IPv4', '{i[1]}', '{i[2]}', {i[3]}, 0, '{gateway}', {vlan})"
try:
cursor.execute(cmd)
except Exception as e:
print(cmd, file=stderr)
raise e
if debug or verbose:
print(cmd)
cursor.close()
# Commit to the DB
if not debug:
try:
print("Commit to DB...")
db.commit()
except Exception as e:
raise e
else:
print("Commited to DB")
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):
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 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 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 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 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 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 _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 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 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
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()
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
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
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()
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 __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 remove_whmcs_ipv4(remove: [(IPv4Address, str, IPv4Address, int)],
db: Connect,
debug: bool = False,
verbose: bool = False):
"""
This function remove IPv6 on the router
:param remove: The list of IPs, MACs to remove
:type remove: [(IPv4Address, str, IPv4Address, int)]
:param db: The database connection of WHMCS
:type db: pymysql.Connect
:param debug: Disable commit on database
:type debug: bool
:param verbose: Print each command on router
:type verbose: bool
"""
cursor = db.cursor()
for i in remove:
cmd = f"DELETE FROM mg_proxmox_addon_ip WHERE ip = '{i[0]}'"
try:
cursor.execute(cmd)
except Exception as e:
print(cmd, file=stderr)
raise e
if debug or verbose:
print(cmd)
cursor.close()
# Commit to the DB
if not debug:
try:
print("Commit to DB...")
db.commit()
except Exception as e:
raise e
else:
print("Commited to DB")
