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

user_9875052 0
Viewed 32

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.

python regex
1 Answers

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.
user_38329700
Related