Run SQL Query from Txt File Python

Question

Hello i am trying to load a huge query (5000+ lines) from a text file and execute the whole query at the once using the code below. How can i load my query from a txt file and execute it using python pandas

i opened up my text file using the code below

query1 = open("Script.txt","r")

and tried executing the script with the code below.

df_ora1 = pd.read_sql(query1, con=connection1)

I am getting the following error

DatabaseError: Execution failed on sql '<_io.TextIOWrapper expecting string or bytes object

my text file has """ start query

end query """

Use query1 = open("Script.txt","r").read()

bigbounty
– bigbounty

2020-07-30 14:49:39 +00:00
Commented Jul 30, 2020 at 14:49 — bigbounty
– bigbounty, Commented Jul 30, 2020 at 14:49
Always store sql queries in .sql files

bigbounty
– bigbounty

2020-07-30 14:50:14 +00:00
Commented Jul 30, 2020 at 14:50 — bigbounty
– bigbounty, Commented Jul 30, 2020 at 14:50

Rob Raymond · Accepted Answer · 2020-07-30 15:00:40Z

3

for standalone - I create sql, then write it to a file
your solution - read sql from file. Pass it to read_sql() along with connection

temptable = "tempx"
sql = f"""select Year, count(*) as c 
                        from {temptable} 
                        where Month=1 
                        and Sharpe between 1 and 2 
                        and stock like '%%2%%'
                        group by Year"""
with open("script.sql","w") as f: f.write(sql)
# read sql script from file.  pass it to pandas with connection
with open("script.sql") as f: sql = f.read()
engine = sqlalchemy.create_engine('mysql+pymysql://sniffer:[email protected]/sniffer')
conn = engine.connect()
print(pd.read_sql(sql, conn).to_string(index=False))

output

answered Jul 30, 2020 at 15:00

Rob Raymond

31.5k3 gold badges19 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Run SQL Query from Txt File Python

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