def getDeveloperId(dbHandle, app_dict):
cursor = dbHandle.cursor()
dev_name = app_dict['developer_name']
dev_name = conversion.MySQLConverter().escape(dev_name)
if 'dev_website' in app_dict:
dev_web = app_dict['dev_website']
else:
dev_web = ""
dev_web = conversion.MySQLConverter().escape(dev_web)
if 'dev_email' in app_dict:
dev_email = app_dict['dev_email']
else:
dev_email = ""
dev_email = conversion.MySQLConverter().escape(dev_email)
if 'dev_location' in app_dict:
dev_loc = app_dict['dev_location']
else:
dev_loc = ""
dev_loc = conversion.MySQLConverter().escape(dev_loc)
sqlStatementdDevId = "SELECT `id` FROM `developer` WHERE `name` = '" + dev_name + "';"
try:
cursor.execute(sqlStatementdDevId)
data = cursor.fetchone()
if data is not None:
return data[0]
else:
#If the developer id was not found we will not execute the while loop and execute the following code
sqlStatementdDevIdInsert = "INSERT into `developer`(`name`,`website`,`email`,`country`) VALUES('" + dev_name + "','" + dev_web + "','" + dev_email + "','" + dev_loc + "');"
print sqlStatementdDevIdInsert
return databaseHandler.dbManipulateData(dbHandle,
sqlStatementdDevIdInsert)
except:
print "Unexpected error:", sys.exc_info()[0]
raise
def getDeveloperId(dbHandle, app_dict):
cursor = dbHandle.cursor()
dev_name = app_dict['developer_name']
dev_name = conversion.MySQLConverter().escape(dev_name)
if 'dev_website' in app_dict:
dev_web = app_dict['dev_website']
else:
dev_web = ""
dev_web = conversion.MySQLConverter().escape(dev_web)
if 'dev_email' in app_dict:
dev_email = app_dict['dev_email']
else:
dev_email = ""
dev_email = conversion.MySQLConverter().escape(dev_email)
if 'dev_location' in app_dict:
dev_loc = app_dict['dev_location']
else:
dev_loc = ""
dev_loc = conversion.MySQLConverter().escape(dev_loc)
sqlStatementdDevId = "SELECT `id` FROM `developer` WHERE `name` = '" + dev_name + "';"
try:
cursor.execute(sqlStatementdDevId)
if cursor.rowcount > 0:
queryOutput = cursor.fetchall()
for row in queryOutput:
return row[0]
else:
sqlStatementdDevIdInsert = "INSERT into `developer`(`name`,`website`,`email`,`country`) VALUES('" + dev_name + "','" + dev_web + "','" + dev_email + "','" + dev_loc + "');"
return databaseHandler.dbManipulateData(dbHandle,
sqlStatementdDevIdInsert)
except:
print "Unexpected error:", sys.exc_info()[0]
raise
def convert_search_to_query(phrase: str) -> str:
# Use MySQL Library For Escaping Search Text
sql_converter: conversion.MySQLConverter = conversion.MySQLConverter()
phrase = sql_converter.escape(value=phrase)
phrase = phrase.replace(' ', '%')
phrase = '%' + phrase + '%'
return phrase
def setStreamJSON(repo: Dolt, table: str, tweet_id: str, data: dict):
sql_converter: conversion.MySQLConverter = conversion.MySQLConverter()
escaped_json: str = sql_converter.escape(value=json.dumps(data))
tweets: Table = Table(table)
query: QueryBuilder = Query.update(tweets) \
.set(tweets.stream_json, escaped_json) \
.where(tweets.id == tweet_id)
repo.sql(query=query.get_sql(quote_char=None), result_format="csv")
def addMediaFiles(repo: Dolt, table: str, tweet_id: str, data: List[str]):
sql_converter: conversion.MySQLConverter = conversion.MySQLConverter()
escaped_json: str = sql_converter.escape(value=json.dumps(data))
media: Table = Table(table)
query: QueryBuilder = Query.into(media) \
.insert(tweet_id, escaped_json)
# query: QueryBuilder = Query.update(media) \
# .set(media.file, escaped_json) \
# .where(media.id == tweet_id)
repo.sql(query=query.get_sql(quote_char=None), result_format="csv")
def get_pages_for_word(self, word):
sql = '''SELECT pages.*, words.id, indexes.score
FROM words
JOIN indexes on (words.id = indexes.word_id)
JOIN pages ON (indexes.page_id = pages.id)
WHERE words.text = "%s"
ORDER BY indexes.score DESC''' % conversion.MySQLConverter(
).escape(str(word))
self.cursor.execute(sql)
result = self.cursor.fetchall()
if len(result) > 0:
self.word_ids[word] = result[0][-2]
return result
def setUp(self):
self.cnv = conversion.MySQLConverter()
def test_converter(self):
for i in range(0, self.sample_size):
conv = conversion.MySQLConverter()
self.samples[i] = len(gc.get_objects())
self._assert_flat_line(self.samples)
def mysql_select(current_skyline_app, select):
"""
Select data from mysql database
:param current_skyline_app: the Skyline app that is calling the function
:param select: the select string
:type select: str
:return: tuple
:rtype: tuple, boolean
- **Example usage**::
from skyline_functions import mysql_select
query = 'select id, metric from anomalies'
result = mysql_select(query)
- **Example of the 0 indexed results tuple, which can hold multiple results**::
>> print('results: %s' % str(results))
results: [(1, u'test1'), (2, u'test2')]
>> print('results[0]: %s' % str(results[0]))
results[0]: (1, u'test1')
.. note::
- If the MySQL query fails a boolean will be returned not a tuple
* ``False``
* ``None``
"""
ENABLE_DEBUG = False
try:
if settings.ENABLE_PANORAMA_DEBUG:
ENABLE_DEBUG = True
except:
nothing_to_do = True
try:
if settings.ENABLE_WEBAPP_DEBUG:
ENABLE_DEBUG = True
except:
nothing_to_do = True
try:
if settings.ENABLE_IONOSPHERE_DEBUG:
ENABLE_DEBUG = True
except:
nothing_to_do = True
current_skyline_app_logger = str(current_skyline_app) + 'Log'
current_logger = logging.getLogger(current_skyline_app_logger)
if ENABLE_DEBUG:
current_logger.info('debug :: entering mysql_select')
try:
mysql.connector
except:
import mysql.connector
try:
conversion
except:
from mysql.connector import conversion
try:
re
except:
import re
try:
cnx = mysql.connector.connect(**config)
if ENABLE_DEBUG:
current_logger.info('debug :: connected to mysql')
except mysql.connector.Error as err:
current_logger.error('error :: mysql error - %s' % str(err))
current_logger.error('error :: failed to connect to mysql')
return False
if cnx:
try:
if ENABLE_DEBUG:
current_logger.info('debug :: %s' % (str(select)))
# NOTE: when a LIKE SELECT is made the query string is not run
# through the conversion.MySQLConverter().escape method as it
# it would not work and kept on returning mysql error - 1064 with
# multiple single quotes e.g. use near '\'carbon%\'' at line 1
# Various patterns were attempted to no avail, it seems to be
# related to % character. pseudo basic HTTP auth has been added to
# the webapp just in someone does not secure it properly, a little
# defence in depth, so added WEBAPP_ALLOWED_IPS too.
pattern_match = None
try:
pattern_match = re.search(' LIKE ', str(select))
except:
current_logger.error('error :: pattern_match - %s' % traceback.format_exc())
if not pattern_match:
try:
pattern_match = re.search(' like ', str(select))
except:
current_logger.error('error :: pattern_match - %s' % traceback.format_exc())
# TBD - not sure how to get it escaping safely
pattern_match = True
if pattern_match:
query = str(select)
if ENABLE_DEBUG:
current_logger.info('debug :: unescaped query - %s' % (str(query)))
cursor = cnx.cursor()
cursor.execute(query)
else:
query = conversion.MySQLConverter().escape(select)
if ENABLE_DEBUG:
current_logger.info('debug :: escaped query - %s' % (str(query)))
cursor = cnx.cursor()
cursor.execute(query.format(query))
# cursor = cnx.cursor()
# cursor.execute(query)
result = cursor.fetchall()
cursor.close()
cnx.close()
return result
except mysql.connector.Error as err:
current_logger.error('error :: mysql error - %s' % str(err))
current_logger.error(
'error :: failed to query database - %s' % (str(select)))
try:
cnx.close()
return False
except:
return False
else:
if ENABLE_DEBUG:
current_logger.error('error - failed to connect to mysql')
# Close the test mysql connection
try:
cnx.close()
return False
except:
return False
return False
def createSQLStatementAndInsert(dbHandle, app_dict):
if 'app_name' in app_dict:
app_name = app_dict['app_name']
app_name = conversion.MySQLConverter().escape(app_name)
#print app_name
app_pkg_name = app_dict['app_pkg_name']
developer_id = getDeveloperId(dbHandle, app_dict)
app_category_id = getCategoryId(dbHandle, app_dict)
if 'review_rating' in app_dict:
review_rating = app_dict['review_rating']
else:
review_rating = 0.0
if 'review_count' in app_dict:
review_count = app_dict['review_count']
else:
review_count = 0
if 'app_desc' in app_dict:
app_desc = app_dict['app_desc']
else:
app_desc = ''
escaped_text_desc = conversion.MySQLConverter().escape(app_desc)
if 'whats_new' in app_dict:
whats_new = app_dict['whats_new']
else:
whats_new = ''
escaped_text_whats_new = conversion.MySQLConverter().escape(whats_new)
if 'Updated' in app_dict:
updated = app_dict['Updated']
else:
updated = '1984-08-31'
if 'Installs' in app_dict:
installs = app_dict['Installs']
else:
installs = 0
if 'Current_Version' in app_dict:
version = app_dict['Current_Version']
else:
version = ''
if 'Requires_Android' in app_dict:
android_reqd = app_dict['Requires_Android']
else:
android_reqd = ''
if 'Content_Rating' in app_dict:
content_rating = app_dict['Content_Rating']
else:
content_rating = ''
sqlStatement = "INSERT INTO `appdata`(`app_pkg_name`,`app_name`,`developer_id`,`app_category_id`,`review_rating`,`review_count`,`desc`,`whats_new`,`updated`,`installs`,`version`,`android_reqd`,`content_rating`) VALUES('" + app_pkg_name + "','" + app_name + "'," + str(
developer_id
) + "," + str(app_category_id) + "," + str(review_rating) + "," + str(
review_count
) + ",'" + escaped_text_desc + "','" + escaped_text_whats_new + "','" + updated + "'," + str(
installs
) + ",'" + version + "','" + android_reqd + "','" + content_rating + "');"
print sqlStatement
databaseHandler.dbManipulateData(dbHandle, sqlStatement)
