将外部SQL文件读入Pandas Dataframe [英] Read External SQL File into Pandas Dataframe

问题描述

这是一个简单的问题，我无法找到答案。我有一个带有两个命令的.SQL文件。我想让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 commands into a DataFrame.

SQL文件的命令是如此，使用今天的日期进行更长的查询。

The SQL file's commands are as such, with the longer query using today's date.

SET @todaydate = DATE(NOW());
SELECT ...long query....;

建立连接（prod_db）后，我尝试通过以下方式使用read_sql并获得错误消息'NoneType'对象不可迭代'

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) 

我也尝试使用此处描述的功能和方法在python中读取外部sql脚本，但我不确定如何将结果获取到pandas数据框中（或者我可能丢失了某些内容）。

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)

推荐答案

我有一个可能适合您的解决方案。它应该给您一个漂亮的 pandas.DataFrame 。

I have a solution that might work for you. It should give you a nice little pandas.DataFrame.

首先，您必须阅读sql文件中的查询。然后只需使用 pd.read_sql_query（）而不是 pd.read_sql（）

First, you have to read the query inside the sql file. Then just use the pd.read_sql_query() instead of pd.read_sql()

我确定您知道它，但是这里是该函数的文档： http://pandas.pydata.org/pandas-docs/version/0.20/genic/pandas.read_sql_query.html#pandas.read_sql_query

I am sure you know it, but here is the doc for the function: http://pandas.pydata.org/pandas-docs/version/0.20/generated/pandas.read_sql_query.html#pandas.read_sql_query

# 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() 

我可以向您保证它可以与T-SQL一起使用，但是我从未与MySQL一起使用。

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

这篇关于将外部SQL文件读入Pandas Dataframe的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！