Database Reference
In-Depth Information
How it works...
Psycopg
isthemostpopularPostgreSQLadapterforPython,anditcanbeusedto
createPythonscriptsthatsendSQLcommandstoPostGIS.Inthisrecipe,youcre-
atedaPythonscriptthatqueriesweatherdatafromthe
OpenWeatherData.org
web
serverusingthepopular
JSON
formattogettheoutputdataandthenusedthatdata
to update two PostGIS layers.
Foroneofthelayers,
cities
,theweatherdataisusedtoupdatethe
temperat-
ure
field using the temperature data of the weather station closest to the city. For
this purpose, you used an
UPDATE SQL
command. The other layer,
wstations
,
isupdatedeverytimeanewweatherstationisidentifiedfromtheweatherdataand
inserted in the layer. In this case, you used an
INSERT SQL
statement.
This is a quick overview of the script's behavior (you can find more details in the
comments within the Python code): in the beginning, a PostgreSQL connection is
createdusingthePsycopg
connection
object.The
connection
objectiscreated
using the main connection parameters (
dbname
,
user
, and
password
, while de-
fault values for
server name
and
port
are not specified, as the default values,
localhost
and
5432
areused).Theconnectionbehaviorissetto
auto commit
sothatanySQLperformedbypsycopgwillberunimmediatelyandwillnotbeem-
bedded in a transaction.
Using a cursor, you first iterate all of the records in the
cities
PostGIS layer: for
eachofthecities,youneedtogetthetemperaturefromthe
OpenWeatherData.org
web server. For this purpose, for each city, you make a call to the
GetWeather-
Data
method,passingthecoordinatesofthecitytoit.Themethodqueriestheserv-
erusingthe
urllib2
libraryandparsestheJSONresponseusingthe
simplejson
Python library.
YoushouldsendtheURLrequesttoa
try...catch
block.Thisway,ifthereisany
issuewiththewebservice(internetconnectionnotavailable,anyHTTPstatuscodes
differentfrom200,orwhateverelse),theprocesscansafelycontinuewiththedata
of the next city (iteration).
