def add_needresourcecategory(name):
nysed = dbhelper()
exists = nysed.check_needresourcecategory_exists(name)
if exists == False:
nysed.create_needresourcecategory(name)
#print "\tAdded Need/Resource Category '{0}' Successfully.".format(name)
ret = nysed.get_needresourcecategory_by_name(name)
return ret
def add_county(name):
nysed = dbhelper()
exists = nysed.check_county_exists(name)
if exists == False:
nysed.create_county(name)
#print "\tAdded County '{0}' Successfully.".format(name)
ret = nysed.get_county_by_name(name)
return ret
def add_district(name,countyid):
nysed = dbhelper()
exists = nysed.check_district_exists(name)
if exists == False:
nysed.create_district(name,countyid)
#print "\tAdded District '{0}' Successfully.".format(name)
ret = nysed.get_district_by_name(name)
return ret
def add_county(name):
nysed = dbhelper()
exists = nysed.check_county_exists(name)
if exists == False:
nysed.create_county(name)
#print "\tAdded County '{0}' Successfully.".format(name)
ret = nysed.get_county_by_name(name)
return ret
def add_schooltype(name):
nysed = dbhelper()
exists = nysed.check_schooltype_exists(name)
if exists == False:
nysed.create_schooltype(name)
#print "\tAdded School Type '{0}' Successfully.".format(name)
ret = nysed.get_schooltype_by_name(name)
return ret
def add_needresourcecategory(name):
nysed = dbhelper()
exists = nysed.check_needresourcecategory_exists(name)
if exists == False:
nysed.create_needresourcecategory(name)
#print "\tAdded Need/Resource Category '{0}' Successfully.".format(name)
ret = nysed.get_needresourcecategory_by_name(name)
return ret
def add_schooltype(name):
nysed = dbhelper()
exists = nysed.check_schooltype_exists(name)
if exists == False:
nysed.create_schooltype(name)
#print "\tAdded School Type '{0}' Successfully.".format(name)
ret = nysed.get_schooltype_by_name(name)
return ret
def add_gradeorganization(name):
nysed = dbhelper()
exists = nysed.check_gradeorganization_exists(name)
if exists == False:
nysed.create_gradeorganization(name)
#print "\tAdded Grade Organization '{0}' Successfully.".format(name)
ret = nysed.get_gradeorganization_by_name(name)
return ret
def add_district(name, countyid):
nysed = dbhelper()
exists = nysed.check_district_exists(name)
if exists == False:
nysed.create_district(name, countyid)
#print "\tAdded District '{0}' Successfully.".format(name)
ret = nysed.get_district_by_name(name)
return ret
def add_school(name, bedscode, enrollment, countyid, districtid, gradeorganizationid, needresourcecategoryid, schooltypeid):
nysed = dbhelper()
exists = nysed.check_school_exists(name, bedscode)
if exists == False:
nysed.create_school(name, bedscode, enrollment, countyid, districtid, gradeorganizationid, needresourcecategoryid, schooltypeid)
#print "\tAdded School '{0}' Successfully.".format(name)
ret = nysed.get_school_by_name(name)
return ret
def add_gradeorganization(name):
nysed = dbhelper()
exists = nysed.check_gradeorganization_exists(name)
if exists == False:
nysed.create_gradeorganization(name)
#print "\tAdded Grade Organization '{0}' Successfully.".format(name)
ret = nysed.get_gradeorganization_by_name(name)
return ret
def add_school(name, bedscode, enrollment, countyid, districtid,
gradeorganizationid, needresourcecategoryid, schooltypeid):
nysed = dbhelper()
exists = nysed.check_school_exists(name, bedscode)
if exists == False:
nysed.create_school(name, bedscode, enrollment, countyid, districtid,
gradeorganizationid, needresourcecategoryid,
schooltypeid)
#print "\tAdded School '{0}' Successfully.".format(name)
ret = nysed.get_school_by_name(name)
return ret
def main(argv):
if len(argv) != 2:
print "Usage:\n\t\t{0} <poly_file.csv>".format(argv[0])
return
print "Application Start."
print "Loading poly file ..."
csvfile = open(argv[1], "r")
lines = csv.reader(csvfile, skipinitialspace=True)
db = dbhelper()
print "Getting all counties ..."
counties = db.get_all_counties()
print "Adding Geometry data to {0} counties ...".format(len(counties))
for cells in lines:
# pull out county name
csvcountyname = cells[0]
# pull out geometry
csvgeometry = cells[4]
if db.check_county_exists(csvcountyname) == True:
countyid, countyname, geometry = db.get_county_by_name(
csvcountyname)
db.update_county_geometry(countyid, csvgeometry)
print "Geometry Updated Successfull for '{0}' County".format(
countyname)
else:
print "ERROR: Unknown county name: '{0}'".format(csvcountyname)
def test_create_needresourcecatigory():
nysdb = dbhelper()
print "Testing create_needresourcecatigory() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
nysdb.create_needresourcecatigory(name)
exists = nysdb.check_needresourcecatigory_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def test_create_gradeorganization():
nysdb = dbhelper()
print "Testing create_gradeorganization() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
nysdb.create_gradeorganization(name)
exists = nysdb.check_gradeorganization_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def test_create_needresourcecatigory():
nysdb = dbhelper()
print "Testing create_needresourcecatigory() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
nysdb.create_needresourcecatigory(name)
exists = nysdb.check_needresourcecatigory_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def test_create_gradeorganization():
nysdb = dbhelper()
print "Testing create_gradeorganization() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
nysdb.create_gradeorganization(name)
exists = nysdb.check_gradeorganization_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def main(argv):
if len(argv) != 2:
print "Usage:\n\t\t{0} <poly_file.csv>".format(argv[0])
return
print "Application Start."
print "Loading poly file ..."
csvfile = open(argv[1],"r")
lines = csv.reader(csvfile,skipinitialspace=True)
db = dbhelper()
print "Getting all counties ..."
counties = db.get_all_counties()
print "Adding Geometry data to {0} counties ...".format(len(counties))
for cells in lines:
# pull out county name
csvcountyname = cells[0]
# pull out geometry
csvgeometry = cells[4]
if db.check_county_exists(csvcountyname) == True:
countyid,countyname,geometry = db.get_county_by_name(csvcountyname)
db.update_county_geometry(countyid,csvgeometry)
print "Geometry Updated Successfull for '{0}' County".format(countyname)
else:
print "ERROR: Unknown county name: '{0}'".format(csvcountyname)
def test_create_eventtype():
nysdb = dbhelper()
print "Testing create_eventtype() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
# ... assume that there is an id #1 in each of these ...
nysdb.create_eventtype(name, 1, 1)
exists = nysdb.check_eventtype_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def test_create_eventtype():
nysdb = dbhelper()
print "Testing create_eventtype() ..."
rnd = randint(0, 10000)
name = "__test_name_{0}__".format(rnd)
# ... assume that there is an id #1 in each of these ...
nysdb.create_eventtype(name, 1, 1)
exists = nysdb.check_eventtype_exists(name)
if exists:
print "PASSED\n"
success = 1
else:
print "FAILED\n"
success = 0
return success
def main(argv):
if len(argv) != 4:
print "Usage:\n\t\t{0} <file.csv> <startyear> <schoolyearname>".format(argv[0])
return
print "Application Start."
csvfile = open(argv[1],"r")
lines = csv.reader(csvfile,skipinitialspace=True)
db = dbhelper()
# create a new school year if it doesn't already exists, and then get the id for use later
startyear = argv[2]
schoolyearname = argv[3]
if db.check_schoolyear_exists(startyear) == False:
db.create_schoolyear(startyear,schoolyearname)
schoolyearid,_start,_name = db.get_schoolyear_by_startyear(startyear)
rowcount = 0
startcolumn = 8
for cells in lines:
if rowcount < 4:
print "Ignoring line row."
else:
_schoolname = cells[2]
_bedscode = cells[3]
#print "School = {0}".format(_schoolname)
if db.check_school_exists(_schoolname, _bedscode):
# get school name
schoolid,schoolname,bedscode,enrollment,countyid,districtid,gradeorganizationid,needresourcecategoryid,schooltypeid = db.get_school_by_name(_schoolname)
# get all of the incidenss in the correct order from the db
incidenttypes = db.get_all_incidenttypes()
#print "Processing {0} incidenttypes ...".format(len(incidenttypes))
# start at our column offset
ccount = startcolumn
# add all of the counts with their respective incidenttypes
for i in range(0, len(incidenttypes)):
# split tuple
incidenttypeid,incidenttypename,weaponsrelated = incidenttypes[i]
#print "\tCreating incident(s) for incident type '{0}'".format(incidenttypename)
# if weapons related, we need to add the next two columns, else just the 1
if weaponsrelated == 1:
#print "\tSetting Value '{0}' for weapon involved incident type '{1}'".format(cells[ccount],incidenttypename)
# add with the weapon
db.create_incident(cells[ccount], schoolyearid, 1, incidenttypeid, schoolid)
# inc to next cell
ccount += 1
#print "\tSetting Value '{0}' for no weapon involved incident type '{1}'".format(cells[ccount],incidenttypename)
# add without the weapon
db.create_incident(cells[ccount], schoolyearid, 0, incidenttypeid, schoolid)
else:
#print "\tSetting Value '{0}' for weapon-independant incident type '{1}'".format(cells[ccount],incidenttypename)
# add just without the weapon
db.create_incident(cells[ccount], schoolyearid, 0, incidenttypeid, schoolid)
# inc to next cell
ccount += 1
#print "Row Proccessed."
else:
print "ERROR: School did not exists within database, possible database corruption."
print "\nSchool Name: \n\t'{0}'\nBEDS Code:\n\t'{1}'".format(_schoolname,_bedscode)
break
rowcount += 1
if rowcount % 100 == 0:
print "Row {0} Processed.".format(rowcount)
print "Application End."
def main(argv):
if len(argv) != 2:
print "Usage:\n\t\timport_simplespy <file.csv>\n"
return
print "Application Start."
print "Reading input file ..."
# open the csv files
csvfile = open(argv[1], "r")
#filedata = csvfile.read()
print "Breaking file into rows ..."
# break the file into rows
#lines = filedata.split("\n"); # can't do this because they HAVE FREAKIN COMMA'S IN THE SCHOOL NAMES, OMG ...
# pull lines from csv file
lines = csv.reader(csvfile, skipinitialspace=True)
# remove the first 4 rows, as these are not data
#lines.remove(lines[0])
#lines.remove(lines[1])
#lines.remove(lines[2])
#lines.remove(lines[3])
#print "Iterating Through {0} Rows ...".format(len(lines))
rowcount = 0
helper = dbhelper()
# see if the unknown category for these exists, and if it doesn't then create it
if helper.check_gradeorganization_exists("UNKNOWN") == False:
helper.create_gradeorganization("UNKNOWN")
if helper.check_needresourcecategory_exists("UNKNOWN") == False:
helper.create_needresourcecategory("UNKNOWN")
# pull the index of the unknowns for these two. If the school is already known, these are ignored
gradeorganizationid, _name = helper.get_gradeorganization_by_name(
"UNKNOWN")
needresourcecategoryid, _name = helper.get_needresourcecategory_by_name(
"UNKNOWN")
for cells in lines:
if rowcount < 3:
print "Ignoring line row."
else:
# split on commas to get each individual cell
#cells = line.split(",")
#print "Procesing cell data ..."
# cell decode
#
# data will only be valid for 2010 - 2011
#
#print "Row {0}: {1},{2},{3},{4},{5},{6},{7},{8}".format(rowcount,cells[0],cells[1],cells[2],cells[3],cells[4],cells[5],cells[6],cells[7])
county = cells[0].replace('"', '')
district = cells[2].replace('"', '')
schoolname = cells[3].replace('"', '')
bedscode = cells[1].replace('"', '')
#gradeorg = cells[4].replace('"','')
#resoucecat = cells[5].replace('"','')
schooltype = cells[4].replace('"', '')
cell5 = cells[5].replace('"', '')
if cell5 != "":
enrollment = long(float(cell5))
else:
enrollment = 0
# add all the new data to the database
countyid, _name, _geometry = add_county(county)
districtid, _name, _countyid = add_district(district, countyid)
#gradeorganizationid,_name = add_gradeorganization(gradeorg)
#needresourcecategoryid,_name = add_needresourcecategory(resoucecat)
schooltypeid, _name = add_schooltype(schooltype)
# since county, gradeorg, or resourcecat aren't in the spreadsheet for this school year,
# we need to query them from 'future' years where the data is available
# make the assumption the distrct exists since this file should have already gone
# through import_simples_<schoolyear>.py
# get district and make sure it exists
#if helper.check_district_exists(district) == False:
# print "District '{0}' is unknown, ".format(district)
#return
if True:
districtid, _districtname, countyid = helper.get_district_by_name(
district)
# check to see if the school exists
if helper.check_school_exists(schoolname, bedscode) == False:
print "New School Found, fudging grade organization and need/resource category"
# set to invalid index's, if the school already exists these will be ignored
#gradeorganizationid = invalidgradeorganizationindex;
#needresourcecategoryid =
# add the school with the database data
schoolid, _schoolname, _bedscode, _enrollment, _countyid, _districtid, _gradeorganizationid, _needresourcecategoryid, _schooltypeid = add_school(
schoolname, bedscode, enrollment, countyid, districtid,
gradeorganizationid, needresourcecategoryid, schooltypeid)
#print "Added School: Name = {0}, ID = {1}".format(_schoolname,schoolid)
if rowcount % 100 == 0:
print "Row {0} Processed.".format(rowcount)
rowcount += 1
print "\n{0} Rows processed successfully.\n".format(rowcount)
print "Application End."
def main(argv):
if len(argv) != 2:
print "Usage:\n\t\timport_simplespy <file.csv>\n"
return
print "Application Start."
print "Reading input file ..."
# open the csv files
csvfile = open(argv[1],"r")
#filedata = csvfile.read()
print "Breaking file into rows ..."
# break the file into rows
#lines = filedata.split("\n"); # can't do this because they HAVE FREAKIN COMMA'S IN THE SCHOOL NAMES, OMG ...
# pull lines from csv file
lines = csv.reader(csvfile,skipinitialspace=True)
# remove the first 4 rows, as these are not data
#lines.remove(lines[0])
#lines.remove(lines[1])
#lines.remove(lines[2])
#lines.remove(lines[3])
#print "Iterating Through {0} Rows ...".format(len(lines))
rowcount = 0
helper = dbhelper()
# see if the unknown category for these exists, and if it doesn't then create it
if helper.check_gradeorganization_exists("UNKNOWN") == False:
helper.create_gradeorganization("UNKNOWN")
if helper.check_needresourcecategory_exists("UNKNOWN") == False:
helper.create_needresourcecategory("UNKNOWN")
# pull the index of the unknowns for these two. If the school is already known, these are ignored
gradeorganizationid,_name = helper.get_gradeorganization_by_name("UNKNOWN")
needresourcecategoryid, _name = helper.get_needresourcecategory_by_name("UNKNOWN")
for cells in lines:
if rowcount < 3:
print "Ignoring line row."
else:
# split on commas to get each individual cell
#cells = line.split(",")
#print "Procesing cell data ..."
# cell decode
#
# data will only be valid for 2010 - 2011
#
#print "Row {0}: {1},{2},{3},{4},{5},{6},{7},{8}".format(rowcount,cells[0],cells[1],cells[2],cells[3],cells[4],cells[5],cells[6],cells[7])
county = cells[0].replace('"','')
district = cells[2].replace('"','')
schoolname = cells[3].replace('"','')
bedscode = cells[1].replace('"','')
#gradeorg = cells[4].replace('"','')
#resoucecat = cells[5].replace('"','')
schooltype = cells[4].replace('"','')
cell5 = cells[5].replace('"','')
if cell5 != "":
enrollment = long(float(cell5))
else:
enrollment = 0
# add all the new data to the database
countyid,_name,_geometry = add_county(county)
districtid,_name,_countyid = add_district(district,countyid)
#gradeorganizationid,_name = add_gradeorganization(gradeorg)
#needresourcecategoryid,_name = add_needresourcecategory(resoucecat)
schooltypeid,_name = add_schooltype(schooltype)
# since county, gradeorg, or resourcecat aren't in the spreadsheet for this school year,
# we need to query them from 'future' years where the data is available
# make the assumption the distrct exists since this file should have already gone
# through import_simples_<schoolyear>.py
# get district and make sure it exists
#if helper.check_district_exists(district) == False:
# print "District '{0}' is unknown, ".format(district)
#return
if True:
districtid,_districtname,countyid = helper.get_district_by_name(district)
# check to see if the school exists
if helper.check_school_exists(schoolname,bedscode) == False:
print "New School Found, fudging grade organization and need/resource category"
# set to invalid index's, if the school already exists these will be ignored
#gradeorganizationid = invalidgradeorganizationindex;
#needresourcecategoryid =
# add the school with the database data
schoolid,_schoolname,_bedscode,_enrollment,_countyid,_districtid,_gradeorganizationid,_needresourcecategoryid,_schooltypeid = add_school(schoolname,bedscode, enrollment, countyid, districtid, gradeorganizationid, needresourcecategoryid, schooltypeid)
#print "Added School: Name = {0}, ID = {1}".format(_schoolname,schoolid)
if rowcount % 100 == 0:
print "Row {0} Processed.".format(rowcount)
rowcount += 1
print "\n{0} Rows processed successfully.\n".format(rowcount)
print "Application End."
