CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!
CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!
Path: blob/master/Day36-45/code/contact/main.py
Views: 729
"""1-- 创建名为address的数据库2create database address default charset utf8;34-- 切换到address数据库5use address;67-- 创建联系人表tb_contacter8create table tb_contacter9(10conid int auto_increment comment '编号',11conname varchar(31) not null comment '姓名',12contel varchar(15) default '' comment '电话',13conemail varchar(255) default'' comment '邮箱',14primary key (conid)15);16"""17import pymysql1819INSERT_CONTACTER = """20insert into tb_contacter (conname, contel, conemail)21values (%s, %s, %s)22"""23DELETE_CONTACTER = """24delete from tb_contacter where conid=%s25"""26UPDATE_CONTACTER = """27update tb_contacter set conname=%s, contel=%s, conemail=%s28where conid=%s29"""30SELECT_CONTACTERS = """31select conid as id, conname as name, contel as tel, conemail as email32from tb_contacter limit %s offset %s33"""34SELECT_CONTACTERS_BY_NAME = """35select conid as id, conname as name, contel as tel, conemail as email36from tb_contacter where conname like %s37"""38COUNT_CONTACTERS = """39select count(conid) as total from tb_contacter40"""414243class Contacter(object):4445def __init__(self, id, name, tel, email):46self.id = id47self.name = name48self.tel = tel49self.email = email505152def input_contacter_info():53name = input('姓名: ')54tel = input('手机: ')55email = input('邮箱: ')56return name, tel, email575859def add_new_contacter(con):60name, tel, email = input_contacter_info()61try:62with con.cursor() as cursor:63if cursor.execute(INSERT_CONTACTER,64(name, tel, email)) == 1:65print('添加联系人成功!')66except pymysql.MySQLError as err:67print(err)68print('添加联系人失败!')697071def delete_contacter(con, contacter):72try:73with con.cursor() as cursor:74if cursor.execute(DELETE_CONTACTER, (contacter.id, )) == 1:75print('联系人已经删除!')76except pymysql.MySQLError as err:77print(err)78print('删除联系人失败!')798081def edit_contacter_info(con, contacter):82name, tel, email = input_contacter_info()83contacter.name = name or contacter.name84contacter.tel = tel or contacter.tel85contacter.email = email or contacter.email86try:87with con.cursor() as cursor:88if cursor.execute(UPDATE_CONTACTER,89(contacter.name, contacter.tel,90contacter.email, contacter.id)) == 1:91print('联系人信息已经更新!')92except pymysql.MySQLError as err:93print(err)94print('更新联系人信息失败!')959697def show_contacter_detail(con, contacter):98print('姓名:', contacter.name)99print('手机号:', contacter.tel)100print('邮箱:', contacter.email)101choice = input('是否编辑联系人信息?(yes|no)')102if choice == 'yes':103edit_contacter_info(con, contacter)104else:105choice = input('是否删除联系人信息?(yes|no)')106if choice == 'yes':107delete_contacter(con, contacter)108109110def show_search_result(con, cursor):111contacters_list = []112for index, row in enumerate(cursor.fetchall()):113contacter = Contacter(**row)114contacters_list.append(contacter)115print('[%d]: %s' % (index, contacter.name))116if len(contacters_list) > 0:117choice = input('是否查看联系人详情?(yes|no)')118if choice.lower() == 'yes':119index = int(input('请输入编号: '))120if 0 <= index < cursor.rowcount:121show_contacter_detail(con, contacters_list[index])122123124def find_all_contacters(con):125page, size = 1, 5126try:127with con.cursor() as cursor:128cursor.execute(COUNT_CONTACTERS)129total = cursor.fetchone()['total']130while True:131cursor.execute(SELECT_CONTACTERS,132(size, (page - 1) * size))133show_search_result(con, cursor)134if page * size < total:135choice = input('继续查看下一页?(yes|no)')136if choice.lower() == 'yes':137page += 1138else:139break140else:141print('没有下一页记录!')142break143except pymysql.MySQLError as err:144print(err)145146147def find_contacters_by_name(con):148name = input('联系人姓名: ')149try:150with con.cursor() as cursor:151cursor.execute(SELECT_CONTACTERS_BY_NAME,152('%' + name + '%', ))153show_search_result(con, cursor)154except pymysql.MySQLError as err:155print(err)156157158def find_contacters(con):159while True:160print('1. 查看所有联系人')161print('2. 搜索联系人')162print('3. 退出查找')163choice = int(input('请输入: '))164if choice == 1:165find_all_contacters(con)166elif choice == 2:167find_contacters_by_name(con)168elif choice == 3:169break170171172def main():173con = pymysql.connect(host='1.2.3.4', port=3306,174user='yourname', passwd='yourpass',175db='address', charset='utf8',176autocommit=True,177cursorclass=pymysql.cursors.DictCursor)178while True:179print('=====通讯录=====')180print('1. 新建联系人')181print('2. 查找联系人')182print('3. 退出系统')183print('===============')184choice = int(input('请选择: '))185if choice == 1:186add_new_contacter(con)187elif choice == 2:188find_contacters(con)189elif choice == 3:190con.close()191print('谢谢使用, 再见!')192break193194195if __name__ == '__main__':196main()197198199