2018.5.8 python操纵sqlite数据库

创建：

create_Email = "CREATE TABLE if not exists emails (
id INTEGER NOT NULL,
user VARCHAR(64),
email VARCHAR(64),
PRIMARY KEY (id)
);"

try:

sqlite_conn=sqlite3.connect(DB_SQLITE_NAME)

sqlite_cursor.execute(create_Email)
sqlite_conn.commit()

except sqlite3.Error,e:
traceback.print_exc()
sqlite_conn.rollback()
finally:
sqlite_conn.close()

面向对象：
class Email(object):
  """docstring for Email"""
  def __init__(self):
      self.emails = []
      lines=self._get()
      if lines != None and lines != 'failed':
for line in lines:
  email = {}
  email['id'] = line[0]
  email['user'] = line[1]
  email['email'] = line[2]
  self.emails.append(email)

  def _list_all_member(self):
      tmp_str=''
      for name,value in vars(self).items():
          if value != None:
              if type(value) == int:
                  tmp_str=tmp_str+name+'='+str(value)+','
              else:
                  tmp_str=tmp_str+name+'='+"'"+str(value)+"'"+','
      return tmp_str

  def _list_all_member_insert(self,email):
      tmp_str=''
      value_str=' VALUES ('
      for name,value in email.items():
      if value != None:
          tmp_str+=name+','
          if type(value)==int:
          value_str+=str(value)+','
          else:
          value_str+="'"+str(value)+"'"+','
      tmp_str=tmp_str.strip(',')+')'
      value_str=value_str.strip(',')+')'
  return tmp_str,value_str

  def _get(self):
  try:
   rows = []
      sqlite_conn=sqlite3.connect(DB_SQLITE_NAME)
      sqlite_cursor=sqlite_conn.cursor()
      sql_select="SELECT * FROM EMAILS;"
      sqlite_cursor.execute(sql_select)
      for row in sqlite_cursor:
          rows.append(row)
      return rows
  except sqlite3.Error,e:
      return 'failed'
      finally:
      sqlite_conn.close()

  def delete(self,id):
  for email in self.emails:
      if email['id'] == id:
          self.emails.remove(email)
          sql_delete= "DELETE FROM EMAILS WHERE id = '" + id + "';"
          local_sql_exe(sql_delete)

  def add(self,email):
      self.emails.append(email)
      sql_insert='INSERT INTO EMAILS ( '
              name_str,value_str=self._list_all_member_insert(email)
      sql_insert+=name_str+value_str
      local_sql_exe(sql_insert)

  def clear(self):
      self.emails = []
   sql_delete= "DELETE FROM EMAILS;"
      local_sql_exe(sql_delete)

调用:

from sqlite import Email

email= Email()

email.emails

email.add(email)

email.delete(id)

一边喊着救命，一边享受沉沦。