BigQuery (Dataframe) to XML

Question

I am trying to do an XML export of a BigQuery query via python using a code I found at this link: Python Pandas Dataframe to XML but I have the error below, could you help me solve this?

import os
import pandas as pd
from datetime import datetime, timedelta
from google.cloud import bigquery

script_path = "C:\\Keys\\key.json"
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = script_path

client = bigquery.Client(project='project_id')
query_job = client.query("SELECT field1, field2, field3 FROM dataset.table ")
df_bq = query_job.to_dataframe()

def to_xml(df, filename=None, mode='w'):
    def row_to_xml(row):
        xml = ['<item>']
        for i, col_name in enumerate(row.index):
            xml.append('  <field name="{0}">{1}</field>'.format(col_name, row.iloc[i]))
        xml.append('</item>')
        return '\n'.join(xml)
    res = '\n'.join(df.apply(row_to_xml, axis=1))

    if filename is None:
        return res
    with open(filename, mode) as f:
        f.write(res)

pd.DataFrame.to_xml = to_xml

df.to_xml('test.xml')

NameError Traceback (most recent call last) in ----> 1 df.to_xml('table.xml')

NameError: name 'df' is not defined

score 1 · Accepted Answer · answered Aug 07 '19 at 15:35

1

There is no variable df assigned to anything, before line df.to_xml('test.xml'). You are essentially calling a method from a non-existent variable. Therefore, NameError prompts.

answered Aug 07 '19 at 15:35

David

567
4
16

Thanks for your comment, I knew it was simple, I'm getting better at Python and asking questions around here help me evolve. If possible, I would like you to consider this in your assessment as I can't ask any more questions on stackoverflow right now. – Felipe FB Aug 07 '19 at 21:33

score 0 · Answer 2 · answered Aug 07 '19 at 16:57

I made some code changes and managed to generate the file from a bigquery select

import os
import pandas as pd
from datetime import datetime, timedelta
from google.cloud import bigquery

script_path = "C:\\Keys\\key.json"
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = script_path

client = bigquery.Client(project='project_id')
query_job = client.query("SELECT field1, field2, field3 FROM dataset.table ")
df = query_job.to_dataframe()

def to_xml(df, filename=None, mode='w'):
    def row_to_xml(row):
        xml = ['<item>']
        for i, col_name in enumerate(row.index):
            xml.append('  <field name="{0}">{1}</field>'.format(col_name, row.iloc[i]))
        xml.append('</item>')
        return '\n'.join(xml)
    res = '\n'.join(df.apply(row_to_xml, axis=1))

    if filename is None:
        return res
    with open(filename, mode, encoding="utf-8") as f:
        f.write(res)

pd.DataFrame.to_xml = to_xml

df.to_xml('test.xml')

BigQuery (Dataframe) to XML

2 Answers2