def setUp(self): utils.pg_host = "localhost" utils.pg_base = "osmose_test" utils.pg_pass = "******" utils.db_string = "host='%s' port='%s' dbname='%s' user='******' password='******'" % ( utils.pg_host, utils.pg_port, utils.pg_base, utils.pg_user, utils.pg_pass) self.dbconn = utils.get_dbconn() self.dbcurs = self.dbconn.cursor() self.dbcurs.execute(open("tools/database/drop.sql", "r").read()) self.dbcurs.execute(open("tools/database/schema.sql", "r").read()) self.dbcurs.execute( "INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (1, "xx1", "yy1")) self.dbcurs.execute( "INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (2, "xx2", "yy2")) self.dbcurs.execute( "INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (1, "xx1")) self.dbcurs.execute( "INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (2, "xx2")) self.dbconn.commit()
def listitem(lang): conn = utils.get_dbconn() curs = conn.cursor() items = {} curs.execute( "SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;" % lang) for res in curs.fetchall(): items[res['categ']] = dict(res) items[res['categ']]['item'] = {} curs.execute( "SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;" % lang) for res in curs.fetchall(): items[res['categ']]['item'][res['item']] = dict(res) items[res['categ']]['item'][res['item']]['class'] = {} curs.execute( "SELECT class, dynpoi_class.item, dynpoi_item.categ, COALESCE(Max(title->'%s'), Max(title->'en')) AS title FROM dynpoi_class JOIN dynpoi_item ON dynpoi_item.item=dynpoi_class.item GROUP BY class, dynpoi_class.item, dynpoi_item.categ" % lang) for res in curs.fetchall(): if items[res['categ']]['item'].get(res['item']): items[res['categ']]['item'][res['item']]['class'][ res['class']] = res['title'] return items
def listitem(lang): conn = utils.get_dbconn() curs = conn.cursor() items = {} curs.execute("SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;" % lang) for res in curs.fetchall(): items[res["categ"]] = dict(res) items[res["categ"]]["item"] = {} curs.execute("SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;" % lang) for res in curs.fetchall(): items[res["categ"]]["item"][res["item"]] = dict(res) items[res["categ"]]["item"][res["item"]]["class"] = {} curs.execute( "SELECT DISTINCT class, dynpoi_class.item, dynpoi_item.categ, COALESCE(title->'%s', title->'en') AS title FROM dynpoi_class JOIN dynpoi_item ON dynpoi_item.item=dynpoi_class.item" % lang ) for res in curs.fetchall(): if items[res["categ"]]["item"].get(res["item"]): items[res["categ"]]["item"][res["item"]]["class"][res["class"]] = res["title"] return items
def setUp(self): utils.pg_base = "osmose_test" utils.pg_pass = "******" self.dbconn = utils.get_dbconn() self.dbcurs = self.dbconn.cursor() self.dbcurs.execute(open("tools/database/drop.sql", "r").read()) self.dbcurs.execute(open("tools/database/schema.sql", "r").read()) self.dbcurs.execute("INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (1, "xx1", "yy1")) self.dbcurs.execute("INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (2, "xx2", "yy2")) self.dbcurs.execute("INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (1, "xx1")) self.dbcurs.execute("INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (2, "xx2")) self.dbconn.commit()
def setUp(self): utils.pg_base = "osmose_test" utils.pg_pass = "******" self.dbconn = utils.get_dbconn() self.dbcurs = self.dbconn.cursor() self.dbcurs.execute(open("tools/database/drop.sql", "r").read()) self.dbcurs.execute(open("tools/database/schema.sql", "r").read()) self.dbcurs.execute( "INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (1, "xx1", "yy1")) self.dbcurs.execute( "INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (2, "xx2", "yy2")) self.dbcurs.execute( "INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (1, "xx1")) self.dbcurs.execute( "INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (2, "xx2")) self.dbconn.commit()
def setUp(self): utils.pg_host = "localhost" utils.pg_base = "osmose_test" utils.pg_pass = "******" utils.db_string = "host='%s' port='%s' dbname='%s' user='******' password='******'" % (utils.pg_host, utils.pg_port, utils.pg_base, utils.pg_user, utils.pg_pass) self.dbconn = utils.get_dbconn() self.dbcurs = self.dbconn.cursor() self.dbcurs.execute(open("tools/database/drop.sql", "r").read()) self.dbcurs.execute(open("tools/database/schema.sql", "r").read()) # Re-initialise search_path as cleared by schema.sql self.dbcurs.execute("SET search_path TO \"$user\", public;") self.dbcurs.execute("INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (1, "xx1", "yy1")) self.dbcurs.execute("INSERT INTO source (id, country, analyser) VALUES (%s, %s, %s);", (2, "xx2", "yy2")) self.dbcurs.execute("INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (1, "xx1")) self.dbcurs.execute("INSERT INTO source_password (source_id, password) VALUES (%s, %s);", (2, "xx2")) self.dbconn.commit()
def listitem(lang): conn = utils.get_dbconn() curs = conn.cursor() items = {} curs.execute("SELECT categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_categ;" % lang) for res in curs.fetchall(): items[res['categ']] = dict(res) items[res['categ']]['item'] = {} curs.execute("SELECT item, categ, COALESCE(menu->'%s', menu->'en') AS menu FROM dynpoi_item;" % lang) for res in curs.fetchall(): items[res['categ']]['item'][res['item']] = dict(res) items[res['categ']]['item'][res['item']]['class'] = {} curs.execute("SELECT class.class, class.item, dynpoi_item.categ, COALESCE(title->'%s', title->'en') AS title FROM class JOIN dynpoi_item ON dynpoi_item.item=class.item" % lang) for res in curs.fetchall(): if items[res['categ']]['item'].get(res['item']): items[res['categ']]['item'][res['item']]['class'][res['class']] = res['title'] return items
def update(source, url, logger=printlogger(), remote_ip=""): source_id = int(source["id"]) ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler( update_parser(source_id, source, url, remote_ip, dbconn, dbcurs)) ## download the file if needed if url.startswith("http://"): socket.setdefaulttimeout(180) tmp_path = "/tmp/osmose/" if not os.path.exists(tmp_path): os.makedirs(tmp_path) fname = tempfile.mktemp(dir=tmp_path, prefix="update") urllib.urlretrieve(url, fname) #mysock = urllib.urlopen(source["url"]) #open(fname,'w').write(mysock.read()) istemp = True else: fname = url istemp = False ## open the file if url.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif url.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) ## parse the file parser.parse(f) ## update subtitle from new errors execute_sql( dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle, lat = marker.lat, lon = marker.lon FROM marker WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql( dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle FROM marker WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) ## remove false positive no longer present # execute_sql(dbcurs, """DELETE FROM dynpoi_status # WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,elems FROM marker WHERE source = %s) AND # source = %s AND # date < now()-interval '7 day'""", # (source_id, source_id, )) execute_sql( dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql( dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) execute_sql( dbcurs, """UPDATE dynpoi_class SET count = (SELECT count(*) FROM marker WHERE marker.source = dynpoi_class.source AND marker.class = dynpoi_class.class) WHERE dynpoi_class.source = %s""", (source_id, )) ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f if istemp: os.remove(fname)
#! /usr/bin/env python #-*- coding: utf-8 -*- import cgi, re, sys, os, psycopg2.extras import utils conn = utils.get_dbconn() #psycopg2.extras.register_hstore(conn, globally=True, oid=1408668) #psycopg2.extras.register_hstore(conn) cur = conn.cursor("1") cur_mod = conn.cursor() sql = """ SELECT m.source, m.class, m.subclass, m.lat, m.lon, m.item, m.subtitle_en, m.subtitle_fr, m.elems, m.data FROM dynpoi_marker m """ cur.execute(sql) num_err = 0 many_res = cur.fetchmany(1000) while many_res: print(num_err, "\r", end=' ') sys.stdout.flush() num_err += 1000 for res in many_res: sql_marker = """ INSERT INTO marker (source,class,subclass,lat,lon,elems,item,subtitle)
#! /usr/bin/env python #-*- coding: utf-8 -*- import cgi, re, sys, os, psycopg2.extras import utils conn = utils.get_dbconn() #psycopg2.extras.register_hstore(conn, globally=True, oid=1408668) #psycopg2.extras.register_hstore(conn) cur = conn.cursor("1") cur_mod = conn.cursor() sql = """ SELECT m.source, m.class, m.subclass, m.lat, m.lon, m.item, m.subtitle_en, m.subtitle_fr, m.elems, m.data FROM dynpoi_marker m """ cur.execute(sql) num_err = 0 many_res = cur.fetchmany(1000) while many_res: print num_err, "\r", sys.stdout.flush() num_err += 1000 for res in many_res: sql_marker = """
def update(source, o, logger=printlogger()): source_id = int(source["id"]) ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler(update_parser(source_id, source, dbcurs, o)) dbcurs.execute( "SELECT remote_url from dynpoi_update u join dynpoi_update_last l on u.source = l.source and u.timestamp = l.timestamp where l.source = %d" % int(source["id"])) url = [x[0] for x in dbcurs.fetchall()][0] ## download the file if needed if url.startswith("http://"): fname = tempfile.mktemp() urllib.urlretrieve(url, fname) #mysock = urllib.urlopen(source["url"]) #open(fname,'w').write(mysock.read()) istemp = True else: return fname = url istemp = False ## open the file if url.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif url.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) o.write("<html>\n") o.write("<head>\n") o.write("<title>Statistiques pour %s</title>\n" % source["comment"]) o.write("<link rel='stylesheet' type='text/css' href='style.css' />\n") o.write("</head>\n") o.write("<body>\n") o.write("<h1>Statistiques pour %s</h1>\n" % source["comment"]) ## parse the file parser.parse(f) o.write("</body>\n") o.write("</html>\n") ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f if istemp: os.remove(fname)
def update(source, url, logger=printlogger(), remote_ip=""): source_id = int(source["id"]) ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler(update_parser(source_id, source, url, remote_ip, dbconn, dbcurs)) ## download the file if needed if url.startswith("http://"): socket.setdefaulttimeout(180) tmp_path = "/tmp/osmose/" if not os.path.exists(tmp_path): os.makedirs(tmp_path) fname = tempfile.mktemp(dir=tmp_path, prefix="update") urllib.urlretrieve(url, fname) # mysock = urllib.urlopen(source["url"]) # open(fname,'w').write(mysock.read()) istemp = True else: fname = url istemp = False ## open the file if url.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif url.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) ## parse the file parser.parse(f) ## update subtitle from new errors execute_sql( dbcurs, """SELECT * FROM marker WHERE (source,class,subclass,elems) IN (SELECT source,class,subclass,elems FROM dynpoi_status WHERE source = %s AND elems != '')""", (source_id,), ) for res in dbcurs.fetchall(): execute_sql( dbcurs, """UPDATE dynpoi_status SET subtitle = %s, lat = %s, lon = %s WHERE source = %s AND class = %s AND subclass = %s AND elems = %s""", (res["subtitle"], res["lat"], res["lon"], res["source"], res["class"], res["subclass"], res["elems"]), ) execute_sql( dbcurs, """SELECT * FROM marker WHERE (source,class,subclass,lat,lon) IN (SELECT source,class,subclass,lat,lon FROM dynpoi_status WHERE source = %s AND elems = '')""", (source_id,), ) for res in dbcurs.fetchall(): execute_sql( dbcurs, """UPDATE dynpoi_status SET subtitle = %s WHERE source = %s AND class = %s AND subclass = %s AND lat = %s AND lon = %s""", (res["subtitle"], res["source"], res["class"], res["subclass"], res["lat"], res["lon"]), ) ## remove false positive no longer present # execute_sql(dbcurs, """DELETE FROM dynpoi_status # WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,elems FROM marker WHERE source = %s) AND # source = %s AND # date < now()-interval '7 day'""", # (source_id, source_id, )) execute_sql( dbcurs, """DELETE FROM marker WHERE (source,class,subclass,elems) IN (SELECT source,class,subclass,elems FROM dynpoi_status WHERE source = %s AND elems != '')""", (source_id,), ) execute_sql( dbcurs, """DELETE FROM marker WHERE (source,class,subclass,lat,lon) IN (SELECT source,class,subclass,lat,lon FROM dynpoi_status WHERE source = %s AND elems = '')""", (source_id,), ) execute_sql( dbcurs, """UPDATE dynpoi_class SET count = (SELECT count(*) FROM marker WHERE marker.source = dynpoi_class.source AND marker.class = dynpoi_class.class) WHERE dynpoi_class.source = %s""", (source_id,), ) ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f if istemp: os.remove(fname)
def update(source, o, logger = printlogger()): source_id = int(source["id"]) ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler(update_parser(source_id, source, dbcurs, o)) dbcurs.execute("SELECT remote_url from dynpoi_update u join dynpoi_update_last l on u.source = l.source and u.timestamp = l.timestamp where l.source = %d" % int(source["id"])) url = [x[0] for x in dbcurs.fetchall()][0] ## download the file if needed if url.startswith("http://"): fname = tempfile.mktemp() urllib.urlretrieve(url, fname) #mysock = urllib.urlopen(source["url"]) #open(fname,'w').write(mysock.read()) istemp = True else: return fname = url istemp = False ## open the file if url.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif url.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) o.write("<html>\n") o.write("<head>\n") o.write("<title>Statistiques pour %s</title>\n" % source["comment"]) o.write("<link rel='stylesheet' type='text/css' href='style.css' />\n") o.write("</head>\n") o.write("<body>\n") o.write("<h1>Statistiques pour %s</h1>\n" % source["comment"]) ## parse the file parser.parse(f) o.write("</body>\n") o.write("</html>\n") ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f if istemp: os.remove(fname)
def update(source_id, fname, logger=printlogger(), remote_ip=""): ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler( update_parser(source_id, fname, remote_ip, dbconn, dbcurs)) ## open the file if fname.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif fname.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) ## parse the file parser.parse(f) ## update subtitle from new errors execute_sql( dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle, lat = marker.lat, lon = marker.lon FROM marker WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql( dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle FROM marker WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) ## remove false positive no longer present # execute_sql(dbcurs, """DELETE FROM dynpoi_status # WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,elems FROM marker WHERE source = %s) AND # source = %s AND # date < now()-interval '7 day'""", # (source_id, source_id, )) execute_sql( dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql( dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) execute_sql( dbcurs, """UPDATE dynpoi_class SET count = (SELECT count(*) FROM marker WHERE marker.source = dynpoi_class.source AND marker.class = dynpoi_class.class) WHERE dynpoi_class.source = %s""", (source_id, )) ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f
def update(source_id, fname, logger = printlogger(), remote_ip=""): ## open connections dbconn = utils.get_dbconn() dbcurs = dbconn.cursor() ## xml parser parser = make_parser() parser.setContentHandler(update_parser(source_id, fname, remote_ip, dbconn, dbcurs)) ## open the file if fname.endswith(".bz2"): import bz2 f = bz2.BZ2File(fname) elif fname.endswith(".gz"): import gzip f = gzip.open(fname) else: f = open(fname) ## parse the file parser.parse(f) ## update subtitle from new errors execute_sql(dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle, lat = marker.lat, lon = marker.lon FROM marker WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql(dbcurs, """ UPDATE dynpoi_status SET subtitle = marker.subtitle FROM marker WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) ## remove false positive no longer present # execute_sql(dbcurs, """DELETE FROM dynpoi_status # WHERE (source,class,subclass,elems) NOT IN (SELECT source,class,subclass,elems FROM marker WHERE source = %s) AND # source = %s AND # date < now()-interval '7 day'""", # (source_id, source_id, )) execute_sql(dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems != '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems """, (source_id, )) execute_sql(dbcurs, """ DELETE FROM marker USING dynpoi_status WHERE marker.source = %s AND marker.elems = '' AND dynpoi_status.source = marker.source AND dynpoi_status.class = marker.class AND dynpoi_status.subclass = marker.subclass AND dynpoi_status.elems = marker.elems AND dynpoi_status.lat = marker.lat AND dynpoi_status.lon = marker.lon """, (source_id, )) execute_sql(dbcurs, """UPDATE dynpoi_class SET count = (SELECT count(*) FROM marker WHERE marker.source = dynpoi_class.source AND marker.class = dynpoi_class.class) WHERE dynpoi_class.source = %s""", (source_id, )) ## commit and close dbconn.commit() dbconn.close() ## close and delete f.close() del f