SQLite Loads CSV to table

Below is an example of basic SQLite operations and load cvs to table.

Please note: self.c.execute(stm,(name,)) in def removeRule()


Otherwise,

Traceback (most recent call last):

File "SQLite.py", line 91, in <module>

sqlite.removeRule('rule1')

File "SQLite.py", line 55, in removeRule

self.c.execute(stm)

sqlite3.OperationalError: no such column: rule1

Because (name,) forces python to make a tuple out of record.

import sqlite3, csv

class SqliteDB:

 def __init__(self, dbFile, tableName):
  self.conn = sqlite3.connect(dbFile)
  self.c = self.conn.cursor()
  self.tableName = tableName

 def createTable(self):
  stm = "CREATE TABLE IF NOT EXISTS {0} \
               (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL)"\
                .format(self.tableName)
  self.c.execute(stm)

 def insertRule(self, rid, name): 
  stm = "INSERT OR IGNORE INTO {0} VALUES (?, ?)"\
                    .format(self.tableName)
  self.c.execute(stm, (rid, name))

 def removeRule(self, name):
    stm = "DELETE FROM {0} WHERE NAME = ?".format(self.tableName)
    self.c.execute(stm,(name,))

 def close(self):
  self.conn.commit()
  self.conn.close()


 def loadCSV(self, csvfile):
  with open(csvfile,'rb') as fin:
      dr = csv.DictReader(fin) # comma is default delimiter
      to_db = [(i['ID'], i['NAME']) for i in dr]
  stm = "INSERT OR IGNORE INTO {0} (ID, NAME) VALUES (?,?)"\
                .format(self.tableName)
  self.c.executemany(stm, to_db)

Reference: https://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html