Read External Sql File into Pandas Dataframe
This Is a Simple Question That I Haven't Been Able to Find an Answer To. I Have A. Sql File with Two Commands. I'd Like to Have Pandas Pull the Result of Those...
This is a simple question that I haven't been able to find an answer to. I have a .SQL file with two commands. I'd like to have Pandas pull the result of those commands into a DataFrame.
The SQL file's commands are as such, with the longer query using today's date.
SET @todaydate = DATE(NOW());
SELECT ...long query....;
I've attempted to use read_sql in the following way after establishing my connection (prod_db) and get the error message ''NoneType' object is not iterable'
sqlpath = 'path.sql'
scriptFile = open(sqlpath,'r')
script = scriptFile.read()
df = pd.read_sql(script,prod_db)
I've also tried to use the function and approach described here reading external sql script in python but I'm not sure how to get the result into a pandas dataframe (or perhaps I'm missing something). It doesn't seem to be reading the results as I get 'Command Skipped' repeatedly.
def executeScriptsFromFile(filename):
fd = open(filename, 'r')
sqlFile = fd.read()
fd.close()
# all SQL commands (split on ';')
sqlCommands = sqlFile.split(';')
# Execute every command from the input file
for command in sqlCommands:
try:
c.execute(command)
except OperationalError, msg:
print "Command skipped: ", msg
df = executescriptsfromfile(sqlpath)
2 Answers
I have a solution that might work for you. It should give you a nice little pandas.DataFrame.
First, you have to read the query inside the sql file. Then just use the pd.read_sql_query() instead of pd.read_sql()
I am sure you know it, but here is the doc for the function:
# Read the sql file
query = open('filename.sql', 'r')
# connection == the connection to your database, in your case prob_db
DF = pd.read_sql_query(query.read(),connection)
query.close()
I can assure you that it is working with T-SQL, but I never used it with MySQL.
This is a MWE of how it worked for me:
query = open('./query_file.sql', 'r')
db_config = {
'server': server address,
'port': port,
'user': user,
'password': password,
'database': db name
}
try:
sql_conn = pymssql.connect(**db_config)
logging.info('SQL connection is opened')
avise_me_df = pd.read_sql(query.read(),sql_conn)
logging.info('pandas df recorded')
except OperationalError as e:
connected = False
logging.error('Error reading data from SQL table')
else:
connected = True
finally:
if connected:
sql_conn.close()
logging.info('SQL connection is closed')
I hope this might help.
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
