Create:
Create_email = "CREATE TABLE if not exists emails (\n\
ID INTEGER not null,\n\
User VARCHAR (+), \n\
Email VARCHAR (+), \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 ()
Object-oriented:
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:
&nbs P if email[' id '] = = ID:
& nbsp 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)
Call:
From SQLite import Email
Email= Email ()
Email.emails
Email.add (email)
Email.delete (ID)
2018.5.8 Python manipulating SQLite database