Jun-06-2022, 02:29 PM
I have a SQL Server table where 3 entries are present. I am passing these values one by one to an API function(Main) and getting Magnetic Declination(MD) value, then I need to append all the MD values that generated by API function to produce "finaloutput.csv".
User just need to pass
SQL table looks like this:
The whole python logic in simple words is that user enter startyear and endyear, then it calls sql function(goes to sql server table), grab the first row of table,then pass that first row to the API function(Main), generates the Magnetic Declination(MD) value, save the result(Result1), then once again calls sql function(goes to sql server table), now grab the second row, pass those second row to the API function, generate the MD value for second value,save the result(Result2),.... and so on...When it reaches to the last row of SQL server, grab the last row, pass that last row to the API function, generates the MD value for last value, save the result(Result3), then it append all result(Result1+Result2+Result3)and generate a csv file.
Any help???
User just need to pass
startYear
and endYear
. It is constant for all 3 projects. For example, I have used 2000 (startYear
) and 2001 (endYear
).SQL table looks like this:
Project Longitude Latitude elet -9.9148984350 73.5676615356 faya -51.1355149330 76.1481769320 Wondi -72.8008870450 128.3046359844Currently, my "finaloutput.csv"(Python is generating) is looking like this (which is wrong). It is missing 4 entries (see actual output csv):
Project Year Latitude Longitude MD SourceFile Wondi 2000 -72.8008870450 128.3046359844 37.86 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s Wondi 2001 -72.8008870450 128.3046359844 37.98 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%sI want my final CSV look like this:
Project Year Latitude Longitude MD SourceFile elet 2000 -9.9148984350 73.5676615356 38.322 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s elet 2001 -9.9148984350 73.5676615356 38.422 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s faya 2000 -51.1355149330 76.1481769320 49.23 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s faya 2000 -51.1355149330 76.1481769320 49.53 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s Wondi 2000 -72.8008870450 128.3046359844 37.86 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s Wondi 2001 -72.8008870450 128.3046359844 37.98 http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%sI think I am missing appending logic. I am having a trouble in writing a append logic.
The whole python logic in simple words is that user enter startyear and endyear, then it calls sql function(goes to sql server table), grab the first row of table,then pass that first row to the API function(Main), generates the Magnetic Declination(MD) value, save the result(Result1), then once again calls sql function(goes to sql server table), now grab the second row, pass those second row to the API function, generate the MD value for second value,save the result(Result2),.... and so on...When it reaches to the last row of SQL server, grab the last row, pass that last row to the API function, generates the MD value for last value, save the result(Result3), then it append all result(Result1+Result2+Result3)and generate a csv file.
import requests import json import argparse import time import pandas as pd import warnings import pyodbc warnings.filterwarnings("ignore") ## # sample command: python stackoverflow.py -startYear '' -endYear '' ## parser = argparse.ArgumentParser(description="Process some integers.") parser.add_argument("-startYear", help="Start of the Year") parser.add_argument("-endYear", help="End of the Year") parser.add_argument("--verbose", help="display processing information") start = time.time() def main(project,latitude,longitude,startYear,endYear,verbose): hostname = "http://www.ngdc.noaa.gov/geomag-web/calculators/calculateDeclination?%s" df_1=pd.DataFrame() for year in range(startYear, endYear+1): parameters = { 'lat1': latitude, # [deg] 'lon1': longitude, # [deg] 'model': 'IGRF', # [Model] 'startYear': year, # [year] 'startMonth': 7, # [month] 'startDay':1, # [date] 'resultFormat': 'json', # [format] } try: parameters["year"] = year response = requests.get(hostname, params= dict(parameters, ps=str(year))) # extract JSON payload of response as Python dictionary json_payload = response.json() # raise an Exception if we encoutnered any HTTP error codes like 404 response.raise_for_status() except requests.exceptions.ConnectionError as e: # handle any typo errors in url or endpoint, or just patchy internet connection print(e) except requests.exceptions.HTTPError as e: # handle HTTP error codes in the response print(e, json_payload['error']) except requests.exceptions.RequestException as e: # general error handling print(e, json_payload['error']) else: json_payload = response.json() #print(json.dumps(json_payload, indent=4, sort_keys=True)) df = pd.DataFrame(json_payload['result']) new_row = { "Project": project, "SourceFile": hostname, "Year": year, "MD": df.iloc[0, 2], "Latitude": latitude, "Longitude": longitude } df_1 = df_1.append(new_row, ignore_index=True) df_1 = df_1[['Project','Year', 'Latitude', 'Longitude','MD','SourceFile']] df_1.to_csv('finaloutput.csv',index=False) def sql(): try: # Trusted Connection to Named Instance connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;DATABASE=abc;UID=auto;PWD=12345;') cursor=connection.cursor() cursor.execute("SELECT * FROM [dbo].[Centroids]") while 1: row=cursor.fetchone() if not row: break project = row[0] Latitude = row[3] Longitude = row[2] cursor.close() connection.close() return (project,Latitude,Longitude,) except pyodbc.Error as ex: print("Exception: ",ex) cursor.close() connection.close() if __name__ == '__main__': start = time.time() args = parser.parse_args() startYear = int(args.startYear) endYear = int(args.endYear) verbose = args.verbose project,latitude,longitude=sql() main(project,latitude,longitude,startYear,endYear,verbose) # Calling Main Function print("Processed time:", time.time() - start) # Total Time
Any help???