The address book written by Python realizes fuzzy query function through database storage

1. Requirements

The address book is stored in the database, and it is required to query by name/telephone number. There is only one input entry for the query condition, which automatically identifies whether the input is name or number, and allows fuzzy query.

2. Implement functionality

It can be operated by inputting instructions.

(1) First enter "add" to add contact information to the address book.

sql1 =
'insert into TA(ID,NAME,AGE,ADDRESS,TELENUMBER)'
sql1 += 'values("%d","%s","%d","%s","%s");' % (ID,name, age, address, telenumber)
conn.execute(sql1)
conn.commit() #  Submit, otherwise you cannot save 

(2) Enter "delete" to delete the specified contact information.

Enter name deletion:

cursor = c.execute( "SELECT name from TA where name = '%s';" %i)

Enter the phone number to delete:

cursor = c.execute( "SELECT name from TA where telenumber= '%s';" % i)

(3) Enter "search", you can enter contact person or telephone number to inquire contact person information, which realizes fuzzy inquiry and accurate inquiry.

Enter name query:

sql1 = "SELECT id,name,age, address, telenumber from TA where telenumber like '%" + i +
"%'"
cursor = c.execute(sql1)

Enter the telephone number for enquiry:

sql1= "SELECT id,name,age, address, telenumber from TA where name like '%" +i+
"%'"
cursor = c.execute(sql1)

(4) Enter "searchall" to query all contact information.

cursor = c.execute( "SELECT id, name, age, address, telenumber from TA" )

3. Database sqlite3

Python comes with a lightweight relational database sqlite. This 1 database uses SQL language. As a back-end database, sqlite can be used to build websites with Python, or make tools with data storage requirements. sqlLite is also widely used in other fields, such as HTML5 and mobile terminal. The interface to this database is provided by sqlite3 in the Python standard library. Therefore, this time, sqlite3 database is used to store contact information of address book.

Source code:

import sqlite3
import re
# Open a local database for storing user information 
conn = sqlite3.connect('mysql_telephone_book.db')
c = conn.cursor()
# Create a table under the database, and the code for creating the table is in the 1 It needs to be commented out after the second execution, otherwise executing the program again will 1 Direct prompt: The table already exists 
'''c.execute("CREATE TABLE TA
    (ID INT PRIMARY KEY   NOT NULL,
    NAME      TEXT  NOT NULL,
    AGE      INT   NOT NULL,
    ADDRESS    CHAR(50),
    TELENUMBER     TEXT);")'''

conn.commit()# Commit the current transaction 
# Add user information 
def insert():
  global conn
  c = conn.cursor()
  ID=int(input(" Please enter id No.: "))
  name=input(" Please enter a name: ")
  age=int(input(" Please enter an age: "))
  address=input(" Please enter the address: ")
  telenumber=input(" Please enter a phone number :")
  sql1 = 'insert into TA(ID,NAME,AGE,ADDRESS,TELENUMBER)'
  sql1 += 'values("%d","%s","%d","%s","%s");' % (ID,name, age, address, telenumber)
  conn.execute(sql1)
  conn.commit()# Submit, otherwise you cannot save 
  print(" Submission succeeded! ! ")
# Delete user information 
def delete():
  global conn
  c=conn.cursor()
  i = input(" Please enter the name or phone number of the contact you want to delete :")
  if len(i) < 11:
    cursor = c.execute("SELECT name from TA where name = '%s';"%i)
    for row in cursor:
      if i == row[0]:
        c.execute("DELETE from TA where name ='%s';"%i)
        conn.commit()
        print(" Successfully deleted contact information! ! ")
        break
    else:
      print(" This contact does not exist! ! ")
  else :
    cursor = c.execute("SELECT name from TA where telenumber= '%s';" % i)
    for row in cursor:
      if i == row[0]:
        c.execute("DELETE from TA where telenumber ='%s';" % i)
        conn.commit()
        print(" Successfully deleted contact information! ! ")
        break
    else:
      print(" The phone number is wrong! ! ")
# Query user information 
def search():
  global conn
  c = conn.cursor()
  i = input(" Please enter the name or phone number of the contact you want to query :")
  if i.isnumeric():
    sql1 = "SELECT id,name,age, address, telenumber from TA where telenumber like '%" + i + "%'"
    cursor = c.execute(sql1)
    res=cursor.fetchall()
    if len(res)!=0:
      for row in res:
        print("id:{0}".format(row[0]))
        print(" Name :{0}".format(row[1]))
        print(" Age :{0}".format(row[2]))
        print(" Address :{0}".format(row[3]))
        print(" Telephone number :{0}".format(row[4]))
    else:
      print(" There is no such phone number! ! ")
  else:
    sql1="SELECT id,name,age, address, telenumber from TA where name like '%"+i+"%'"
    cursor = c.execute(sql1)
    res=cursor.fetchall()
    if len(res) == 0:
      print(" This contact does not exist! ! ")
    else:
      for row in res:
        print("id:{0}".format(row[0]))
        print(" Name :{0}".format(row[1]))
        print(" Age :{0}".format(row[2]))
        print(" Address :{0}".format(row[3]))
        print(" Telephone number :{0}".format(row[4]))
# Show all user information 
def showall():
  global conn
  c = conn.cursor()
  cursor = c.execute("SELECT id, name, age, address, telenumber from TA")
  for row in cursor:
    print("id:{0}".format(row[0]))
    print(" Name :{0}".format(row[1]))
    print(" Age :{0}".format(row[2]))
    print(" Address :{0}".format(row[3]))
    print(" Telephone number :{0}".format(row[4]))
print(" The instructions are as follows: \n1. Input \"add\" Add contact information to the address book \n2. Input \"delete\" Delete the specified contact information in the address book  \n3. Input \"searchall\" Query all users in the address book  \n4. Input \"search\" Find information by name or mobile phone number  ")
while 1:
  temp = input(" Please enter instructions: ")
  if temp == "add":
    insert()
    print(" Add successfully! ")
    temp1=input(" Do you want to continue with the address book? (y or n)")
    if temp1=="n":
      print(" Successful exit! ! ")
      break
    else:
      continue
  elif temp=="delete":
    delete()
    temp1 = input(" Do you want to continue with the address book? (y or n)")
    if temp1 == "n":
      print(" Successful exit! ! ")
      break
    else:
      continue
  elif temp=="searchall":
    showall()
    temp1 = input(" Do you want to continue with the address book? (y or n)")
    if temp1 == "n":
      print(" Successful exit! ! ")
      break
    else:
      continue
  elif temp=="search":
    search()
    temp1 = input(" Do you want to continue with the address book? (y or n)")
    if temp1 == "n":
      print(" Successful exit! ! ")
      break
    else:
      continue
  else:
    print(" Please enter the correct instructions! ! ")
conn.close()# Close the database 

Summarize