def createVariable(self):
parent = self.item.parent().text(0)
if parent == 'Project Tables':
database = self.project.name
elif parent == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
tablename = self.item.text(0)
self.populateVariableDictionary(tablename)
create = CreateVariable(self.project, database, tablename, self.variableTypeDictionary, "Create New Variable", "modifydata")
if create.exec_():
projectDBC.dbc.open()
newVarName = create.newVarNameEdit.text()
numericExpression = create.formulaEdit.toPlainText()
whereExpression = create.whereEdit.toPlainText()
if len(whereExpression)<1:
whereExpression = '1'
if len(numericExpression) <1:
QMessageBox.warning(self, "Data", QString("""Invalid numeric expression, enter again"""))
else:
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""alter table %s add column %s text""" %(tablename, newVarName)):
print ("""alter table %s add column %s text""" %(tablename, newVarName))
raise FileError, query.lastError().text()
if not query.exec_("""update %s set %s = %s where %s""" %(tablename, newVarName,
numericExpression, whereExpression)):
raise FileError, query.lastError().text()
projectDBC.dbc.close()
def populateVariableDictionary(self, tablename):
parent = self.item.parent().text(0)
if parent == 'Project Tables':
database = self.project.name
elif parent == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
self.variableTypeDictionary = {}
query = QSqlQuery(projectDBC.dbc)
query.exec_("""desc %s""" %tablename)
FIELD, TYPE, NULL, KEY, DEFAULT, EXTRA = range(6)
while query.next():
field = query.value(FIELD).toString()
type = query.value(TYPE).toString()
null = query.value(NULL).toString()
key = query.value(KEY).toString()
default = query.value(DEFAULT).toString()
extra = query.value(EXTRA).toString()
self.variableTypeDictionary['%s' %field] = type
projectDBC.dbc.close()
def deleteRows(self):
tablename = self.item.text(0)
parentText = self.item.parent().text(0)
if parentText == 'Project Tables':
database = self.project.name
elif parentText == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
self.populateVariableDictionary(tablename)
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
deleteRows = DeleteRows(self.project, parentText, tablename, self.variableTypeDictionary, "Delete Records", "modifydata")
if deleteRows.exec_():
projectDBC.dbc.open()
reply = QMessageBox.question(self, "Delete Records", "Would you like to continue?",
QMessageBox.Yes| QMessageBox.Cancel)
if reply == QMessageBox.Yes:
query = QSqlQuery(projectDBC.dbc)
whereExpression = deleteRows.whereEdit.toPlainText()
#print whereExpression, 'is the text'
if not whereExpression == "":
if not query.exec_("""delete from %s where %s""" %(tablename, whereExpression)):
raise FileError, query.lastError().text()
else:
QMessageBox.warning(self, "Delete Records", """No filter expression selected, 0 records deleted.""",
QMessageBox.Ok)
projectDBC.dbc.close()
def tableChildren(self, parent, scenario=0):
if scenario > 0:
databaseName = self.project.name + 'scenario' + str(self.project.scenario)
else:
databaseName = self.project.name
projectDBC = createDBC(self.project.db, databaseName)
projectDBC.dbc.open()
self.query = QSqlQuery(projectDBC.dbc)
if not self.query.exec_("""show tables"""):
raise FileError, self.query.lastError().text()
tableItems = []
while self.query.next():
tableItems.append(self.query.value(0).toString())
projectDBC.dbc.close()
tableItems.sort()
for i in tableItems:
child = QTreeWidgetItem(parent, [QString(i)])
def checkProjectDatabase(self, db, projectName):
projectDBC = createDBC(db)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""Create Database %s""" % (projectName)):
print query.lastError().text()
reply = QMessageBox.question(
self, "Project Setup Wizard",
QString(
"""Cannot create a MySQL database when the database already exists. \n\n"""
"""Do you wish to keep the old MySQL database?"""
"""\n If Yes then re-specify the project name."""
"""\n If you wish to delete the previous MySQL data, select No."""
), QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.No:
confirm = QMessageBox.question(
self, "Project Setup Wizard",
QString("""Are you sure you want to continue?"""),
QMessageBox.Yes | QMessageBox.No)
if confirm == QMessageBox.Yes:
if not query.exec_("""Drop Database %s""" % (projectName)):
print "FileError: %s" % (query.lastError().text())
projectDBC.dbc.close()
self.projectDatabaseDummy = False
if not query.exec_("""Create Database %s""" %
(projectName)):
print "FileError: %s" % (query.lastError().text())
projectDBC.dbc.close()
self.projectDatabaseDummy = False
for i in range(5):
if not query.exec_(
"""Drop Database %s%s%s""" %
(projectName, 'scenario', str(i + 1))):
print "FileError: %s" % (query.lastError().text())
self.projectDatabaseDummy = False
if not query.exec_(
"""Create Database %s%s%s""" %
(projectName, 'scenario', str(i + 1))):
print "FileError: %s" % (query.lastError().text())
self.projectDatabaseDummy = False
projectDBC.dbc.close()
self.projectDatabaseDummy = True
else:
projectDBC.dbc.close()
self.projectDatabaseDummy = False
else:
projectDBC.dbc.close()
self.projectDatabaseDummy = False
else:
for i in range(5):
if not query.exec_("""Create Database %s%s%s""" %
(projectName, 'scenario', str(i + 1))):
print "FileError: %s" % (query.lastError().text())
self.projectDatabaseDummy = False
projectDBC.dbc.close()
self.projectDatabaseDummy = True
self.emit(SIGNAL("completeChanged()"))
def saveSelectedTable(self):
if self.treeParent == "Project Tables":
database = self.project.name
if self.treeParent == "Scenario Tables":
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/%s.%s' %(self.folder, self.tablename, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
if not query.exec_("""select * from %s into outfile """
"""'%s' fields terminated by '%s'"""
%(self.tablename, filename, self.fileSep)):
raise FileError, query.lastError().text()
tableVarDict, tableVars = self.getVariables(self.tablename, query)
self.storeMetaData(tableVars, self.folder, self.tablename)
projectDBC.dbc.close()
def __init__(self, project):
self.project = project
self.state = self.project.state
self.stateAbb = self.project.stateAbb
self.stateCode = self.project.stateCode
#self.loc = DATA_DOWNLOAD_LOCATION + os.path.sep + self.state + os.path.sep + 'PUMS2000'
#self.loc = '%s' %os.path.realpath(self.loc)
#print type(DATA_DOWNLOAD_LOCATION), type(self.state), type('PUMS2000')
self.loc = os.path.join(DATA_DOWNLOAD_LOCATION, '%s' %self.state, 'PUMS2000')
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
self.housingVariablesSelected = []
self.housingVariablesSelectedDummy = False
self.personVariablesSelected = []
self.personVariablesSelectedDummy = False
self.pumsVariableTable()
def saveSelectedTable(self):
if self.treeParent == "Project Tables":
database = self.project.name
if self.treeParent == "Scenario Tables":
database = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
folder = self.folder.replace('\\', '/')
filename = '%s/%s.%s' % (folder, self.tablename, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
if not query.exec_("""select * from %s into outfile """
"""'%s' fields terminated by '%s'""" %
(self.tablename, filename, self.fileSep)):
raise FileError, query.lastError().text()
tableVarDict, tableVars = self.getVariables(self.tablename, query)
self.storeMetaData(tableVars, self.folder, self.tablename)
projectDBC.dbc.close()
def deleteColumns(self):
parent = self.item.parent().text(0)
if parent == 'Project Tables':
database = self.project.name
elif parent == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, database)
tablename = self.item.text(0)
self.populateVariableDictionary(tablename)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
title = "Delete Dialog - %s" %tablename
deleteVariablesdia = VariableSelectionDialog(self.variableTypeDictionary, title = title, icon = "modifydata",
warning = "Note: Select variables to delete.")
if deleteVariablesdia.exec_():
deleteVariablesSelected = deleteVariablesdia.selectedVariableListWidget.variables
for i in deleteVariablesSelected:
if not query.exec_("""alter table %s drop %s""" %(tablename, i)):
raise FileError, query.lastError().text()
projectDBC.dbc.close()
def defaultTransformations(self):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
tablename = self.item.text(0)
checkPUMSTableTransforms = False
checkSFTableTransforms = False
if not self.project.sampleUserProv.userProv:
if tablename == 'housing_pums':
if self.project.sampleUserProv.defSource == 'Census 2000':
queries = DEFAULT_HOUSING_PUMS2000_QUERIES
checkPUMSTableTransforms = True
else:
queries = DEFAULT_HOUSING_PUMSACS_QUERIES
checkPUMSTableTransforms = True
if tablename == 'person_pums':
if self.project.sampleUserProv.defSource == 'Census 2000':
queries = DEFAULT_PERSON_PUMS2000_QUERIES
checkPUMSTableTransforms = True
else:
queries = DEFAULT_PERSON_PUMSACS_QUERIES
checkPUMSTableTransforms = True
if checkPUMSTableTransforms:
for i in queries:
#print "Executing Query: %s" %i
if not query.exec_("""%s""" %i):
if not query.lastError().number() == 1051:
print "FileError: %s" %query.lastError().text()
if not self.project.controlUserProv.userProv:
if tablename[:13] == 'mastersftable':
checkSFTableTransforms = True
if checkSFTableTransforms:
if self.project.controlUserProv.defSource == 'Census 2000':
queries = DEFAULT_SF2000_QUERIES
else:
queries = DEFAULT_SFACS_QUERIES
for i in queries:
#print "Executing Query: %s" %i
if not query.exec_(i %tablename):
print "FileError: %s" %query.lastError().text()
if not (checkPUMSTableTransforms or checkSFTableTransforms):
QMessageBox.warning(self, "Default Transformations",
"""No default transformations are defined for the table""",
QMessageBox.Ok)
#print "FileError: The file does not have default transformations"
projectDBC.dbc.close()
self.populate()
def __init__(self, project, parent=None):
super(SetCorrDialog, self).__init__(parent)
self.setMinimumSize(800,500)
self.setWindowTitle("Corresponding Sample Categories with Marginal Variables")
self.setWindowIcon(QIcon("./images/varcorr.png"))
import copy
self.project = copy.deepcopy(project)
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.persControlGroupBox = QGroupBox("""a. Do you wish to match distributions of both Persons """
"""and Household attributes of interest?""")
self.persControlYes = QRadioButton("Yes")
self.persControlNo = QRadioButton("No")
self.persControlYes.setChecked(True)
hLayout = QHBoxLayout()
hLayout.addWidget(self.persControlYes)
hLayout.addWidget(self.persControlNo)
self.persControlGroupBox.setLayout(hLayout)
setCorrespondenceLabel = QLabel("""b. Set the correspondence between """
"""the sample variable categories and the columns in the Marginals table""")
self.tabWidget = SetCorrTabWidget(self.project)
dialogButtonBox = QDialogButtonBox(QDialogButtonBox.Cancel| QDialogButtonBox.Ok)
correspondenceWarning = QLabel("""<font color = blue>Note: Select household/person/groupquarter variables of interest """
"""from the <b>'Sample Variable'</b> that you wish to control. Once these have been selected, """
"""create appropriate mapping between the categories of the selected variables and """
"""the columns in the marginal tables. To do this, highlight a category from the """
"""<b>'Selected Variable Categories'</b> and the corresponding column name under the """
"""<b>'Marginal Variables'</b> and click on <b>'Add Correspondence'</b>.</font>""")
correspondenceWarning.setWordWrap(True)
layout = QVBoxLayout()
layout.addWidget(self.persControlGroupBox)
layout.addWidget(Separator())
layout.addWidget(setCorrespondenceLabel)
layout.addWidget(self.tabWidget)
layout.addWidget(correspondenceWarning)
layout.addWidget(dialogButtonBox)
self.setLayout(layout)
hhldSelVariableDicts = copy.deepcopy(self.project.selVariableDicts.hhld)
self.populate(hhldSelVariableDicts, self.tabWidget.housingTab)
if self.tabWidget.personAnalyzed:
personSelVariableDicts = copy.deepcopy(self.project.selVariableDicts.person)
self.populate(personSelVariableDicts, self.tabWidget.personTab)
if self.tabWidget.gqAnalyzed:
gqSelVariableDicts = copy.deepcopy(self.project.selVariableDicts.gq)
self.populate(gqSelVariableDicts, self.tabWidget.gqTab)
self.connect(dialogButtonBox, SIGNAL("accepted()"), self.acceptCheck)
self.connect(dialogButtonBox, SIGNAL("rejected()"), self, SLOT("reject()"))
self.connect(self.persControlYes, SIGNAL("clicked()"), self.persControlYesAction)
self.connect(self.persControlNo, SIGNAL("clicked()"), self.persControlNoAction)
def save(self):
scenarioDatabase = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/summary.%s' %(self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
self.createSummaryTables(query, 'housing')
self.createSummaryTables(query, 'person')
"""
print self.project.selVariableDicts
varCorrDict = {}
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.hhld))
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.gq))
self.createControlVarTotals()
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.person))
print varCorrDict
"""
varCorrDict = {}
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.hhld))
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.gq))
self.createHousingMarginalsTable(query, varCorrDict.values())
varCorrDict = self.variableControlCorrDict(self.project.selVariableDicts.person)
self.createMarginalsTable(query, varCorrDict.values())
self.createGivenControlTotalColumns(query, self.project.selVariableDicts.hhld)
if self.isGqAnalyzed:
self.createGivenControlTotalColumns(query, self.project.selVariableDicts.gq)
if self.isPersonAnalyzed:
self.createGivenControlTotalColumns(query, self.project.selVariableDicts.person)
self.createMarginalsSummaryTable(query)
if not query.exec_("""select * from comparison into outfile """
"""'%s' fields terminated by '%s'""" %(filename, self.fileSep)):
raise FileError, query.lastError().text()
summaryTableVarDict, summaryTableVars = self.getVariables('comparison', query)
self.storeMetaData(summaryTableVars, self.folder, 'summary')
def checkProjectDatabase(self, db, projectName):
projectDBC = createDBC(db)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""Create Database %s""" %(projectName)):
print query.lastError().text()
reply = QMessageBox.question(self, "Project Setup Wizard",
QString("""Cannot create a MySQL database when the database already exists. \n\n"""
"""Do you wish to keep the old MySQL database?"""
"""\n If Yes then re-specify the project name."""
"""\n If you wish to delete the previous MySQL data, select No."""),
QMessageBox.Yes|QMessageBox.No)
if reply == QMessageBox.No:
confirm = QMessageBox.question(self, "Project Setup Wizard",
QString("""Are you sure you want to continue?"""),
QMessageBox.Yes|QMessageBox.No)
if confirm == QMessageBox.Yes:
if not query.exec_("""Drop Database %s""" %(projectName)):
print "FileError: %s" %(query.lastError().text())
projectDBC.dbc.close()
self.projectDatabaseDummy = False
if not query.exec_("""Create Database %s""" %(projectName)):
print "FileError: %s" %(query.lastError().text())
projectDBC.dbc.close()
self.projectDatabaseDummy = False
for i in range(5):
if not query.exec_("""Drop Database %s%s%s""" %(projectName, 'scenario', str(i + 1))):
print "FileError: %s" %(query.lastError().text())
self.projectDatabaseDummy = False
if not query.exec_("""Create Database %s%s%s""" %(projectName, 'scenario', str(i + 1))):
print "FileError: %s" %(query.lastError().text())
self.projectDatabaseDummy = False
projectDBC.dbc.close()
self.projectDatabaseDummy = True
else:
projectDBC.dbc.close()
self.projectDatabaseDummy = False
else:
projectDBC.dbc.close()
self.projectDatabaseDummy = False
else:
for i in range(5):
if not query.exec_("""Create Database %s%s%s""" %(projectName, 'scenario', str(i + 1))):
print "FileError: %s" %(query.lastError().text())
self.projectDatabaseDummy = False
projectDBC.dbc.close()
self.projectDatabaseDummy = True
self.emit(SIGNAL("completeChanged()"))
def tableList(self):
scenarioDatabase = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
self.projectDBC = createDBC(self.project.db, scenarioDatabase)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
tables = []
if not self.query.exec_("""show tables"""):
raise FileError, self.query.lastError.text()
while self.query.next():
tables.append('%s' %self.query.value(0).toString())
return tables
def check(self):
db = DBInfo(self.hostnameLineEdit.text(), self.usernameLineEdit.text(),
self.passwordLineEdit.text())
try:
dbconnection = createDBC(db)
if not dbconnection.dbc.open():
raise FileError, dbconnection.dbc.lastError().text()
self.connectionDummy = True
dbconnection.dbc.close()
except Exception, e:
dbconnection.dbc.close()
self.connectionDummy = False
def tableList(self):
scenarioDatabase = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
self.projectDBC = createDBC(self.project.db, scenarioDatabase)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
tables = []
if not self.query.exec_("""show tables"""):
raise FileError, self.query.lastError.text()
while self.query.next():
tables.append('%s' % self.query.value(0).toString())
return tables
def tableList(self, database):
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
tables = []
if not query.exec_("""show tables"""):
raise FileError, query.lastError.text()
while query.next():
tables.append("%s" % query.value(0).toString())
projectDBC.dbc.close()
return tables
def tables(self):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
tables = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""show tables"""):
raise FileError, query.lastError().text()
while query.next():
tables.append('%s' %query.value(0).toString())
projectDBC.dbc.close()
return tables
def tables(self):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
tables = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""show tables"""):
raise FileError, query.lastError().text()
while query.next():
tables.append('%s' % query.value(0).toString())
projectDBC.dbc.close()
return tables
def check(self):
db = DBInfo(self.hostnameLineEdit.text(),
self.usernameLineEdit.text(),
self.passwordLineEdit.text())
try:
dbconnection = createDBC(db)
if not dbconnection.dbc.open():
raise FileError, dbconnection.dbc.lastError().text()
self.connectionDummy = True
dbconnection.dbc.close()
except Exception, e:
dbconnection.dbc.close()
self.connectionDummy = False
def variablesInTable(self, tablename):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
variables = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""desc %s""" % tablename):
raise FileError, query.lastError().text()
FIELD = 0
while query.next():
field = query.value(FIELD).toString()
variables.append('%s' % field)
projectDBC.dbc.close()
return variables
def variablesInTable(self, tablename):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
variables = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""desc %s""" %tablename):
raise FileError, query.lastError().text()
FIELD = 0
while query.next():
field = query.value(FIELD).toString()
variables.append('%s' %field)
projectDBC.dbc.close()
return variables
def categories(self, varname):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
cats = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""select %s from %s group by %s""" %(varname, self.sampleSelTable, varname)):
raise FileError, query.lastError().text()
CATEGORY = 0
while query.next():
cat = unicode(query.value(CATEGORY).toString())
cats.append(cat)
self.sampleVarsDict['%s' %varname] = cats
projectDBC.dbc.close()
def categories(self, varname):
projectDBC = createDBC(self.project.db, self.project.name)
projectDBC.dbc.open()
cats = []
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""select %s from %s group by %s""" %
(varname, self.sampleSelTable, varname)):
raise FileError, query.lastError().text()
CATEGORY = 0
while query.next():
cat = unicode(query.value(CATEGORY).toString())
cats.append(cat)
self.sampleVarsDict['%s' % varname] = cats
projectDBC.dbc.close()
def __init__(self, project):
self.project = project
self.state = self.project.state
self.stateAbb = self.project.stateAbb
self.stateCode = self.project.stateCode
self.loc = DATA_DOWNLOAD_LOCATION + os.path.sep + self.state + os.path.sep + 'SF2000'
self.loc = os.path.realpath(self.loc)
self.countiesSelected = self.project.region.keys()
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
self.rawSF = RAW_SUMMARY2000_FILES
self.rawSFNamesNoExt = RAW_SUMMARY2000_FILES_NOEXT
def __init__(self, project):
self.project = project
self.state = self.project.state
self.stateAbb = self.project.stateAbb
self.stateCode = self.project.stateCode
self.loc = DATA_DOWNLOAD_LOCATION + os.path.sep + self.state + os.path.sep + 'SF2000'
self.loc = os.path.realpath(self.loc)
self.countiesSelected = self.project.region.keys()
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
self.rawSF = RAW_SUMMARY2000_FILES
self.rawSFNamesNoExt = RAW_SUMMARY2000_FILES_NOEXT
def copyTable(self):
#tablename = self.item.text(0)
parent = self.item.parent().text(0)
if self.parentText == 'Project Tables':
database = self.project.name
elif self.parentText == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
copyNameDialog = NameDialog("Copy Table - %s" %self.tablename)
if copyNameDialog.exec_():
newTablename = copyNameDialog.nameLineEdit.text()
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""create table %s select * from %s""" %(newTablename, self.tablename)):
raise FileError, query.lastError().text()
self.populate()
projectDBC.dbc.close()
def renameTable(self):
tablename = self.item.text(0)
parent = self.item.parent().text(0)
if parent == 'Project Tables':
database = self.project.name
elif parent == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
renameNameDialog = NameDialog("Rename Table - %s" %tablename)
if renameNameDialog.exec_():
newTablename = renameNameDialog.nameLineEdit.text()
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""alter table %s rename to %s""" %(tablename, newTablename)):
raise FileError, query.lastError().text()
self.populate()
projectDBC.dbc.close()
def dropTable(self):
tablename = self.item.text(0)
parent = self.item.parent().text(0)
if parent == 'Project Tables':
database = self.project.name
elif parent == 'Scenario Tables':
database = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
reply = QMessageBox.question(self, "Delete Table - %s" %tablename, "Do you wish to continue?",
QMessageBox.Yes| QMessageBox.Cancel)
if reply == QMessageBox.Yes:
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""drop table %s""" %tablename):
raise FileError, query.lastError().text()
self.populate()
projectDBC.dbc.close()
def copyTable(self):
#tablename = self.item.text(0)
parent = self.item.parent().text(0)
if self.parentText == 'Project Tables':
database = self.project.name
elif self.parentText == 'Scenario Tables':
database = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
copyNameDialog = NameDialog("Copy Table - %s" % self.tablename)
if copyNameDialog.exec_():
newTablename = copyNameDialog.nameLineEdit.text()
projectDBC = createDBC(self.project.db, database)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
if not query.exec_("""create table %s select * from %s""" %
(newTablename, self.tablename)):
raise FileError, query.lastError().text()
self.populate()
projectDBC.dbc.close()
def save(self):
scenarioDatabase = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/housing_synthetic_data.%s' %(self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
hhldVariablesDict, hhldVariables = self.getVariables('hhld_sample', query)
hhldVariablesDict = self.deleteDictEntries(hhldVariablesDict)
hhldSelVariables = self.getSelectedVariables(hhldVariablesDict, self.project.hhldVars,
"Select Household Variables to Add to Synthetic Data")
hhldvarstr = ","
if hhldSelVariables is not None:
for i in hhldSelVariables:
hhldvarstr = hhldvarstr + '%s,' %i
hhldvarstr = hhldvarstr[:-1]
else:
hhldvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No household variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table temphou1 select housing_synthetic_data.* %s from housing_synthetic_data"""
""" left join hhld_sample using (serialno)""" %(hhldvarstr)):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 drop column hhuniqueid"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 add index(serialno)"""):
raise FileError, query.lastError().text()
if self.project.gqVars:
gqVariablesDict, gqVariables = self.getVariables('gq_sample', query)
gqVariablesDict = self.deleteDictEntries(gqVariablesDict)
gqSelVariables = self.getSelectedVariables(gqVariablesDict, self.project.gqVars,
"Select Groupquarter Variables to Add to Synthetic Data")
gqvarstr = ","
if gqSelVariables is not None:
for i in gqSelVariables:
gqvarstr = gqvarstr + '%s,' %i
gqvarstr = gqvarstr[:-1]
else:
gqvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No groupquarter variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table temphou2 select temphou1.* %s from temphou1"""
""" left join gq_sample using (serialno)""" %(gqvarstr)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""alter table temphou1 rename to temphou2"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS HOUSING DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""alter table temphou2 drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou2 add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table serialcorr add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""create table temphou3 select temphou2.*, serialno from temphou2"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""select * from temphou3 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables('temphou3', query)
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" %query.lastError().text()
else:
if not query.exec_("""select * from temphou2 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables('temphou2', query)
self.storeMetaData(housingSynTableVars, self.folder, 'housing_synthetic_data')
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" %query.lastError().text()
if not self.project.gqVars:
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" %query.lastError().text()
filename = '%s/person_synthetic_data.%s' %(self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
personVariablesDict, personVariables = self.getVariables('person_sample', query)
personVariablesDict = self.deleteDictEntries(personVariablesDict)
personSelVariables = self.getSelectedVariables(personVariablesDict, self.project.personVars,
"Select Person Variables to Add to Synthetic Data")
personvarstr = ","
if personSelVariables is not None:
for i in personSelVariables:
personvarstr = personvarstr + '%s,' %i
personvarstr = personvarstr[:-1]
else:
personvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No person variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table tempperson"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table tempperson select person_synthetic_data.* %s from person_synthetic_data"""
""" left join person_sample using (serialno)""" %(personvarstr)):
raise FileError, query.lastError().text()
if not query.exec_("""alter table tempperson drop column personuniqueid"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS PERSON DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table tempperson1"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""alter table tempperson drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table tempperson add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""create table tempperson1 select tempperson.*, serialno from tempperson"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""select * from tempperson1 into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables('tempperson1', query)
#if not query.exec_("""drop table tempperson1"""):
# print "FileError:%s" %query.lastError().text()
else:
if not query.exec_("""select * from tempperson into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables('tempperson', query)
self.storeMetaData(personSynTableVars, self.folder, 'person_synthetic_data')
#if not query.exec_("""drop table tempperson"""):
# print "FileError:%s" %query.lastError().text()
projectDBC.dbc.close()
def __init__(self, project, jobserver, parent=None):
self.job_server = jobserver
super(RunDialog, self).__init__(parent)
self.setWindowTitle("Run Synthesizer")
self.setWindowIcon(QIcon("./images/run.png"))
self.setMinimumSize(800, 500)
self.project = project
scenarioDatabase = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.gqAnalyzed = self.isGqAnalyzed()
self.runGeoIds = []
self.dialogButtonBox = QDialogButtonBox(QDialogButtonBox.Cancel
| QDialogButtonBox.Ok)
selGeographiesLabel = QLabel("Selected Geographies")
self.selGeographiesList = ListWidget()
outputLabel = QLabel("Output Window")
self.outputWindow = QTextEdit()
self.selGeographiesButton = QPushButton("Select Geographies")
self.runSynthesizerButton = QPushButton("Run Synthesizer")
self.runSynthesizerButton.setEnabled(False)
runWarning = QLabel(
"""<font color = blue>Note: Select geographies by clicking on the <b>Select Geographies</b> button """
"""and then click on <b>Run Synthesizer</b> to start synthesizing population.</font>"""
)
runWarning.setWordWrap(True)
vLayout1 = QVBoxLayout()
vLayout1.addWidget(self.selGeographiesButton)
vLayout1.addWidget(selGeographiesLabel)
vLayout1.addWidget(self.selGeographiesList)
vLayout2 = QVBoxLayout()
vLayout2.addWidget(self.runSynthesizerButton)
vLayout2.addWidget(outputLabel)
vLayout2.addWidget(self.outputWindow)
hLayout = QHBoxLayout()
hLayout.addLayout(vLayout1)
hLayout.addLayout(vLayout2)
vLayout3 = QVBoxLayout()
vLayout3.addLayout(hLayout)
vLayout3.addWidget(runWarning)
vLayout3.addWidget(self.dialogButtonBox)
self.setLayout(vLayout3)
self.connect(self.selGeographiesButton, SIGNAL("clicked()"),
self.selGeographies)
self.connect(self.runSynthesizerButton, SIGNAL("clicked()"),
self.runSynthesizer)
self.connect(self.dialogButtonBox, SIGNAL("accepted()"), self,
SLOT("accept()"))
self.connect(self.dialogButtonBox, SIGNAL("rejected()"), self,
SLOT("reject()"))
def save(self):
scenarioDatabase = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/housing_synthetic_data.%s' % (self.folder,
self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
#os.chmod(filename, 0777)
os.remove(filename)
if check < 2:
hhldVariablesDict, hhldVariables = self.getVariables(
'hhld_sample', query)
hhldVariablesDict = self.deleteDictEntries(hhldVariablesDict)
hhldSelVariables = self.getSelectedVariables(
hhldVariablesDict, self.project.hhldVars,
"Select Household Variables to Add to Synthetic Data")
hhldvarstr = ","
if hhldSelVariables is not None:
for i in hhldSelVariables:
hhldvarstr = hhldvarstr + '%s,' % i
hhldvarstr = hhldvarstr[:-1]
else:
hhldvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No household variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table temphou1 select housing_synthetic_data.* %s from housing_synthetic_data"""
""" left join hhld_sample using (serialno)""" %
(hhldvarstr)):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table temphou1 drop column hhuniqueid"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 add index(serialno)"""):
raise FileError, query.lastError().text()
if self.project.gqVars:
gqVariablesDict, gqVariables = self.getVariables(
'gq_sample', query)
gqVariablesDict = self.deleteDictEntries(gqVariablesDict)
gqSelVariables = self.getSelectedVariables(
gqVariablesDict, self.project.gqVars,
"Select Groupquarter Variables to Add to Synthetic Data")
gqvarstr = ","
if gqSelVariables is not None:
for i in gqSelVariables:
gqvarstr = gqvarstr + '%s,' % i
gqvarstr = gqvarstr[:-1]
else:
gqvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No groupquarter variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table temphou2 select temphou1.* %s from temphou1"""
""" left join gq_sample using (serialno)""" %
(gqvarstr)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""alter table temphou1 rename to temphou2"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS HOUSING DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""alter table temphou2 drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou2 add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table serialcorr add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""create table temphou3 select temphou2.*, serialno from temphou2"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables(
'temphou3', query)
if self.uniqueIds is None:
if not query.exec_(
"""select * from temphou3 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""create table if not exists temphou_unique select * from temphou3 where 0"""
):
raise FileError, query.lastError().text()
if not query.exec_("""delete from temphou_unique"""):
raise FileError, query.lastError().text()
fileRef = open(
"%s/housing_synthetic_data.%s" %
(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from temphou3"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows / limitRows
offset = 0
for i in range(numBins + 1):
if not query.exec_(
"""select * from temphou3 limit %s offset %s"""
% (limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i + 1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' % query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency * 2] = '%s' % (i + 1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
#print ("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
# """fields terminated by '%s'""" %(self.folder, self.fileType, self.fileSep))
if not query.exec_("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print housingSynTableVarDict, housingSynTableVars
#print "-- Unique RECORDS EXPORT --"
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" % query.lastError().text()
else:
housingSynTableVarDict, housingSynTableVars = self.getVariables(
'temphou2', query)
if self.uniqueIds is None:
#print ("""select * from temphou2 into outfile """
# """'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
# %(self.folder, self.fileType, self.fileSep))
if not query.exec_(
"""select * from temphou2 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""create table if not exists temphou_unique select * from temphou2 where 0"""
):
raise FileError, query.lastError().text()
if not query.exec_("""delete from temphou_unique"""):
raise FileError, query.lastError().text()
fileRef = open(
"%s/housing_synthetic_data.%s" %
(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from temphou2"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows / limitRows
offset = 0
for i in range(numBins + 1):
if not query.exec_(
"""select * from temphou2 limit %s offset %s"""
% (limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i + 1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' % query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency * 2] = '%s' % (i + 1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#if not query.exec_("""
#print housingSynTableVarDict, housingSynTableVars
#print "-- Unique RECORDS EXPORT --"
self.storeMetaData(housingSynTableVars, self.folder,
'housing_synthetic_data')
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" % query.lastError().text()
if not self.project.gqVars:
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" % query.lastError().text()
filename = '%s/person_synthetic_data.%s' % (self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
personVariablesDict, personVariables = self.getVariables(
'person_sample', query)
personVariablesDict = self.deleteDictEntries(personVariablesDict)
if self.project.personVars is None:
personVarsList = []
else:
personVarsList = self.project.personVars
personSelVariables = self.getSelectedVariables(
personVariablesDict, personVarsList,
"Select Person Variables to Add to Synthetic Data")
personvarstr = ","
if personSelVariables is not None:
for i in personSelVariables:
personvarstr = personvarstr + '%s,' % i
personvarstr = personvarstr[:-1]
else:
personvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No person variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table tempperson"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table tempperson select person_synthetic_data.* %s from person_synthetic_data"""
""" left join person_sample using (serialno, pnum)""" %
(personvarstr)):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table tempperson drop column personuniqueid"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS PERSON DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table tempperson1"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""alter table tempperson drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table tempperson add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""create table tempperson1 select tempperson.*, serialno from tempperson"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables(
'tempperson1', query)
if self.uniqueIds is None:
if not query.exec_(
"""select * from tempperson1 into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""create table if not exists tempperson_unique select * from tempperson1 where 0"""
):
raise FileError, query.lastError().text()
if not query.exec_("""delete from tempperson_unique"""):
raise FileError, query.lastError().text()
fileRef = open(
"%s/person_synthetic_data.%s" %
(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from tempperson1"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows / limitRows
offset = 0
for i in range(numBins + 1):
if not query.exec_(
"""select * from tempperson1 limit %s offset %s"""
% (limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i + 1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' % query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency * 2] = '%s' % (i + 1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/person_synthetic_data.%s' into table tempperson_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print personSynTableVarDict, personSynTableVars
#print "-- Unique RECORDS EXPORT --"
else:
personSynTableVarDict, personSynTableVars = self.getVariables(
'tempperson', query)
if self.uniqueIds is None:
if not query.exec_(
"""select * from tempperson into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""create table if not exists tempperson_unique select * from tempperson where 0"""
):
raise FileError, query.lastError().text()
if not query.exec_("""delete from tempperson_unique"""):
raise FileError, query.lastError().text()
fileRef = open(
"%s/person_synthetic_data.%s" %
(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from tempperson"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows / limitRows
offset = 0
for i in range(numBins + 1):
if not query.exec_(
"""select * from tempperson limit %s offset %s"""
% (limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i + 1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' % query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency * 2] = '%s' % (i + 1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/person_synthetic_data.%s' into table tempperson_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print personSynTableVarDict, personSynTableVars
#print "-- Unique RECORDS EXPORT --"
self.storeMetaData(personSynTableVars, self.folder,
'person_synthetic_data')
#if not query.exec_("""drop table tempperson"""):
# print "FileError:%s" %query.lastError().text()
projectDBC.dbc.close()
def save(self):
scenarioDatabase = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/summary.%s' % (self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
self.createSummaryTables(query, 'housing')
self.createSummaryTables(query, 'person')
"""
print self.project.selVariableDicts
varCorrDict = {}
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.hhld))
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.gq))
self.createControlVarTotals()
varCorrDict.update(self.variableControlCorrDict(self.project.selVariableDicts.person))
print varCorrDict
"""
varCorrDict = {}
varCorrDict.update(
self.variableControlCorrDict(
self.project.selVariableDicts.hhld))
varCorrDict.update(
self.variableControlCorrDict(self.project.selVariableDicts.gq))
self.createHousingMarginalsTable(query, varCorrDict.values())
varCorrDict = self.variableControlCorrDict(
self.project.selVariableDicts.person)
self.createMarginalsTable(query, varCorrDict.values())
self.createGivenControlTotalColumns(
query, self.project.selVariableDicts.hhld)
if self.isGqAnalyzed:
self.createGivenControlTotalColumns(
query, self.project.selVariableDicts.gq)
if self.isPersonAnalyzed:
self.createGivenControlTotalColumns(
query, self.project.selVariableDicts.person)
self.createMarginalsSummaryTable(query)
if not query.exec_("""select * from comparison into outfile """
"""'%s' fields terminated by '%s'""" %
(filename, self.fileSep)):
raise FileError, query.lastError().text()
summaryTableVarDict, summaryTableVars = self.getVariables(
'comparison', query)
self.storeMetaData(summaryTableVars, self.folder, 'summary')
def save(self):
scenarioDatabase = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/housing_synthetic_data.%s' %(self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
#os.chmod(filename, 0777)
os.remove(filename)
if check < 2:
hhldVariablesDict, hhldVariables = self.getVariables('hhld_sample', query)
hhldVariablesDict = self.deleteDictEntries(hhldVariablesDict)
hhldSelVariables = self.getSelectedVariables(hhldVariablesDict, self.project.hhldVars,
"Select Household Variables to Add to Synthetic Data")
hhldvarstr = ","
if hhldSelVariables is not None:
for i in hhldSelVariables:
hhldvarstr = hhldvarstr + '%s,' %i
hhldvarstr = hhldvarstr[:-1]
else:
hhldvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No household variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table temphou1 select housing_synthetic_data.* %s from housing_synthetic_data"""
""" left join hhld_sample using (serialno)""" %(hhldvarstr)):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 drop column hhuniqueid"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 add index(serialno)"""):
raise FileError, query.lastError().text()
if self.project.gqVars:
gqVariablesDict, gqVariables = self.getVariables('gq_sample', query)
gqVariablesDict = self.deleteDictEntries(gqVariablesDict)
gqSelVariables = self.getSelectedVariables(gqVariablesDict, self.project.gqVars,
"Select Groupquarter Variables to Add to Synthetic Data")
gqvarstr = ","
if gqSelVariables is not None:
for i in gqSelVariables:
gqvarstr = gqvarstr + '%s,' %i
gqvarstr = gqvarstr[:-1]
else:
gqvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No groupquarter variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table temphou2 select temphou1.* %s from temphou1"""
""" left join gq_sample using (serialno)""" %(gqvarstr)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""alter table temphou1 rename to temphou2"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS HOUSING DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""alter table temphou2 drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou2 add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table serialcorr add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""create table temphou3 select temphou2.*, serialno from temphou2"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables('temphou3', query)
if self.uniqueIds is None:
if not query.exec_("""select * from temphou3 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""create table if not exists temphou_unique select * from temphou3 where 0"""):
raise FileError, query.lastError().text()
if not query.exec_("""delete from temphou_unique"""):
raise FileError, query.lastError().text()
fileRef = open("%s/housing_synthetic_data.%s" %(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from temphou3"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows/limitRows
offset = 0
for i in range(numBins+1):
if not query.exec_("""select * from temphou3 limit %s offset %s"""
%(limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i+1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' %query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency*2] = '%s' %(i+1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
#print ("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
# """fields terminated by '%s'""" %(self.folder, self.fileType, self.fileSep))
if not query.exec_("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print housingSynTableVarDict, housingSynTableVars
#print "-- Unique RECORDS EXPORT --"
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" %query.lastError().text()
else:
housingSynTableVarDict, housingSynTableVars = self.getVariables('temphou2', query)
if self.uniqueIds is None:
#print ("""select * from temphou2 into outfile """
# """'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
# %(self.folder, self.fileType, self.fileSep))
if not query.exec_("""select * from temphou2 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""create table if not exists temphou_unique select * from temphou2 where 0"""):
raise FileError, query.lastError().text()
if not query.exec_("""delete from temphou_unique"""):
raise FileError, query.lastError().text()
fileRef = open("%s/housing_synthetic_data.%s" %(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from temphou2"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows/limitRows
offset = 0
for i in range(numBins+1):
if not query.exec_("""select * from temphou2 limit %s offset %s"""
%(limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i+1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' %query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency*2] = '%s' %(i+1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/housing_synthetic_data.%s' into table temphou_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#if not query.exec_("""
#print housingSynTableVarDict, housingSynTableVars
#print "-- Unique RECORDS EXPORT --"
self.storeMetaData(housingSynTableVars, self.folder, 'housing_synthetic_data')
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" %query.lastError().text()
if not self.project.gqVars:
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" %query.lastError().text()
filename = '%s/person_synthetic_data.%s' %(self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
personVariablesDict, personVariables = self.getVariables('person_sample', query)
personVariablesDict = self.deleteDictEntries(personVariablesDict)
if self.project.personVars is None:
personVarsList = []
else:
personVarsList = self.project.personVars
personSelVariables = self.getSelectedVariables(personVariablesDict, personVarsList,
"Select Person Variables to Add to Synthetic Data")
personvarstr = ","
if personSelVariables is not None:
for i in personSelVariables:
personvarstr = personvarstr + '%s,' %i
personvarstr = personvarstr[:-1]
else:
personvarstr = ""
QMessageBox.warning(self, "Export Synthetic Population Tables",
"""No person variables selected for exporting""", QMessageBox.Ok)
if not query.exec_("""drop table tempperson"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""create table tempperson select person_synthetic_data.* %s from person_synthetic_data"""
""" left join person_sample using (serialno, pnum)""" %(personvarstr)):
raise FileError, query.lastError().text()
if not query.exec_("""alter table tempperson drop column personuniqueid"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS PERSON DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table tempperson1"""):
print "FileError:%s" %query.lastError().text()
if not query.exec_("""alter table tempperson drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table tempperson add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_("""create table tempperson1 select tempperson.*, serialno from tempperson"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables('tempperson1', query)
if self.uniqueIds is None:
if not query.exec_("""select * from tempperson1 into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""create table if not exists tempperson_unique select * from tempperson1 where 0"""):
raise FileError, query.lastError().text()
if not query.exec_("""delete from tempperson_unique"""):
raise FileError, query.lastError().text()
fileRef = open("%s/person_synthetic_data.%s" %(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from tempperson1"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows/limitRows
offset = 0
for i in range(numBins+1):
if not query.exec_("""select * from tempperson1 limit %s offset %s"""
%(limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i+1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' %query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency*2] = '%s' %(i+1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/person_synthetic_data.%s' into table tempperson_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print personSynTableVarDict, personSynTableVars
#print "-- Unique RECORDS EXPORT --"
else:
personSynTableVarDict, personSynTableVars = self.getVariables('tempperson', query)
if self.uniqueIds is None:
if not query.exec_("""select * from tempperson into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
%(self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
else:
if not query.exec_("""create table if not exists tempperson_unique select * from tempperson where 0"""):
raise FileError, query.lastError().text()
if not query.exec_("""delete from tempperson_unique"""):
raise FileError, query.lastError().text()
fileRef = open("%s/person_synthetic_data.%s" %(self.folder, self.fileType), 'w')
if not query.exec_("""select count(*) from tempperson"""):
raise FileError, query.lastError().text()
while query.next():
numRows = query.value(0).toInt()[0]
limitRows = 500000
numBins = numRows/limitRows
offset = 0
for i in range(numBins+1):
if not query.exec_("""select * from tempperson limit %s offset %s"""
%(limitRows, offset)):
raise FileError, query.lastError().text()
offset = (i+1) * limitRows
rec = query.record()
cols = rec.count()
colIndices = range(cols)
indexOfFrequency = rec.indexOf("frequency")
while query.next():
freq = query.value(indexOfFrequency).toInt()[0]
cols = []
for i in colIndices:
cols.append('%s' %query.value(i).toString())
cols.append(self.fileSep)
for i in range(freq):
cols[indexOfFrequency*2] = '%s' %(i+1)
fileRef.write(''.join(cols[:-1]))
fileRef.write('\n')
fileRef.close()
folder = self.folder.replace("\\", "/")
if not query.exec_("""load data local infile '%s/person_synthetic_data.%s' into table tempperson_unique """\
"""fields terminated by '%s'""" %(folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
#print personSynTableVarDict, personSynTableVars
#print "-- Unique RECORDS EXPORT --"
self.storeMetaData(personSynTableVars, self.folder, 'person_synthetic_data')
#if not query.exec_("""drop table tempperson"""):
# print "FileError:%s" %query.lastError().text()
projectDBC.dbc.close()
def __init__(self, project, jobserver, parent=None):
self.job_server = jobserver
super(RunDialog, self).__init__(parent)
self.setWindowTitle("Run Synthesizer")
self.setWindowIcon(QIcon("./images/run.png"))
self.setMinimumSize(800,500)
self.project = project
scenarioDatabase = '%s%s%s' %(self.project.name, 'scenario', self.project.scenario)
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.gqAnalyzed = self.isGqAnalyzed()
self.runGeoIds = []
self.dialogButtonBox = QDialogButtonBox(QDialogButtonBox.Cancel| QDialogButtonBox.Ok)
selGeographiesLabel = QLabel("Selected Geographies")
self.selGeographiesList = ListWidget()
outputLabel = QLabel("Output Window")
self.outputWindow = QTextEdit()
self.selGeographiesButton = QPushButton("Select Geographies")
self.runSynthesizerButton = QPushButton("Run Synthesizer")
self.runSynthesizerButton.setEnabled(False)
runWarning = QLabel("""<font color = blue>Note: Select geographies by clicking on the <b>Select Geographies</b> button """
"""and then click on <b>Run Synthesizer</b> to start synthesizing population.</font>""")
runWarning.setWordWrap(True)
vLayout1 = QVBoxLayout()
vLayout1.addWidget(self.selGeographiesButton)
vLayout1.addWidget(selGeographiesLabel)
vLayout1.addWidget(self.selGeographiesList)
vLayout2 = QVBoxLayout()
vLayout2.addWidget(self.runSynthesizerButton)
vLayout2.addWidget(outputLabel)
vLayout2.addWidget(self.outputWindow)
hLayout = QHBoxLayout()
hLayout.addLayout(vLayout1)
hLayout.addLayout(vLayout2)
vLayout3 = QVBoxLayout()
vLayout3.addLayout(hLayout)
vLayout3.addWidget(runWarning)
vLayout3.addWidget(self.dialogButtonBox)
self.setLayout(vLayout3)
self.connect(self.selGeographiesButton, SIGNAL("clicked()"), self.selGeographies)
self.connect(self.runSynthesizerButton, SIGNAL("clicked()"), self.runSynthesizer)
self.connect(self.dialogButtonBox, SIGNAL("accepted()"), self, SLOT("accept()"))
self.connect(self.dialogButtonBox, SIGNAL("rejected()"), self, SLOT("reject()"))
def save(self):
scenarioDatabase = '%s%s%s' % (self.project.name, 'scenario',
self.project.scenario)
projectDBC = createDBC(self.project.db, scenarioDatabase)
projectDBC.dbc.open()
query = QSqlQuery(projectDBC.dbc)
filename = '%s/housing_synthetic_data.%s' % (self.folder,
self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
hhldVariablesDict, hhldVariables = self.getVariables(
'hhld_sample', query)
hhldVariablesDict = self.deleteDictEntries(hhldVariablesDict)
hhldSelVariables = self.getSelectedVariables(
hhldVariablesDict, self.project.hhldVars,
"Select Household Variables to Add to Synthetic Data")
hhldvarstr = ","
if hhldSelVariables is not None:
for i in hhldSelVariables:
hhldvarstr = hhldvarstr + '%s,' % i
hhldvarstr = hhldvarstr[:-1]
else:
hhldvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No household variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table temphou1 select housing_synthetic_data.* %s from housing_synthetic_data"""
""" left join hhld_sample using (serialno)""" %
(hhldvarstr)):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table temphou1 drop column hhuniqueid"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou1 add index(serialno)"""):
raise FileError, query.lastError().text()
if self.project.gqVars:
gqVariablesDict, gqVariables = self.getVariables(
'gq_sample', query)
gqVariablesDict = self.deleteDictEntries(gqVariablesDict)
gqSelVariables = self.getSelectedVariables(
gqVariablesDict, self.project.gqVars,
"Select Groupquarter Variables to Add to Synthetic Data")
gqvarstr = ","
if gqSelVariables is not None:
for i in gqSelVariables:
gqvarstr = gqvarstr + '%s,' % i
gqvarstr = gqvarstr[:-1]
else:
gqvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No groupquarter variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table temphou2 select temphou1.* %s from temphou1"""
""" left join gq_sample using (serialno)""" %
(gqvarstr)):
raise FileError, query.lastError().text()
else:
if not query.exec_(
"""alter table temphou1 rename to temphou2"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS HOUSING DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""alter table temphou2 drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_("""alter table temphou2 add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table serialcorr add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""create table temphou3 select temphou2.*, serialno from temphou2"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""select * from temphou3 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables(
'temphou3', query)
if not query.exec_("""drop table temphou3"""):
print "FileError:%s" % query.lastError().text()
else:
if not query.exec_(
"""select * from temphou2 into outfile """
"""'%s/housing_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
housingSynTableVarDict, housingSynTableVars = self.getVariables(
'temphou2', query)
self.storeMetaData(housingSynTableVars, self.folder,
'housing_synthetic_data')
if not query.exec_("""drop table temphou1"""):
print "FileError:%s" % query.lastError().text()
if not self.project.gqVars:
if not query.exec_("""drop table temphou2"""):
print "FileError:%s" % query.lastError().text()
filename = '%s/person_synthetic_data.%s' % (self.folder, self.fileType)
check = self.checkIfFileExists(filename)
if check == 0:
os.remove(filename)
if check < 2:
personVariablesDict, personVariables = self.getVariables(
'person_sample', query)
personVariablesDict = self.deleteDictEntries(personVariablesDict)
personSelVariables = self.getSelectedVariables(
personVariablesDict, self.project.personVars,
"Select Person Variables to Add to Synthetic Data")
personvarstr = ","
if personSelVariables is not None:
for i in personSelVariables:
personvarstr = personvarstr + '%s,' % i
personvarstr = personvarstr[:-1]
else:
personvarstr = ""
QMessageBox.warning(
self, "Export Synthetic Population Tables",
"""No person variables selected for exporting""",
QMessageBox.Ok)
if not query.exec_("""drop table tempperson"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""create table tempperson select person_synthetic_data.* %s from person_synthetic_data"""
""" left join person_sample using (serialno)""" %
(personvarstr)):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table tempperson drop column personuniqueid"""):
raise FileError, query.lastError().text()
if self.project.sampleUserProv.defSource == "ACS 2005-2007":
#print 'ACS PERSON DATA MODIFYING THE SERIALNOS'
if not query.exec_("""drop table tempperson1"""):
print "FileError:%s" % query.lastError().text()
if not query.exec_(
"""alter table tempperson drop column serialno"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""alter table tempperson add index(hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""create table tempperson1 select tempperson.*, serialno from tempperson"""
""" left join serialcorr using (hhid)"""):
raise FileError, query.lastError().text()
if not query.exec_(
"""select * from tempperson1 into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables(
'tempperson1', query)
#if not query.exec_("""drop table tempperson1"""):
# print "FileError:%s" %query.lastError().text()
else:
if not query.exec_(
"""select * from tempperson into outfile """
"""'%s/person_synthetic_data.%s' fields terminated by '%s'"""
% (self.folder, self.fileType, self.fileSep)):
raise FileError, query.lastError().text()
personSynTableVarDict, personSynTableVars = self.getVariables(
'tempperson', query)
self.storeMetaData(personSynTableVars, self.folder,
'person_synthetic_data')
#if not query.exec_("""drop table tempperson"""):
# print "FileError:%s" %query.lastError().text()
projectDBC.dbc.close()
def __init__(self, project):
self.project = project
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
def __init__(self, project, tablename, treeParent, parent=None):
super(DisplayTable, self).__init__(parent)
self.project = project
if treeParent == 'Project Tables':
databaseName = self.project.name
else:
databaseName = self.project.name + 'scenario' + str(
self.project.scenario)
self.projectDBC = createDBC(self.project.db, databaseName)
self.projectDBC.dbc.open()
self.tablename = tablename
self.setWindowTitle("Data Table - %s" % self.tablename)
self.setWindowIcon(QIcon("./images/modifydata.png"))
self.variableTypeDictionary = {}
self.populateVariableDictionary()
self.model = QSqlTableModel(parent, self.projectDBC.dbc)
self.model.setTable(self.tablename)
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setMinimumSize(QSize(800, 500))
outputLabel = QLabel("Output")
self.output = QTextEdit()
self.output.setMinimumSize(QSize(800, 100))
layoutView = QVBoxLayout()
layoutView.addWidget(self.view)
layoutView.addWidget(outputLabel)
layoutView.addWidget(self.output)
descButton = QPushButton("Descriptives")
freqButton = QPushButton("Frequencies")
layoutButton = QVBoxLayout()
layoutButton.addWidget(descButton)
layoutButton.addWidget(freqButton)
layoutButton.addItem(QSpacerItem(10, 550))
hLayout = QHBoxLayout()
hLayout.addLayout(layoutView)
hLayout.addLayout(layoutButton)
buttonBox = QDialogButtonBox(QDialogButtonBox.Cancel
| QDialogButtonBox.Ok)
vLayout = QVBoxLayout()
vLayout.addLayout(hLayout)
vLayout.addWidget(buttonBox)
self.setLayout(vLayout)
self.connect(buttonBox, SIGNAL("accepted()"), self, SLOT("accept()"))
self.connect(buttonBox, SIGNAL("rejected()"), self, SLOT("reject()"))
self.connect(descButton, SIGNAL("clicked()"), self.descriptives)
self.connect(freqButton, SIGNAL("clicked()"), self.frequencies)
def __init__(self, project, tablename, treeParent, parent=None):
super(DisplayTable, self).__init__(parent)
self.project = project
if treeParent == 'Project Tables':
databaseName = self.project.name
else:
databaseName = self.project.name + 'scenario' + str(self.project.scenario)
self.projectDBC = createDBC(self.project.db, databaseName)
self.projectDBC.dbc.open()
self.tablename = tablename
self.setWindowTitle("Data Table - %s" %self.tablename)
self.setWindowIcon(QIcon("./images/modifydata.png"))
self.variableTypeDictionary = {}
self.populateVariableDictionary()
self.model = QSqlTableModel(parent, self.projectDBC.dbc)
self.model.setTable(self.tablename)
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setMinimumSize(QSize(800, 500))
outputLabel = QLabel("Output")
self.output = QTextEdit()
self.output.setMinimumSize(QSize(800, 100))
layoutView = QVBoxLayout()
layoutView.addWidget(self.view)
layoutView.addWidget(outputLabel)
layoutView.addWidget(self.output)
descButton = QPushButton("Descriptives")
freqButton = QPushButton("Frequencies")
layoutButton = QVBoxLayout()
layoutButton.addWidget(descButton)
layoutButton.addWidget(freqButton)
layoutButton.addItem(QSpacerItem(10, 550))
hLayout = QHBoxLayout()
hLayout.addLayout(layoutView)
hLayout.addLayout(layoutButton)
buttonBox = QDialogButtonBox(QDialogButtonBox.Cancel| QDialogButtonBox.Ok)
vLayout = QVBoxLayout()
vLayout.addLayout(hLayout)
vLayout.addWidget(buttonBox)
self.setLayout(vLayout)
self.connect(buttonBox, SIGNAL("accepted()"), self, SLOT("accept()"))
self.connect(buttonBox, SIGNAL("rejected()"), self, SLOT("reject()"))
self.connect(descButton, SIGNAL("clicked()"), self.descriptives)
self.connect(freqButton, SIGNAL("clicked()"), self.frequencies)
def __init__(self, project, parent=None):
super(SetCorrDialog, self).__init__(parent)
self.setMinimumSize(800, 500)
self.setWindowTitle(
"Corresponding Sample Categories with Marginal Variables")
self.setWindowIcon(QIcon("./images/varcorr.png"))
import copy
self.project = copy.deepcopy(project)
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.persControlGroupBox = QGroupBox(
"""a. Do you wish to match distributions of both Persons """
"""and Household attributes of interest?""")
self.persControlYes = QRadioButton("Yes")
self.persControlNo = QRadioButton("No")
self.persControlYes.setChecked(True)
hLayout = QHBoxLayout()
hLayout.addWidget(self.persControlYes)
hLayout.addWidget(self.persControlNo)
self.persControlGroupBox.setLayout(hLayout)
setCorrespondenceLabel = QLabel(
"""b. Set the correspondence between """
"""the sample variable categories and the columns in the Marginals table"""
)
self.tabWidget = SetCorrTabWidget(self.project)
dialogButtonBox = QDialogButtonBox(QDialogButtonBox.Cancel
| QDialogButtonBox.Ok)
correspondenceWarning = QLabel(
"""<font color = blue>Note: Select household/person/groupquarter variables of interest """
"""from the <b>'Sample Variable'</b> that you wish to control. Once these have been selected, """
"""create appropriate mapping between the categories of the selected variables and """
"""the columns in the marginal tables. To do this, highlight a category from the """
"""<b>'Selected Variable Categories'</b> and the corresponding column name under the """
"""<b>'Marginal Variables'</b> and click on <b>'Add Correspondence'</b>.</font>"""
)
correspondenceWarning.setWordWrap(True)
layout = QVBoxLayout()
layout.addWidget(self.persControlGroupBox)
layout.addWidget(Separator())
layout.addWidget(setCorrespondenceLabel)
layout.addWidget(self.tabWidget)
layout.addWidget(correspondenceWarning)
layout.addWidget(dialogButtonBox)
self.setLayout(layout)
hhldSelVariableDicts = copy.deepcopy(
self.project.selVariableDicts.hhld)
self.populate(hhldSelVariableDicts, self.tabWidget.housingTab)
if self.tabWidget.personAnalyzed:
personSelVariableDicts = copy.deepcopy(
self.project.selVariableDicts.person)
self.populate(personSelVariableDicts, self.tabWidget.personTab)
if self.tabWidget.gqAnalyzed:
gqSelVariableDicts = copy.deepcopy(
self.project.selVariableDicts.gq)
self.populate(gqSelVariableDicts, self.tabWidget.gqTab)
self.connect(dialogButtonBox, SIGNAL("accepted()"), self.acceptCheck)
self.connect(dialogButtonBox, SIGNAL("rejected()"), self,
SLOT("reject()"))
self.connect(self.persControlYes, SIGNAL("clicked()"),
self.persControlYesAction)
self.connect(self.persControlNo, SIGNAL("clicked()"),
self.persControlNoAction)
def __init__(self, project):
self.project = project
self.projectDBC = createDBC(self.project.db, self.project.name)
self.projectDBC.dbc.open()
self.query = QSqlQuery(self.projectDBC.dbc)
