write regular expression to segregate parameters from pyodbc mssql/mysql database expression

Question

Check parameter match : <dbtype>[-odbc] '://' <hostname [.FQDN]> ':' <port> '/' <dbname> '?' <user> '&' <pwd>

p_db = "mssql-odbc://sct-mssql-db.c6p0dl1fqnlk.us-east-1.rds.amazonaws.com:1433/sct_mssql_db?mssql_user'&'schemaconversion"
pattern = re.compile(r"^(\w+)(-odbc)?://([a-zA-Z0-9_-]+[\.a-zA-Z0-9_-]*):?([a-zA-Z0-9]*)/?([a-zA-Z0-9_]?[\.a-zA-Z0-9_-]*)\??([\\\.a-z#A-Z0-9_-]*)&?([\\!\.a-zA-Z#0-9_-]*)$")
(dbtype, driver, hostname, port, dbname, user, pwd) = pattern.search(db).groups()

This expression is not working for me only in the starting point where it should pick datatype value.

Answer 1

You can use

pattern = re.compile(r"^(\w+)(-odbc)?://([\w-][\w.-]*)(?::([a-zA-Z0-9]*))?(?:/(\w[\w.-]*)\?([\w\\.#-]*)'&'([\w\\!.#-]*))?$", re.ASCII)

See the regex demo.

Details:

• ^ - start of string
• (\w+) - Group 1: one or more word chars
• (-odbc)? - an optional Group 2: -odbc string
• :// - a fixed string
• ([\w-][\w.-]*) - Group 3: a word char or a hyphen and then zero or more word, . or - chars
• (?::([a-zA-Z0-9]*))? - an optional sequence of a : char and then zero or more letters or digits (Group 4)
• (?:/(\w[\w.-]*)\?([\w\\.#-]*)'&'([\w\\!.#-]*))? - an optional sequence of
  • / - a / char
  • (\w[\w.-]*) - Group 5: a word char and then zero or more word, . or - chars
  • \? - a ? char
  • ([\w\\.#-]*) - Group 6: zero or more word, ., \, # or - chars
  • '&' - a fixed string
  • ([\w\\!.#-]*) - Group 7: zero or more word, \, !, ., # or - chars
• $ - end of string.