当前位置: 源码素材网 » Python教程 » 详情页

Python 结合MYSQL实现通讯录

  •   时间:2018-12-12
  • 概述:通讯录

 python通讯录程序,在实现方面,数据库使用了mysql,连接形式采用sqlite3,其实程序比较简单,实现了基本的通讯录联系人添加、删除、修改、查看等功能,向初学者演示Python与数据库的一些简单操作技巧,代码中给出了丰富的注释,很有利于学习参考。

import sqlite3
#打开本地数据库用于存储用户信息
conn = sqlite3.connect('mysql_person.db')
print "Opened database successfully";
'''
#在该数据库下创建表,创建表的这段代码在第一次执行后需要注释掉,否则再次执行程序会一直提示:该表已存在
conn.execute("CREATE TABLE MT(ID INT PRIMARY KEY NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL INT NOT NULL)")
print "Table created successfully";
conn.close()#关闭连接
'''
#增加用户信息
def insert():
    ID = input('请输入用户ID:\n'.decode('utf-8').encode('gb2312'))
    NAME = raw_input('请输入用户昵称:\n'.decode('utf-8').encode('gb2312'))
    AGE = input('请输入年龄:\n'.decode('utf-8').encode('gb2312'))
    ADDRESS = raw_input('请输入用户地址:\n'.decode('utf-8').encode('gb2312'))
    SALARY = input('请输入用户薪水:\n'.decode('utf-8').encode('gb2312'))
    sql1 = 'insert into MT(ID,NAME,AGE,ADDRESS,SALARY)'
    sql1 += 'values("%d","%s","%d","%s","%d");'%(ID,NAME,AGE,ADDRESS,SALARY)
    conn.execute(sql1)
    conn.commit()#提交,否则无法保存
    print "Records insert successfully"
#删除用户信息
def delete():
    name = raw_input("请输入所要删除的联系人姓名:".decode('utf-8').encode('gb2312'))
    cursor = conn.execute("SELECT name from MT where name = '%s';"%name)
    for row in cursor:
        if name == row[0]:
            conn.execute("DELETE from MT where name = '%s';"%name)
            conn.commit()
            print "Records delete successfully"
            break
    else:
        print "sorry,不存在该用户".decode('utf-8').encode('gb2312')
#修改用户信息
def modify():
    name = raw_input("请输入要修改用户的姓名:".decode('utf-8').encode('gb2312'))
    sql4 = "SELECT id, name, age,address, salary  from MT where name = '%s';"%name
    cursor = conn.execute(sql4)
    for row in cursor:
        print "ID = ", row[0]
        print "NAME = ", row[1]
        print "AGE = ",row[2]
        print "ADDRESS = ", row[3]
        print "SALARY = ", row[4], "\n"
	def change():
		print '1.修改该用户地址'.decode('utf-8').encode('gb2312')
		print '2.修改该用户年龄'.decode('utf-8').encode('gb2312')
		print '3.修改该用户薪水'.decode('utf-8').encode('gb2312')
		print '4.修改完毕,退出'.decode('utf-8').encode('gb2312')
	while True:
		change()
		a=raw_input('请输入您的选择菜单号:'.decode('utf-8').encode('gb2312'))
		if a=='1':
			A = raw_input("请输入要修改用户的新地址:".decode('utf-8').encode('gb2312'))
			sql31 = "UPDATE MT set address = '%s' where name = '%s';"%(A,name)
			conn.execute(sql31)
			continue
		if a=='2':
			B = input("请输入要修改用户的新年龄:".decode('utf-8').encode('gb2312'))
			sql32 = "UPDATE MT set age = '%d' where name = '%s';"%(B,name)
			conn.execute(sql32)
			continue
		if a=='3':
			C = input("请输入要修改用户的新薪水:".decode('utf-8').encode('gb2312'))
			sql33 = "UPDATE MT set salary = '%d' where name = '%s';"%(C,name)
			conn.execute(sql33)
			continue
		if a=='4':
			print "感谢使用!".decode('utf-8').encode('gb2312')
			break
	conn.commit()
	print "修改成功".decode('utf-8').encode('gb2312')
	sql5 = "SELECT id, name, age,address, salary  from MT where name = '%s';"%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], "\n"
#查询用户信息
conn = sqlite3.connect('mysql_person.db')
def search():
    conn = sqlite3.connect('mysql_person.db')
    name = raw_input('请输入要查询的用户姓名:'.decode('utf-8').encode('gb2312'))
    sql2 = "SELECT id,name,age, address, salary from MT where name= '%s';" % (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], "\n"
        break
    else:
        print "sorry,没有该用户信息".decode('utf-8').encode('gb2312')
#显示所有用户信息
def showall():
    cursor = conn.execute("SELECT id, name, age, address, salary  from MT")
    for row in cursor:
        print "ID = ", row[0]
        print "NAME = ", row[1]
        print "AGE = ",row[2]
        print "ADDRESS = ", row[3]
        print "SALARY = ", row[4], "\n"
    print "Operation done successfully";
    cursor = conn.execute("select count(*) from MT;")
    for row in cursor:
        print "一共有%d个用户".decode('utf-8').encode('gb2312')%row[0]
def menu():
    print '1.新增联系人'.decode('utf-8').encode('gb2312')
    print '2.删除联系人'.decode('utf-8').encode('gb2312')
    print '3.修改联系人'.decode('utf-8').encode('gb2312')
    print '4.查询联系人'.decode('utf-8').encode('gb2312')
    print '5.显示所有联系人'.decode('utf-8').encode('gb2312')
    print '6.退出程序'.decode('utf-8').encode('gb2312')
    print 'What do you want to do?'
while True:
    menu()
    x = raw_input('请输入您的选择菜单号:'.decode('utf-8').encode('gb2312'))
    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 "谢谢使用!".decode('utf-8').encode('gb2312')
        exit()
        continue
    else:
        print "输入的选项不存在,请重新输入!".decode('utf-8').encode('gb2312')
        continue

    相关声明:

      若“Python 结合MYSQL实现通讯录”有损您的权益,请告之我们删除内容。
      部分文章来源于网络,版权归原作者所有。