描述：
需要输入ID，虽然ID是主键，但是没有实现自增功能；
忘记加电话号码了；
如果插入用户名相同，则后续的查、改、删功能受到影响；
所以，我这个修改版主要修正了以上三点缺陷。具体如下
将用户ID设置为自增字段，即就是不用输入ID，程序会自动帮你将插入的用户进行排序并附上ID，默认从1开始；SQLite实现主键自增功能，请查看SQLiteAUTOINCREMENT/自动递增这篇博客。
创建表时增加了电话号码字段；
插入的用户名不能重复；
好了，废话不多说，附上新代码，哈哈~~~
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
#-*- coding:utf-8 -*-
import sqlite3
#打开本地数据库用于存储用户信息
conn = sqlite3.connect('mysql_person.db')
#在该数据库下创建表,创建表的这段代码在第一次执行后需要注释掉，否则再次执行程序会一直提示：该表已存在
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('请输入用户昵称:')
cursor = conn.execute("SELECT name from MT_table1 where name = '%s';"%USER_NAME)
for row in cursor:
if USER_NAME == row[0]:
print "sorry,改用户名已存在，请重新输入用户名"
break
else:
AGE = input('请输入年龄:')
ADDRESS = raw_input('请输入用户地址:')
SALARY = input('请输入用户薪水:')
PhoneNumber = input("请输入联系方式:")
#为避免代码太长，以下我用字符串的连接特性将代码缩短
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("请输入所要删除的联系人姓名:")
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("请输入要修改用户的姓名:")
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("请输入要修改用户的新地址:")
New_age = input("请输入要修改用户的新年龄:")
New_salary = input("请输入要修改用户的新薪水:")
New_num = input("输入要修改用户的新号码:")
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('请输入要查询的用户姓名')
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,没有该用户信息"
#显示所有用户信息
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个用户"%row[0]
def menu():
print '1.新增联系人'
print '2.删除联系人'
print '3.修改联系人'
print '4.查询联系人'
print '5.显示所有联系人'
print '6.退出程序'
print 'What do you want to do?'
while True:
menu()
x = raw_input('请输入您的选择菜单号:')
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 "输入的选项不存在，请重新输入！"
continue
|
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://blog.csdn.net/mtbaby/article/details/59105813