def getCurrencyToScrapXidFromUSD(self):
# Get a company that never has been updated or is NULL
query = "SELECT * from currencies WHERE xidFromUSD IS NULL ORDER BY RAND() LIMIT 1"
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result[0]
def saveOptSVM(self, companyId, svm, kernel, rate, numberOfDaysSample,
numberOfTrainVectors):
query = "INSERT INTO companiesSVM (company_id, svm, kernel, rate, number_of_days_sample, number_of_train_vectors, created_at, updated_at) VALUES ('%s','%s','%s','%s','%s','%s', NOW(), NOW()) ON DUPLICATE KEY UPDATE company_id='%s', svm='%s', kernel='%s', rate='%s', number_of_days_sample='%s', number_of_train_vectors='%s'" % (
companyId, svm, kernel, rate, numberOfDaysSample,
numberOfTrainVectors, companyId, svm, kernel, rate,
numberOfDaysSample, numberOfTrainVectors)
Database().runQuery(query)
def saveOptSVCR(self, companyId, max, min):
query = "INSERT INTO companiesSVCR (company_id, max_kernel, max_rate, max_repeats, max_samples, max_train_vectors, min_kernel, min_rate, min_repeats, min_samples, min_train_vectors, created_at, updated_at) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s', NOW(), NOW()) ON DUPLICATE KEY UPDATE max_rate = IF(VALUES(max_rate) > max_rate, VALUES(max_rate), max_rate), max_kernel = IF(VALUES(max_rate) > max_rate, VALUES(max_kernel), max_kernel), max_repeats = IF(VALUES(max_rate) > max_rate, VALUES(max_repeats), max_repeats), max_samples = IF(VALUES(max_rate) > max_rate, VALUES(max_samples), max_samples), max_train_vectors = IF(VALUES(max_rate) > max_rate, VALUES(max_train_vectors), max_train_vectors), min_rate = IF(VALUES(min_rate) < min_rate, VALUES(min_rate), min_rate), min_kernel = IF(VALUES(min_rate) < min_rate, VALUES(min_kernel), min_kernel), min_repeats = IF(VALUES(min_rate) < min_rate, VALUES(min_repeats), min_repeats), min_samples = IF(VALUES(min_rate) < min_rate, VALUES(min_samples), min_samples), min_train_vectors = IF(VALUES(min_rate) < min_rate, VALUES(min_train_vectors), min_train_vectors)" % (
companyId, max["kernel"], max["rate"], max["repeats"],
max["numberOfDaysSample"], max["numberOfTrainVectors"],
min["kernel"], min["rate"], min["repeats"],
min["numberOfDaysSample"], min["numberOfTrainVectors"])
Database().runQuery(query)
def updateCompanyCurrency(self, company_id, currency):
query = "UPDATE companies SET currency = %s WHERE id = %s" % (
xid, company_id)
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def getHistory(self, company_id, limit):
# Get company history in USD
query = "SELECT * from (SELECT histories.open as conversion FROM histories left join currencies on currencies.symbol = histories.currency WHERE company_id = '%s' ORDER BY histories.date ASC LIMIT %s) as query where conversion is not null" % (
company_id, limit)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result
def getCompaniesBySymbolLike(self, symbolLike):
# Get company history in USD
query = "SELECT id FROM companies WHERE symbol LIKE '%s'" % (
symbolLike)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result
def getHistoryInUSD(self, company_id, limit):
# Get company history in USD
query = "SELECT * from (SELECT ROUND(IF(currency = 'USD', histories.open, histories.open * currencyHistoryToUSD.price),3) as conversion FROM histories left join currencies on currencies.symbol = histories.currency left JOIN currencyHistoryToUSD ON (currencies.id = currencyHistoryToUSD.currency_id AND currencyHistoryToUSD.date = histories.date) WHERE company_id = '%s' ORDER BY histories.date ASC LIMIT %s) as query where conversion is not null" % (
company_id, limit)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result
def updateCurrencyXidFromUSD(self, currency_id, xid):
query = "UPDATE currencies SET xidFromUSD = %s WHERE id = %s" % (
xid, currency_id)
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def getCompanyToOptSVR(self, currency):
if type(currency) is not None:
currencyFilter = " AND currency = '" + currency + "'"
query = "SELECT companies.id FROM companies LEFT JOIN companiesSVR on companiesSVR.company_id = companies.id WHERE (companiesSVR.company_id IS NULL %s) ORDER BY RAND() LIMIT 1" % (
currencyFilter)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result[0]
def getIfCompanyProcessed(self, company, svm, kernel, numberOfDaysSample,
numberOfTrainVectors):
# Get company history in USD
query = "SELECT id FROM companiesSVM WHERE company_id = '%s' AND svm = '%s' AND kernel = '%s' AND number_of_days_sample = '%s' AND number_of_train_vectors = '%s' " % (
company, svm, kernel, numberOfDaysSample, numberOfTrainVectors)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return True
def updateCompanyXidAndCurrency(self, company_id, values):
if (not values or not values["xid"] or not values["currency"]):
return
query = "UPDATE companies SET xid = %s, currency= '%s' WHERE id = %s" % (
values["xid"], values["currency"], company_id)
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def getSectorToScrap(self):
# Get a sector that never has been updated or is NULL
query = "SELECT * from sectors WHERE (last_full_update IS NULL OR last_full_update < NOW() - INTERVAL " + str(
Settings.maxTimeForUpdateDB) + " HOUR) ORDER BY RAND() LIMIT 1"
update = {
"table": "sectors",
"column": "last_full_update"
} #Block the column for not being update in multithread cases
result = Database().runQuery(query, update)
if not result or not result[0]:
return False
return result[0]
def saveIndustries(self, industries):
valuesQuery = []
for industry in industries:
valuesQuery.append(
"('%s', '%s', '%s', NOW(), NOW())" %
(industry["industry_name"], industry["industry_slug"],
industry["sector_id"]))
if not valuesQuery:
return
queryValues = ",".join(str(item) for item in valuesQuery)
query = "INSERT INTO industries (name, slug, sector_id, created_at, updated_at) VALUES %s ON DUPLICATE KEY UPDATE name=VALUES(name), slug=VALUES(slug), sector_id=VALUES(sector_id), updated_at=NOW()" % queryValues
Database().runQuery(query)
def saveSectors(self, sectors):
valuesQuery = []
for sector in sectors:
valuesQuery.append(
"('%s', '%s', '%s', '%s', NOW(), NOW())" %
(sector["sector_name"], sector["sector_slug"],
sector["sector_industries"], sector["sector_companies"]))
if not valuesQuery:
return
queryValues = ",".join(str(item) for item in valuesQuery)
query = "INSERT INTO sectors (name, slug, industries, companies, created_at, updated_at) VALUES %s ON DUPLICATE KEY UPDATE name=VALUES(name), slug=VALUES(slug), industries=VALUES(industries), companies=VALUES(companies), updated_at=NOW()" % queryValues
Database().runQuery(query)
def getCompaniesByCurrency(self, currencySymbols):
# Get company history in USD
inQuery = ""
if type(currencySymbols) is list:
for i in range(0, len(currencySymbols)):
inQuery += "'" + currencySymbols[i] + "',"
inQuery = "IN (" + inQuery[:-1] + ")"
else:
inQuery = "IN ('" + currencySymbols + "')"
query = "SELECT id FROM companies WHERE currency %s" % (inQuery)
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result
def saveCurrencyHistory(self, histories):
valuesQuery = []
for history in histories:
valuesQuery.append(
"('%s', '%s', '%s', NOW(), NOW())" %
(history["currency_id"], history["date"], history["price"]))
if not valuesQuery:
return
queryValues = ",".join(str(item) for item in valuesQuery)
query = "INSERT INTO currencyHistoryToUSD (currency_id, date, price, created_at, updated_at) VALUES %s ON DUPLICATE KEY UPDATE currency_id=VALUES(currency_id), date=VALUES(date), price=VALUES(price), updated_at=NOW()" % queryValues
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def saveCurrencies(self, currencies):
valuesQuery = []
for currency in currencies:
valuesQuery.append(
"('%s', '%s', NOW(), NOW())" %
(currency["name"].replace("'", "\\'"), currency["symbol"]))
if not valuesQuery:
return
queryValues = ",".join(str(item) for item in valuesQuery)
query = "INSERT INTO currencies (name, symbol, created_at, updated_at) VALUES %s ON DUPLICATE KEY UPDATE symbol=VALUES(symbol), name=VALUES(name), updated_at=NOW()" % queryValues
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def getCompanyToScrap(self, currency=None):
# Get a company that never has been updated or is NULL
filterByCurrency = "" #Filter by currency?
if type(currency) is not None:
filterByCurrency = "AND currency='" + currency + "'"
query = "SELECT * from companies WHERE (last_full_update IS NULL OR last_full_update < NOW() - INTERVAL " + str(
Settings.maxTimeForUpdateDB
) + " HOUR) %s ORDER BY RAND() LIMIT 1" % (filterByCurrency)
update = {
"table": "companies",
"column": "last_full_update"
} #Block the column for not being update in multithread cases
result = Database().runQuery(query, update)
if not result or not result[0]:
return False
return result[0]
def saveHistory(self, histories):
valuesQuery = []
for history in histories:
valuesQuery.append(
"('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', NOW(), NOW())"
% (history["company_id"], history["currency"], history["date"],
history["open"], history["high"], history["low"],
history["close"], history["volume"]))
if not valuesQuery:
return
queryValues = ",".join(str(item) for item in valuesQuery)
query = "INSERT INTO histories (company_id, currency, date, open, high, low, close, volume, created_at, updated_at) VALUES %s ON DUPLICATE KEY UPDATE company_id=VALUES(company_id), currency=VALUES(currency), date=VALUES(date), open=VALUES(open), high=VALUES(high), close=VALUES(close), volume=VALUES(volume), updated_at=NOW()" % queryValues
try:
Database().runQuery(query)
except MySQLError:
#If lock error don't do nothing
print("========Thread doing lock violantion========")
def getCompanyToOptPendingSVM(self):
query = "SELECT company_id FROM (SELECT count(*) as count, company_id, MAX(updated_at) as updated_at FROM companiesSVM GROUP BY company_id) as t WHERE t.count < 300 AND updated_at < NOW() - INTERVAL 15 MINUTE ORDER BY RAND() LIMIT 1"
result = Database().runQuery(query)
if not result or not result[0]:
return False
return result[0]