如何执行python脚本然后将结果存储为Power BI中的pandas数据集?

loading...


0

我是Microsoft Power BI的新手,我甚至不确定我的问题在Power BI的上下文中是否有效 .

我已经知道如何使用Power BI连接python . 我也知道如何获取数据集(作为pandas数据集)并显示它 .

我目前的要求是显示CSV文件的数据 . 此CSV文件具有增量性质 . 基于python脚本,新行将附加到此CSV . 该脚本运行得很好,但是当使用Power BI执行脚本内容时,我没有得到任何结果 .

执行脚本后,导航器窗口中的数据集不可用 .

Vielen Dank!

import pyodbc
import os
import pandas as pd

def fetchdata(query, start_date, dataset, dataset_exists=False):
    try:
        print('\nSetting up a connection to the database...\n')
        connection = pyodbc.connect('DSN=ASPEN_64;', timeout=1200)
    except:
        print('\nPlease check the name of the DSN. There also might be connectivity \
          issues. Please reconnect/repair the connection using Umbrella.\n')
        exit(0)

    query = query.replace('?', "'" + start_date + "'")
    cursor = connection.cursor()
    cursor.execute(query)

    columns = [column[0] for column in cursor.description]

    try:
        print('\nFetching the data from database. This operation would take time to \
          complete. Please wait....\n')
        data=cursor.fetchall()

    except ConnectionError as e:
        print(e)
        cursor.close()
        connection.close()

    df = pd.DataFrame([tuple(row) for row in data], index=None, columns=columns)

    try:
        if not dataset_exists:
            df.to_csv(path_or_buf=dataset, sep=',', header=True, index=False, mode='w')
        else:
            df.append([dataset, df], ignore_index=True)

    except IOError as e:
        print(e)

def main():
    dataset = <datasetPath>

    if os.path.exists(dataset):
        with open(dataset, 'rb') as file:
            file.seek(-2, os.SEEK_END)
            while file.read(1) !=b'\n':
                file.seek(-2, os.SEEK_CUR)

            last_row = file.readline().decode('utf-8')
            start_date =  last_row.split(',')[2]
            dataset_exists = True

    else:
        start_date = '01-JAN-14 00:00'
        dataset_exists = False
    sql_file = open(sqlfile, 'r')
    query = sql_file.read()
    fetchdata(query, start_date, dataset, dataset_exists)

if __name__== '__main__':
    main()
    dataset = pd.read_csv(<dataset>, header=0)

loading...

0回答

No Data

评论

暂时没有评论!