Import File to sqlite script

写的一个 Python 的练手的小工具。
已有一个单词的数据表，其中有单词、音标、意思、发音，发音的音频文件放在一个目录下，文件名为[word].mp3。现要将这些音频文件导入表中；使用 Tkinter 做 GUI 界面。

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys
import pdb
import tkMessageBox

import Tkinter as tk
import ttk
import tkFileDialog
from Tkinter import Tk, Text, Entry, OptionMenu, BOTH, W, N, E, S, StringVar
from ttk import Frame, Button, Label, Style

print 'sqlite version: %s' % lite.version


def readFile(filePath):
    try:
        fin = open(filePath, "rb")
        return fin.read()

    except IOError, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)

    finally:
        if fin:
            fin.close()


class SqliteUtil:
    
    def __init__(self, sqliteFile):
        self.conn = lite.connect(sqliteFile)
        self.cur = self.conn.cursor()
                
    def fetchall(self, sqlStmt):
        self.cur.execute(sqlStmt)
        return self.cur.fetchall()
                
    def fetchone(self, sqlStmt):
        self.cur.execute(sqlStmt)
        return self.cur.fetchone()
        
    def setBinData(self, tableName, binColumnName, filePath, whereClause):
        try:
            data = readFile(filePath)
            binData = lite.Binary(data)
            sql = 'UPDATE %s SET %s=? WHERE %s' % (tableName, binColumnName, whereClause)
            print sql
            self.cur.execute(sql, (binData,) )
            print 'Affected rows using sqlite.Binary(): %d' % self.cur.rowcount
            self.cur.execute(sql, [buffer(data)])
            print 'Affected rows using buffer(): %d' % self.cur.rowcount
            self.conn.commit()

        except lite.Error, e:
            if self.conn:
                self.conn.rollback()

            print "Error %s:" % e.args[0]
            sys.exit(1)

        #finally:
            #if conn:
                #conn.close()

class WordFrame(Frame):
    
    def __init__(self, parent):
        Frame.__init__(self, parent)
        self.parent = parent
        self.initUI()
        self.sqliteUtil = None
        
    @staticmethod
    def refreshOptionMenu(om, oldVarChoice, newChoices):
        # Reset var and delete all old options
        oldVarChoice.set('')
        #oldVarChoice.set(newChoices[0])
        om['menu'].delete(0, 'end')

        # Insert list of new options (tk._setit hooks them up to var)
        for choice in newChoices:
            om['menu'].add_command(label=choice, command=tk._setit(oldVarChoice, choice))
        
    def browseSqlite(self):
        ftypes = [('Sqlite files', '*.sqlite'), ('All files', '*')]
        dlg = tkFileDialog.Open(self, filetypes=ftypes)
        sqliteFile = dlg.show()

        if sqliteFile != '':
            self.varFilename.set(sqliteFile)
            
            self.sqliteUtil = SqliteUtil(sqliteFile)
            tables = self.sqliteUtil.fetchall("SELECT name FROM sqlite_master WHERE type='table'")
            #self.varTable.set(tuple(map(lambda x: x[0], tables)))
            self.refreshOptionMenu(self.omTables, self.varTable, map(lambda x: x[0], tables))
            
            # fetch the cols
            cols = self.sqliteUtil.fetchall('PRAGMA table_info(%s)' % (tables[0]))
            self.refreshOptionMenu(self.omCols, self.varCol, map(lambda x: x[1], cols))

            # pdb.set_trace()
        
    def initUI(self):
        self.parent.title("导入音频数据")
        self.style = ttk.Style()
        self.style.theme_use("default")
        self.pack(fill=BOTH, expand=1)
        self.centerWindow()

        # self.columnconfigure(1, weight=1)

        # row 0: file selection
        panelFileSelection = Frame(self)
        Label(panelFileSelection, text="sqlite文件:").pack(side="left", fill=None, expand=False)
        self.varFilename=StringVar()
        self.txtFileName = Entry(panelFileSelection, textvariable=self.varFilename, width=60)
        self.txtFileName.pack(side="left", fill=None, expand=True)

        btnBrowse = Button(panelFileSelection, text="...", command=self.browseSqlite)
        #btnBrowser.grid(row=0, column=4, pady=4)
        btnBrowse.pack(side="right", fill=None, expand=False)
        
        panelFileSelection.grid(row=0, column=0, columnspan=4, sticky="we")

        # Row 1: word table/audio column name
        self.varTable = StringVar(self)
        Label(self, text="表名:").grid(row=1, sticky=W, padx=5, pady=4)
        self.omTables = OptionMenu(self, self.varTable, '')
        self.omTables.grid(row=1, column=1, sticky=E+W)
        self.omTables['menu'].bind("<Unmap>", self.onSelectTable)
        
        self.varCol = StringVar(self)
        Label(self, text="列名:").grid(row=1, column=2, sticky=E, padx=5, pady=4)
        self.omCols = OptionMenu(self, self.varCol, '')
        self.omCols.grid(row=1, column=3, sticky=E+W)

        # Row 3: close/import buttons
        btnImport = Button(self, text="导入", command=self.importAudios)
        btnImport.grid(row=2, column=2, padx=5, pady=5)
        
        btnClose = Button(self, text="关闭", command=self.quit)
        btnClose.grid(row=2, column=1, padx=5, pady=5)
        
    def quit(self):
        print 'in quit()'
        print self.sqliteUtil
        if not (self.sqliteUtil is None) and not (self.sqliteUtil.conn is None):
            print 'to close conn'
            self.sqliteUtil.conn.close()
        print 'to exit()'
        sys.exit(0)
        
    def importAudios(self):
        # tkMessageBox.showinfo("col: ", self.varCol.get())
        
	# use loop here
        audioFile = 'C:/Users/fouding/Desktop/fetion_received_files/a bas.mp3'
        self.sqliteUtil.setBinData(self.varTable.get(), self.varCol.get(), audioFile, "word='think'")


    def onSelectTable(self, val):
        print 'onSelectTable'
        sender = val.widget
        tblName = sender.get(sender.curselection())
        
        # fetch the cols
        cols = self.sqliteUtil.fetchall('PRAGMA table_info(%s)' % (tblName))
        self.refreshOptionMenu(self.omCols, self.varCol, map(lambda x: x[1], cols))

    def centerWindow(self):
        w = 500
        h = 200
        
        sw = self.parent.winfo_screenwidth()
        sh = self.parent.winfo_screenheight()

        x = (sw - w) / 2
        y = (sh - h) / 2

        self.parent.geometry('%dx%d+%d+%d' % (w, h, x, y))

def main():
    root = tk.Tk()
    app = WordFrame(root)
    root.mainloop()

if __name__ == '__main__':
    main()