"""
通讯录
"""
import MySQLdb

conn = None


def ensure_connection():
 global conn
 if not conn:
 conn = MySQLdb.connect(host='47.104.31.138', port=3306,
 user='hellokitty', password='Hellokitty.618',
 database='python2005', charset='utf8mb4')
 return conn


def add_contact():
 """添加联系人"""
 ensure_connection()
 name = input('姓名: ').strip()
 tel = input('电话: ').strip()
 email = input('邮箱: ').strip()
 company = input('公司: ').strip()
 memo = input('备注: ').strip()
 try:
 with conn.cursor() as cursor:
 affected_rows = cursor.execute(
 'insert into tb_contact (name, tel, email, company, memo) '
 'values (%s, %s, %s, %s, %s)',
 (name, tel, email, company, memo)
 )
 if affected_rows == 1:
 print('新增联系人成功!')
 conn.commit()
 except MySQLdb.MySQLError as err:
 print(err)
 conn.rollback()


def show_all_contacts():
 """显示所有联系人"""
 ensure_connection()
 with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
 cursor.execute('select id, name from tb_contact')
 display_contacts(cursor.fetchall())


def display_contacts(all_rows):
 """显示联系人名字清单"""
 ensure_connection()
 for index, row_dict in enumerate(all_rows):
 print(f'[{index + 1}]: {row_dict["name"]}')
 try:
 choice = int(input('输入编号查看联系人详情: '))
 if 0 < choice <= len(all_rows):
 contact_id = all_rows[choice - 1]['id']
 show_contact_detail(contact_id)
 except ValueError:
 pass


def delete_contact(contact_id):
 """删除联系人"""
 ensure_connection()
 try:
 with conn.cursor() as cursor:
 affected_rows = cursor.execute(
 'delete from tb_contact where id=%s', (contact_id, )
 )
 if affected_rows == 1:
 print('联系人信息已删除!')
 conn.commit()
 except MySQLdb.MySQLError as err:
 print(err)
 conn.rollback()


def edit_contact(contact_id, contact_dict):
 """编辑联系人"""
 ensure_connection()
 name = input('姓名: ').strip() or contact_dict['name']
 tel = input('电话: ').strip() or contact_dict['tel']
 email = input('邮箱: ').strip() or contact_dict['email']
 company = input('公司: ').strip() or contact_dict['company']
 memo = input('备注: ').strip() or contact_dict['memo']
 try:
 with conn.cursor() as cursor:
 affected_rows = cursor.execute(
 'update tb_contact set name=%s, tel=%s, email=%s, company=%s, memo=%s '
 'where id=%s',
 (name, tel, email, company, memo, contact_id)
 )
 if affected_rows == 1:
 print('联系人信息已修改!')
 conn.commit()
 except MySQLdb.MySQLError as err:
 print(err)
 conn.rollback()


def show_contact_detail(contact_id):
 """显示联系人的详情"""
 ensure_connection()
 with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
 cursor.execute(
 'select name, tel, email, company, memo from tb_contact where id=%s',
 (contact_id, )
 )
 contact_dict = cursor.fetchone()
 print(f'\n姓名:{contact_dict["name"]}')
 print(f'电话:{contact_dict["tel"]}')
 print(f'邮箱:{contact_dict["email"]}')
 print(f'公司:{contact_dict["company"]}')
 print(f'备注:{contact_dict["memo"]}', end='\n\n')
 print('1. 删除联系人')
 print('2. 编辑联系人')
 try:
 choice = int(input('请选择: '))
 if choice == 1:
 confirm = input(f'确定要删除联系人{contact_dict["name"]}的信息?(yes/no)')
 if confirm.strip().lower() == 'yes':
 delete_contact(contact_id)
 elif choice == 2:
 edit_contact(contact_id, contact_dict)
 except ValueError:
 pass


def search_contacts():
 """搜索联系人"""
 ensure_connection()
 keyword = input('关键词: ')
 keyword = f'%{keyword}%'
 with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
 cursor.execute(
 'select id, name from tb_contact '
 'where name like %s or company like %s or memo like %s',
 (keyword, keyword, keyword)
 )
 display_contacts(cursor.fetchall())


def list_contacts():
 print('1. 查看所有联系人')
 print('2. 搜索联系人')
 print('3. 返回')
 choice = 0
 while choice <= 0 or choice > 3:
 try:
 choice = int(input('请选择: '))
 except ValueError:
 pass
 if choice == 1:
 show_all_contacts()
 elif choice == 2:
 search_contacts()


def main():
 print('欢迎使用通讯录程序'.center(20, '='))
 while True:
 print('1. 新增联系人')
 print('2. 查看联系人')
 print('3. 退出程序')
 choice = 0
 while choice <= 0 or choice > 3:
 try:
 choice = int(input('请选择: '))
 except ValueError:
 pass
 if choice == 1:
 add_contact()
 elif choice == 2:
 list_contacts()
 else:
 print('程序结束'.center(20, '='))
 break


if __name__ == '__main__':
 main()