def write_roles(): first_page = requests.get(config.WEREWOLF_THREAD) parsed_page = BeautifulSoup(first_page.text) third_post = parsed_page.find_all('div', class_='post')[2] roles = {} roles['werewolf'] = third_post.find_all('span', attrs={'style': "color: #FF0000"}) roles['vampire'] = third_post.find_all('span', attrs={'style': "color: #400000"}) roles['village'] = third_post.find_all('span', attrs={'style': "color: #00BF00"}) roles['switchable'] = third_post.find_all( 'span', attrs={'style': "color: #808000"}) roles['neutral'] = third_post.find_all('span', attrs={'style': "color: #8040FF"}) cursor = get_connection().cursor() for role, tags in roles.items(): for tag in tags: if 'ROLES' in tag.text: continue else: match = role_pattern.match(tag.text) if match: cursor.execute( 'insert into role(name, faction) values(%s, %s)', (match.group('role').strip(), role))
def get_all_posts(start_at_database_end=False): cursor = get_connection().cursor() cursor.execute('select name from player') names = cursor.fetchall() names = set([_['name'] for _ in names]) start = 0 if start_at_database_end: cursor.execute('select max(thread_sequence) as max_post from post') start = int(cursor.fetchone()['max_post'])+1 sequence_number = start url = config.WEREWOLF_THREAD + '&start={}'.format(start) page = requests.get(url) parsed_page = BeautifulSoup(page.text) posts = parsed_page.find_all('div', class_='post') number_of_posts = int(num_posts_pattern.match(parsed_page.find('div', class_='pagination').text.strip()).group('num_posts')) while start < number_of_posts: print start, len(posts) for post in posts: username = post.find_all('a', class_='username-coloured')[0] post_time = username.parent.nextSibling.strip(u' \xbb') post_time = datetime.strptime(post_time, time_format) post_time = timezone('US/Pacific').localize(post_time) username = username.text.lower() post_content = str(post.find('div', class_='content')) if not username in names: print username else: cursor.execute('insert into post(player, time, content, thread_sequence) values(%s, %s, %s, %s)', (username, post_time, post_content, sequence_number)) sequence_number += 1 start += 30 url = config.WEREWOLF_THREAD + '&start={}'.format(start) page = requests.get(url) parsed_page = BeautifulSoup(page.text) posts = parsed_page.find_all('div', class_='post') number_of_posts = int(num_posts_pattern.match(parsed_page.find('div', class_='pagination').text.strip()).group('num_posts'))
def connect_db(self): name = self.cboConnection.currentText() try: self.conn = dbutils.get_connection(dbutils.get_postgres_conn_info(name)) self.lblMessage.setText("") except StandardError, e: self.conn = None self.lblMessage.setText("<font color=red>"+ e.message +"</font>")
def __init__(self, filename): """ Initialization """ self.filename = filename self.content = open(filename, 'r').read().split('\n') self.connection = dbutils.get_connection() self.md5sum_file = util.md5_for_file(open(self.filename, 'r')) self.logger = bp_logger('Log Parser')
def find_votes(): cursor = get_connection().cursor() cursor.execute('select * from post') posts = cursor.fetchall() cursor = get_connection().cursor() cursor.execute('select name from player') names = cursor.fetchall() names = set([_['name'] for _ in names]) for row in posts: post_content = row['content'] parsed_content = BeautifulSoup(post_content) bolds = set( parsed_content.find_all('span', attrs={'style': 'font-weight: bold'})) votes = {} votes['lynch'] = filter_votes(bolds, '#FF0000', 'lynch') votes['no kill'] = find_no_kills(votes['lynch']) votes['lynch'] = votes['lynch'].difference(votes['no kill']) votes['rescind'] = filter_votes(bolds, '#00BF00', 'rescind') votes['mayor'] = filter_votes(bolds, '#0000FF', 'mayor') votes['rescind mayor'] = filter_votes(bolds, '#FF8000', 'rescind mayor') votes['non standard'] = find_any_color_vote(bolds)\ .difference(votes['lynch'])\ .difference(votes['no kill'])\ .difference(votes['rescind'])\ .difference(votes['mayor'])\ .difference(votes['rescind mayor']) for k, v in votes.items(): for tag in v: context = str(tag) vote_type = k post = row['thread_sequence'] player_for = ALIASES[tag.find('span').text.lower().strip(' .')] if player_for in names: cursor.execute( 'insert into vote(post, player_for, type, context) values(%s, %s, %s, %s)', (post, player_for, vote_type, context)) else: print player_for
def make_app(): loop = asyncio.new_event_loop() asyncio.set_event_loop(loop) engine = loop.run_until_complete(dbutils.get_engine()) connection = loop.run_until_complete(dbutils.get_connection(engine)) return tornado.web.Application( [(r'/js/(.*)', tornado.web.StaticFileHandler, { 'path': os.path.join(os.path.dirname(__file__), 'js') }), (r"/api/feedback", FeedbackAPIHandler, dict(db_conn=connection)), (r"/", MainHandler), (r"/list", MainHandler)], **settings.APP_SETTINGS)
def find_votes(): cursor = get_connection().cursor() cursor.execute('select * from post') posts = cursor.fetchall() cursor = get_connection().cursor() cursor.execute('select name from player') names = cursor.fetchall() names = set([_['name'] for _ in names]) for row in posts: post_content = row['content'] parsed_content = BeautifulSoup(post_content) bolds = set(parsed_content.find_all('span', attrs={'style': 'font-weight: bold'})) votes = {} votes['lynch'] = filter_votes(bolds, '#FF0000', 'lynch') votes['no kill'] = find_no_kills(votes['lynch']) votes['lynch'] = votes['lynch'].difference(votes['no kill']) votes['rescind'] = filter_votes(bolds, '#00BF00', 'rescind') votes['mayor'] = filter_votes(bolds, '#0000FF', 'mayor') votes['rescind mayor'] = filter_votes(bolds, '#FF8000', 'rescind mayor') votes['non standard'] = find_any_color_vote(bolds)\ .difference(votes['lynch'])\ .difference(votes['no kill'])\ .difference(votes['rescind'])\ .difference(votes['mayor'])\ .difference(votes['rescind mayor']) for k, v in votes.items(): for tag in v: context = str(tag) vote_type = k post = row['thread_sequence'] player_for = ALIASES[tag.find('span').text.lower().strip(' .')] if player_for in names: cursor.execute('insert into vote(post, player_for, type, context) values(%s, %s, %s, %s)', (post, player_for, vote_type, context)) else: print player_for
def get_all_posts(start_at_database_end=False): cursor = get_connection().cursor() cursor.execute('select name from player') names = cursor.fetchall() names = set([_['name'] for _ in names]) start = 0 if start_at_database_end: cursor.execute('select max(thread_sequence) as max_post from post') start = int(cursor.fetchone()['max_post']) + 1 sequence_number = start url = config.WEREWOLF_THREAD + '&start={}'.format(start) page = requests.get(url) parsed_page = BeautifulSoup(page.text) posts = parsed_page.find_all('div', class_='post') number_of_posts = int( num_posts_pattern.match( parsed_page.find( 'div', class_='pagination').text.strip()).group('num_posts')) while start < number_of_posts: print start, len(posts) for post in posts: username = post.find_all('a', class_='username-coloured')[0] post_time = username.parent.nextSibling.strip(u' \xbb') post_time = datetime.strptime(post_time, time_format) post_time = timezone('US/Pacific').localize(post_time) username = username.text.lower() post_content = str(post.find('div', class_='content')) if not username in names: print username else: cursor.execute( 'insert into post(player, time, content, thread_sequence) values(%s, %s, %s, %s)', (username, post_time, post_content, sequence_number)) sequence_number += 1 start += 30 url = config.WEREWOLF_THREAD + '&start={}'.format(start) page = requests.get(url) parsed_page = BeautifulSoup(page.text) posts = parsed_page.find_all('div', class_='post') number_of_posts = int( num_posts_pattern.match( parsed_page.find( 'div', class_='pagination').text.strip()).group('num_posts'))
def write_roles(): first_page = requests.get(config.WEREWOLF_THREAD) parsed_page = BeautifulSoup(first_page.text) third_post = parsed_page.find_all("div", class_="post")[2] roles = {} roles["werewolf"] = third_post.find_all("span", attrs={"style": "color: #FF0000"}) roles["vampire"] = third_post.find_all("span", attrs={"style": "color: #400000"}) roles["village"] = third_post.find_all("span", attrs={"style": "color: #00BF00"}) roles["switchable"] = third_post.find_all("span", attrs={"style": "color: #808000"}) roles["neutral"] = third_post.find_all("span", attrs={"style": "color: #8040FF"}) cursor = get_connection().cursor() for role, tags in roles.items(): for tag in tags: if "ROLES" in tag.text: continue else: match = role_pattern.match(tag.text) if match: cursor.execute( "insert into role(name, faction) values(%s, %s)", (match.group("role").strip(), role) )
def write_users(): first_page = requests.get(config.WEREWOLF_THREAD) parsed_page = BeautifulSoup(first_page.text) second_post = parsed_page.find_all("div", class_="post")[1] cursor = get_connection().cursor() in_players = False hit_br = False for tag in second_post.descendants: if not in_players and tag == "PLAYER LIST:": in_players = True elif in_players: if tag.name == "br": if hit_br: break else: hit_br = True else: hit_br = False match = player_pattern.match(tag.strip()) if match: cursor.execute( "insert into player(name, role, status) values(%s, %s, %s)", (match.group("name").strip(), match.group("role"), match.group("status")), )
def write_users(): first_page = requests.get(config.WEREWOLF_THREAD) parsed_page = BeautifulSoup(first_page.text) second_post = parsed_page.find_all('div', class_='post')[1] cursor = get_connection().cursor() in_players = False hit_br = False for tag in second_post.descendants: if not in_players and tag == 'PLAYER LIST:': in_players = True elif in_players: if tag.name == 'br': if hit_br: break else: hit_br = True else: hit_br = False match = player_pattern.match(tag.strip()) if match: cursor.execute( 'insert into player(name, role, status) values(%s, %s, %s)', (match.group('name').strip(), match.group('role'), match.group('status')))
FROM ng_roads_sources ORDER BY geom <-> (SELECT ST_SetSRID(ST_MakePoint({long0}, {lat0}), 4326)) LIMIT 1), (SELECT source_id FROM ng_roads_sources ORDER BY geom <-> (SELECT ST_SetSRID(ST_MakePoint({long1}, {lat1}), 4326)) LIMIT 1), false) di INNER JOIN ng_2po_4pgr ng ON di.edge = ng.id) k) SELECT public.ST_AsGeoJSON(ST_Transform(ST_Union(the_geom),3857)) as geom FROM tmp ''' cursor = conn.cursor(cursor_factory=pg_extras.RealDictCursor) cursor.execute(rout) results = cursor.fetchall() return results[0]['geom'] if __name__ == '__main__': conn = db.get_connection() params = cgi.FieldStorage() op = assign_param('op', params) if op == 'route': long0 = assign_param('long0', params) lat0 = assign_param('lat0', params) long1 = assign_param('long1', params) lat1 = assign_param('lat1', params) results = route(conn, long0, lat0, long1, lat1) else: results = {'message': 'Unknown service'} get_done(json.dumps(results)) get_done(results)
def get_db_cur(self): # Create a new new connection if required if self.db_conn is None: self.db_conn = dbutils.get_connection(self.conn_info) return self.db_conn.cursor()
def outer_function(self): conn = get_connection() curs = conn.cursor() curs.execute("INSERT INTO jamtest VALUES(2)") self.inner_function() raise Exception()
def _db_cur(self): con_info = get_postgres_conn_info(self.config['connection']) con = get_connection(con_info) return con.cursor()
def create_table_and_insert(self): conn = get_connection() curs = conn.cursor() curs.execute("DROP TABLE IF EXISTS jamtest") curs.execute("CREATE TABLE jamtest(row1 int)") curs.execute("INSERT INTO jamtest VALUES(1)")
def clear_roles(): cursor = get_connection().cursor() cursor.execute('delete from role')
def clear_roles(): cursor = get_connection().cursor() cursor.execute("delete from role")
def clear_all_posts(): cursor = get_connection().cursor() cursor.execute('delete from post')
def assert_correct_data(self, data): conn = get_connection() curs = conn.cursor() curs.execute("SELECT * FROM jamtest") self.assertEqual(curs.fetchall(), data)
def _get_cur(self): ci = get_postgres_conn_info(self.postGisConnectionComboBox.currentText()) cur = get_connection(ci).cursor() return cur
def _get_cur(self): ci = get_postgres_conn_info( self.postGisConnectionComboBox.currentText()) cur = get_connection(ci).cursor() return cur
def clear_players(): cursor = get_connection().cursor() cursor.execute("delete from player")
def insert_and_fail(self): conn = get_connection() curs = conn.cursor() curs.execute("INSERT INTO jamtest VALUES(2)") raise Exception()
def clear_votes(): cursor = get_connection().cursor() cursor.execute('delete from vote')
def inner_function(self): conn = get_connection() curs = conn.cursor() curs.execute("INSERT INTO jamtest VALUES(3)")
def clear_players(): cursor = get_connection().cursor() cursor.execute('delete from player')