Python regex search and identify the first occurence

Question

I have a sql string and I need to identify the first occurrence of the database and table name from the sql.

sql = 'select col1, col2, "base" as db_name, "employee" as table_name from base.employee where id is not NULL union select col1, col2, "base" as db_name, "employee" as table_name from base.employee where ts is not NULL'

result_dbname = re.search(',?"(.*)as db_name', sql)

db_name = result_dbname.group(1).replace("\"", "")

print(db_name)

Expected result
base

Actual Result
base as db_name, employee as table_name from base.employee where id is not NULL union select col1, col2, base

I would like to capture only the first occurrence

Answer 1

You can try to use match group:

m = re.match(".*(\".*\") as db_name, (\".*\") as table_name.*", sql)
m.groups()
# ('"base"', '"employee"')

Then you can strip the quotation marks.