[Python] How to fetch data from report in Tally using Python and save the data in Google Big...

I am trying to fetch data from reports in Tally using python script and save it into Google Big Query. I fetched the data from Tally report using XMLrequest and I got the response. Then I tried to put the data in the Google Big Query after converting it into 'JSON'. But I could not put the data in the Google Big Query'. Also, I want to do CDC` before saving the data in Google Big Query for adding the updated details(delete rows,updated rows,newly added rows)

code:

import requests
import json
import os
import pandas as pd
from google.cloud import bigquery
import os, json
from datetime import datetime
import xml.etree.ElementTree as et

url = "URL"

data_tdl = """<ENVELOPE>
<HEADER>
<TALLYREQUEST>Export Data</TALLYREQUEST>
</HEADER>
<BODY>
<EXPORTDATA>
<REQUESTDESC>
<STATICVARIABLES>
<SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>
<!-- Specify the FROM DATE here -->
<SVFROMDATE>20170401</SVFROMDATE>
<!-- Specify the TO DATE here -->
<SVTODATE>20210331</SVTODATE>
</STATICVARIABLES>
<REPORTNAME>Ratio Analysis</REPORTNAME>
</REQUESTDESC>
</EXPORTDATA>
</BODY>
</ENVELOPE>

"""
try:
req = requests.post(url=url, data=data_tdl)
# print(req.text)
xml_data = req.text

except:
xml_data = " Connection Not established, Try again after the server is up ............................!"
print("connection not established.............................!")

with open('/home/abin/sme/response.xml', 'w') as f: # Writing in XML file
for line in xml_data:
f.write(line)
print("file saved...")

## Converts schema dictionary to BigQuery's expected format for job_config.schema
def format_schema(schema):
formatted_schema = []
for row in schema:
formatted_schema.append(bigquery.SchemaField(row['name'], row['type'], row['mode']))
return formatted_schema

### Create dummy data to load
df_cols = ['RATIONAME', ' RATIOVALUE']

xtree = et.parse('response.xml')
xroot = xtree.getroot()
print(xroot)
rows = []

for node in xroot:
# print(node)
res = []
for el in df_cols:
if node is not None and node.find(el) is not None:
res.append(node.find(el).text)
else:
res.append(None)
rows.append({df_cols: res
for i, _ in enumerate(df_cols)})
# print(rows)
out_df = pd.DataFrame(rows, columns=df_cols)
# print(out_df)

### Convert dataframe to JSON object
json_data = out_df.to_json(orient = 'records')
json_object = json.loads(json_data)

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = r"credentials.json"

### Define schema as on BigQuery table, i.e. the fields id, first_name and last_name
table_schema = {
'name': 'ratio_name',
'type': 'STRING',
'mode': 'NULLABLE'
}, {
'name': 'ratio_value',
'type': 'STRING',
'mode': 'NULLABLE'
}

project_id = 'project id'
dataset_id = 'xml_data'
table_id = 'ratio_analysis'

client = bigquery.Client(project = project_id)
dataset = client.dataset(dataset_id)
table = dataset.table(table_id)

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
job_config.schema = format_schema(table_schema)

now = datetime.now()

current_time = now.strftime("%H:%M:%S")
print("Time before data dumping =", current_time)
job = client.load_table_from_json(json_object, table, job_config = job_config)
current_time1 = now.strftime("%H:%M:%S")
print("Time after data dumping =", current_time1)
print(job.result())


XML Response:

<ENVELOPE>
<RATIONAME>Working Capital</RATIONAME>
<RATIOVALUE>3,83,272.14 Dr</RATIOVALUE>
<RATIONAME>Cash-in-hand</RATIONAME>
<RATIOVALUE>1,28,788.00 Dr</RATIOVALUE>
<RATIONAME>Bank Accounts</RATIONAME>
<RATIOVALUE>39,18,723.51 Dr</RATIOVALUE>
<RATIONAME>Bank OD A/c</RATIONAME>
<RATIOVALUE>75,000.00 Cr</RATIOVALUE>
</ENVELOPE>


Can anyone suggest a solution to solve this issue?

Continue reading...