Changeset 509 for trunk


Ignore:
Timestamp:
Mar 25, 2007, 6:45:06 PM (13 years ago)
Author:
Nick Burch
Message:

Proper importing on multiple occassions, as per ticket #39

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/scripts/freethepostcode.org-importer/importer.py

    r508 r509  
    1616# Database settings
    1717dbtype = "postgres"
    18 dbname = "npemaps"
     18dbname = "npemap"
    1919dbhost = "localhost"
    20 dbuser = "npemaps"
     20dbuser = "npemap"
    2121dbpass = ""
    2222
     
    4949
    5050
     51# See if our delete reason exists, and if no, add it
     52delete_reason = None
     53reason_text = "Gone from FTP"
     54while delete_reason == None:
     55        sql = "SELECT id FROM delete_reasons WHERE reason = %s"
     56        sth = dbh.cursor()
     57        sth.execute(sql, reason_text)
     58        ids = sth.fetchall()
     59        sth.close()
     60        if len(ids) == 1:
     61                for id in (ids):
     62                        delete_reason = id.id
     63        else:
     64                sth = dbh.cursor()
     65                sth.execute("INSERT INTO delete_reasons (reason) VALUES (%s)", reason_text)
     66                sth.close()
     67
     68
    5169# Download the latest list of postcodes if needed
    5270download = False
     
    113131
    114132
    115 # Prompt before removing all the current ones
    116 sql = "SELECT COUNT(*) AS count FROM postcodes WHERE source = %s"
     133# Grab all of the current ones
     134sql = "SELECT id, outward, inward, deleted FROM postcodes WHERE source = %s AND NOT deleted"
    117135sth = dbh.cursor()
    118136sth.execute(sql, ftp_source)
    119 counts = sth.fetchall()
    120 count = counts[0][0]
     137
     138ftps = {}
     139while 1:
     140        row = sth.fetchone()
     141        if row == None:
     142                break
     143        id, outward, inward, deleted = row
     144        ftps["%s %s" % (outward, inward)] = \
     145                                { 'outward':outward, 'inward':inward, 'id':id, 'deleted':deleted, 'done':False }
    121146sth.close()
     147count = len(ftps.keys())
    122148
    123149print "There are currently %d entries in the database from freethepostcode.org" % count
     
    126152# Only prompt if it's a big difference
    127153if count == 0 or len(postcodes) == 0 or len(postcodes) < count or (len(postcodes)-count) > 50:
    128         print "Are you sure you wish to remove the old entries, to add new ones?"
     154        print "Are you sure you wish to run an import?"
    129155        confirm = raw_input("")
    130156        print ""
     
    138164
    139165
    140 # Delete the old entries
    141 sql = "DELETE FROM postcodes WHERE source = %s"
    142 sth = dbh.cursor()
    143 sth.execute(sql, ftp_source)
    144 print "Deleted %d entries" % sth.rowcount
    145 sth.close()
    146 dbh.commit()
    147 
    148 
    149 # Add the latest list to the database
    150 sql = "INSERT INTO postcodes (outward, inward, raw_postcode_outward, raw_postcode_inward, easting, northing, ie_easting, ie_northing, source) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
     166# Add the latest list to the database, tweaking if already there
     167add_sql = "INSERT INTO postcodes (outward, inward, raw_postcode_outward, raw_postcode_inward, easting, northing, ie_easting, ie_northing, source) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
     168upd_sql = "UPDATE postcodes SET outward=%s, inward=%s, raw_postcode_outward=%s, raw_postcode_inward=%s, easting=%s, northing=%s, ie_easting=%s, ie_northing=%s WHERE id=%s"
     169del_sql = "UPDATE postcodes SET deleted='t', delete_reason=%s WHERE id=%s"
    151170sth = dbh.cursor()
    152171worked = 0
     
    154173# Add the postcodes
    155174for postcode in postcodes:
    156         sth.execute(sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftp_source))
    157         worked = worked + sth.rowcount
    158 
    159 print "Added %d entries" % worked
    160 
     175        pc = "%s %s" % (postcode["outer"], postcode["inner"])
     176        print "Processing %s" % pc
     177
     178        if ftps.has_key(pc):
     179                # Update existing one
     180                ftps[pc]['done'] = True
     181                sth.execute(upd_sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftps[pc]['id']))
     182                print "\tupdated record at %d" % ftps[pc]['id']
     183        else:
     184                # New record, add
     185                sth.execute(add_sql, (postcode["outer"], postcode["inner"], postcode["raw_outer"], postcode["raw_inner"], postcode["easting"], postcode["northing"], postcode["ie_easting"], postcode["ie_northing"], ftp_source))
     186                print "\tadded new postcode"
     187        worked = worked + 1
     188
     189print "Processed %d entries" % worked
     190
     191
     192# Find ones that have gone
     193print "\nDeleting any postcodes no longer in FTP:"
     194for gone_pc in [ pc for pc in ftps.keys() if not ftps[pc]['done'] ]:
     195        print "\tflagging as deleted old FTP postcode %s" % gone_pc
     196        sth.execute(del_sql, (delete_reason, ftps[gone_pc]['id']))
    161197
    162198# All done
     199print "\nAll Done"
    163200sth.close()
    164201dbh.commit()
Note: See TracChangeset for help on using the changeset viewer.