1

I am using Python to pull data from an API and update a MySQL database with those values. I realized that as my code is right now, the data I want from the API is only being INSERTED into the database the first time the code is ran but the database needs to be updated with new values whenever the code is executed after the first time. The API provides close to real - time values of position, speed, altitude etc. of current airlines in flight. My database looks like this:

table aircraft:
+-------------+------------+------------+-----------+-----------+
|  longitude  |  latitude  |  velocity  | altitude  |  heading  |
+-------------+------------+------------+-----------+-----------+
|             |            |            |           |           |
|             |            |            |           |           |

I am quite new to MySQL and I am having trouble finding how to do this the right way. The point is to run the code in order to update the table whenever I want. Or possibly every 10 seconds or so. I am using Python's MySQLdb module in order to execute SQL commands within the python code. Here is the main part of what I currently have.

#"states" here is a list  of state vectors from the api that have the data I want
states = api.get_states()

#creates a cursor object to execute SQL commands
#the parameter to this function should be an SQL command 
cursor = db.cursor()

#"test" is the database name
cursor.execute("USE test")

print("Adding states from API ")
for s in states.states:
    if( s.longitude is not None and s.latitude is not None):
        cursor.execute("INSERT INTO aircraft(longitude, latitude) VALUES (%r, %r);", (s.longitude, s.latitude))
    else:
        ("INSERT INTO aircraft(longitude, latitude) VALUES (NULL, NULL);")
    if(s.velocity is not None):
        cursor.execute("INSERT INTO aircraft(velocity) VALUES (%r);" % s.velocity)
    else:
        cursor.execute("INSERT INTO aircraft(velocity) VALUES (NULL);")
    if(s.altitude is not None):
        cursor.execute("INSERT INTO aircraft(altitude) VALUES (%r);" % s.altitude)
    else:
        cursor.execute("INSERT INTO aircraft(altitude) VALUES (NULL);")
    if(s.heading is not None):
        cursor.execute("INSERT INTO aircraft(heading) VALUES (%r);" % s.heading)
    else:
        cursor.execute("INSERT INTO aircraft(heading) VALUES (NULL);")

db.commit()
Flower
  • 381
  • 1
  • 6
  • 17
  • Are you asking how to perform a task periodically? – wwii Sep 18 '17 at 02:46
  • Possible duplicate of [What is the best way to repeatedly execute a function every x seconds in Python?](https://stackoverflow.com/questions/474528/what-is-the-best-way-to-repeatedly-execute-a-function-every-x-seconds-in-python) – wwii Sep 18 '17 at 02:50
  • Im asking how to update the database periodically – Flower Sep 18 '17 at 03:09
  • Refactor the process to a function then try to implement the solutions in the possible duplicate SO Q&A. – wwii Sep 18 '17 at 03:29

0 Answers0