Read External SQL File into Pandas Dataframe

Question

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)

paleonix · Accepted Answer · 2023-04-30 22:38:39Z

44

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 and execute the query
with open('filename.sql', 'r') as query:
    # connection == the connection to your database, in your case prob_db
    DF = pd.read_sql_query(query.read(),connection)

I can assure you that it is working with T-SQL, but I never used it with MySQL.

edited Apr 30, 2023 at 22:38

paleonix

3,3155 gold badges20 silver badges42 bronze badges

answered Oct 25, 2017 at 18:34

Geof

4493 silver badges3 bronze badges

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

5 Comments

Ricky McMaster Over a year ago

I can confirm this works perfectly in MySQL. Many thanks :)

Louis Yang Over a year ago

Could you explain how can one setup the connection? What is the easiest way to setup a local temporary SQL connection?

MEdwin Over a year ago

connection can be done to the database for example to an Oracle database using: connection = cx_Oracle.connect(dsn = "DBCON.LD")

Umar.H Over a year ago

recommend using a context manager to open/close the file for anyone who wanders here like me ! with open(sql_file,'r') as f:... it will handle the closing of the file when you're done reading it into a pandas dataframe.

Hunaidkhan Over a year ago

just for information of new users , connection will be done by sqlalchemy import sqlalchemy as db # SQLAlchemy connectable connection = db.create_engine('sqlite:///filename.sql').connect()

Miguel Rueda · Accepted Answer · 2019-10-24 14:29:38Z

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.

Collectives™ on Stack Overflow

Read External SQL File into Pandas Dataframe

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related