def create_movies(self):
sql = """
CREATE TABLE if not exists `library_movies` (
`MovieID` int(11) NOT NULL,
`MovieTitle` varchar(255) NOT NULL,
`MovieGenres` varchar(255) NOT NULL,
PRIMARY KEY (`MovieID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
self.con.excute(sql)
sql = "SET foreign_key_checks = 0"
self.con.excute(sql)
sql = "TRUNCATE table library_movies"
self.con.excute(sql)
sql = "SET foreign_key_checks = 1"
self.con.excute(sql)
movies = pd.read_csv(r'movies.csv', sep=",", engine='python')
movies.columns = ['movieId', 'title', 'genres']
for index, row in movies.iterrows():
sql = "INSERT INTO `library_movies` (`MovieID`, `MovieTitle`, `MovieGenres`) values (%s, '%s', '%s')" % (
row['movieId'], escape_string(
row['title']), escape_string(row['genres']))
print(sql)
self.con.excute(sql)
def cache_tag(self, tag: str, lang: str, lang_id: str, tag_type: str):
'''
Cache tag info.
'''
tid = str(uuid.uuid3(uuid.NAMESPACE_DNS, tag)) # generate id for tag
if tid in self.tag_removal_list: # tag exists, so remove it from removal_list
self.tag_removal_list.remove(tid)
def verify_type(res_dict):
if not 'type' in res_dict:
return
# if type is changed but is greater than the previous, then keep the previous
self.cur.execute(
f"select type from tags where id = '{tid}' and type < {res_dict['type']};"
)
res = self.cur.fetchall()
if not res is None and not res == ():
del res_dict['type']
sql, _ = self._cache_insert(
'tags_cache', tid, {'default_tag': escape_string(tag)}, 'tags', {
'default_lang': escape_string(lang),
'default_lang_id': escape_string(lang_id),
'type': tag_type
}, verify_type)
if not sql is None:
self.cur.execute(sql) # insert into cache
return tid
def process_item(self, item, spider):
if item['itemType'] == 'role' :
sqlStatement = "insert into rundstedt.role (id, name, gender, school, neigongyanxiu, price, status, server_name,server_id,grade) " \
"SELECT '{}','{}','{}','{}','{}',{},'{}','{}','{}','{}' FROM DUAL WHERE NOT EXISTS " \
"(SELECT * FROM rundstedt.role WHERE id='{}')"
self.cursor.execute(sqlStatement.format(item['roleID'],item['name'],item['gender'],item['school'],item['neigongyanxiu'],item['price'],
item['status'],item['server'],item['serverId'],item['grade'],item['roleID']))
self.connect.commit()#执行添加
elif item['itemType'] == 'treasure' :
sqlStatement = "insert into rundstedt.treasure (id, role_id, data_info, wuxue, is750) " \
"SELECT '{}','{}','{}','{}',{} FROM DUAL WHERE NOT EXISTS (SELECT * FROM rundstedt.treasure WHERE id='{}')"
self.cursor.execute(sqlStatement.format(item['tID'], item['roleID'],escape_string(item['dataInfo']),item['skill'],item['is750'],item['tID']))
self.connect.commit()#执行添加
elif item['itemType'] == 'treasureprop' :
sqlStatement = "insert into rundstedt.treasure_prop (treasure_id, prop) " \
"SELECT '{}','{}' FROM DUAL WHERE NOT EXISTS (SELECT * FROM rundstedt.treasure_prop WHERE treasure_id= '{}' AND prop='{}')"
self.cursor.execute(sqlStatement.format(item['tID'],item['prop'],item['tID'],item['prop']))
self.connect.commit()#执行添加
elif item['itemType'] == 'threeSkills' :
sqlStatement = "insert into rundstedt.threeskills (role_id, data_info, wuxue, skill) " \
"SELECT '{}','{}','{}','{}' FROM DUAL WHERE NOT EXISTS (SELECT * FROM rundstedt.threeskills WHERE wuxue='{}' AND skill='{}')"
self.cursor.execute(sqlStatement.format(item['roleID'],escape_string(item['dataInfo']),item['wuxue'],item['skill'],item['wuxue'],item['skill']))
self.connect.commit()#执行添加
elif item['itemType'] == 'skin' :
sqlStatement = "insert into rundstedt.skin (role_id, name, type, quality, photo) " \
"SELECT '{}','{}','{}','{}','{}' FROM DUAL WHERE NOT EXISTS (SELECT * FROM rundstedt.skin WHERE role_id='{}' AND name='{}')"
self.cursor.execute(sqlStatement.format(item['roleID'],item['name'],item['type'],item['quality'],item['photo'],item['roleID'],item['name']))
self.connect.commit()#执行添加
return item
def update_password(client: Connection, username: str, host: str,
password: str):
with client.cursor() as c:
# NOTE: this syntax is deprecated but is the only syntax that works
# consistently for both mysql 5.6 and 5.7
c.execute('SET PASSWORD FOR \'%s\'@\'%s\' = '
'PASSWORD(\'%s\');' %
(escape_string(username), escape_string(host),
escape_string(password)))
def getASection(aa, title):
cursor = conn.cursor()
try:
# aa = 'https://tieba.baidu.com/p/7198585197'
# f = open(bookName, "a", encoding='utf-8') # 利用追加的方式打开文件，这样后面章节的内容不会覆盖前面章节
rsp = requests.get(aa)
rsp.encoding = 'utf-8'
bs = BeautifulSoup(rsp.content, 'lxml', from_encoding='utf-8')
body = bs.select('div.d_post_content_main ')
bodyTime = bs.select(
'div.d_post_content_main.d_post_content_firstfloor')
author = bs.select('a.p_author_name.j_user_card')
fabutime = bs.select('span.tail-info')
# print(fabutime)
n = 0
for i in body:
authorss = author[n]
authors = authorss.text
bodys = body[n]
content = bodys.text
bodyss = escape_string(filter_emoji(str(bodys), ''))
contents = escape_string(filter_emoji(str(content), ''))
ax = re.compile("post_content_\d{1,20}").findall(str(bodys))
contentsss = re.compile(
'<div class="d_post_content j_d_post_content" .*?>.*?</div>'
).findall(str(bodys))
onlys = re.sub("\D", '', str(ax))
only = aa + '?pid=' + onlys + '#' + onlys
b = contentsss[0]
s2 = re.sub(r'<.*?>', '', b)
avc = re.findall('\d{4}-\d{2}-\d{2} \d{2}:\d{2}', str(bodys))
n = n + 1
title = title
times = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
timesss = datetime.datetime.strptime(str(avc[0]), "%Y-%m-%d %H:%M")
site = '王者荣耀cp吧'
article_url = aa
data = (title, site, str(b), times, s2, authors, article_url, only,
timesss)
query = "INSERT INTO yq_article(article_title,article_site,article_contenthtml,article_download_time,article_content,article_author,article_url,article_onlyl_url,article_pub_time) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
data)
try:
cursor.execute(query)
conn.commit()
except Exception as err:
print("sql语句执行错误", err, query)
conn.rollback()
cursor.close()
# f.writelines(content)
# f.close()
except IndexError as e:
print("======", e)
finally:
print('finally...')
def update_password(client: Connection, username: str, host: str,
password: str):
with client.cursor() as c:
# NOTE: this syntax is deprecated but is the only syntax that works
# consistently for both mysql 5.6 and 5.7
c.execute('SET PASSWORD FOR \'%s\'@\'%s\' = '
'PASSWORD(\'%s\');' % (
escape_string(username),
escape_string(host),
escape_string(password)
))
def import_shows(last_episodes=0):
source_id = utils.get_alternate_key_id(media_source)
for tvdb_id in show_ids_to_add:
show_id = utils.search_media_id(2, media_source, tvdb_id)
show = tvdb.Series(tvdb_id)
episodes_found = show.Episodes.all()
if last_episodes > 0:
episodes_found = episodes_found[-last_episodes:]
count = 0
logger.info("Found %s Episodes for %s" %
(len(episodes_found), show.info()['seriesName']))
for episode in episodes_found:
source_media_id = episode['id']
season_number = episode['airedSeason']
episode_number = episode['airedEpisodeNumber']
air_date = episode['firstAired']
title = episode['episodeName']
sql = "INSERT IGNORE INTO episodeCrossReference (sourceId, mediaType, showId, seasonNumber, episodeNumber, title, airDate, sourceMediaId) VALUES('%s', '4', '%s', '%s', '%s', '%s','%s','%s');" % (
source_id, show_id, season_number, episode_number,
escape_string(title), air_date, source_media_id)
count += int(utils.mysql_execute(sql))
logger.info("%s Episodes for %s Found, %s Added" %
(len(episodes_found), show.info()['seriesName'], count))
check_for_matches()
def attackLog(ip, attackType, attackCommand, time):
print(attackType, attackCommand)
attackCommand = escape_string(attackCommand)
database("ALTER TABLE attackLog AUTO_INCREMENT =1")
sql = "insert into attackLog(ip, attackType,attackCommand, time) values('%s','%s','%s','%s')"
database(sql % (ip, attackType, attackCommand, time))
print('success')
def mysql_format(self):
'''
数据类型转换+字符串标准化 用于mysql入landinn库时数据标准化
:return: 'element'
'''
if self.element is None or self.element == "":
return 'NULL'
elif isinstance(self.element,str):
self.element = self.standardization()
self.element = escape_string(self.element)
elif pd.isna(self.element):
return 'NULL'
else:
self.element = escape_string(str(self.element))
return self.strQ2B()
return "'" + self.element + "'"
def create_tags(self):
sql = """
CREATE TABLE if not exists `library_tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`TagContent` varchar(255) NOT NULL,
`Timestamp` int(11) NOT NULL,
`MovieID_id` int(11) NOT NULL,
`UserID_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `library_tags_MovieID_id_c6d94589_fk_library_movies_MovieID` (`MovieID_id`),
KEY `library_tags_UserID_id_12b1694e_fk_library_users_UserID` (`UserID_id`),
CONSTRAINT `library_tags_MovieID_id_c6d94589_fk_library_movies_MovieID` FOREIGN KEY (`MovieID_id`) REFERENCES `library_movies` (`MovieID`),
CONSTRAINT `library_tags_UserID_id_12b1694e_fk_library_users_UserID` FOREIGN KEY (`UserID_id`) REFERENCES `library_users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
"""
self.con.excute(sql)
sql = "SET foreign_key_checks = 0"
self.con.excute(sql)
sql = "TRUNCATE table library_tags"
self.con.excute(sql)
sql = "SET foreign_key_checks = 1"
self.con.excute(sql)
ratings = pd.read_csv(r'tags.csv', sep=",", engine='python')
ratings.columns = ['userId', 'movieId', 'tag', 'timestamp']
for index, row in ratings.iterrows():
try:
sql = "INSERT INTO `library_tags` (`TagContent`, `Timestamp`, `MovieID_id`, `UserID_id`)" \
" values ('%s', %s, %s, %s)" % (escape_string(row['tag']), row['timestamp'], row['movieId'], row['userId'])
print(sql)
self.con.excute(sql)
except Exception as e:
print(e)
def update_SQL(col_name, col_value, arxiv_id):
sql = "UPDATE paper SET `%s`='%s' WHERE user_id like '%%%s%%'" % (
col_name, escape_string(col_value), arxiv_id)
# 插入到数据库
# 执行sql语句
cur.execute(sql)
# 提交到数据库执行
conn.commit()
def cache_author(self, uid: str, name: str, avatar: str):
'''
Cache author info.
'''
uid = '-' if uid == '' or uid is None else uid
aid = str(uuid.uuid3(uuid.NAMESPACE_DNS,
uid)) # generate id for author
if aid in self.author_removal_list: # author exists, so remove it from removal_list
self.author_removal_list.remove(aid)
sql, _ = self._cache_insert('authors_cache', aid,
{'author_uid': escape_string(uid)},
'authors', {
'author_name': escape_string(name),
'avatar': escape_string(avatar)
})
if not sql is None:
self.cur.execute(sql) # insert into cache
return aid
def str_format(v):
"""Format the input string."""
if v.startswith('CASRN='):
return ''
v_new = v.lstrip('-., ').rstrip('( ').replace(' ', ' ').strip(' \\/|')
v_new = re.sub(r'(?:^[\(\)]|[\(\)]$)', '', v_new).strip() \
if ('(' in v_new and ')' not in v_new) or \
(')' in v_new and '(' not in v_new) \
else v_new
return escape_string(re.sub(r_words, '', symbol_cleanup(v_new)))
def getASection(url, title):
cursor = conn.cursor()
try:
# f = open(bookName, "a", encoding='utf-8') # 利用追加的方式打开文件，这样后面章节的内容不会覆盖前面章节
rsp = requests.get(url, headers=headers)
rsp.encoding = 'utf-8'
n = 0
bs = BeautifulSoup(rsp.text, 'html.parser')
body = bs.select('div.d_post_content.j_d_post_content ')
n = 0
for i in body:
bodys = body[n]
content = bodys.text
#bodys = "🙉🙉dsadsadasdas"
bodyss = escape_string(filter_emoji(str(bodys), ''))
contents = escape_string(filter_emoji(str(content), ''))
n = n + 1
title = title
times = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
site = '中国政府网'
data = (title, site, str(bodyss), times, contents)
query = "INSERT INTO yq_article(article_title,article_site,article_contenthtml,article_download_time,article_content) VALUES ('%s','%s','%s','%s','%s')" % (
data)
try:
cursor.execute(query)
conn.commit()
except Exception as err:
print("sql语句执行错误", err, query)
conn.rollback()
cursor.close()
# f.writelines(content)
# f.close()
except IndexError as e:
print("======", e)
finally:
print('finally...')
def stdSqlData(s, db=None):
'''
变量插入sql语句格式化
'''
if isinstance(s, str): s = s.strip()
if isNull(s): return 'null'
if type(s) in [list, dict, set]: s = str(s)
try:
if db is None:
return "'" + escape_string(s) + "'"
return db.escape(s)
except (TypeError, AttributeError):
return repr(s)
def escape(s, percent=True):
"""Excape quotes and percent in given string."""
# pymysql expects unicode argument to escape_string with Python 3
s = frappe.as_unicode(escape_string(frappe.as_unicode(s)),
"utf-8").replace("`", "\\`")
# NOTE separating % escape, because % escape should only be done when using LIKE operator
# or when you use python format string to generate query that already has a %s
# for example: sql("select name from `tabUser` where name=%s and {0}".format(conditions), something)
# defaulting it to True, as this is the most frequent use case
# ideally we shouldn't have to use ESCAPE and strive to pass values via the values argument of sql
if percent:
s = s.replace("%", "%%")
return "'" + s + "'"
def _where(self, query):
sql = ''
if type(query).__name__ == 'dict':
for key in query:
opeartor = '='
value = ''
if type(query[key]).__name__ != 'dict':
tmp = {'=': query[key]}
else:
tmp = query[key]
for k in tmp:
opeartor = k
value = escape_string(str(tmp[k]))
sql += key + ' `' + opeartor + '` "' + value + '" and '
return sql[0:-4]
def create_database(client: Connection, name: str,
charset: str=None, collation: str=None):
if charset:
charset_part = ' DEFAULT CHARACTER SET %s' % charset
else:
charset_part = ''
if collation:
collation_part = ' DEFAULT COLLATE %s' % collation
else:
collation_part = ''
with client.cursor() as c:
c.execute('CREATE DATABASE `%s`%s%s' % (
escape_string(name), charset_part, collation_part
))
def create_database(client: Connection,
name: str,
charset: str = None,
collation: str = None):
if charset:
charset_part = ' DEFAULT CHARACTER SET %s' % charset
else:
charset_part = ''
if collation:
collation_part = ' DEFAULT COLLATE %s' % collation
else:
collation_part = ''
with client.cursor() as c:
c.execute('CREATE DATABASE `%s`%s%s' %
(escape_string(name), charset_part, collation_part))
def on_result(self, task, result):
db = pymysql.connect(host="localhost",
user="******",
password="******",
database="cms_db",
charset='utf8')
cursor = db.cursor()
assert task['taskid']
assert task['project']
assert task['url']
assert result
##your processing code goes here
sql = 'INSERT IGNORE INTO news(title, source, time, url, content, created_at, updated_at, creator_id) VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", %s)' % \
(escape_string(result['title']), result['source'], result['date'], result['url'], escape_string(result['content']), t, t, 1)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
print(pymysql.err)
db.close()
def _parse_insert(self, table, grpup):
sql = 'insert into ' + table + ' '
if type(grpup).__name__ == 'dict':
grpup = [grpup]
columns = []
for data in grpup:
for key in data:
if key not in columns:
columns.append(key)
values = []
for data in grpup:
tmp = []
for key in columns:
if key in data:
value = escape_string(str(data[key]))
tmp.append(value)
else:
tmp.append('')
values.append(tmp)
sql += ' (`' + '`, `'.join(columns) + '`) values '
value_sql = ''
for value in values:
value_sql += ' ("' + '", "'.join(value) + '"), '
return sql + value_sql[0:-2]
def escape(obj, mapping=None): if isinstance(obj, str): return "'" + escape_string(obj) + "'" return escape_item(obj, charset, mapping=mapping)
for i in range(1, 13):
for j in range(1, monthDays[i]+1):
url = baseUrl+formatDate(j, i) + ".html"
print("Currently Processing: " + url)
print()
response = requests.get(url)
soup = BeautifulSoup(response.content, "lxml").findAll('div', { "class":"newhighlight" })
try:
for block in soup:
if(block.strong):
year = int(block.strong.text)
heading = str(block.find('span', { "class": "c9"}).text).strip()
desc = ""
for text in block.findAll(text=True):
if( not (text.parent.name == "strong" or text.parent.name == "span")):
desc += str(text).strip()
with Connection.cursor() as cursor:
sql = "INSERT INTO entries(`day`, `month`, `year`, `heading`, `description`) VALUES(%s, %s, %s, %s, %s)"
#print(heading+ desc)
cursor.execute(sql, (j, i, year, escape_string(heading), escape_string(desc)))
Connection.commit()
except:
pass
finally:
pass
Connection.close()
# sql = "INSERT INTO entries (`day`,`month`,`year`,`heading`,`description`) VALUES (%s, %s, %s, %s, %s)"
def escape(self, obj, mapping=encoders):
if isinstance(obj, str):
return "'" + escape_string(obj) + "'"
return escape_item(obj, 'utf8mb4', mapping=mapping)
def escape_string(self, s):
if (self.server_status &
SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):
return s.replace("'", "''")
return escape_string(s)
filename = "/Users/billxzy1215/Desktop/COMP 2043 SE/QuizApp/data/course/course_china_2020.xlsx"
Module_Id = []
Name = []
Des = []
data = xlrd.open_workbook(filename)
sh = data.sheet_by_index(0)
# with open (filename,encoding='utf-8') as myFile:
# lines = csv.reader(myFile)
# for line in lines:
# print(line)
for rx in range(sh.nrows):
Module_Id.append(sh.row(rx)[1].value)
Name.append(escape_string(sh.row(rx)[2].value))
html = sh.row(rx)[5].value
pre = re.compile('>(.*?)<') #html去标签化
text= ''.join(pre.findall(html))
Des.append(escape_string(text))
# ------------------- mysql connection ---------------------------
db = pymysql.connect(host="49.234.104.64",port=3306,user="******",passwd="44c140566acb1d28",db="QuizApp")
cursor = db.cursor()
# SELECT EXAMPLE
# sql = "SELECT * FROM Student"
# try:
# cursor.execute(sql)
# results = cursor.fetchall()
def getarticle(url):
response = requests.get(url, headers=headers)
response.encode = 'utf-8'
content = response.text.encode(response.encoding).decode(
response.apparent_encoding)
soup = BeautifulSoup(content, 'lxml')
#取出a标签 href属性 符合该正则关系式的 a标签
urls = soup.find_all(
"a",
attrs={
'href': re.compile("http://www.douguo.com/article/detail/\d{1,10}")
})
for i in urls:
cursor = conn.cursor()
try:
#取出所有的a标签里的href属性
url = urljoin(response.url, i["href"])
#排重所需要的url参数
data = (url)
query = 'select * from yq_article where article_onlyl_url = "%s"' % (
str(data))
except Exception as err:
print("sql语句执行错误", err)
conn.rollback()
try:
cursor.execute(query)
#查询返回的参数
result = cursor.fetchall()
conn.commit()
#如果存在 跳过此次循环 如果没有继续执行
if result:
continue
else:
# 取出域名
res = urlparse(url)
domain = res.netloc
# 请求地址 获取网页源代码
rsp = requests.get(url, headers=headers)
bs = BeautifulSoup(rsp.content,
'html.parser',
from_encoding='utf-8')
# 正则匹配h1标签标题
title = re.compile('<h3>(.*?)</h3>').findall(str(bs))
# 取出该标签下的内容
aa = bs.select('div.art-info')
# 正则匹配时间
time = re.compile(
'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}').findall(str(aa))
times = time[0]
# 循环获取的参数 拼出时间格式
# for g in times:
# e, r, t, y, u,v = times
# dates = e + "-" + r + "-" + t + " " + y + ":" + u + ":"+ v
# 时间格式转换
b = datetime.datetime.strptime(times, '%Y-%m-%d %H:%M:%S')
# 正则匹配来源
souce = re.compile('来自：(.*?)</a>').findall(str(aa))
# 取出正文内容
content_html = bs.select('div.par')
content = content_html[0].text
content_html = content_html[0].prettify()
ac = escape_string(content_html)
download_time = datetime.datetime.now().strftime(
"%Y-%m-%d %H:%M:%S")
article_site = '豆果美食网'
data = (
title[0],
article_site,
ac,
download_time,
content,
url,
b,
souce[0],
domain,
)
query = "INSERT INTO yq_article(article_title,article_site,article_contenthtml,article_download_time,article_content,article_url,article_pub_time,article_souce,article_domain) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
data)
try:
cursor.execute(query)
conn.commit()
except Exception as err:
print("sql语句执行错误", err)
conn.rollback()
except:
import traceback
print(traceback.format_exc())
def escape(self, stmt):
"""Removes single quotes and escapes back slashes because they mess up SQL"""
return escape_string(stmt).strip() if stmt else None
def escape(s):
if isinstance(s, str):
return '\'{}\''.format(escape_string(s))
return escape(str(s))
def test_escape_string(self):
self.assertEqual(
converters.escape_string(u"foo\nbar"),
u"foo\\nbar"
)
def execute_sql(query_type, **kwargs):
query = None
return_value = None
try:
if query_type not in ('select', 'update', 'insert', 'delete'):
return "query type is not valid"
elif query_type != 'select' and type(
kwargs['table']) == set and len(kwargs['table']) > 1:
return "only select type can contain multiple tables"
elif query_type == "select":
select_formatted = ", ".join(kwargs['select'])
table_formatted = ", ".join(kwargs['table'])
join_formatted = " and ".join(
["%s=%s" % (k, kwargs['join'][k])
for k in kwargs['join']]) if 'join' in kwargs else None
query = "SELECT %s FROM %s" % (select_formatted, table_formatted)
if 'where' in kwargs:
where_formatted = " and ".join([
"%s='%s'" % (k, kwargs['where'][k])
for k in kwargs['where']
])
query = "%s WHERE %s" % (query, where_formatted)
if join_formatted:
query = "%s AND %s" % (query, join_formatted)
elif query_type == "update":
table_formatted = "".join(kwargs['table'])
set_formatted = ", ".join(
["%s=%s" % (k, kwargs['set'][k]) for k in kwargs['set']])
where_formatted = " and ".join([
"%s='%s'" % (k, escape_string(kwargs['where'][k]))
for k in kwargs['where']
])
return_value = "id" if 'return_value' not in kwargs else kwargs[
'return_value']
query = "UPDATE %s SET %s WHERE %s" % (
table_formatted, set_formatted, where_formatted)
elif query_type == "delete":
table_formatted = "".join(kwargs['table'])
where_formatted = " and ".join([
"%s='%s'" % (k, escape_string(kwargs['where'][k]))
for k in kwargs['where']
])
return_value = "id" if 'return_value' not in kwargs else kwargs[
'return_value']
query = "DELETE FROM %s WHERE %s" % (table_formatted,
where_formatted)
elif query_type == "insert":
table_formatted = "".join(kwargs['table'])
columns = ",".join([str(k) for k in kwargs['values']])
values = ",".join([
"'" + escape_string(str(kwargs['values'][k])) + "'"
for k in kwargs['values']
])
return_value = "id" if 'return_value' not in kwargs else kwargs[
'return_value']
query = "INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(`id`)" % (
table_formatted, columns, values)
query += ";" if 'on_duplicate' not in kwargs else ",%s" % ", ".join(
[
"%s='%s'" % (k, kwargs['on_duplicate'][k])
for k in kwargs['on_duplicate']
])
if query is not None:
query = query.replace("'None'", "Null").replace("None", "Null")
logger.debug(query)
with warnings.catch_warnings():
warnings.simplefilter("ignore")
conn = pymysql.connect(host=config.host,
port=config.port,
user=config.user,
password=config.passwd,
database=config.dbname)
cursor = conn.cursor()
cursor.execute(query)
if query_type == "select":
result = cursor.fetchall()
else:
conn.commit()
if return_value == "id":
result = cursor.lastrowid
elif return_value == "count":
result = cursor.rowcount
cursor.close()
conn.close()
return result
else:
logger.error(query)
return None
except OperationalError:
pass
except Exception as e:
logger.error('Error on line {} - {} - {}'.format(
type(e).__name__,
sys.exc_info()[-1].tb_lineno, e))
logger.error(query)
return None
def cache(self, info: dict):
'''
Cache datapack info
'''
did = str(uuid.uuid3(uuid.NAMESPACE_DNS,
info['link'])) # generate id for datapack
if did in self.datapack_removal_list: # datapack exists, so remove it from removal_list
self.datapack_removal_list.remove(did)
# language
lang = self.languages[info['default_lang']]['name']
lang_id = info['default_lang']
# cache author
aid = self.cache_author(info['author_uid'], info['author_name'],
info['author_avatar'])
# cache tags
tag_class = [
info['source'], info['game_version'], info['tag'], info['keywords']
]
tag_id = []
for i in range(0, 4): # for each tag type
if type(tag_class[i]) == list:
tag_id += [
self.cache_tag(_t, lang, lang_id, i) for _t in tag_class[i]
]
else:
tag_id.append(self.cache_tag(tag_class[i], lang, lang_id, i))
# insert datapack
def set_update_time(res_dict):
if res_dict['status'] == '+':
res_dict['update_time'] = info['update_time']
elif res_dict[
'status'] == '*': # if 'update_time' is included after spider crawling, let final update time be 'update_time'; if not, generate new
res_dict['update_time'] = info['update_time'] if not info[
'update_time'] == info['post_time'] else datetime.now(
).strftime("%Y-%m-%d %H:%M:%S")
intro = escape_string('\n'.join(
info['summary'])) # connect all summaries to form introduction
sql, status = self._cache_insert(
'datapacks_cache',
did,
{
'link': info['link'],
'source': info['source'],
'post_time': info['post_time']
},
'datapacks',
{
'default_lang':
escape_string(lang),
'default_lang_id':
escape_string(lang_id),
'default_name':
escape_string(info['default_name']),
'default_intro':
'-' if intro is None or intro == '' else intro,
'author_id':
aid,
# content is related to authorization
'full_content':
escape_string(
info['content_full'] if self.
_check_datapack_auth(did, aid) else info['content_raw']),
},
set_update_time)
if not sql is None:
self.cur.execute(sql) # insert into cache
# cache datapack-tag relation
self.cur.execute(
f"select tag_id from datapack_tags where datapack_id = '{did}';")
res = self.cur.fetchall()
tag_id_old = set(
[i[0] for i in res] if not res is None and len(res) > 0 else [])
for tid in tag_id:
if tid not in tag_id_old: # new relation
self.cur.execute(f'''
insert into datapack_tag_relations_cache (datapack_id, tag_id, status) values ('{did}', '{tid}', '+')
on duplicate key
update status = '+';
''')
else:
tag_id_old.remove(tid)
for tid in tag_id_old: # delete relation
self.cur.execute(f'''
insert into datapack_tag_relations_cache (datapack_id, tag_id, status) values ('{did}', '{tid}', '-')
on duplicate key
update status = '-';
''')
# cache image
if not info['author_avatar'] in [None, 'auto', 'none', '']:
self.cache_img(f"author/{aid}.png", info['author_avatar'], '+')
if not info['cover_img'] in [None, 'auto', 'none', '']:
self.cache_img(f"cover/{did}.png", info['cover_img'], '+')
return did
def test_escape_string_bytes(self):
self.assertEqual(
converters.escape_string(b"foo\nbar"),
b"foo\\nbar"
)
def escape_string(self, s):
if (self.server_status
& SERVER_STATUS.SERVER_STATUS_NO_BACKSLASH_ESCAPES):
return s.replace("'", "''")
return escape_string(s)
def setup_message(message_name,
test=False,
caregiver_name='caregiver',
care_recipient_name='care recipient',
msd_time='0:00am'):
#default
extra_morning_msg = False
#html code
html_newline = '<br />'
bld = '<strong>'
end_bld = '</strong>'
cntr = '<center>'
end_cntr = '</center>'
#getting recommendaiton category for morning message (always after '<>')
if '<>' in message_name:
A_category = message_name.split('<>')[1]
message_name = message_name.split('<>')[0]
#check if this message is the extra morning message
if '[!]' in message_name:
extra_morning_msg = True #to be used to change answer choices
message_name = message_name.replace('[!]', '')
#get json directory
json_path = DIR_PATH.replace('\\', '/').replace('pkg', 'json_prompts.json')
#open json with all prompts and their ids
with open(json_path, 'r') as file:
json_prompts = json.load(file)
#pick the prompt for tthe custom message
suid, vsid, message, retrieval_code, qtype = json_prompts[
message_name].values()
#Morning Messages Formatting -------------------------------
#for morning reflection messages replace [MM] and if the question has [MRM]
if '[MM]' in message:
_, _, dflt_mrn_msge, _, _ = json_prompts[
'morning:gen_message:1'].values()
message = message.replace('[MM]', dflt_mrn_msge)
if '[MRM]' in message:
# pick random morning reflection question (only happens if message is general number 8)
randnum1 = random.randint(1, 4)
reflection_mssge_id = 'morning:positive:reflection:' + str(randnum1)
_, _, reflection_mssge, _, _ = json_prompts[
reflection_mssge_id].values()
message = message.replace('[MRM]', reflection_mssge)
#adding on to messages
#Morning encouragement message: check if there is a special insert
if '[A]' in message:
message = message.replace(
'[A]', json_prompts['morning:addon:A']
[A_category]) # must choose message based on recommendation
if '[B]' in message:
randnum = random.randint(0, len(json_prompts['morning:addon:B']) - 1)
message = message.replace('[B]',
json_prompts['morning:addon:B'][randnum])
if '[C]' in message:
randnum = random.randint(0, len(json_prompts['morning:addon:C']) - 1)
message = message.replace('[C]',
json_prompts['morning:addon:C'][randnum])
#Recommendation Messages Formatting-----------------------------
# must label all recomendations with their type
recomm_types_dict = {
'timeout': 'Time out',
'breathing': 'Deep Breathing',
'bodyscan': 'Body Scan',
'enjoyable': 'Enjoyable Activity'
}
for r_type in recomm_types_dict.keys():
if r_type in message_name:
# must use <br /> instead of \n (php)
type_title = 'Stress Management Tip: ' + bld + recomm_types_dict[
r_type] + end_bld + html_newline * 2
#add recommendation type
message = type_title + message
#read image name and style for image from json file
image_name, image_style = json_prompts['recomm_images'][r_type]
#make image url with ip 191.168.0.107
image_url = 'http://' + get_host_ip() + '/ema_images/' + image_name
#add image to message
message = message + html_newline * 2 + cntr + '<img src="' + image_url + '" style="' + image_style + '">' + end_cntr
#Check if must add Audio addon sequence -------------
if ('[AudioAddon: breathing]' in message) or ('[AudioAddon: bodyscan]'
in message):
#find which category audio messages to add, get the prompt name
audiopromptName = ''
if '[AudioAddon: breathing]' in message:
audiopromptName = '[AudioAddon: breathing]'
elif '[AudioAddon: bodyscan]' in message:
audiopromptName = '[AudioAddon: bodyscan]'
#take out [AudioAddon:breathing] (prompt name) from message
message = message.replace(audiopromptName, '')
#get the full prompt
audioprompt = json_prompts[audiopromptName]
#search for the audio file names [Audio: audioname.mp3] -> audioname.mp3
audiofiles_lst = re.findall(r'\[Audio: (.*?)\]', audioprompt)
audioHTML_format = ''
counting_files = 1
#for each audiofile add html code around it
for audiofile in audiofiles_lst:
#make audio url
audio_url = 'http://' + get_host_ip() + '/ema_images/' + audiofile
#find this audio file and format with html
audioHTML_format = html_newline + html_newline + cntr + '<audio controls><source src="' + audio_url + '" type="audio/mpeg"></audio>' + end_cntr
#if not last audio file
if counting_files < len(audiofiles_lst):
#make a new line for the next audio file
audioHTML_format = audioHTML_format + html_newline
#take out the name of this file and replace with the HTML formatted string
audioprompt = audioprompt.replace('[Audio: ' + audiofile + ']',
audioHTML_format)
counting_files += 1
#once breathingprompt is formatted with html, add it to the original message
message = message + html_newline + audioprompt
#check in messages
if '[distractions]' in message:
randdist = random.randint(
0,
len(json_prompts['recomm:checkin:distract']) - 1)
message = message.replace(
'[distractions]',
json_prompts['recomm:checkin:distract'][randdist])
#-- ALL Messages Formatting --
#changes the name in the message (must retrieve names from DeploymentInformation.db)
#if the message uses a name, replace default with actual name from database
if '[caregiver name]' in message:
message = message.replace('[caregiver name]', caregiver_name)
if '[care recipient name]' in message:
message = message.replace('[care recipient name]', care_recipient_name)
if '[TIME]' in message:
message = message.replace('[TIME]', msd_time)
#if message is multiple choice/radio button, retrieve answer choices.
#if message is extra morning msg, replace answer choices with thanks
if ('[]' in message) or extra_morning_msg:
#normal multiple choice or radio button questions
if '[]' in message:
answer_choices = message.split('[]')[1]
choices_sent = answer_choices #for reward_data
message = message.split('[]')[0]
# only for multiple choice questions add 'check...
if qtype == 'multiple choice':
message += ' (check as many as apply)'
#make the only answer choice thanks
if extra_morning_msg:
extra_morning_msg = False
#should not have the option for another encouragement msg
answer_choices = '1 Thanks!'
#to change options choices must incode in binary
binary_choices = answer_choices.replace("\n",
os.linesep).encode("ascii")
#change the answer choices
try:
db = get_conn()
cursor = db.cursor()
update_query = "UPDATE ema_settings SET value = %s WHERE suid = %s AND object = %s AND name like %s"
cursor.execute(update_query,
(binary_choices, str(suid), vsid, 'options'))
db.commit()
except Exception as err:
log('Failed to update logged ema request:', str(err))
db.rollback()
finally:
db.close()
#dynamically change likert scale
if ('[Likert]' in message) and (qtype == 'slide bar'):
#change the labels of likert scale dynamically
message = likert_dynamic_answers(message, suid)
#textbox messgaes that need only numbers. Make the textbox smaller and only allow numbers. (Evening messages in baseline and regular period).
if ('textbox:interactions' in message_name) or ('textboxinteractions'
in message_name):
#add breakline to allow next button to show
message += html_newline * 2
# close the div and place script. Use onload since this statement occurs before the textarea. Use jquery validate plugin for numerical validation. Found out EMA is using the textarea tag by adding <textarea> without closing tag to make code spill out
message += '</div> <script> window.onload = function(){ var mytextarea = document.getElementsByTagName("textarea")[0]; mytextarea.style.width = "100px"; mytextarea.style.height = "auto"; mytextarea.rows = 1; mytextarea.placeholder = "0"; mytextarea.setAttribute("data-rule-number", "true");}; </script> <div> '
#if there is a next line in message
if '\n' in message:
message = message.replace('\n', html_newline)
#for STORING in reward data
stored_message_sent = message.replace(html_newline, '').replace(
bld,
'').replace(end_bld,
'').replace(cntr,
'').replace(end_cntr,
'') #we dont want <br /> or bold
stored_message_sent = escape_string(
stored_message_sent) #must use escape for the \' in message
stored_message_name = message_name
#converting prompt to binary
binary_prompt = message.encode('ascii')
#change bin file in ema_settings table (dynamic messaging)
try:
db = get_conn()
cursor = db.cursor()
update_query = "UPDATE ema_settings SET value = %s WHERE suid = %s AND object = %s AND name like %s"
#cursor.execute(update_query,(binary_prompt, '23','6747','question'))
cursor.execute(update_query,
(binary_prompt, str(suid), vsid, 'question'))
db.commit()
except Exception as err:
log('Failed to update logged ema request:', str(err))
db.rollback()
finally:
db.close()
# returns suid, retrieval code, and question type
return suid, retrieval_code, qtype, stored_message_sent, stored_message_name
