描述：
上一篇博客我寫了一個(gè)簡(jiǎn)單的通訊錄，但是還是覺(jué)得不夠完美：
需要輸入ID，雖然ID是主鍵，但是沒(méi)有實(shí)現(xiàn)自增功能；
忘記加電話號(hào)碼了；
如果插入用戶名相同，則后續(xù)的查、改、刪功能受到影響；
所以，我這個(gè)修改版主要修正了以上三點(diǎn)缺陷。具體如下
將用戶ID設(shè)置為自增字段，即就是不用輸入ID，程序會(huì)自動(dòng)幫你將插入的用戶進(jìn)行排序并附上ID，默認(rèn)從1開(kāi)始；SQLite實(shí)現(xiàn)主鍵自增功能，請(qǐng)查看SQLiteAUTOINCREMENT/自動(dòng)遞增這篇博客。創(chuàng)建表時(shí)增加了電話號(hào)碼字段；插入的用戶名不能重復(fù)；好了，廢話不多說(shuō)，附上新代碼，哈哈~~~
#-*- coding:utf-8 -*-import sqlite3#打開(kāi)本地數(shù)據(jù)庫(kù)用于存儲(chǔ)用戶信息conn = sqlite3.connect('MySQL_person.db')#在該數(shù)據(jù)庫(kù)下創(chuàng)建表,創(chuàng)建表的這段代碼在第一次執(zhí)行后需要注釋掉，否則再次執(zhí)行程序會(huì)一直提示：該表已存在conn.execute ('''CREATE TABLE MT_table1( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, PhoneNumber INT NOT NULL);''')print "Table created successfully";#增加用戶信息def insert(): USER_NAME = raw_input('請(qǐng)輸入用戶昵稱:') cursor = conn.execute("SELECT name from MT_table1 where name = '%s';"%USER_NAME) for row in cursor: if USER_NAME == row[0]: print "sorry,改用戶名已存在，請(qǐng)重新輸入用戶名" break else: AGE = input('請(qǐng)輸入年齡:') ADDRESS = raw_input('請(qǐng)輸入用戶地址:') SALARY = input('請(qǐng)輸入用戶薪水:') PhoneNumber = input("請(qǐng)輸入聯(lián)系方式:") #為避免代碼太長(zhǎng)，以下我用字符串的連接特性將代碼縮短 sql1 = 'INSERT INTO MT_table1(NAME,AGE,ADDRESS,SALARY,PhoneNumber)' sql1 += 'VALUES("%s","%d","%s","%d","%d");'%(USER_NAME,AGE,ADDRESS,SALARY,PhoneNumber) conn.execute(sql1) conn.commit() print "Records insert successfully"#刪除用戶信息def delete(): delete_name = raw_input("請(qǐng)輸入所要?jiǎng)h除的聯(lián)系人姓名:") cursor = conn.execute("SELECT name from MT_table1 where name = '%s';"%delete_name) for row in cursor: if delete_name == row[0]: conn.execute("DELETE from MT_table1 where name = '%s';"%delete_name) conn.commit() print "Records delete successfully" break else: print "sorry,不存在該用戶"#修改用戶信息def modify(): update_name = raw_input("請(qǐng)輸入要修改用戶的姓名:") sql6 = "SELECT name from MT_table1 where name = '%s';"%update_name cursor = conn.execute(sql6) for row in cursor: if update_name == row[0]: New_addr = raw_input("請(qǐng)輸入要修改用戶的新地址:") New_age = input("請(qǐng)輸入要修改用戶的新年齡:") New_salary = input("請(qǐng)輸入要修改用戶的新薪水:") New_num = input("輸入要修改用戶的新號(hào)碼:") sql3 = "UPDATE MT_table1 set address = '%s',age = '%d',salary = '%d',PhoneNumber = '%d' where / name = '%s';"%(New_addr,New_age,New_salary,New_num,update_name) conn.execute(sql3) conn.commit() print "修改成功" sql5 = "SELECT id, name, age,address, salary ,PhoneNumber from MT_table1 where name = '%s';"%update_name cursor = conn.execute(sql5) for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "AGE = ",row[2] print "ADDRESS = ", row[3] print "SALARY = ", row[4] print "PhoneNumber = ",row[5], "/n" break else: print "sorry,不存在該用戶信息"#查詢用戶信息def search(): conn = sqlite3.connect('mysql_person.db') search_name = raw_input('請(qǐng)輸入要查詢的用戶姓名') sql2 = "SELECT id,name,age, address, salary ,PhoneNumber from MT_table1 where name= '%s';" % (search_name) cursor = conn.execute(sql2) for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "AGE = ",row[2] print "ADDRESS = ", row[3] print "SALARY = ", row[4] print "PhoneNumber = ",row[5], "/n" break else: print "sorry,沒(méi)有該用戶信息"#顯示所有用戶信息def showall(): cursor = conn.execute("SELECT id, name,age, address, salary ,PhoneNumber from MT_table1") for row in cursor: print "ID = ", row[0] print "NAME = ", row[1] print "AGE = ",row[2] print "ADDRESS = ", row[3] print "SALARY = ", row[4] print "PhoneNumber = ",row[5], "/n" print "Operation done successfully"; cursor = conn.execute("select count(*) from MT_table1;") for row in cursor: print "一共有%d個(gè)用戶"%row[0]def menu(): print '1.新增聯(lián)系人' print '2.刪除聯(lián)系人' print '3.修改聯(lián)系人' print '4.查詢聯(lián)系人' print '5.顯示所有聯(lián)系人' print '6.退出程序' print 'What do you want to do?'while True: menu() x = raw_input('請(qǐng)輸入您的選擇菜單號(hào):') if x == '1': insert() continue if x == '2': delete() continue if x == '3': modify() continue if x == '4': search() continue if x == '5': showall() continue if x == '6': print "謝謝使用！" exit() continue else: print "輸入的選項(xiàng)不存在，請(qǐng)重新輸入！" continue新聞熱點(diǎn)
疑難解答
網(wǎng)友關(guān)注
