创建:
create_Email = "CREATE TABLE if not exists emails (\n\
id INTEGER NOT NULL,\n\
user VARCHAR(64),\n\
email VARCHAR(64),\n\
PRIMARY KEY (id)\n\
);"
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)