def btn_query_clicked(self):
condition = self.conditionEdit.text()
dbname = "dbbook" if self.comboBox.currentText() == "豆瓣" else "Fang"
db = DBHelper()
rows = db.Query_by_condition(dbname, condition)
if rows is None:
QtWidgets.QMessageBox.warning(self.btn_delete, "提示", '查询失败')
return None
self.tableWidget.setRowCount(len(rows))
self.tableWidget.setColumnCount(3 if dbname == 'dbbook' else 5)
if dbname == 'dbbook':
self.tableWidget.setHorizontalHeaderLabels(
['title', 'author', 'rate'])
for row, i in zip(rows, range(self.tableWidget.rowCount())):
for column, j in zip(row,
range(self.tableWidget.columnCount())):
newItem = QtWidgets.QTableWidgetItem(str(column))
self.tableWidget.setItem(i, j, newItem)
else:
self.tableWidget.setHorizontalHeaderLabels([
'describtion', 'structure', 'areasize', 'selling_price',
'address'
])
for row, i in zip(rows, range(self.tableWidget.rowCount())):
for column, j in zip(row,
range(self.tableWidget.columnCount())):
newItem = QtWidgets.QTableWidgetItem(str(column))
self.tableWidget.setItem(i, j, newItem)
def __generateExecCommand(self):
dbworkingDir = ContextInitiator().getProperDict(
)['dbWorkingFloder'] + '/'
db = DBHelper()
self.__getSmr = db.dbExecCommand("smr2CmrPrdct.sql", self.__logsPath,
"smr2CmrPrdct.log")
self.__makeSpace = db.dbExecCommand("space4StagePrdct.sql",
self.__logsPath,
"space4StagePrdct.log")
self.__ins2Differ = db.dbExecCommand(
"ins2Differ.sql") # ,self.__logsPath,"ins2Differ.log"
# self.__backupSnyc = db.dbExecCommand("backupSnyc.sql")#,self.__logsPath,"backupSnyc.log"
self.__smrImpFail = db.dbExecCommand("smrImpFailed.sql",
self.__logsPath,
"smrImpFailed.log")
self.__productsData = db.dbExecCommand("getProduct.sql",
self.__logsPath,
"getProduct.log")
self.__impPrdctFaild = db.dbExecCommand("impPrductFailed.sql",
self.__logsPath,
"impPrductFailed.log")
self.__impSourceCSV = dbHelper.dbExecCmd4Remote(
dbworkingDir, "impSmr2CmrPrdct.sql")
self.__importProduct = dbHelper.dbExecCmd4Remote(
dbworkingDir, "impProduct.sql")
self.__impSourceCSVLocal = db.dbExecCommand(
"impSmr2CmrPrdct.sql") # ,logsPath,"impSmrFromCsv.log"
self.__importProductLocal = db.dbExecCommand(
"impProduct.sql") # ,logsPath,"impProduct.log"
class GetMookClass:
def __init__(self):
self.c = Crawler()
self.db = DBHelper('localhost', 'root', '', 'test', 3306)
self.lorder = int(time.time())
def usage(self):
print '''
-h print this message
-e everyday run, just check first page and find new classes
-a all refresh, check all the pages and add new classes
'''
def run(self):
if len(sys.argv) == 1:
self.usage()
else:
try:
opts, args = getopt.getopt(sys.argv[1:], "hea")
for op, value in opts:
if op == "-h":
self.usage()
elif op == "-e":
self.startCrawl()
elif op == "-a":
self.startCrawl(1)
else:
self.usage()
except:
self.usage()
def startCrawl(self, all=0):
self.c.login("http://www.imooc.com/course/list", "http://www.imooc.com/user/login")
if(all):
index = 1
while(self.crawlSinglePage(index)):
index = index + 1
else:
self.crawlSinglePage(1)
def crawlSinglePage(self, pageId):
url = 'http://www.imooc.com/course/list?page=%d' % pageId
classes = self.c.getClasses(url)
if(len(classes) == 0):
return 0
else:
for cls in classes:
dbcls = self.db.selectClassByMid(cls.mid)
if(not dbcls):
cls.lorder = self.lorder
cid = self.db.insertClass(cls)
self.refreshTitles(cid, cls.mid)
return 1
def refreshTitles(self, cid, mid):
titles = self.c.getTitles(cid, mid)
pid = 0
for title in titles:
if(title.mid == 0):
pid = self.db.insertTitle(title)
else:
title.pid = pid
self.db.insertTitle(title)
def btn_register_click(self):
user_info = {
'username': self.username_Edit.text(),
'password': self.passEdit.text()
}
db = DBHelper()
result = db.Regist_user(user_info)
QtWidgets.QMessageBox.information(self.btn_login, "提示", result)
def btn_delete_clicked(self):
condition = self.conditionEdit.text()
dbname = "dbbook" if self.comboBox.currentText() == "豆瓣" else "Fang"
db = DBHelper()
result = db.Delete_by_condition(dbname, condition)
if not result:
QtWidgets.QMessageBox.warning(self.btn_delete, "提示", '删除失败或未输入条件')
else:
QtWidgets.QMessageBox.information(self.btn_delete, "提示", '删除成功')
def dashboard():
db = DBHelper()
data = {'todo': 0, 'checked': 0, 'pending': 0}
for row in db.select():
if row['status'] == 0:
data['todo'] += 1
elif row['status'] == 1:
data['checked'] += 1
elif row['status'] == 2:
data['pending'] += 1
db.close()
return json.dumps(data)
def btn_login_click(self, Login):
user_info = {
'username': self.username_Edit.text(),
'password': self.passEdit.text()
}
db = DBHelper()
result = db.Confirm_user(user_info)
if result:
QtWidgets.QMessageBox.information(self.btn_login, "提示", '登录成功')
self.MWinow_UI.setupUi(self.MWindow, user_info['username'])
self.center(self.MWindow)
Login.close()
self.MWindow.show()
else:
QtWidgets.QMessageBox.warning(self.btn_login, "提示", '登录失败')
def main():
db = DBHelper()
while True:
print("********************")
print()
print("press 1 to insert new user")
print("press 2 to display all user")
print("press 3 to delete user")
print("press 4 to update user")
print("press 5 to exit program")
print()
try:
choice = int(input())
if (choice == 1):
##insert user
uid = int(input("Enter user id:"))
username = input("Enter user name: ")
userphone = input("Enter user phone: ")
db.insert_user(uid, username, userphone)
elif choice == 2:
##display user
db.fetch_all()
pass
elif choice == 3:
##delete user
userid = int(
input("enter user id to which you want to delete"))
db.delete_user(userid)
elif choice == 4:
#update user
userid = int(input("Enter user id you want to update:"))
username = input("Enter new name: ")
userphone = input("Enter new phone: ")
db.update_user(userid, username, userphone)
elif choice == 5:
break
else:
print("INVALID TRY AGAIN")
except Exception as e:
print(e)
print("Invalid Details !Try again")
def main():
db = DBHelper()
while True:
print("************WELCOME****************")
print("Press 1 to insert user")
print("Press 2 to display all user")
print("Press 3 to delete user")
print("Press 4 to update user")
print("Press 5 to exit program")
print()
try:
choice = int(input())
if (choice == 1):
#insert
uid = int(input("Enter User ID:"))
username = input("Enter UserName:"******"Enter phone Number:")
db.insert_user(uid, username, userPhone)
elif choice == 2:
#display
db.fetch_userData()
elif choice == 3:
#delete
uid = int(input("Enter User ID to delete:"))
db.delete_data(uid)
elif choice == 4:
#update
uid = int(input("Enter User ID you want to update:"))
username = input("Enter new UserName:"******"Enter new phone Number:")
db.update_data(uid, username, userPhone)
elif choice == 5:
#exit
break
else:
print("Invalid Input Try Again")
except Exception as e:
print(e)
print("Invalid User Try Again Dear!")
def __generateTmpSql(self):
ex = ''
try:
db = DBHelper()
smrTmpSql = db.sql2TmpSql("smr2CmrPrdct.sql",
{"workingDir": self.__spDir})
productTmpSql = db.sql2TmpSql("getProduct.sql",
{"workingDir": self.__spDir})
# impProductTmpSql = db.sql2TmpSql("impProduct.sql",{"workingDir":self.__spDir4Outer})
# impSmrTmpSql = db.sql2TmpSql("impSmr2CmrPrdct.sql",{"workingDir":self.__spDir4Outer,"stagingTable":self.__containStaging})
impProductTmpSql = db.sql2TmpSql("impProduct.sql",
{"workingDir": self.__spDir})
impSmrTmpSql = db.sql2TmpSql("impSmr2CmrPrdct.sql", {
"workingDir": self.__spDir,
"stagingTable": self.__containStaging
})
diffTmpSql = db.sql2TmpSql(
"ins2Differ.sql", {
"stagingTable": self.__containStaging,
"rulesDataTable": self.__containData
})
# buckupTmpSql = db.sql2TmpSql("backupSnyc.sql",{"stagingTable":self.__containStaging,"rulesDataTable":self.__containData})
impSmrFailTmpSql = db.sql2TmpSql(
"smrImpFailed.sql", {"stagingTable": self.__containStaging})
space4StagePrdct = db.sql2TmpSql(
"space4StagePrdct.sql",
{"stagingTable": self.__containStaging})
impPdctFailTmpSql = db.sql2TmpSql("impPrductFailed.sql", {})
except Exception as e:
ex = str(e)
else:
pass
finally:
return ex
def download():
db = DBHelper()
db.export()
db.close()
filename = 'label.zip'
response = make_response(
send_from_directory('static/',
filename.encode('utf-8').decode('utf-8'),
as_attachment=True))
response.headers["Content-Disposition"] = "attachment; filename={}".format(
filename.encode().decode('latin-1'))
return response
def delete():
args = request.args
db = DBHelper()
if args:
# save value
filename = args.get('filename')
db.delete(filename)
data = {'message': 'OK'}
else:
data = {'message': 'No data'}
db.close()
return json.dumps(data)
def next_picture():
args = request.args
db = DBHelper()
fetch_status = 0
if args:
# save value
value = args.get('value')
filename = args.get('filename')
status = args.get('status')
fetch_status = args.get('fetch_status')
db.update(filename, value, status)
data = db.select_by_status(fetch_status)
if data:
data = data[0]
else:
data = {}
db.close()
return json.dumps(data)
def __init__(self , my_time, my_greeting ="Hi"):
self.db=DBHelper()
self.last_update_id = None
self.updates = None
self.TOKEN = 'token user telegram'
self.URL = "https user data".format(self.TOKEN)
self.WPA_CONF_PATH = '/etc/wpa_supplicant/wpa_supplicant.conf'
self.GOOGLE_SERVER_ADDRESS = ('speech.googleapis.com', 443)
self.chat_ID = "user id chat"
self.boolean_thread = False
self.my_wifi = False
self.send_message(my_greeting, self.chat_ID)
self.kill = False
self.exit = False
self.my_callback = None
self.mosquitto = False
self.start_time = my_time
self.time_control = 0.0
class GetMookClass:
def __init__(self):
self.c = Crawler()
self.db = DBHelper('localhost', 'root', '', 'test', 3306)
self.lorder = int(time.time())
def usage(self):
print '''
-h print this message
-e everyday run, just check first page and find new classes
-a all refresh, check all the pages and add new classes
'''
def run(self):
if len(sys.argv) == 1:
self.usage()
else:
try:
opts, args = getopt.getopt(sys.argv[1:], "hea")
for op, value in opts:
if op == "-h":
self.usage()
elif op == "-e":
self.startCrawl()
elif op == "-a":
self.startCrawl(1)
else:
self.usage()
except:
self.usage()
def startCrawl(self, all=0):
self.c.login("http://www.imooc.com/course/list",
"http://www.imooc.com/user/login")
if (all):
index = 1
while (self.crawlSinglePage(index)):
index = index + 1
else:
self.crawlSinglePage(1)
def crawlSinglePage(self, pageId):
url = 'http://www.imooc.com/course/list?page=%d' % pageId
classes = self.c.getClasses(url)
if (len(classes) == 0):
return 0
else:
for cls in classes:
dbcls = self.db.selectClassByMid(cls.mid)
if (not dbcls):
cls.lorder = self.lorder
cid = self.db.insertClass(cls)
self.refreshTitles(cid, cls.mid)
return 1
def refreshTitles(self, cid, mid):
titles = self.c.getTitles(cid, mid)
pid = 0
for title in titles:
if (title.mid == 0):
pid = self.db.insertTitle(title)
else:
title.pid = pid
self.db.insertTitle(title)
def spawnDBTask(self, db, sql, data):
dbh = DBHelper(db)
returnedData = dbh.query(sql, data)
self.dbPane.messages.insert('end',
f'Done! Found {len(returnedData)} games.')
self.after(0, self.populateTree, returnedData)
def __init__(self):
self.dbHelper = DBHelper()
class movieSun(object):
def __init__(self):
self.dbHelper = DBHelper()
#测试创建数据库（settings配置文件中的MYSQL_DBNAME,直接修改settings配置文件即可）
def testCreateDatebase(self):
self.dbHelper.createDatabase()
#测试创建表
def testCreateTable(self):
sql="create table moviesun(id int primary key auto_increment,title varchar(100),href varchar(100),data varchar(500))"
self.dbHelper.createTable(sql)
#测试插入
def testInsert(self,movieid,title,href,data):
sql="insert into moviesun(id,title,href,data) values(%s,%s,%s,%s)"
params=(movieid,title,href,data,)
self.dbHelper.insert(sql,*params) # *表示拆分元组，调用insert（*params）会重组成元组
#更新数据
def testUpdate(self,title,href,data,movie_id):
sql="update moviesun set title=%s,href=%s,data=%s where id=%s"
params=(title,href,data,movie_id,)
self.dbHelper.update(sql,*params)
#删除数据
def testDelete(self,movie_id):
sql="delete from moviesun where id=%s"
params=(movie_id,)
self.dbHelper.delete(sql,*params)
#删除全部数据
def testDeleteall(self):
sql="delete from moviesun"
#params=(movie_id,)
self.dbHelper.deleteall(sql)
def testSelectall(self):
sql = 'select title from moviesun where 1=1'
return self.dbHelper.select(sql)
def __DBConnStr(self):
dicty = ContextInitiator().getProperDict()
db = DBHelper()
self.__remoteDbAlia = db.getDbAliaCmd(
(dicty['dbInstance'], dicty['alia'], dicty['nodeName']))
self.__remoteDBNode = db.getDbNodeCmd((
dicty['nodeName'],
dicty['dbIp'],
dicty['port'],
dicty['dbInstance'],
))
self.__conn2RemoteDb = db.getConn2DbCmd(
(dicty['alia'], dicty['usr'], dicty['pwd']))
self.__ourDbAlia = db.getDbAliaCmd(
(dicty['ourDbInstance'], dicty['ourAlia'], dicty['ourNodeName']))
self.__ourDBNode = db.getDbNodeCmd(
(dicty['ourNodeName'], dicty['ourDbIp'], dicty['ourPort'],
dicty['ourDbInstance']))
self.__conn2OurDb2 = db.getConn2DbCmd(
(dicty['ourAlia'], dicty['ourUsr'], dicty['ourPwd']))
self.__isNodeExist = db.isNodeExistCmd((dicty['nodeName']))
self.__isAliaExist = db.isAliaExistCmd((dicty['alia'])) # .upper()
self.__isOurNodeExist = db.isNodeExistCmd((dicty['ourNodeName']))
self.__isOurAliaExist = db.isAliaExistCmd(
(dicty['ourAlia'])) # .upper()
self.__uncataDBAlia = db.getUncataDBAliaCmd((dicty['alia']))
self.__uncataNode = db.getUncataNode((dicty['nodeName']))
self.__uncataOurDBAlia = db.getUncataDBAliaCmd((dicty['ourAlia']))
self.__uncataOurNode = db.getUncataNode((dicty['ourNodeName']))
self.__connOuterSpaceDb = db.getOuterSpaceDbCmd(
(dicty['ourDbInstance'], dicty['ourUsr'], dicty['ourPwd']))
def __init__(self):
self.c = Crawler()
self.db = DBHelper('localhost', 'root', '', 'test', 3306)
self.lorder = int(time.time())
def __init__(self):
self.db = DBHelper()
self.db.setup()
class ChatBot(object):
api_key = "314877931:AAFUGdvrc9wRDN7mEmHjgbk6YJJ5m0NZgIM"
URL = "https://api.telegram.org/bot{}/".format(api_key)
def __init__(self):
self.db = DBHelper()
self.db.setup()
def get_url(self, url):
response = requests.get(url)
content = response.content.decode("utf8")
return content
def get_json_from_url(self, url):
content = self.get_url(url)
js = json.loads(content)
return js
def get_updates(self, offset=None):
url = self.URL + "getUpdates?timeout=100"
if offset:
url += "&offset={}".format(offset)
js = self.get_json_from_url(url)
return js
def get_last_update_id(self, updates):
update_ids = []
for update in updates["result"]:
update_ids.append(int(update["update_id"]))
return max(update_ids)
def get_last_chat_id_and_text(self, updates):
num_updates = len(updates["result"])
last_update = num_updates - 1
text = updates["result"][last_update]["message"]["text"]
chat_id = updates["result"][last_update]["message"]["chat"]["id"]
return (text, chat_id)
def handle_updates(self, updates):
for update in updates["result"]:
text = update["message"]["text"]
chat = update["message"]["chat"]["id"]
items = self.db.get_items()
if text == "/done":
keyboard = self.build_keyboard(items)
self.send_message("Select an item to delete", chat, keyboard)
elif text == "/start":
self.send_message(
"Welcome to your personal To Do list. Send any text to me and I'll store it as an item. Send /done to remove items",
chat)
elif text.startswith("/"):
continue
elif text in items:
self.db.delete_item(text)
items = self.db.get_items()
keyboard = self.build_keyboard(items)
self.send_message("Select an item to delete", chat, keyboard)
else:
self.db.add_item(text)
items = self.db.get_items()
message = "\n".join(items)
self.send_message(message, chat)
def build_keyboard(self, items):
keyboard = [[item] for item in items]
reply_markup = {"keyboard": keyboard, "one_time_keyboard": True}
return json.dumps(reply_markup)
def send_message(self, text, chat_id, reply_markup=None):
text = urllib.quote(text, safe='')
url = self.URL + "sendMessage?text={}&chat_id={}&parse_mode=Markdown".format(
text, chat_id)
if reply_markup:
url += "&reply_markup={}".format(reply_markup)
self.get_url(url)
def main(self):
last_update_id = None
while True:
updates = self.get_updates(last_update_id)
if len(updates["result"]) > 0:
last_update_id = self.get_last_update_id(updates) + 1
self.handle_updates(updates)
time.sleep(0.5)
import json
import requests
import time
import urllib
import io
import random
from dbHelper import DBHelper
from secrets import bot_token, appid
db = DBHelper()
URL = "https://api.telegram.org/bot{}/".format(bot_token)
WOLF_URL = "http://api.wolframalpha.com/v2/"
SHORT_ANS_URL = WOLF_URL + "result?appid={}".format(appid)
SIMPLE_ANS_URL = WOLF_URL + "simple?appid={}".format(appid)
def get_url(url):
"""
sends a GET request to the telegram API
"""
response = requests.get(url)
content = response.content.decode("utf8")
return content
def get_json(url):
"""
converts the response from the telegram API into json format
"""
content = get_url(url)
js = json.loads(content)
return respone
if '_id' not in data:
respone = make_response(
jsonify({
"success": "collection.get_one:fail",
"errMsg": "missing id"
}))
respone.status_code = 400
return respone
_openid = data['_openid']
_id = data['_id']
result = db_helper.selectHomeworkSingle(_openid, _id)
if result:
result['_id'] = str(result['_id']).lstrip("Object(").rstrip(")")
respone = make_response(
jsonify({
"success": "collection.get_one:OK",
"result": result
}))
respone.status_code = 200
return respone
if __name__ == '__main__':
db_helper = DBHelper()
app.run(host="0.0.0.0", port="8989", debug=False)
def __init__(self):
self.c = Crawler()
self.db = DBHelper('localhost', 'root', '', 'test', 3306)
self.lorder = int(time.time())
