def startWebServer():
""" Function: startWebServer()
Description: 启动数据展示模块
Input: 无
Return: :return :无
Others: 无
"""
print '准备开启数据展示模块'
try:
cmd = 'gnome-terminal -x bash -c "{}/web_server;exec bash;"'.format(
os.path.abspath('.'))
os.system(cmd)
except Exception as error:
try:
import web_server
web_server.start()
except Exception:
DataBase.writeLog(
"展示模块开启失败，请检查服务器地址是否被占用:" + error,
status='[ERROR]')
print "展示模块开启失败，请检查服务器地址是否被占用:" + error
global ERROR
ERROR = True
return
if not ERROR:
DataBase.writeLog('数据展示模块开启成功')
def __init__(self, width, height):
pygame.init()
self.width = width
self.height = height
self.board = Board.Board(width, height)
self.player = Player(TRIANGLE_SIZE, TRIANGLE_SIZE,
width / 2 - TRIANGLE_SIZE / 2, height * 5 / 6)
self.level = START_LEVEL
self.enemies = self.getEnemies(self.level)
self.bulletToDisplay = []
self.ammoToDisplay = []
self.clock = pygame.time.Clock()
self.gameFlag = False
self.timerFlag = False
self.moveThread = ''
self.enemiesMovementThread = ''
self.gameTime = 0 # don't know why, but I created some kind of game timer
self.levelTime = 0
self.currFps = 0
self.TextFieldLabels = []
self.textBoxEnter = ''
self.playerName = ''
self.db = DataBase('scores.db')
self.db.connect()
self.db.addScoreTable('scoresTable')
self.db.close()
def __init__(self):
self.crawler = GoogleCrawler()
self.db = DataBase()
self.kakao = KakaoTalk()
self.db_table_name = 'googlecrawler'
self.keywords = []
def __init__(self, link, positionTarget, modelSK, language="en"):
self.__vect = [] #Contem as colunas que não são target
self.__target = [] #Contem a coluna target em forma de vetor
self.__totalVet = [] #contem o vetor de forma total
self.__model = modelSK #model KNN
self.__zeroPositivo = [
] #vetor que ira armazenar os testes positivos de 0
self.__umPositivo = [
] #vetor que ira armazenar os testes positivos de 1
self.__columnsZero = {} #colunas dos casos negativos
self.__columnsUm = {} #colunas dos casos positivos
#variaveis de treinamento e teste
self.__x_train = []
self.__x_test = []
self.__y_train = []
self.__y_test = []
try:
self.__Strings = ET.parse("Xmls\\Strings_" + language +
".xml").getroot()
self.data = DataBase(link, language) #Associação
except FileNotFoundError:
print("File not found !!!")
print("Language english defined.")
self.__Strings = ET.parse("Xmls\\Strings_en.xml").getroot()
self.data = DataBase(link) #Associação
for i in range(0, len(self.data.data)):
self.__totalVet.append(list(self.data.data.iloc[i].values))
self.defineTarget(positionTarget)
def openDB(self, name, login, password):
obj = dict()
tot = dict()
dbPath = self.getDataBasePath(name)
used_tables = get_used_tables()
print('Used tables', used_tables)
print('Path', dbPath)
if not dbPath:
obj["Result"] = False
elif name in used_tables:
obj["Result"] = False
else:
print("Here")
self.db = DataBase(readJson(dbPath))
if self.db.authentication(login, password):
add_used_table(name.strip())
self.path = dbPath
obj["Result"] = True
tables = []
for o in self.db.getTableNamesSet():
tables.append(o)
obj["Tables"] = tables
obj["Name"] = name
obj["Login"] = login
obj["Password"] = password
else:
self.db = None
self.path = ""
obj["Result"] = False
tot["openDataBase"] = obj
print('Self db', self.db.toJson())
return json.dumps(tot)
def main():
db = DataBase()
db.connect()
m = Miner()
m.get_files()
m.get_tags()
m.insert()
def openDB(self, name, login, password):
obj = dict()
tot = dict()
dbPath = self.getDataBasePath(name)
if not dbPath:
obj["Result"] = False
elif name in set(self.used_tables.get()):
obj["Result"] = False
else:
self.db = DataBase(readJson(dbPath))
if self.db.authentication(login, password):
e = set(self.used_tables.get())
e.add(name)
self.used_tables.set(e)
self.path = dbPath
obj["Result"] = True
tables = []
for o in self.db.getTableNamesSet():
tables.append(o)
obj["Tables"] = tables
obj["Name"] = name
obj["Login"] = login
obj["Password"] = password
else:
self.db = None
self.path = ""
obj["Result"] = False
tot["openDataBase"] = obj
return json.dumps(tot)
def ImportDatabase():
df = ReadDataform("moneyold.csv")
Time, Name, Amount, Type = ReadData(df)
#处理type数据
TypeID = 0
TypeDic = {}
for TypeItem in Type:
if TypeDic.__contains__(TypeItem) == False:
TypeID += 1
TypeDic[TypeItem] = TypeID
db = DataBase("money.db")
#先放项目类型数据库
sql = "INSERT INTO IncomeType VALUES( "
for TypeItem in TypeDic:
insertSQL = sql + str(TypeDic[TypeItem]) + " , \"" + TypeItem + "\");"
db.Execute(insertSQL)
# 再放收入支出表
sql = "INSERT INTO INCOME VALUES ( "
ID = 0
for item in Time:
time = Time[ID]
name = Name[ID]
amount = tools.Yuan2Fen(Amount[ID])
typeID = TypeDic[Type[ID]]
#if typeID == 25:
# print(time, name, amount, Amount[ID])
ID += 1
insertSQL = sql + str(ID) + " , " + str(time) + " , \"" + name + "\" , " + str(amount) + " , " + str(typeID) + ");"
db.Execute(insertSQL)
'''
def main(event, context):
db = DB('innodb')
db.dropTables()
print('[INFO]: Tables have been dropped!')
db.createTables()
print('[INFO]: Tables have been created!')
print('[INFO]: DONE!')
def test_write_array_of_measurements_to_database(self):
data_point = Measurement(value=20, timeStamp=time.time(), units='degC')
array_of_points = [data_point, data_point, data_point]
o = DataBase('DB/Dummy')
o.write_array_of_measurements_to_database(array_of_points, 'Dummy')
# TODO: insert assertion
def unique_table_rows(self, table, text):
base = DataBase(self.host, self.user, self.password, self.database)
search = '%' + text + '%'
function = Search(self.host, self.user, self.password)
columns = base.showColumnInfo(self.database, table)
column_list = base.getColumnInfo(columns, 0)
search_column_list = [column_list[1], column_list[2], column_list[3]]
search_result = []
for column in search_column_list:
result = function.searchInColumn(table, column, search)
if result is not None:
for res in result:
search_result.append(list(res))
temp_equals = []
for i in range(len(search_result)):
temp_equals.append(search_result[i][1])
list_of_links_without_repeats = list(set(temp_equals))
unique_table_rows = []
for i in range(len(search_result)):
if search_result[i][
1] in list_of_links_without_repeats and search_result[
i] not in unique_table_rows:
unique_table_rows.append(search_result[i])
return unique_table_rows
def showDBs(a_list, env):
db = DataBase()
dbname_list = db.getAllDB()
if dbname_list:
showAsTable(['Databases'], dbname_list)
else:
print "There is no databases!"
def testMode(host):
""" Function: testMode()
Description: 测试模式，以测试模式顺序启动各个模块
Input: 主机地址
Return: 无
Others: 无
"""
DataBase.mysqlInit(DataBase.mysqlConnect())
p = multiprocessing.Pool(5)
p.apply_async(startGatherServer())
p.apply_async(startSendData, args=(host,))
p.apply_async(startWebServer)
p.apply_async(startBrowser(host))
p.apply_async(startHDFS())
p.close()
p.join()
time.sleep(2)
print 'All subprocess started!'
time.sleep(2)
if not ERROR:
DataBase.writeLog('测试模式正常开启')
print '测试模式正常开启'
else:
print '测试模式开启失败'
def startBrowser(host='127.0.0.1'):
""" Function: startBrowser()
Description: 自动启动浏览器。
Input: 数据报表网址
Return: :return :无
Others: 无
"""
time.sleep(10)
i = 0
print '正在尝试开启浏览器'
while True:
time.sleep(4)
i = i + 1
if isConnect():
webbrowser.open('http://' + host + ':4000/chart')
print '浏览器成功开启，正在跳转到动态图表页面'
return
else:
print '通信失败'
if i > 4:
print '浏览器开启失败，请检查web_server是否运行在5000端口'
DataBase.writeLog(
'浏览器开启失败，请检查web_server是否运行在5000端口',
status='[ERROR]')
global ERROR
ERROR = True
sys.exit(2)
def main():
db = DataBase('root', 'carroz98')
dados = []
dados = fetchAll()
global sec_between
global total_time
if (sec_between > total_time):
total_time = sec_between
sec_spent = 0
while sec_spent < total_time:
dados = fetchAll()
for dic in dados:
sensor, patient, biometrics = parse_json(dic)
if (db.verify(sensor)):
db.insert(sensor)
if (db.verify(patient)):
db.insert(patient)
db.insert(biometrics)
time.sleep(sec_between)
sec_spent += sec_between
def startGatherServer():
""" Function: startHDFS()
Description: 启动分布式平台
Input: mac地址
Return: :return :无
Others: 无
"""
print '准备开启数据采集模块'
try:
cmd = 'gnome-terminal -x bash -c "{}/DataGatheringServer;exec bash;"'.format(
os.path.abspath('.'))
os.system(cmd)
except Exception:
try:
from DataGathering import DataGatheringServer
DataGatheringServer.start()
except Exception as error:
DataBase.writeLog(
"采集模块开启失败，请检查服务器地址是否被占用:" + error,
status='[ERROR]')
print "采集模块开启失败，请检查服务器地址是否被占用:" + error
global ERROR
ERROR = True
return
DataBase.writeLog('数据采集模块开启成功')
def create_app(test_config=None):
# crea e configura l'app
app = Flask(__name__, instance_relative_config=True)
app.config.from_mapping(
SECRET_KEY='dev',
DATABASE=os.path.join(app.instance_path, 'flaskr.sqlite'),
)
if test_config is None:
# carica i config, quando non si sta testando
app.config.from_pyfile('config.py', silent=True)
else:
# carica i config passati in input
app.config.update(test_config)
try:
os.makedirs(app.instance_path)
except OSError:
pass
# registra i comandi del database
DataBase.init_app(app)
# registra tutti i blueprint
app.register_blueprint(Home.bp)
app.register_blueprint(Auth.bp)
app.register_blueprint(Place.bp)
app.register_blueprint(User.bp)
# una pagina che ci ridirige verso /home
@app.route('/')
def prova():
return redirect(url_for('Home.home'))
return app
class DataValidation:
def __init__(self, host, user, password, database):
self.host = host
self.user = user
self.password = password
self.database = database
self.base = DataBase(self.host, self.user, self.password,
self.database)
def getArrayTable(self, table_name):
column_list = self.base.getColumnList(self.database, table_name)
table_data = []
for i in range(len(column_list)):
column = self.base.select(table_name, column_list[i])
table_data.append(column)
table_data = numpy.array(table_data).T
return table_data
def validationInsertData(self, table_name, new_data):
column_list = self.base.getColumnList(self.database, table_name)
table_data = self.getArrayTable(table_name)
add_list = []
for row in new_data:
for table_cell in row:
if table_cell not in table_data:
add_list.append(row)
self.base.insertInto(table_name, add_list, column_list)
def __init__(self, host, user, password, database):
self.host = host
self.user = user
self.password = password
self.database = database
self.base = DataBase(self.host, self.user, self.password,
self.database)
def test_RegexInDB():
global pts
db = DataBase()
for i in pts: db.AddSeq(i)
print(db.RegexInDB("FGHI")) #list of tuples (key in db, index in seq)
print(db.RegexInDB("FGHI",1)) #list of indexes in seq
print(db.RegexInDB("E*FGHI"))
def test_BlastParse():
#ProteinBlast > BlastParse > BlastFetch pipeline
db = DataBase()
#db.ProteinBlast(1)
ANs = db.BlastParse("Blast_mitf_on.xml") #dict = {ANs : description}
for i in ANs.keys(): print(i,"\n",ANs[i],end="\n\n")
db.ProteinFetch(list(ANs.keys())[:3]) #choose results
print("Sequences added: ",len(db.dseqs))
def post(self):
db = DataBase()
db_cur = db.get_object_cur()
db_cur.execute('''TRUNCATE TABLE users, forum, thread, messages, votes, usersForums''')
db.obj_reconnect(True)
self.set_header("Content-type", "application/json")
self.set_status(200)
def __init__(self):
self.ct = ColorText()
self.exp = Explanation()
self.db = DataBase()
self.unUsedKeys = "../../DataBase/unUsedKeys.txt"
self.usedKeys = "../../DataBase/usedKeys.txt"
self.cachedKeys = []
self.s = socket.socket()
def upload():
if request.method == 'GET':
return render_template('upload.html', user=current_user)
else:
file = request.files['file_data']
filename = attachement.save(file)
form = request.form
DataBase.saveApp(form['id'],form['name'],form['version'],form['info'],filename,current_user.user_id)
return jsonify({'code': 200, 'msg': '上传成功'})
def __refreshLable(self):
db = DataBase()
db.feedbackMsg = []
for speed, angle in db.feedbackMsg:
speed = db.feedbackMsg[1:3]
angle = db.feedbackMsg[2:6]
self.Cur_speed_label.setText(speed)
self.Cur_angle_label.setText(angle)
def onSave(self, event):
dataBase = DataBase()
try:
plant = Plants.getByDialog(self)
except:
return None
dataBase.addNew(plant)
self.Close()
self.GetParent().loadData()
def post(post_id):
db = get_db()
dbase = DataBase(db)
post_id = post_id[4:]
post = dbase.getPost(post_id)
author = dbase.getUser(post['author_id'])
author = author['username']
user = dbase.getUser(current_user.get_id())
return render_template('post.html', user=user, post=post, author=author)
def menu():
try:
db = DataBase()
db.ImportHistory()
except: print("Unable to import into database")
while True:
print("""
======== Funções ============
0. Sair
1.DataBase.AddSeq
2.DataBase Blast
3.DataBase.DBupgma
4.DataBase.DBmultiAlign
5.DataBase.FindInDB
6.DataBase.FreqInDB
7.DataBase history
8.DataBase.LoadFile
9.DataBase.RegexInDB
10.DataBase.SaveFile
11.DataBase.SearchDomains
12.DataBase.SimilarInDB
13.DataBase.SMFromFile
""")
op = input("Escolha uma opção: ")
if op == "1":
test_AddSeq()
elif op == "2":
test_BlastParse()
elif op == "3":
test_DBupgma()
elif op == "4":
test_DBmultiAlign()
elif op == "5":
test_FindInDB()
elif op == "6":
test_FreqInDB()
elif op == "7":
test_history()
elif op == "8":
test_LoadFile()
elif op == "9":
test_RegexInDB()
elif op == "10":
test_SaveFile()
elif op == "11":
test_SearchDomains()
elif op == "12":
test_SimilarInDB()
elif op == "13":
test_SMFromFile()
elif op == "0":
db.ExportHistory()
break
else:
print("\n Invalid option.")
def getData():
db = DataBase('../Sensors/DB/SensorData')
results = db.read_from_database('DS18B20', 5000, 'timeStamp') # limit to 96 data points for now
time = [row.timeStamp for row in results]
value = [row.value for row in results]
measurements = json.dumps({'time': time, 'values':value })
return measurements
def __init__(self):
"""
the constructor function of the MainServer object.
"""
self.server_socket = socket.socket()
self.database = DataBase()
self.online_users = {} # {socket:UserInfo object}
self.pending_users = {} # {socket:ip address}
self.mess_handler = MessagesFunctions()
self.running = True
def test_create_database_table(self):
file_path = 'DB/Dummy'
o = DataBase(file_path)
o.create_database_table('Dummy', timeStamp='REAL', value='REAL', units='TEXT')
# check if db has been created
os.path.isfile(file_path)
# check if table has been created
tables = o.list_all_tables_in_db()
self.assertEqual(tables, ['Dummy'])
def __init__(self):
self.host = 'localhost'
self.user = '******'
self.password = ''
self.database = 'neural_network'
self.base = DataBase(self.host, self.user, self.password,
self.database)
self.activation_function = lambda x: scipy.special.expit(x)
class DataLog:
""" Class that performs data logging of sensor data """
def __init__(self, sensor, measurementInterval, writeInterval):
self.sensor = sensor
self.timeStep = measurementInterval
self.writeInterval = writeInterval
self.measurements = []
self.table_name = sensor.sensorID
self.db = DataBase('DB/SensorData')
def recordSingleMeasurement(self):
measurement = self.sensor.getMeasurement()
return measurement
def appendMeasurement(self):
measurement = self.recordSingleMeasurement()
self.measurements.append(measurement)
return True
def clearMeasurements(self):
self.measurements = []
def run(self):
# todo: make it automatic what table columns are supposed to be there, derived from contents of measurement object?
self.db.create_database_table(self.table_name, timeStamp='REAL', value='REAL', units='TEXT')
timerDataAquisition = PeriodicTimer(self.timeStep, self.appendMeasurement)
timerWriteToFile = PeriodicTimer(self.writeInterval, self.writeMeasurementsToDatabase)
timerDataAquisition.start()
time.sleep(1)
timerWriteToFile.start()
try:
while True:
pass
finally:
self.writeMeasurementsToDatabase()
timerDataAquisition.cancel()
timerWriteToFile.cancel()
def writeMeasurementsToDatabase(self):
writeData = self.measurements
if not writeData:
pass
else:
self.clearMeasurements()
# Write data to db
self.db.write_array_of_measurements_to_database(writeData, self.table_name)
return True
def onDelete(self, event):
index = self.id
dataBase = DataBase()
message = wx.MessageDialog(self, u"确认删除本条信息么？", u"删除确认")
if message.ShowModal() == wx.ID_OK:
dataBase.delete(index)
self.Close()
if True == self.GetParent().isSearch:
self.GetParent().loadFindData()
else:
self.GetParent().loadData()
def get_granularity_rules(self, negative_rule_number):
# 1. get the sub train myDataSet from negative rules
self.granularity_database_array = [
DataBase() for x in range(negative_rule_number)
]
self.granularity_rule_Base_array = []
isTrain = True
# need to get below 4 values:
# self.train_myDataSet.getnInputs(), self.nLabels,
# self.train_myDataSet.getRanges(), self.train_myDataSet.getNames()
nVars = self.train_myDataSet.get_nvars()
nInputs = self.train_myDataSet.get_ninputs()
nominal_array = self.train_myDataSet.nominal_array
integer_array = self.train_myDataSet.integer_array
# from negative rule get small_disjunct_train array
self.extract_small_disjunct_train_array_step_one(
self.train_myDataSet, isTrain, nVars, nInputs, nominal_array,
integer_array)
# prepare granularity_database_array:
# from negative rule get small_disjunct_train array
for i in range(0, self.negative_rule_number):
# 2. for each sub train myDataSet, do self.granularity_data_base[i]= DataBase()
self.granularity_database_array[i] = DataBase()
self.granularity_database_array[i].init_with_three_parameters(
self.nLabels, self.my_dataset_train_sub_zone[i], i)
self.generate_granularity_rules()
self.prunerules_granularity_rules()
print("self.fileDB = " + str(self.fileDB))
print("self.fileRB = " + str(self.fileRB))
for i in range(0, self.negative_rule_number):
self.granularity_database_array[i].save_file(self.fileDB)
# Finally we should fill the training and test output files with granularity rule results
accTra = self.doOutput(self.val_myDataSet, self.outputTr)
accTst = self.doOutput(self.test_myDataSet, self.outputTst)
print(
"Accuracy for granularity rules obtained in training data is : " +
str(accTra))
print("Accuracy for granularity rules obtained in test data is: " +
str(accTst))
# print("Accuracy for normal rules obtained in test: " + str(accTst))
# self.nLabels = int(self.nLabels) + 1
# print(" self.nLabels after being added by one " + str(self.nLabels))
self.decide_more_granularity_or_not()
return self.granularity_rule_Base_array
def __init__(self):
super(AuthForm, self).__init__()
self.setupUi(self)
self.__keyboard_logic = KeyboardLogic()
self.__graphic_window = GraphicWindow()
self.password_line_edit.setEchoMode(QLineEdit.Password)
# Переопределение событий нажатия и отпускания клавиш
self.password_line_edit.keyPressEvent = self.keyPressEvent
self.password_line_edit.keyReleaseEvent = self.keyReleaseEvent
# Подключение сигналов к слотам
self.password_push_button.clicked.connect(
self.clicked_on_password_push_button)
self.actionMorning.triggered.connect(
self.get_entering_speed_password_stats)
self.actionDay.triggered.connect(
self.get_entering_speed_password_stats)
self.actionEvening.triggered.connect(
self.get_entering_speed_password_stats)
self.actionNight.triggered.connect(
self.get_entering_speed_password_stats)
self.actionEntering_password_dynamic.triggered.connect(
self.get_input_password_dynamic)
# self.actionInsert.triggered.connect(self.data_base_func)
self.actionOpen.triggered.connect(self.open_file)
self.actionSave.triggered.connect(self.save_file)
self.actionExit.triggered.connect(self.close)
self.registrationRadioButton.toggled.connect(self.prepare_user)
self.identificationRadioButton.toggled.connect(self.prepare_user)
# self.verificationRadioButton.toggled.connect(self.set_login_and_password_lines_enabled)
self.verificationRadioButton.toggled.connect(self.show_dialog)
self.registrationRadioButton.clicked.connect(self.prepare_user)
self.db_action_open.triggered.connect(self.open_db)
self.logoutPushButton.clicked.connect(self.logout)
self.password_info_label.setVisible(False)
self.password_info_label_2.setVisible(False)
self.__vector = list()
self.__err_msg = "" # сообщение об ошибке
self.__pressed_key = "" # нажатая клавиша
self.__released_key = "" # отпущенная клавиша
self.__pressed_key_time = 0 # время нажатия клавищи
self.__released_key_time = 0 # время отпускания клавиши
self.__chair_and_time_pairs = None
self.__needed_count = 10 # неоходимое кол-во раз ввода пароля для сбора статистики
self.__is_overlaid = False # Булевская переменная для детектирования наложения
self.__db = DataBase('test.db')
self.__user_id = -1
self.__input_id = -1
self.__is_access_granted = False
self.__mean_pressed_released_diff = dict()
self.username_line_edit.setDisabled(True)
self.password_line_edit.setDisabled(True)
self.__transfered_data = pyqtSignal(DataBase)
self.__db_form = None
def onSave(self, event):
index = self.id
dataBase = DataBase()
try:
plant = Plants.getByDialog(self)
except:
return None
dataBase.update(plant, index)
self.Close()
if True == self.GetParent().isSearch:
self.GetParent().loadFindData()
else:
self.GetParent().loadData()
def processData (self,productid,data,tablename):
#zcb_insu_process
#今天的日期 格式 yyyy-mm-dd
startTime = str(time.strftime("%Y-%m-%d", time.localtime()))
endTime = str(datetime.date.today() + datetime.timedelta(days=1))
currentTime_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
db = DataBase()
query_sql = "select * from "+tablename+" where productId = '"+productid+"' and createDate < '"+endTime+"' and createDate>='"+startTime+"'"
#print "query_sql-->"+query_sql
query_result = db.execute(query_sql);
data["updateDate"] = currentTime_str
if len(query_result)<1:
#当天没有数据，执行插入
#zcb_insu_process
data["createDate"] = currentTime_str
sql = db.parseInsert(tablename, data)
#print "processData-->insert sql==>"+sql
else :
id = query_result[0]["id"]
#print id
sql = db.parseUpdate(tablename, data, "id='"+str(id)+"'")
#print "processData-->update sql==>"+sql
db.execute(sql);
db.dataBaseClose()
def __init__(self, sensor, measurementInterval, writeInterval):
self.sensor = sensor
self.timeStep = measurementInterval
self.writeInterval = writeInterval
self.measurements = []
self.table_name = sensor.sensorID
self.db = DataBase('DB/SensorData')
def synzcb(self,startDate):
DB = DataBase()
t_startDate = time.strptime(startDate, "%Y-%m-%d")
d_startDate = datetime.datetime(* t_startDate[:6])
d_endDate = d_startDate + datetime.timedelta(days=7)
s = str(time.strftime("%Y%m%d", t_startDate))
e=datetime.datetime.strftime((d_endDate - datetime.timedelta(days=1)), '%Y%m%d')
for tableName in self.tablenames:
tn = tableName+"_"+s+"_"+e
print tableName
sql = '''
create table '''+tn+'''
select productid,max(ifnull(bishu,0))as bishu,max(ifnull(wanchenglv ,0))as wanchenglv from (
select * from '''+tableName+"_process"+''' where state = '已售罄' and createDate >= \''''+str(d_startDate)+'''\' and createDate < \''''+str(d_endDate)+'''\'
)a group by productid'''
sql_index='''alter table '''+tn+''' add index index_'''+tn+''' (`productid`)
'''
#sqw_engine = "ALTER "+tableName+" engineTest ENGINE = 'INNODB'"
print sql
DB.execute(sql)
DB.execute(sql_index)
DB.dataBaseClose()
def synzcb_loan (self,startDate):
today = PageUtils.getCurrentDate()
startDate = PageUtils.delayed(today,-1)
startDate2 = PageUtils.delayed(today,-2)
DB = DataBase()
sql = '''
insert into zcb_loan_state (productid)
select productid from zcb_loan where productid in (
select productid from zcb_loan_process where createDate >= \''''+str(startDate2)+'''\' and createDate < \''''+str(startDate)+'''\' and productid not in
(select productid from zcb_loan_process where createDate >= \''''+str(startDate)+'''\' and createDate < \''''+str(today)+'''\'))
and state != '已下架' and state !='已售罄'
'''
print sql
print "exe row -->",DB.execute(sql)
#DB.createLog({"type":"loan_synState","msg":"个人企业贷同步"+str(startDate)+"的数据成功"})#
DB.dataBaseClose()
return
def getProductId (self):
database_obj = DataBase()
# sql = "select id,productid from "+tableName +" WHERE state != '已下架' and state !='已售罄' order by updateDate asc limit 0 ,5000 "
# sql = "select id,productid from "+tableName +" WHERE (state != '已下架' and state !='已售罄') or state is null order by updateDate desc limit 0 ,10000 "
pagesize = 20
'''if self.g_index%2 == 0:
pageno = (self.g_index - self.g_index/2)*pagesize
else:
pageno = (self.g_index - 1 - self.g_index/2)*pagesize
print pageno
'''
pageno = self.g_index * pagesize
# createDate desc,updateDate asc,
# sql = "select id,productid from zcb_loan WHERE (state != '已下架' and state !='已售罄') or state is null order by updateNum asc limit "+str(pageno)+" , "+str(pagesize)
# sql = "select id,productid from "+tableName +" WHERE length(productName)=0 or productName is null limit 0 , 111000"
sql = "select id,productid from zcb_loan_state where isdel = 0 limit " + str(pageno) + " , " + str(pagesize)
print "getProductId sql -->",sql
lists = database_obj.execute(sql)
database_obj.dataBaseClose()
return lists
def getProductId (self,tablenameKey):
tableName = self.DBtableNames[tablenameKey]
print tableName
database_obj = DataBase()
#sql = "select id,productid from "+tableName +" WHERE state != '已下架' and state !='已售罄' order by updateDate asc limit 0 ,5000 "
#sql = "select id,productid from "+tableName +" WHERE (state != '已下架' and state !='已售罄') or state is null order by updateDate desc limit 0 ,10000 "
pagesize = 20
'''if self.g_index%2 == 0:
pageno = (self.g_index - self.g_index/2)*pagesize
else:
pageno = (self.g_index - 1 - self.g_index/2)*pagesize
print pageno
'''
pageno = self.g_index*pagesize
#createDate desc,updateDate asc,
sql = "select id,productid from "+tableName +" WHERE (state != '已下架' and state !='已售罄') or state is null order by updateNum asc limit "+str(pageno)+" , "+str(pagesize)
#sql = "select id,productid from "+tableName +" WHERE length(productName)=0 or productName is null limit 0 , 111000"
print sql
lists = database_obj.execute(sql)
database_obj.dataBaseClose()
return lists
def synMainData(self, productid, tablename, data):
query_sql = "select * from " + tablename + " where productId = '" + productid + "'"
# print "synMainData query_sql-->"+query_sql
db = DataBase()
query_result = db.execute(query_sql)
# currentTime_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
currentTime_str = PageUtils.getCurrentTime()
if len(query_result) < 1: # insert data
data["createDate"] = currentTime_str
data["updateDate"] = currentTime_str
insert_sql = db.parseInsert(tablename, data)
db.execute(insert_sql)
db.dataBaseClose()
def __init__(self, master=None):
self.db = DataBase("DataBase.xml")
self.master = master
self.NB = ttk.Notebook(self.master)
self.mainFrame = Frame(self.master)
self.sqlInputEntry = Entry(self.mainFrame, relief=SUNKEN)
self.sqlInputEntry.bind('<Return>', self.startQuery)
self.resetButton = Button(self.mainFrame, text='reset', command=self.reset)
self.startButton = Button(self.mainFrame, text='start', command=self.startQuery)
self.resultWindow = Frame(self.mainFrame, relief=RIDGE, borderwidth=2)
self.DBtableCanvas = {} # store the db table cnavas
self.DBtableFrame = {}
def processData(self, productid, data, tablename):
# zcb_insu_process
# 今天的日期 格式 yyyy-mm-dd
startData = PageUtils.getCurrentDate()
endData = PageUtils.delayed(startData, 1)
currentTime_str = PageUtils.getCurrentTime() # time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
db = DataBase()
query_sql = (
"select * from "
+ tablename
+ " where productId = '"
+ productid
+ "' and createDate < '"
+ endData
+ "' and createDate>='"
+ startData
+ "'"
)
# print "processData query_sql-->"+query_sql
query_result = db.execute(query_sql)
data["updateDate"] = currentTime_str
if len(query_result) < 1:
# 当天没有数据，执行插入
# zcb_insu_process
data["createDate"] = currentTime_str
sql = db.parseInsert(tablename, data)
# print "processData-->insert sql==>"+sql
else:
id = query_result[0]["id"]
# print id
sql = db.parseUpdate(tablename, data, "id='" + str(id) + "'")
# print "processData-->update sql==>"+sql
db.execute(sql)
db.dataBaseClose()
def test_write_measurement_to_database(self):
o = DataBase('DB/Dummy')
data_point = Measurement(value=20, timeStamp=time.time(), units='degC')
o.write_measurement_to_database(data_point, 'Dummy')
class sqlApp:
'''
responsible for the control of interface
'''
def __init__(self, master=None):
self.db = DataBase("DataBase.xml")
self.master = master
self.NB = ttk.Notebook(self.master)
self.mainFrame = Frame(self.master)
self.sqlInputEntry = Entry(self.mainFrame, relief=SUNKEN)
self.sqlInputEntry.bind('<Return>', self.startQuery)
self.resetButton = Button(self.mainFrame, text='reset', command=self.reset)
self.startButton = Button(self.mainFrame, text='start', command=self.startQuery)
self.resultWindow = Frame(self.mainFrame, relief=RIDGE, borderwidth=2)
self.DBtableCanvas = {} # store the db table cnavas
self.DBtableFrame = {}
def reset(self):
self.sqlInputEntry.delete(0, END)
self.resultWindow.destroy()
self.resultWindow = Frame(self.mainFrame, relief=RIDGE, borderwidth=2)
self.resultWindow.place(x=80, y=80, width=700, height=500)
def startQuery(self, event=None):
analyzer =sqlAnalyzer( self.sqlInputEntry.get() )
analyzer.startAnalyze()
parser = sqlParser(self.db, analyzer.getAttribList(), analyzer.getTableList(), analyzer.getConditionList() )
parser.startParse()
resultCanvas = scrolledCanvas(self.resultWindow, 700, 500)
resultCanvas.populate(parser.getResult())
def display(self):
self.mainFrame.place(width=860, height=640)
self.sqlInputEntry.place(x=90, y=30, width=660, height=25)
self.resetButton.place(x=760, y=30, width=45, height=25)
self.startButton.place(x=815, y=30, width=40, height=25)
self.resultWindow.place(x=80, y=80, width=700, height=500)
self.NB.add(child=self.mainFrame, text='SQL query') #!!!!! very important you must call place() before add them
table = self.db.getTable()
for tableName in table.keys():
self.DBtableFrame[tableName] = Frame(self.master)
self.DBtableCanvas[tableName]=scrolledCanvas( self.DBtableFrame[tableName], 855, 615)
self.DBtableCanvas[tableName].populate(table[tableName])
self.NB.add(child=self.DBtableFrame[tableName], text=tableName)
self.NB.place(width=860, height=640)
def updateData(self, data):
_main = data.get("m")
_slaves = data.get("s")
main = {}
slave = {}
print _main
print _slaves
db = DataBase()
today = self.pageUtils.getCurrentDate()
tomorrow = self.pageUtils.delayed(today, -1)
# 取昨天的数据
sql = "select * from zcb_report_master where date(createdate)='" + str(tomorrow) + "'"
tomorrow_main = db.execute(sql)
if len(tomorrow_main) > 0:
tomorrow_main = tomorrow_main[0]
print "sql==>", sql
for _d in _main:
print "-->", _main.get(_d), "==", tomorrow_main.get(_d)
if _d == "yycjjebl":
main[_d] = _main.get(_d)
else:
if tomorrow_main.get(_d) == None or _main.get(_d) > tomorrow_main.get(_d):
main[_d] = _main.get(_d)
# 今天是否有数据
sql = "select * from zcb_report_master where date(createdate)='" + str(today) + "'"
today_main = db.execute(sql)
if len(today_main) < 1:
# 主表数据插入
sql = db.parseInsert("zcb_report_master", main)
print "insert master sql -->", sql
else:
today_main = today_main[0]
sql = db.parseUpdate("zcb_report_master", main, " id = " + str(today_main.get("id")))
print "update master sql -->", sql
db.execute(sql)
else:
sql = "select * from zcb_report_master where date(createdate)='" + str(today) + "'"
today_main = db.execute(sql)
if len(today_main) < 1:
sql = db.parseInsert("zcb_report_master", _main)
print "insert master sql -->", sql
db.execute(sql)
else:
today_main = today_main[0]
sql = db.parseUpdate("zcb_report_master", _main, "id=" + str(today_main.get("id")))
print "update master sql -->", sql
db.execute(sql)
# 处理从表数据
for _slave in _slaves:
sql = (
"select * from zcb_report_slave where date(createdate)='"
+ str(tomorrow)
+ "' and type='"
+ _slave.get("type")
+ "' and tzqx = '"
+ _slave.get("tzqx")
+ "'"
)
print sql
tomorrow_slave = db.execute(sql)
if len(tomorrow_slave) > 0:
tomorrow_slave = tomorrow_slave[0]
slave = {}
slave["type"] = _slave.get("type")
slave["tzqx"] = _slave.get("tzqx")
for _d in _slave:
print "-->", _slave.get(_d), "==", tomorrow_slave.get(_d)
if tomorrow_slave.get(_d) == None or _slave.get(_d) != tomorrow_slave.get(_d):
print _d, "==>", _slave.get(_d), "!!!!!", tomorrow_slave.get(_d)
slave[_d] = _slave.get(_d)
if len(slave) < 1:
continue
print "slave-->", slave
sql = (
"select * from zcb_report_slave where date(createdate)='"
+ str(today)
+ "' and type='"
+ _slave.get("type")
+ "' and tzqx = '"
+ _slave.get("tzqx")
+ "'"
)
today_slave = db.execute(sql)
if len(today_slave) < 1:
# insert
sql = db.parseInsert("zcb_report_slave", slave)
print "insert slave sql -->", sql
else:
# update
today_slave = today_slave[0]
sql = db.parseUpdate("zcb_report_slave", slave, " id = " + str(today_slave.get("id")))
print "update slave sql -->", sql
db.execute(sql)
else:
sql = (
"select * from zcb_report_slave where date(createdate)='"
+ str(today)
+ "' and type='"
+ _slave.get("type")
+ "' and tzqx = '"
+ _slave.get("tzqx")
+ "'"
)
today_slave = db.execute(sql)
if len(today_slave) < 1:
sql = db.parseInsert("zcb_report_slave", _slave)
print "insert slave sql -->", sql
db.execute(sql)
else:
today_slave = today_slave[0]
sql = db.parseUpdate("zcb_report_slave", _slave, "id = " + str(today_slave.get("id")))
print "update slave sql -->", sql
db.execute(sql)
__author__ = 'DRIA'
from unittest import TestCase
import os.path
from DataBase import DataBase
import json
from measurement import Measurement
import time
db = DataBase('DB/SensorData')
table_list = db.list_all_tables_in_db
results = db.read_from_database('DS18B20')
measurements = json.dumps(results, sort_keys=True, indent=4, separators=(',', ': '))
# The database class. Used to escape some sensitive information.
from DataBase import DataBase
# The authorization class to get the password hash for the admin user
from User.Authorize import Authorize
# Create objects from the configuration and database classes
config = Config('../config/config.dat')
# Parse the config file.
if(config.parseConfigurationFile()) :
# The config file was successfully parsed.
db = DataBase() # Create the db object
siteInfo = config.getConfigurationDict() # Get the site variables
databaseInfo = config.getDatabaseConfigurationDict() # get the db variables
securityInfo = config.getSecurityConfigurationDict() # get the security variables
siteInfo.update(databaseInfo) # Add all the dicts into one master
siteInfo.update(securityInfo)
# Set the admin password
auth = Authorize()
auth.setPassPhrase(securityInfo['passwordSecurityHash'])
siteInfo['adminPassword'] = auth.getHash(securityInfo['administratorPassword'])
# read in the create.sql template.
sqlCreateTemplate = Template(filename='create.sql.template',lookup=templateLookup)
# Escape the passwords to avoid any potentially embarassing sql
def __init__(self):
DataBase.__init__(self)
self.log = logging.getLogger(__name__)
def test_read_from_database(self):
# check if the measurement has been written to the db correctly
o = DataBase('DB/Dummy')
results = o.read_from_database('Dummy', 3, 'timeStamp')
self.assertTrue(len(results) > 0)
self.assertEqual(len(results), 3)
