def createGeocorrTable(self):
check = self.checkIfTableExists('geocorr')
if check:
if self.project.controlUserProv.defSource <> 'ACS 2005-2007':
geocorrTableQuery = ImportUserProvData(
'geocorr', "./data/us2000geocorr.csv", [], [], True, True)
else:
geocorrTableQuery = ImportUserProvData(
'geocorr', "./data/usacsgeocorr.csv", [], [], True, True)
if not self.query.exec_(geocorrTableQuery.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(geocorrTableQuery.query2):
raise FileError, self.query.lastError().text()
def createHousingPUMSTable(self):
web_stabb = self.project.stateAbb[self.state]
hMasterFile = self.loc + os.path.sep + 'ss07h%s.csv' %web_stabb
hMasterVariablesTypes = ['bigint'] * HACS_VARCOUNT
fileProp = FileProperties(hMasterFile)
hMasterVariablesTypes = fileProp.varTypes
self.processTable(hMasterFile, self.loc)
hMasterPUMSTableQuery = ImportUserProvData("housing_raw", hMasterFile,
varTypes=hMasterVariablesTypes,
varNamesFileDummy=True,
varTypesFileDummy=False)
if self.checkIfTableExists('housing_raw'):
if not self.query.exec_(hMasterPUMSTableQuery.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(hMasterPUMSTableQuery.query2):
raise FileError, self.query.lastError().text()
dummyString = ''
for i in self.housingVariablesSelected:
dummyString = dummyString + i + ','
dummyString = dummyString[:-1]
#print dummyString
if not self.query.exec_("""create table housing_pums select %s from housing_raw"""
%(dummyString)):
raise FileError, self.query.lastError().text()
def mysqlQueries(self, name, filePath):
fileProp = FileProperties(filePath)
fileQuery = ImportUserProvData(name, filePath, fileProp.varNames,
fileProp.varTypes,
fileProp.varNamesDummy,
fileProp.varTypesDummy)
return fileQuery
def mysqlQueries(self, name, filePath):
# Generate the mysql queries to import the tables
fileProp = FileProperties(filePath)
fileQuery = ImportUserProvData(name, filePath, fileProp.varNames,
fileProp.varTypes,
fileProp.varNamesDummy,
fileProp.varTypesDummy)
return fileQuery
def pumsVariableTable(self):
check = self.checkIfTableExists('pumsacsvariablelist')
if check:
PUMSVariableDefTable = ImportUserProvData("pumsacsvariablelist",
"./data/pumsacs5yr_variables.csv",
[], [],True, True)
if not self.query.exec_(PUMSVariableDefTable.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(PUMSVariableDefTable.query2):
raise FileError, self.query.lastError().text()
def pumsVariableTable(self):
# Creats a table that contains the location of the different PUMS variables in the raw data files
check = self.checkIfTableExists('PUMS2000VariableList')
if check:
PUMSVariableDefTable = ImportUserProvData(
"PUMS2000VariableList", "./data/PUMS2000_Variables.csv", [],
[], True, True)
if not self.query.exec_(PUMSVariableDefTable.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(PUMSVariableDefTable.query2):
raise FileError, self.query.lastError().text()
def createHousingPUMSTable(self):
# Creating a Housing PUMS Table
self.housingFileName = 'PUMS5_hou_%s.csv' %(self.stateCode[self.state])
self.housingPUMSloc = os.path.join(self.loc, self.housingFileName)
if not self.checkIfFileExists(self.housingPUMSloc):
self.createHousingPUMSFile()
housingVariablesSelected = copy.deepcopy(self.housingVariablesSelected)
housingVariablesSelected.insert(0, 'hhid')
housingVariablesSelectedType = ['bigint'] * len(housingVariablesSelected)
try:
housingPUMSTableQuery = ImportUserProvData("housing_pums", self.housingPUMSloc,
housingVariablesSelected, housingVariablesSelectedType, False, False)
except Exception, e:
raise FileError, e
def createPersonPUMSTable(self):
web_stabb = self.project.stateAbb[self.state]
pMasterFile = self.loc + os.path.sep + 'ss09p%s.csv' %web_stabb
pMasterVariablesTypes = ['bigint'] * PACS5yr_VARCOUNT
#print pMasterFile
fileProp = FileProperties(pMasterFile)
pMasterVariablesTypes = fileProp.varTypes
self.processTable(pMasterFile, self.loc)
pMasterPUMSTableQuery = ImportUserProvData("person_raw", pMasterFile,
varTypes=pMasterVariablesTypes,
varNamesFileDummy=True,
varTypesFileDummy=False)
import time
ti = time.time()
if self.checkIfTableExists('person_raw'):
if not self.query.exec_(pMasterPUMSTableQuery.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(pMasterPUMSTableQuery.query2):
raise FileError, self.query.lastError().text()
dummyString = ''
for i in self.personVariablesSelected:
dummyString = dummyString + i + ','
dummyString = dummyString[:-1]
#print dummyString
#print 'time for creating the raw person table - ', time.time()-ti
ti = time.time()
if not self.query.exec_("""create table person_pums select %s from person_raw"""
%(dummyString)):
raise FileError, self.query.lastError().text()
def createPersonPUMSTable(self):
# Creating a Person PUMS Table
self.personFileName = 'PUMS5_per_%s.csv' % (self.stateCode[self.state])
self.personPUMSloc = os.path.join(self.loc, self.personFileName)
if not self.checkIfFileExists(self.personPUMSloc):
self.createPersonPUMSFile()
personVariablesSelected = copy.deepcopy(self.personVariablesSelected)
personVariablesSelected.insert(0, 'hhid')
personVariablesSelected.insert(0, 'pumano')
personVariablesSelected.insert(0, 'state')
personVariablesSelectedType = ['bigint'] * len(personVariablesSelected)
try:
personPUMSTableQuery = ImportUserProvData(
"person_pums", self.personPUMSloc, personVariablesSelected,
personVariablesSelectedType, False, False)
except Exception, e:
raise FileError, e
def createRawSFTable(self):
# Create raw SF tables which can then be used to create the required summary file tables for use
# population synthesis
# First create the state geo table
if self.checkIfTableExists('sfacsfilestablescorr'):
sfacsFilesTablesCorrTable = ImportUserProvData(
"sfacsfilestablescorr", "./data/sfacsfilestablescorr.csv", [],
[], True, True)
if not self.query.exec_(sfacsFilesTablesCorrTable.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(sfacsFilesTablesCorrTable.query2):
raise FileError, self.query.lastError().text()
tablename = '%sgeo' % (self.stateAbb[self.state])
if self.checkIfTableExists(tablename):
if not self.query.exec_(
"""create table %s (raw text, sumlev float, sfgeoid float, """
"""state float, county float, tract float, bg float, logrecno float)"""
% tablename):
raise FileError, self.query.lastError().text()
geo_loc = self.loc + os.path.sep + self.rawSF[0] % (
self.stateAbb[self.state])
geo_loc = geo_loc.replace("\\", "/")
if not self.query.exec_("""load data local infile '%s'"""
""" into table %sgeo (raw)""" %
(geo_loc, self.stateAbb[self.state])):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set sumlev = mid(raw, 9, 3)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set sfgeoid = mid(raw, 12, 2)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set state = mid(raw, 26, 2)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set county = mid(raw, 28, 3)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set tract = mid(raw, 41, 6)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set bg = mid(raw, 47, 1)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set logrecno = mid(raw, 14, 7)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""alter table %sgeo modify logrecno int""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""alter table %sgeo add primary key (logrecno)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
# Load the other necessary tables
for j in range(len(self.rawSFNamesNoExt[1:])):
filenumber = self.rawSFNamesNoExt[j + 1]
variables, variabletypes = self.variableNames(filenumber)
tablename = "%s%s" % (self.stateAbb[self.state], filenumber)
filename = ('e' + (self.rawSF[j + 1]) %
(self.stateAbb[self.state])).replace('zip', 'txt')
sf_loc = (self.loc + os.path.sep + 'tab4' + os.path.sep +
'sumfile' + os.path.sep + 'prod' + os.path.sep +
'2005thru2007' + os.path.sep + 'data' + os.path.sep +
filename)
sffile = ImportUserProvData(tablename, sf_loc, variables,
variabletypes, False, False)
if self.checkIfTableExists(tablename):
if not self.query.exec_(sffile.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(sffile.query2):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"alter table %s add primary key (logrecno)" %
tablename):
raise FileError, self.query.lastError().text()
def createRawSFTable(self):
# Create raw SF tables which can then be used to create the required summary file tables for use
# population synthesis
# First create the state geo table
if self.checkIfTableExists('sf3filestablescorr'):
sf3FilesTablesCorrTable = ImportUserProvData(
"sf3filestablescorr", "./data/sf3filestablescorr.csv", [], [],
True, True)
if not self.query.exec_(sf3FilesTablesCorrTable.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(sf3FilesTablesCorrTable.query2):
raise FileError, self.query.lastError().text()
tablename = '%sgeo' % (self.stateAbb[self.state])
if self.checkIfTableExists(tablename):
if not self.query.exec_(
"""create table %s (raw text, sumlev float, geocomp float, sfgeoid float, """
"""state float, county float, tract float, bg float, logrecno float)"""
% tablename):
raise FileError, self.query.lastError().text()
geo_loc = os.path.join(self.loc, '%s.uf3' % tablename)
geo_loc = geo_loc.replace("\\", "/")
if not self.query.exec_("""load data local infile '%s'"""
""" into table %sgeo (raw)""" %
(geo_loc, self.stateAbb[self.state])):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set sumlev = mid(raw, 9, 3)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set geocomp = mid(raw, 12, 2)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set sfgeoid = mid(raw, 19, 7)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set state = mid(raw, 30, 2)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set county = mid(raw, 32, 3)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set tract = mid(raw, 56, 6)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set bg = mid(raw, 62, 1)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""update %sgeo set logrecno = mid(raw, 19, 7)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""alter table %sgeo modify logrecno int""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"""alter table %sgeo add primary key (logrecno)""" %
self.stateAbb[self.state]):
raise FileError, self.query.lastError().text()
# Load the other necessary tables
for j in self.rawSFNamesNoExt[1:]:
variables, variabletypes = self.variableNames(j)
filename = "%s%s" % (self.stateAbb[self.state], j)
sf_loc = os.path.join(self.loc, '%s.uf3' % (filename))
sffile = ImportUserProvData(filename, sf_loc, variables,
variabletypes, False, False)
if self.checkIfTableExists(filename):
if not self.query.exec_(sffile.query1):
raise FileError, self.query.lastError().text()
if not self.query.exec_(sffile.query2):
raise FileError, self.query.lastError().text()
if not self.query.exec_(
"alter table %s add primary key (logrecno)" %
filename):
raise FileError, self.query.lastError().text()