Das deutsche Python-Forum

Code: Alles auswählen

#!/usr/bin/env python 
# -*- coding: iso-8859-15 -*- 

import os 
import sys 
import sqlite3 

FILENAME = "hits.sdb3" 
contig_file = open("all_hits.txt", 'r')

def parse_cseq(fh):
    for line in fh:
        line = line.replace('\n', '')
        yield line.split('\t')

def create_db_structure(conn): 
    sql = """CREATE TABLE contigs 
                          (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
                          code TEXT,
                           name TEXT,
                           seq TEXT,
                           start NUM,
                           end NUM,
                           sub_start NUM,
                           sub_end NUM)
                   """
    conn.execute(sql) 
    conn.commit() 
    
    
    conn.executemany("""INSERT INTO contigs
                               (code, name, seq, start, end, sub_start, sub_end)
                               VALUES (?, ?, ?, ? ,? , ?, ?)
                           """, parse_cseq(contig_file))
    
    conn.commit()

    
def select_all_name(conn, sbj): 
    sql = """SELECT COUNT(c.code) AS counting, c.name
             FROM contigs AS c
             WHERE c.name  = ? 
             AND c.code='ALL'
             ORDER BY counting DESC
             """
    cur = conn.cursor() 
    cur.execute(sql, (sbj,)) 
    return cur
    
def main(): 
    if os.path.isfile(FILENAME): 
        os.remove(FILENAME) 
    conn = sqlite3.connect(FILENAME) 
    create_db_structure(conn)
    all = ['Contig5535', 'Contig5534']

    for a in all:
        a = a.replace('\n', '')
        cursor_all = select_all_name(conn, a)
        for line in cursor_all:
            print line

    conn.close()


if __name__ == "__main__": 
    main()
    

Code: Alles auswählen

sqlite> SELECT COUNT(c.code) AS counting, c.name
   ...> FROM contigs AS c
   ...> WHERE c.code = 'ALL'
   ...> GROUP BY c.name
   ...> ORDER BY counting DESC
   ...> ;
16|Contig5534
4|Contig5535