Thursday, March 2, 2017

python sqlite3 sample

#!/usr/bin/python

import sqlite3
import os


def checkdb(database):
    if os.path.isfile(database) != True:
        conn = sqlite3.connect(database)
        c = conn.cursor()
        c.execute('''CREATE TABLE abbr(id integer primary key AUTOINCREMENT,abv text, deff text)''')
        #c.execute('''CREATE TABLE abbr(abv text, deff text)''')
        conn.close()

def insertr(dlist, database):
    abv = dlist[0].strip()
    deff = dlist[1].strip()
    #print abv
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("select * from abbr where abv=?", (abv,))
    rows = c.fetchall()
    #print dir(rows)
   
    #print rows.rowcount
    if len(rows) < 1:
        #print "insert"
        c.execute("INSERT INTO abbr(abv,deff) VALUES (?,?)",(abv,deff))
        conn.commit()
    conn.close()
       
   
def querydb(database):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("select * from abbr")
    rows = c.fetchall()
    #print rows
    for i in rows:
        print i
    conn.close()
def delr(rid):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("delete from abbr where id=?", (str(rid),))
    conn.commit()
    conn.close()
def delrr(rid):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("delete from abbr where id>?", (str(rid),))
    conn.commit()
    conn.close()

def fmoneline(ttext):
    lr = []
    lt = ttext.strip().split("\n")
    part1 = lt[:len(lt)/2]
    part2 = lt[len(lt)/2:]
    for i,j in zip(part1,part2):
        r =  [i,j]
        lr.append(r)
    return lr
def fmtwoline(ttext):
    lr = []
    lt = ttext.strip().split("\n")
    for i in range(0,len(lt), 2):
        r = [lt[i], lt[i + 1]]
        lr.append(r)
       
    return lr
def fmoneline2(ttext):
    lr = []
    lt= ttext.strip().split("\n")
    r = [lt[0], " ".join(lt[1:])]
    lr.append(r)
    return lr

database ="data.db"
checkdb(database)

insertr(["b","bf"],database)

querydb(database)

#delr(1)

t="""
LO-TTP
 Lower Order Trail Termination Point, also called
Network Access Point (NAP)

"""
#record = fmoneline(t)
#record = fmtwoline(t)
record = fmoneline2(t)
for i in record:
    print i
    insertr(i,database)
   
#delrr(31)

No comments:

Post a Comment