runitdb.py

"""
This is a set of SQLite database tools for the file runit.py

create_or_open_db(db_file)
Creates or open a database and keeps it open for use
"""
import os
import sqlite3
def create_or_open_db(db_file):
    db_is_new = not os.path.exists(db_file)
    conn = sqlite3.connect(db_file)
    c = conn.cursor
    if db_is_new:
        #print 'Creating schema'
        c.execute("CREATE VIRTUAL TABLE IF NOT EXISTS runit USING FTS3(target, targetS);")
        conn.commit()
    else:
        pass

    return conn
"""
Insert(db_file, target, targetS)
Inserts data into the database
"""

def Insert(db_file, target, targetS):
    conn = sqlite3.connect(db_file)
    conn.text_factory = lambda x: unicode(x, "utf-8", "ignore")
    c = conn.cursor()
    c.execute('INSERT INTO runit(target, targetS) VALUES(?, ?)',(target, targetS)) 
    conn.commit()
    return conn

"""
View(db_file)
shows the last row entered into the database
"""        
def View(db_file):
    conn = sqlite3.connect(db_file)
    conn.text_factory = lambda x: unicode(x, "utf-8", "ignore")
    c = conn.cursor()    
    c.execute("SELECT rowid, target, targetS FROM runit ORDER BY rowid DESC LIMIT 1;")
    row = c.fetchone()
    c.close();conn.close()
    print row[0],row[1],row[2]
    return row

"""
picks a starting row and the number of rows to print
database = "runit.db"
Start = 10
NumToView= 55
ViewRows(database,NumToView,Start)     
"""        
def ViewRows(database,NumToView,Start):
    conn = sqlite3.connect(database)
    conn.text_factory = lambda x: unicode(x, "utf-8", "ignore")
    c = conn.cursor() 
    for row in c.execute('SELECT rowid, * from runit ORDER BY rowid LIMIT ? OFFSET ?', (NumToView, Start)):    
        print row[0],row[1],row[2]
    c.close();conn.close() 
    return row

"""
delID(database, rowid)
deletes by row id
"""
def delID(database, rowid):
    conn = sqlite3.connect(database)
    conn.text_factory=str 
    c = conn.cursor()
    c.execute("DELETE FROM runit WHERE rowid = ?", (rowid,))
    conn.commit()

    
if __name__ == "__main__":
    db_file = "runit.db"
    create_or_open_db(db_file)    
    View(db_file)        

added ' conn.text_factory = lambda x: unicode(x, "utf-8", "ignore") ' in line 28
to prevent 8 byte errors