Match the table name in an SQL statement. The Python regular expression is used for matching. I wanted to build a tool similar to PLSQL, which is not complete yet. Record it first.
#-*-Coding: UTF-8-*-import rekeylist = ['access', 'add', 'all', 'alter ', 'and', 'any ', 'as', 'asc', 'audit', 'between', 'by', 'Char ', 'check', 'cluster', 'column', 'comment ', 'companys', 'connect', 'create', 'date', 'date', 'decimal', 'default', 'delete', 'desc', 'stinct ', 'drop', 'else', 'clusive ', 'exists', 'file', 'float', 'for', 'from', 'Grant ', 'group ', 'having', 'identified', 'immediate', 'in', 'credentials', 'index', 'initial', 'Insert', 'integer', 'intersect ', 'into', 'is, 'level', 'like', 'lock', 'long ', 'maxextents', 'minus', 'mlslabel ', 'Mode', 'modify', 'noaudit', 'nocomputes', 'not', 'nowait', 'null ', 'number', 'of', 'offline', 'on', 'on', 'option', 'or', 'order', 'P', 'ctfree ', 'prior', 'privileges', 'public', 'Raw', 'rename', 'resource', 'revoke', 'row', 'rowid', 'rownum ', 'rows ', 'select', 'session', 'set', 'share ', 'SIZE', 'smallint', 'start', 'succes Sful ', 'synonm', 'sysdate', 'table', 'then', 'to', 'trigger', 'uid', 'unique ', 'update', 'user', 'validate', 'values', 'varchar ', 'varchar2', 'view', 'whenever', 'where ', 'with'] # sq3string = r "(\ B [rrbb])? ''' [^ '\] * (\. | '(?! '') [^ '\] *) * (''')? "Sq3string = r" (\ B [rrbb])? '[^' \] * (\. | '(?! '') [^ '\] *) *? (')? "# Second to last? Is it for minimum matching sqs1 = r "(\ B [rrbb])? "Sqs2 = r" ((?! Table).) + B "# matching exclusion tableselectmatch = r "(?! ') * \ Bfrom \ B \ s + \ B (? P <table_name> \ W +) \ B \ s + \ B (? P <table_sn> \ W +) \ B (?! ') * "# Match the table name and table name abbreviated as insertmatch = r "(?! ') * \ Binsert \ B \ s + \ binto \ B \ s + \ B (? P <table_name> \ W +) \ B \ s + \ B (? P <table_sn> \ W +) \ B (?! ') * "# Updatematch = r "(?! ') * \ Bupdate \ B \ s + \ B (? P <table_name> \ W +) \ B \ s + \ B (? P <table_sn> \ W + )(?! ') + "# Name of the table matching the insert statement # chars =" select' from TT we, d F '*, t able from dual t where SFSF "selectchars =" select *, 'select * From tab3 T3 where t3.s' from tab1 t where a = 'select * From tab2 T2 where t2.s '"insertchars =" insert into tab1 T1 (AS) values (t, 2, _) "updatechars =" Update tab1 t set df = 'Update tab2 SDF set Mm = 1' "# print (''. join ([I + '|' For I in keylist]) [0:-1]) # chars = selectchars #### remove the strings in the statement, because the string may contain SQL statement # sqloutstr = Re. finditer (sq3string, chars, re. s) ## SLT = [I for I in sqloutstr] ## SLT. reverse () # If sqloutstr: ## for match in SLT: # PL = match. span () # chars = chars [: pl [0] + chars [pl [1]:] # print (chars) #### mstr = selectmatch ## prog = Re. compile (mstr, re. S | re. ignorecase) #### M1 = Prog. match (chars) #### If M1: # print ('m1. string ', m1.string) # print ('matched to:', Chars [m1.start (): m1.end ()]) # M = Prog. search (chars) # If M: # print ('M', M. groupdict (). items () # For key, value in M. groupdict (). items (): # print ('key, value', key, value) selectprog = Re. compile (selectmatch, re. S | re. ignorecase) updateprog = Re. compile (updatematch, re. S | re. ignorecase) inserprog = Re. compile (insertmatch, re. S | re. ignorecase) def delsqlstr (sqlstr): sqloutstr = Re. finditer (sq3string, sqlstr, re. s) SLT = [I for I in sqloutstr] SLT. reverse () If sqloutstr: For match in SLT: PL = match. span () sqlstr = sqlstr [: pl [0] + sqlstr [pl [1]:] Return sqlstrdef getselecttbn (sqlstr): "returns the table name of the SELECT statement, table Name abbreviation "tabname = none shorttabname = none M = selectprog. search (sqlstr) If M: for key, value in M. groupdict (). items (): If key = 'table _ sn ': shorttabname = value if key = 'table _ name': tabname = value # print ('getselecttbn tabname = ', tabname, 'shorttabname = ', shorttabname) return tabname, shorttabnamedef getupdatetbn (sqlstr): "return the table name of the SELECT statement, table Name abbreviation "tabname = none shorttabname = none M = updateprog. search (sqlstr) If M: for key, value in M. groupdict (). items (): If key = 'table _ sn ': shorttabname = value if key = 'table _ name': tabname = value # print ('getupdatetbn tabname = ', tabname, 'shorttabname = ', shorttabname) return tabname, shorttabnamedef getinserttbn (sqlstr): "returns the table name of the SELECT statement, table Name abbreviation "tabname = none shorttabname = none M = inserprog. search (sqlstr) If M: for key, value in M. groupdict (). items (): If key = 'table _ sn ': shorttabname = value if key = 'table _ name': tabname = value # print ('getinserttbn tabname = ', tabname, 'shorttabname = ', shorttabname) return tabname, struct (sqlstr): tmpsqlstr = delsqlstr (sqlstr) tabname = none shorttabname = none tabname, shorttabname = getselecttbn (tmpsqlstr) if tabname: Return tabname, shorttabname tabname, shorttabname = getupdatetbn (tmpsqlstr) If tabname: Return tabname, invalid tabname, shorttabname = getinserttbn (tmpsqlstr) If tabname: Return tabname, shorttabnamechars = "select * From tab1 T1 where t1.name = 'ssd '" Print (chars) print (gettabnameshortname (chars) chars = "Update tab2 t id = 12, name = 'xiaoxiao' "Print (chars) print (gettabnameshortname (chars) chars =" insert into tab3 T3 values (13, 'dada ') "Print (chars) print (gettabnameshortname (chars ))