CODEWITHSUNDEEP
python-3.xpandascx-oracle
user10297084
user10297084

Reputation: 83

Run SQL Query from Txt File Python

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 """

Upvotes: 1

Views: 3255

Answers (1)

Rob Raymond
Rob Raymond

Reputation: 31166

  1. for standalone - I create sql, then write it to a file
  2. 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

 Year    c
 2018  930
 2019  932
 2020  958

Upvotes: 3

Related Questions