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 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