VDOT Smarter Roads Controller Status Postgres input
The snippet can be accessed without any authentication.
Authored by
Neal Feierabend
Here's an example blurb adapted from your mongodb code that should work. It is using psycopg2 as the postgres driver.
You'll need to create a db_config.txt file with the following content (replace password with yours once you get it):
host=vccdb.ops.aws.vtti.ad.vt.edu
user=rviray
password=your_password
dbname=vccI've just thrown this together quickly and haven't tested, so there could be some errors, but hopefully it gives you a good start!
scrape.py 1.34 KiB
import psycopg2
import json
from urllib import request
import time
from multiprocessing import Process
# Bring in your config file that contains your password (which is why we keep it separate) and other parameters
with open ('db_config.txt', 'r') as configFile:
dbConfig = configFile.read()
# Connect to an existing database
conn = psycopg2.connect(dbConfig)
def gather_data(interval,int_number):
starttime=time.time()
while True:
url="https://smarterroads.org/dataset/download/20?token=xxxxxxxxxxxx&api=true&ctlr="+str(int_number)
response = request.urlopen(url)
if response.headers.get('content-type') == "application/json":
data = json.loads(response.read())
# Using the `with` block here, the cursor will automatically be closed after the transaction is committed
with conn.cursor as curs:
curs.execute("INSERT INTO vdot_smarter_roads_controller_status (data) VALUES (%s) RETURNING id",(data[0]))
resultId = curs.fetchone()[0]
time.sleep(interval-((time.time()-starttime))%interval)
print('One post: {0}'.format(resultId))
print(str(int_number) + ' - ' + time.strftime('%Y%m%d%H%M%S'))
# Close connection with the database if you ever exit the loop
# conn.close()
Please register or sign in to comment