Info

Listing 14-3 (continued)

# These SQL statements may need to be tweaked for your database

# (They work with MySQL)

CREATE_EMPLOYEE_SQL = CREATE TABLE EMPLOYEE (

EMPLOYEE_ID INT NOT NULL, FIRST_NAME VARCHAR(20) NOT NULL, LAST_NAME VARCHAR(20) NOT NULL, MANAGER_ID INT )"""

DROP_EMPLOYEE_SQL="DROP TABLE EMPLOYEE" INSERT_SQL = "INSERT INTO EMPLOYEE VALUES "

def SoundexEncoding(str):

Return the 4-character SOUNDEX code for a string. Take first letter, then encode subsequent consonants as numbers. Ignore repeated codes (e.g MM codes as 5, not 55), unless separated by a vowel (e.g. SOS codes as 22)

if (str==None or str==""): return None str = str.upper() # ignore case! SoundexCode=str[0] LastCode=SoundexDict[str[0]] for char in str[1:]:

CurrentCode=SoundexDict[char] if (CurrentCode=="8"):

pass # Don't include, or separate used consonants elif (CurrentCode=="7"):

LastCode=None # Include consonants after vowels elif (CurrentCode!=LastCode): # Skip doubled letters

SoundexCode+=CurrentCode if len(SoundexCode)==4: break # limit to 4 characters # Pad with zeroes (e.g. Lee is L000): SoundexCode += "0"*(4-len(SoundexCode)) return SoundexCode

# Create the EMPLOYEE table def CreateTable(Conn):

NewCursor=Conn.cursor() try:

NewCursor.execute(DROP_EMPLOYEE_SQL) NewCursor.execute(CREATE_EMPLOYEE_SQL) finally:

NewCursor.close()

# insert a new employee into the table def CreateEmployee(Conn,DataValues):

NewCursor=Conn.cursor() try:

NewCursor.execute(INSERT_SQL+DataValues) finally:

NewCursor.close()

# Do a sounds-like query on a name def PrintUsersLike(Conn,Name):

if (Name==None or Name==""): return print "Users with last name similar to",Name+":"

SoundexName = SoundexEncoding(Name)

QuerySQL = "SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM" QuerySQL+= " EMPLOYEE WHERE LAST_NAME LIKE '"+Name[0]+"%'" NewCursor=Conn.cursor() try:

NewCursor.execute(QuerySQL)

for EmployeeRow in NewCursor.fetchall():

if (SoundexEncoding(EmployeeRow[2])==SoundexName): print EmployeeRow finally:

NewCursor.close()

# pass clear_auto_commit=0, because MySQL doesn't support

# transactions (yet) and can't handle autocommit flag

# Replace "MyDB" with your datasource name!

Conn=0DBC.Windows.Connect("MyDB",clear_auto_commit=0) CreateTable(Conn)

CreateEmployee(Conn,'(1,"Bob","Hilbert",Null)')

CreateEmployee(Conn,'(2,"Sarah","Pfizer",Null)')

CreateEmployee(Conn,'(3,"Sandy","Lee",1)')

CreateEmployee(Conn,'(4,"Pat","Labor",2)')

CreateEmployee(Conn,'(5,"Larry","Helper",Null)')

PrintUsersLike(Conn,"Heilbronn")

PrintUsersLike(Conn,"Pfizer")

PrintUsersLike(Conn,"Washington")

PrintUsersLike(Conn,"Lieber")

Listing 14-4: Soundex output

Users with last name similar to Heilbronn:

Users with last name similar to Pfizer:

Users with last name similar to Washington:

Users with last name similar to Lieber:

0 0

Post a comment