My new database program I wrote for a lady at work.

#!usr/bin/env python
from Tkinter import *
import sqlite3
#Witten by Steve Atchison 11/16/2013
def cleargui():
   
    recdate.delete(0,END)
    bookpage.delete(0,END)
    pin.delete(0,END)
    qref.delete(0,END)
    problemnotes.delete(0,END)
    textwindow.delete("1.0",END) 

def connectdb():
    connect = sqlite3.connect("ProblemParcels.db")
    cur = connect.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS problemtable(recorddate text, deednumber text,pin text, quickref text, notes text)")

def addtodatabase():
    connectdb()
    #Get the data from the GUI
    record_date=recdate.get()
    deednum=bookpage.get()
    parcelnum=pin.get()
    quick_ref=qref.get()
    deed_notes=problemnotes.get()
    #add data to the data base
    connect = sqlite3.connect("ProblemParcels.db")
    cur=connect.cursor()
    cur.execute("INSERT INTO problemtable VALUES(?,?,?,?,?)",([record_date,deednum,parcelnum,quick_ref,deed_notes]))
    connect.commit()
    cleargui()
    getdata()

def getdata():#Display data in test window
    cleargui()
    addtoTextwin()
    connect = sqlite3.connect("ProblemParcels.db")
    cur=connect.cursor()
    thedata = cur.execute("SELECT * FROM problemtable")
    for row in thedata:
        textwindow.insert("100.0",row[0]+" - "+row[1]+" - "+row[2]+" - "+row[3]+" - "+row[4]+"\n")
        addlines()

def deleterow():#Delete a row from database using quick ref
    deleterow=qref.get()
    connect = sqlite3.connect("ProblemParcels.db")
    cur=connect.cursor()
    cur.execute("DELETE FROM problemtable WHERE quickref = ?",([deleterow]))
    connect.commit()
    cleargui()
    getdata()

def findqref():#Find data using quick ref
    findquickref=qref.get()
    connect = sqlite3.connect("ProblemParcels.db")
    cur=connect.cursor()
    therow=cur.execute("SELECT * FROM problemtable WHERE quickref = ?",([findquickref]))
    addtoTextwin()
    for row in therow:
        textwindow.insert("100.0",row[0]+" - "+row[1]+" - "+row[2]+" - "+row[3]+" - "+row[4]+"\n")
   

   
def addtoTextwin():
    textwindow.insert("100.0","REC DATE     DEED             PIN          QUICK REF                  NOTES \n\n")

def addlines():
    textwindow.insert("100.0","------------------------------------------------------------------------------------------------------\n")
  
    
root=Tk()
root.title("Problem Parcels")
root.config(bg='lightblue')
#create frames
frame1=Frame(root)
frame1.pack()

frame2=Frame(root)
frame2.pack()

frame3=Frame(root)
frame3.pack()

frame4=Frame(root)
frame4.pack()

frame5=Frame(root)
frame5.pack()

recordlabel=Label(frame1,text="Recorded date:")
recordlabel.pack(side=LEFT)

recdate=Entry(frame1,width=10,text="Record date:")
recdate.pack(side=LEFT,padx=5,pady=2)

bookpagelabel=Label(frame1,text='Deed number:')
bookpagelabel.pack(side=LEFT,padx=5,pady=2)

bookpage=Entry(frame1,width=10,text="Deed Number:")
bookpage.pack(side=LEFT,padx=5,pady=2)

pinlabel=Label(frame1,text='Parcel number:')
pinlabel.pack(side=LEFT,padx=5,pady=2)

pin=Entry(frame1,text="Parcel number:")
pin.pack(side=LEFT,padx=5,pady=2)

qreflabel=Label(frame1,text='Quick Ref:')
qreflabel.pack(side=LEFT,padx=5,pady=2)

qref=Entry(frame1,text="Quick Ref:")
qref.pack(side=LEFT,padx=5,pady=2)

problemlabel=Label(frame3,text="Problem Notes:")
problemlabel.pack(side=LEFT,padx=5,pady=2)
problemnotes=Entry(frame3,text="Problem notes",width='100')
problemnotes.pack(side=LEFT,padx=5,pady=2)

textwindow=Text(root,width='140',height=35,background='white')
textwindow.pack()

aframe=Frame(root)
aframe.pack()

btn=Button(aframe,text='Add to Database ',command=addtodatabase)
btn.pack(padx=5,pady=5,side='left')

btn2=Button(aframe,text="LIST DATA",command=getdata)
btn2.pack(padx=5,pady=5,side='left')

btn3=Button(aframe,text="Clear window",command=cleargui)
btn3.pack(padx=5,pady=5,side='left')

btn4=Button(aframe,text='Delete line',command=deleterow)
btn4.pack(padx=5,pady=5,side='left')

findbtn=Button(aframe,text='Find using Quick ref',command=findqref)
findbtn.pack(padx=5,pady=5,side='left')
root.mainloop()

GUI web scrape program.

 

#Appraiser Data scraper example.
 #Written by Steve Atchison for Shawnee County March 17 2015
import urllib2
from Tkinter import *

def getwebhtml():
     #This gets the html text from the appraiser webpage using the parcel number entered by user: assines it to the_page
     pid = pinnum.get()
     req = urllib2.Request('http://www.snco.us/ap/R_prop/Comp.asp?PRCL_ID='+str(pid)+'&PRCL_CD=01&YEAR=2015')
     response = urllib2.urlopen(req)
     the_page = response.read()

     startchar = the_page.find('PID1=')
     #compareable pin
     comp1=(the_page[int(startchar):int(startchar)+21])
     #saleprice
     saleprice=(the_page[int(startchar+88):int(startchar)+99]) 
     #sale date
     saledate=(the_page[int(startchar+63):int(startchar)+67])

     txtbox.insert("1.0",comp1+'\n')
     txtbox.insert("2.0",'Date: '+saledate+'\n')
     txtbox.insert("3.0",'Sale price:'+saleprice+'\n\n')

     startchar = the_page.find('PID2=')
     #compareable pin
     comp1=(the_page[int(startchar):int(startchar)+21])
     #saleprice
     saleprice=(the_page[int(startchar+88):int(startchar)+99]) 
     #sale date
     saledate=(the_page[int(startchar+63):int(startchar)+67])
   
     txtbox.insert("1.0",comp1+'\n')
     txtbox.insert("2.0",'Date: '+saledate+'\n')
     txtbox.insert("3.0",'Sale price:'+saleprice+'\n\n')
  
     startchar = the_page.find('PID3=')

    #compareable pin
     comp1=(the_page[int(startchar):int(startchar)+21])
     #saleprice
     saleprice=(the_page[int(startchar+88):int(startchar)+99]) 
     #sale date
     saledate=(the_page[int(startchar+63):int(startchar)+67])
   
     txtbox.insert("1.0",comp1+'\n')
     txtbox.insert("2.0",'Date: '+saledate+'\n')
     txtbox.insert("3.0",'Sale price:'+saleprice+'\n\n')

     startchar = the_page.find('PID4=')
     #compareable pin
     comp1=(the_page[int(startchar):int(startchar)+21])
     #saleprice
     saleprice=(the_page[int(startchar+88):int(startchar)+99]) 
     #sale date
     saledate=(the_page[int(startchar+63):int(startchar)+67])
   
     txtbox.insert("1.0",comp1+'\n')
     txtbox.insert("2.0",'Date: '+saledate+'\n')
     txtbox.insert("3.0",'Sale price:'+saleprice+'\n\n')

     startchar = the_page.find('PID5=')
     #compareable pin
     comp1=(the_page[int(startchar):int(startchar)+21])
     #saleprice
     saleprice=(the_page[int(startchar+88):int(startchar)+99]) 
     #sale date
     saledate=(the_page[int(startchar+63):int(startchar)+67])
   
     txtbox.insert("1.0",comp1+'\n')
     txtbox.insert("2.0",'Date: '+saledate+'\n')
     txtbox.insert("3.0",'Sale price:'+saleprice+'\n\n')
     theweb()#opens up webpages for all properties.

def cleartext():
    txtbox.delete("1.0",  END)
    pinnum.delete(0,END)
def theweb():
    for row in arcpy.SearchCursor("Owners"):
        parcelnumber= row.PID
        print(parcelnumber)
       
        webbrowser.open('http://www.snco.us/Ap/R_prop/Listing.asp?PRCL_ID='+str(parcelnumber))
        open
root = Tk()
btn = Button(root, text = "Get Comparables", command = getwebhtml)
btn.pack()
btn2=Button(root, text="Clear", command=cleartext)
btn2.pack()
pinnum=Entry(root)
pinnum.pack()
txtbox = Text(root)
txtbox.pack()
root.mainloop()

Snapshot of the Simple Scrape Program

This is a snap shot of my simple scrape python program.


#!/usr/bin/python
#Appraiser Data scraper
#Written by Steve Atchison for Shawnee County March 17 2015
import urllib2
pid = raw_input('Enter parcel number: ')
#This gets the html text from the appraiser webpage using the parcel number entered by user.
req = urllib2.Request('http://www.snco.us/ap/R_prop/Comp.asp?PRCL_ID='+str(pid)+'&PRCL_CD=01&YEAR=2015')
response = urllib2.urlopen(req)
the_page = response.read()
#erase all data in scrape_data.txt file
f2=open("scrape_data.txt",'w')
f2.close()
def getmoredata(start):
    #This function grabs sales and date from appraiser webpage and saves it to scrape_data.txt
   
    print('--------------------------------')
    #Compare PIN
    thepin=((the_page[int(start+5):int(start)+21]))
    print(thepin)
    #Actual sale price
    saleprice=(the_page[int(start+88):int(start)+99])
    print('Actual sales price = %s') % saleprice  
    #sale date
    saledate=(the_page[int(start+63):int(start)+67])
    print('Sale date = %s') % saledate
   
    f2=open("scrape_data.txt",'a')
    f2.write(thepin +'  ')
    f2.write(saleprice +'  ')
    f2.write(saledate + '\n')
    f2.close()   
   
#Finds the begining point of each parcel number in the html file,
#and then calls the getmoredata function
startchar = the_page.find('PID1=')
getmoredata(startchar)
startchar = the_page.find('PID2=')
getmoredata(startchar)
startchar = the_page.find('PID3=')
getmoredata(startchar)
startchar = the_page.find('PID4=')
getmoredata(startchar)
startchar = the_page.find('PID5=')
getmoredata(startchar)

Python pictures

This is a snapshot of my arcmap desktop after running the appraiser scrape program.

I improved on my letter writing program!

#Written by Steve Atchison 6/5/2013 for Shawnee County
import datetime
from Tkinter import *

def cleargui():
    ent1.delete(0,END)
    deednum.delete(0,END)
    textwin.delete("1.0",END)
    textwin.insert("0.1","Please close GUI to create letter.")
           
#GET DATA FROM GUI
def getdatafromgui():
    notes = textwin.get('1.0',END)#GET TEXT FROM THE TEXT WINDOW
    
    print("\n")
    #SELECT A PARCEL FROM THE OWNER LAYER FIRST BEFORE USING THIS SCRIPT.
    for row in arcpy.SearchCursor("Owners"):
        grantor= ent1.get()
        owner = row.getValue('ONAME')
        mailaddress = row.getValue('MAILADDRESS')
        mailaddress2 = row.getValue('MAILADDRESS2')
        #deed= row.getValue('DBOOKPAGE')

        deed=deednum.get()
        today=datetime.date.today()
        parcelid=row.getValue('PID')
    
#THIS IS WHERE THE SAVE LETTER TO FILE STARTS.
       
    
    f = open(r"G:\GIS and Mapping\TitleLetters\theletter.txt",'w')
    
    getdatafromgui() #Gets data from the GUI  
             
    
    f.write('''




                                                    Shawnee County Appraiser/GIS Department
                                                    1515 NW Saline, Suite 100
                                                    Topeka KS 66618

                                                    '''+"\n")


    f.write("    "+today.strftime("%m/%d/%y"+"\n")+"\n"+"\n"+"\n")

    f.write("    "+str(owner)+"\n")
    f.write("    "+str(mailaddress)+"\n")
    f.write("    "+str(mailaddress2)+ "\n"+"\n"+"\n")



   

    f.write('    '+'Dear '+ str(owner)+'\n')

    f.write('''




    Recently we processed a recorded deed %s that was filed with the Shawnee County
    Register of Deeds Office.  You are being sent this letter to acknowledge what properties
    were affected and what the actions the GIS (Geographic Information Systems) department
    did according to that filed deed.

    Please note below are the actions the GIS Department used in processing %s

    Name NOT Changed from %s to %s

    %s : %s




    Respectfully,    

    Shawnee County Mapping Department/Appraisers  (785)251-6049 ''' %(deed,deed,owner,grantor,parcelid,notes))

    #f.write("\n"+"-------------------------------------------------------------------------------------------------"+"\n")
    cleargui()

root = Tk()
root.config(bg='blue')
root.geometry('700x400')

btn1=Button(root,text='Add to letter',command = write_letter)
btn1.pack()
lbl=Label(root,text="GRANTEE").pack()
ent1 = Entry(root, width =80)
ent1.pack()
lbl2=Label(root,text="Deed number ")
lbl2.pack()
deednum=Entry(root)
deednum.pack()

lbl2=Label(root,text="Actions taken").pack()
textwin=Text(root,width=800,height=26,background='white')
textwin.pack(padx=5,pady=5)

root.mainloop()

#Select a parcel from the owner layer before running this script.

#A layer named "Owner" needs to be present in the table of contents    

#Letter will be created when GUI is closed: Copy and paste into Word. 

PYTHON CLIENT SERVER PROGRAMS

#This is the Client
import socket
s=socket.socket(socket.AF_INET,socket.SOCK_STREAM)
s.connect(("localhost",8001))
s.send("Ping")
result=s.recv(256)
print result

s.close()

#This is a python Server program

#!/usr/bin/env python

import socket

server=socket.socket(socket.AF_INET,socket.SOCK_STREAM)

server.bind(("localhost",8001))

server.listen(3)

while True:

    (client,address)=server.accept()

    msg=client.recv(100)

    print "received", msg

    client.send("pong")

CLIENT / SERVER Program written in python

I found a You tube tutorial on server/client programing, and it actually works too.