KS6NvWVU

2021-09-13 17:06

			from datetime import datetime
import json
import psycopg2
import requests

if __name__ == '__main__':

    try:
        response = requests.get(
            "https://services.marinetraffic.com/api/exportvessels/v:8/myapikey/protocol:jsono")
        data_json_str = response.text
        print("get data ok")

    except(Exception) as error:
        print("get data failed!, ", error)

    # write file
    try:
        datetime_object = datetime.now()
        datestr = str(datetime_object).replace(" ", "_").split(".")[0].replace(":", "-")
        file1 = open("/home/prosia/etl/data_backup_file/ps05_simple_produce1/" + datestr, "w")
        file1.writelines(str(data_json_str))
        file1.close()
        print("write data success!")

    except:
        print("write data failed!")

    connection = psycopg2.connect(user="postgres",
                                  password="password",
                                  host="host",
                                  port="5432",
                                  database="db")
    connection.autocommit = True
    cursor = connection.cursor()

    try:
        # convert json string to json object
        data_json_obj = json.loads(data_json_str)
        print("try ok!")
        # print(data_json_obj)

        for x in data_json_obj:
            print("loop ok!")
            try:
                print(x)

                mmsi = x['MMSI']
                if mmsi == '':
                    mmsi = 'null'

                imo = x['IMO']
                if imo == '':
                    imo = 'null'

                ship_id = x['SHIP_ID']
                if ship_id == '':
                    ship_id = 'null'

                lat = x['LAT']
                lat_vessel = x['LAT']
                if lat == '':
                    lat = 'null'
                    lat_vessel = re.sub(r'(\d+\.\d+)\.(\d+)$', r'\1\2', lat)


                lon = x['LON']
                lon_vessel = x['LON']
                if lon == '':
                    lon = 'null'
                    lon_vessel = re.sub(r'(\d+\.\d+)\.(\d+)$', r'\1\2', lon)

                speed = x['SPEED']
                if speed == '':
                    speed = 'null'

                heading = x['HEADING']
                if heading == '':
                    heading = 'null'

                course = x['COURSE']
                if course == '':
                    course = 'null'

                status = x['STATUS']
                if status == '':
                    status = 'null'

                timestamp = x['TIMESTAMP']
                if timestamp == '':
                    timestamp = 'null'
                else:
                    timestamp = "'" + timestamp + "'"

                dsrc = x['DSRC']
                if dsrc == '':
                    dsrc = 'null'
                else:
                    dsrc = "'" + dsrc + "'"

                utc_seconds = x['UTC_SECONDS']
                if utc_seconds == '':
                    utc_seconds = 'null'
                else:
                    utc_seconds = "'" + utc_seconds + "'"

                postgreSQL_select_Query1 = "insert into ps05_simple values (" + mmsi + "," + imo + "," + ship_id + "," + lat + "," + lon + "," + speed + "," + heading + "," + course + "," + status + "," + timestamp + "," + dsrc + "," + utc_seconds + ")"
                print(postgreSQL_select_Query1)
                cursor.execute(postgreSQL_select_Query1)

                print("insert data ps05_simple succes!")

                postgreSQL_select_Query_Vessel = (
                        "UPDATE show_vessels SET lat=" + lat_vessel + ",lon=" + lon_vessel + ",course=" + course
                        + ",timestamp=" + timestamp + ",status=" + status + ",speed=" + speed + ",mmsi=" + mmsi
                        + " where ship_id=" + ship_id + ""
                )
                print(postgreSQL_select_Query_Vessel)
                cursor.execute(postgreSQL_select_Query_Vessel)
                print("insert data show_vessels succes!")

            except (Exception, psycopg2.Error) as error:
                print("Error while fetching data,", error)
    
    except (Exception, psycopg2.Error) as error:
        print("Error while fetching data,", error)
    
    finally:
        # closing database connection.
        if (connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")