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 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.3046359844

Currently, 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?%s

I 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?%s

I 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???

(Jun-06-2022, 02:29 PM)shantanu97 Wrote:

         cursor.execute("SELECT  * FROM  [dbo].[Centroids]")          while 1: # Endless loop; same as while True:.              row=cursor.fetchone()              if not row:                  break              project = row[0]              Latitude = row[3]              Longitude = row[2]          cursor.close() # Now the loop is finished. Project, Latitude, Longitude contain the values of the last row.          connection.close()          return (project,Latitude,Longitude,)

There are several ways to solve this.

• Use fetchall() instead of fetchone(). In this case the "while 1" is not needed at all.
  
• Use "yield" instead of "return". In this case the function becomes an generator.
  
• Use a list and append the results in the loop to this list.
  

In all cases you have to change the place where sql() is called to handle the results as an iterator.