Problem in formulating append logic

[shantanu97]

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

[ibreeden]

         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.