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.

Tools like SQL*Plus prompt for substitution variables, not bind variables. If you want an example of prompting for substitution variables, see sample_env.py. — Christopher Jones
– Christopher Jones, Commented Sep 23, 2022 at 4:51

snakecharmerb · Accepted Answer · 2022-09-23 06:56:16Z

4

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.

answered Sep 23, 2022 at 6:56

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy: Get bind parameters from sql dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related