SQLAlchemy: Get bind parameters from sql dynamically

Question

I'm trying to use pd and sqlalchemy to run all the sql files in a directory. Currently I can load the text of a sql file into a sqlalchemy.sql.text object, and send that directly to pd.read_sql. What should I use to locate the bind parameters within my sql script so that I can prompt the user for them?

import sys, os, pandas as pd, re, sqlalchemy as sa

os.chdir(sys.argv[1])
os.mkdir("out")

uname = input("Username >>> ")
passw = input("Password >>> ")
engine = sa.create_engine(f"oracle+cx_oracle://{uname}:{passw}@PROD/?encoding=UTF-8&nencoding=UTF-8")

for filename in os.listdir('.'):
    if not re.match(filename,r".*\.sql"): continue #Ignore non-sql files. 
    print("Executing",filename)
    with open(filename,"r") as my_file:
        sql = sa.text(''.join(filename.readlines()))

        ### Something goes here ###

        df = pd.read_sql(''.join(my_file.readlines()),engine)
        df.to_excel(f"./out/{filename}",filename,index=False)

My current best guess is to read through the file line-by-line with a regex that finds things that look like bind params, but I feel like there should be a better way.

Answer 1

You can obtain the names of the bind parameters from the compiled query:

>>> q = text('select id, name from users where name = :p1 and age > :p2')
>>> q.compile().params
{'p1': None, 'p2': None}

In the resulting dictionary, the keys correspond to the bind parameter names. The values are None until values are bound.