def get_genes_ids(db, location=None, use_strand=False, overlap=True): """ Get ensembl ids for genes """ sql, sql_parameters = location_to_sql(location, use_strand, overlap) sql = "{0} {1} {2}".format(SQL_GENES_SIMPLE, sql, SQL_GENES_SIMPLE_ORDER_BY) LOG.debug("SQL:\n{0}".format(sql)) LOG.debug("PARAMETERS: {0}".format(sql_parameters)) conn = sqlite3.connect(db) sqlite3.enable_callback_tracebacks(True) conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute(sql, sql_parameters) gene_ids = [] for r in cursor: gene_ids.append(r['ensembl_id']) cursor.close() conn.close() return gene_ids
def get_genes_simple(db, location=None, use_strand=False, overlap=True): sql, sql_parameters = location_to_sql(location, use_strand, overlap) sql = "{0} {1} {2}".format(SQL_GENES_SIMPLE, sql, SQL_GENES_SIMPLE_ORDER_BY) LOG.debug("SQL:\n{0}".format(sql)) LOG.debug("PARAMETERS: {0}".format(sql_parameters)) conn = sqlite3.connect(db) sqlite3.enable_callback_tracebacks(True) conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute(sql, sql_parameters) genes = [] for r in cursor: genes.append( Gene(r['ensembl_id'], r['seqid'], r['start'], r['end'], r['strand'])) cursor.close() conn.close() return genes
def get_transcripts_simple(db, location=None, use_strand=False, overlap=True): sql, sql_parameters = location_to_sql(location, use_strand, overlap) sql = "{0} {1} {2}".format(SQL_TRANSCRIPTS_SIMPLE, sql, SQL_TRANSCRIPTS_SIMPLE_ORDER_BY) LOG.debug("SQL:\n{0}".format(sql)) LOG.debug("PARAMETERS: {0}".format(sql_parameters)) conn = sqlite3.connect(db) sqlite3.enable_callback_tracebacks(True) conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute(sql, sql_parameters) transcripts = OrderedDict() exons = OrderedDict() for r in cursor: if r['transcript_id'] == r['child_id']: # transcript if r['_key'] not in transcripts: transcripts[r['_key']] = Transcript(r['transcript_id'], r['transcript_seqid'], r['transcript_start'], r['transcript_end'], r['transcript_strand']) else: # exon exon = exons.get( r['_child_key'], Exon(r['child_id'], r['child_seqid'], r['child_start'], r['child_end'], r['child_strand'])) exon.gene_id = r['transcript_id'] exon.transcript_ids[r['transcript_id']] = r['transcript_id'] attribute = r['gtf_attribute'] value = r['value'] if attribute == 'exon_number': exon.exon_number = value exons[r['_child_key']] = exon transcripts = { transcript.ensembl_id: transcript for i, transcript in transcripts.iteritems() } for _id, exon in exons.iteritems(): for _tid in exon.transcript_ids: transcripts[_tid].exons[exon.ensembl_id] = exon cursor.close() conn.close() return transcripts.values()
def get_transcripts_simple(db, location=None, use_strand=False, overlap=True): sql, sql_parameters = location_to_sql(location, use_strand, overlap) sql = "{0} {1} {2}".format(SQL_TRANSCRIPTS_SIMPLE, sql, SQL_TRANSCRIPTS_SIMPLE_ORDER_BY) LOG.debug("SQL:\n{0}".format(sql)) LOG.debug("PARAMETERS: {0}".format(sql_parameters)) conn = sqlite3.connect(db) sqlite3.enable_callback_tracebacks(True) conn.row_factory = sqlite3.Row cursor = conn.cursor() cursor.execute(sql, sql_parameters) transcripts = OrderedDict() exons = OrderedDict() for r in cursor: if r['transcript_id'] == r['child_id']: # transcript if r['_key'] not in transcripts: transcripts[r['_key']] = Transcript(r['transcript_id'], r['transcript_seqid'], r['transcript_start'], r['transcript_end'], r['transcript_strand']) else: # exon exon = exons.get(r['_child_key'], Exon(r['child_id'], r['child_seqid'], r['child_start'], r['child_end'], r['child_strand'])) exon.gene_id = r['transcript_id'] exon.transcript_ids[r['transcript_id']] = r['transcript_id'] attribute = r['gtf_attribute'] value = r['value'] if attribute == 'exon_number': exon.exon_number = value exons[r['_child_key']] = exon transcripts = {transcript.ensembl_id: transcript for i, transcript in transcripts.iteritems()} for _id, exon in exons.iteritems(): for _tid in exon.transcript_ids: transcripts[_tid].exons[exon.ensembl_id] = exon cursor.close() conn.close() return transcripts.values()
def doSearch(self, commands, center, viewBounds = None): results = None db = sqlite.connect(self.layer.filename) db.row_factory = sqlite.Row db.create_function("GeoDistanceSphere",4,GeoDistanceSphere) sqlite.enable_callback_tracebacks(True) print "Search Query:", commands # We assume that all the tables have the same tag set knownTags = [x[1] for x in db.execute("pragma table_info(world_point)")] del knownTags[knownTags.index("way")] del knownTags[knownTags.index("way_area")] del knownTags[knownTags.index("z_order")] parser = SearchParse.SearchParser(knownTags) tokens = parser.parse(commands) print "Parsed search:", str(tokens) query = parsedToSQLite(tokens, center, viewBounds) print "SQL:", query results = list() #FIXME: including spatial_idx without a proper join causes crazy duplicates, this hacks around that if "spatial_idx" in query: sql_queries = [ "select %(tags)s, ST_AsText(Transform(way, 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'point' as type from %(mapName)s_point as osm, idx_%(mapName)s_point_way as spatial_idx where %(query)s", "select %(tags)s, ST_AsText(Transform(ST_StartPoint(way), 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'line' as type from %(mapName)s_line as osm, idx_%(mapName)s_line_way as spatial_idx where %(query)s", "select %(tags)s, ST_AsText(Transform(ST_Centroid(way), 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'polygon' as type from %(mapName)s_polygon as osm, idx_%(mapName)s_polygon_way as spatial_idx where %(query)s", ] else: sql_queries = [ "select %(tags)s, ST_AsText(Transform(way, 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'point' as type from %(mapName)s_point as osm where %(query)s", "select %(tags)s, ST_AsText(Transform(ST_StartPoint(way), 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'line' as type from %(mapName)s_line as osm where %(query)s", "select %(tags)s, ST_AsText(Transform(ST_Centroid(way), 4326)) as point, ST_AsText(Transform(way, 4326)) as geom, 'polygon' as type from %(mapName)s_polygon as osm where %(query)s", ] tag_query = ",".join(["\"%s\"" % t for t in knownTags]) for sql in sql_queries: for row in db.execute(sql % {"mapName":"world", "query":query, "tags":tag_query}): loc = row["point"] try: loc = loc.split("(")[1].split(")")[0].split(" ") except Exception, e: print "Bad geometry (%s) for \"%s\": %s" % (str(e), row["name"], loc) break loc = map(float, loc) distance = GeoDistanceSphere(center.x, center.y, loc[0], loc[1]) result = {"type":row["type"], "name":row["name"], "loc":loc, "distance":distance} if row["type"] == "line": if not row["geom"]: print "Bad geometry for \"%s\": %s" % (row["name"], loc) continue try: points = row["geom"].split("(")[1].split(")")[0].split(",") points = [map(float, p.strip().split(" ")) for p in points] except IndexError: print "Bad geometry for \"%s\": %s" % (row["name"], loc) except: print row["geom"] raise result["line"] = points description = "\n".join(["%s: %s" % (t, row[str(t)]) for t in knownTags if row[str(t)]]) result["description"] = description results.append(result)
from twisted.trial import unittest assert sqlite.version_info >= (2, 1) class DBExcept(Exception): """An error occurred in accessing the database.""" pass class khash(str): """Dummy class to convert all hashes to base64 for storing in the DB.""" # Initialize the database to work with 'khash' objects (binary strings) sqlite.register_adapter(khash, b2a_base64) sqlite.register_converter("KHASH", a2b_base64) sqlite.register_converter("khash", a2b_base64) sqlite.enable_callback_tracebacks(True) class DB: """An sqlite database for storing persistent files and hashes. @type db: L{twisted.python.filepath.FilePath} @ivar db: the database file to use @type conn: L{pysqlite2.dbapi2.Connection} @ivar conn: an open connection to the sqlite database """ def __init__(self, db): """Load or create the database file. @type db: L{twisted.python.filepath.FilePath} @param db: the database file to use
class DBExcept(Exception): """An error occurred in accessing the database.""" pass class khash(str): """Dummy class to convert all hashes to base64 for storing in the DB.""" # Initialize the database to work with 'khash' objects (binary strings) sqlite.register_adapter(khash, b2a_base64) sqlite.register_converter("KHASH", a2b_base64) sqlite.register_converter("khash", a2b_base64) sqlite.enable_callback_tracebacks(True) class DB: """An sqlite database for storing persistent files and hashes. @type db: L{twisted.python.filepath.FilePath} @ivar db: the database file to use @type conn: L{pysqlite2.dbapi2.Connection} @ivar conn: an open connection to the sqlite database """ def __init__(self, db): """Load or create the database file. @type db: L{twisted.python.filepath.FilePath} @param db: the database file to use