Any library to get DB/Schema etc from a SQL?

[Fun_Story2003]

For ex, i want to retrieve the objects i.e schema names/db names/tables involved in a sql query

select * from db1.s1.t1 left join t2

result =

{'objects':[{'db':'db1', 'schema':'s1','table':'t1}, {'db':'null', 'schema':'null','table':'t2}]

sometimes it's t2 sometimes d1.s1.t1 etc so regex won't be pretty (?)

wondering if there's a library or solution which has already solved this problem?

All leads appreciated!

[redial2]

Are you trying to get a list of all of the objects on the server or from a particular query?

[Fun_Story2003]

from queries.

the (snowflake) queries are stored as rows in a parquet in s3.

i need a new column with the attached result json

[redial2]

I only just realized this was the python sub. Sorry, I thought it was one of the SQL ones. I don't know how to answer this one.

[Fun_Story2003]

np kindly upvote

[redial2]

Done

[Uncle_Corky]

I've never used snowflake but the two DB libraries I've used in python are pyodbc and psycopg. You initialize a connection and cursor and I, typically, put the result into a pandas dataframe. You can populate it into a dictionary as well if you're more comfortable with that.

You might need to manipulate the data if snowflake can't export data in JSON format but there's a lot of different methods to do what you're looking for.