def _set_df(self): sql_code = 'ATTACH DATABASE ? AS ?' args = (str(constants.DB_PATHS[constants.STARS_DB]), constants.STARS_DB) con = sql.connect( constants.DB_PATHS[constants.STATEWIDE_DATASETS_DB], uri=True, timeout=constants.DB_TIMEOUT ) db_attached = utilities.execute_sql( sql_code=sql_code, args=args, open_con=con, dontfetch=True ) if db_attached: results = utilities.execute_sql( sql_code=self.query, open_con=con, getcursor=True ) if results: column_names = [i[0] for i in results.description] data = results.fetchall() self.df = pd.DataFrame(data, columns=column_names) con.close()
def _insert_values( self, db_name, column_names, values): ''' Args: db_name: A string that represents the name of the database that contains the table where the values will be inserted. column_names: A list of strings with each of the column names for the table where the values will be inserted. values: A list with the values that will be inserted into the table. ''' column_string = self._get_column_string(column_names) place_holders = self._get_value_place_holders(len(column_names)) sql_code = ( f'INSERT INTO {self.table_name}({column_string}) ' f'VALUES({place_holders})') utilities.execute_sql( sql_code=sql_code, args=values, db_name=db_name, dml=True, many=True )
def _attach_values_db(self): sql_code = 'ATTACH DATABASE ? AS ?' args = (str(constants.DB_PATHS[self.values_db]), self.values_db) utilities.execute_sql(sql_code=sql_code, args=args, open_con=self.con, dontfetch=True)
def _set_tables(self): if not self._business_table_exists(): self._create_table(constants.BUSINESSES_DB, self.business_columns) # drops the values table if it exists sql_code = f'DROP TABLE IF EXISTS {self.table_name}' utilities.execute_sql( sql_code=sql_code, db_name=self.values_db, dml=True ) self._create_table( self.values_db, self.quarterly_columns, quarterly=True)
def _create_table( self, db_name, column_names, quarterly=False): ''' Creates a table in the given database with the column names in the list provided and if the data is quarterly then the column names will have different types. If it is quarterly then the id column will be of the text type, but the rest of the columns will be integers. If the data is not quarterly then the columns will all be of the text type. The table will be created in the database of the name that is provided and with the names the list of column names. The only thing that will change in the definition of the table if the data is quarterly is the data type of the id column in the definition of the table when it is created and the other parts of the table will be the same. Args: db_name: A string representing the name of the database where the new table will be created in. column_names: A list of strings which are the column names that will be used for the new table. quarterly: A boolean that flags whether or not the data will go into one of the quarterly databases or not. ''' data_type = 'INTEGER' if quarterly else 'TEXT' column_string = self._get_column_string(column_names, data_type) if not quarterly: # sets data type for business code id column column = constants.BUSINESS_CODE_ID_COLUMN_NAME.upper() column_string = column_string.replace( f'{column} TEXT', f'{column} INTEGER' ) # sets id as primary key column_string = column_string.replace( f'{constants.ID_COLUMN_NAME} {data_type}', f'{constants.ID_COLUMN_NAME} TEXT PRIMARY KEY' ) schema = f'CREATE TABLE {self.table_name} ({column_string})' if not quarterly: schema = self._insert_business_code_foreign_key(schema) utilities.execute_sql( sql_code=schema, db_name=db_name, dml=True )
def _set_selected_report_attributes(self): condition = ( f'={self.selected_report_ids[0]}' if len(self.selected_report_ids) == 1 else f'IN {self.selected_report_ids}' ) query = ( f'SELECT Id, Name, Path, IsDuplex ' f'FROM {constants.PACKET_REPORTS_TABLE} ' f'WHERE Id {condition}' ) results = utilities.execute_sql( sql_code=query, db_name=constants.STARS_DB, fetchall=True ) if results: # adds the attributes to the list of selected report attributes # according to the order of the selected report ids for attributes in results: report_id = attributes[0] if self.selected_report_attributes: index = self._get_selected_report_attributes_index(report_id) if index < len(self.selected_report_attributes): self.selected_report_attributes.insert(index, attributes) else: self.selected_report_attributes.append(attributes) else: self.selected_report_attributes.append(attributes)
def _get_geo_ranges(self, range_ids): args = [ self.jurisdiction.id, ] # if there is more than one range id or a list or range ids if isinstance(range_ids, list): # the range id will be checked against all range ids range_id_condition = f'IN {tuple(range_ids)}' else: # only the one range id has to be checked range_id_condition = '=?' # adds the range id to the list or arguments that will be passed # along with the sql query args.append(range_ids) query = f''' SELECT street, street_type, dir, pdir, side, low, high FROM geo_ranges WHERE jurisdiction_id=? AND range_id {range_id_condition} ''' results = utilities.execute_sql( sql_code=query, args=args, fetchall=True, db_name=constants.STATEWIDE_DATASETS_DB) return results
def _set_business_code_totals_countywide(self): query = f''' SELECT {constants.BUSINESS_CODE_ID_COLUMN_NAME}, {self.bmy_periods_string_county} FROM {constants.BUSINESS_CODE_TOTALS_TABLE} t, {constants.JURISDICTIONS_TABLE} j WHERE t.{constants.TAC_COLUMN_NAME}=j.{constants.TAC_COLUMN_NAME} AND j.{constants.COUNTY_ID_COLUMN_NAME}=? GROUP BY {constants.BUSINESS_CODE_ID_COLUMN_NAME} ''' args = (self.jurisdiction.county_id, ) results = utilities.execute_sql( sql_code=query, args=args, db_name=constants.STATEWIDE_DATASETS_DB, fetchall=True, attach_db='starsdb') if results: for business_code_id, amount in results: self.business_code_totals[business_code_id] = amount
def _set_jurisdiction_ebi(self): if self.is_countywide: query = f''' SELECT SUM({EBI_COLUMN_NAME}) FROM {EBI_COLUMN_NAME} e, {constants.JURISDICTIONS_TABLE} j WHERE e.Id=j.{constants.TAC_COLUMN_NAME} AND j.HasData=1 AND j.{constants.COUNTY_ID_COLUMN_NAME}=? ''' args = (self.jurisdiction.county_id, ) attach_db = 'starsdb' else: query = f''' SELECT {EBI_COLUMN_NAME} FROM {EBI_COLUMN_NAME} WHERE Id=? ''' args = (self.jurisdiction.tac, ) attach_db = '' results = utilities.execute_sql( sql_code=query, args=args, attach_db=attach_db, db_name=constants.STATEWIDE_DATASETS_DB) if results and results[0] is not None: self.jurisdiction_ebi = results[0] // 1000
def _set_segments(self): ''' Stores the segment ids and their names. ''' self.segments = {} query = f''' SELECT {constants.CATEGORY_ID_COLUMN_NAME}, Id, name FROM {constants.SEGMENTS_TABLE} ''' results = utilities.execute_sql(sql_code=query, db_name=constants.STARS_DB, fetchall=True) if results: # sorts by the segment name results = sorted(results, key=itemgetter(2)) for category_id, segment_id, name in results: if category_id not in self.segments: self.segments[category_id] = {} self.segments[category_id][segment_id] = name
def _set_business_codes(self): ''' Stores the business codes and their ids in a dictionary with the id as the key and the name as the value inside a nested dictionary with the sgment_id as the key. ''' self.business_codes = {} query = f''' SELECT {constants.SEGMENT_ID_COLUMN_NAME}, Id, name FROM {constants.BUSINESS_CODES_TABLE} ''' results = utilities.execute_sql(sql_code=query, db_name=constants.STARS_DB, fetchall=True) if results: # sorts by the business code name results = sorted(results, key=itemgetter(2)) for segment_id, business_code_id, name in results: if name != 'BLANK': if segment_id not in self.business_codes: self.business_codes[segment_id] = {} self.business_codes[segment_id][business_code_id] = name
def _query_tables(self): table_count = len(self.lookup_tables) progress = 1 for i, table_name in enumerate(self.lookup_tables, start=1): if not self.abort: self.progress.update_progress( progress, f'Querying table {i} of {table_count}: {table_name}' ' Results for "{}" = {:,}'.format( self.lookup_value, self.result_count)) query = self.query.replace('table_name', table_name) results = utilities.execute_sql(sql_code=query, open_con=self.con, fetchall=True, show_error=False) if results: self.results.extend(results) self.result_count += len(results) progress += 100 / table_count
def _set_county_pool_amounts(self): query = f''' SELECT SUM(county_pool_amount) FROM {constants.CDTFA_ALLOCATION_TABLE} c, {constants.JURISDICTIONS_TABLE} j WHERE c.{constants.TAC_COLUMN_NAME} = j.{constants.TAC_COLUMN_NAME} AND j.{constants.COUNTY_ID_COLUMN_NAME} = ? AND period IN {tuple(self.period_headers)} GROUP BY period ORDER BY period ''' amounts = utilities.execute_sql( sql_code=query, args=(self.jurisdiction.county_id, ), db_name=constants.STATEWIDE_DATASETS_DB, attach_db=constants.STARS_DB, fetchall=True) if amounts: self.county_pool_amounts = [int(x[0]) for x in amounts]
def _set_categories(self): query = f''' SELECT Id, name FROM {constants.CATEGORIES_TABLE} ''' self.categories = utilities.execute_sql(sql_code=query, db_name=constants.STARS_DB, fetchall=True)
def _set_businesses(self): self.businesses = [] results = utilities.execute_sql(sql_code=self.businesses_query, db_name=constants.BUSINESSES_DB, fetchall=True) if results: self.businesses = results
def _set_category_totals(self): args = ((self.jurisdiction.county_id, ) if self.is_county_wide else (self.jurisdiction.tac, )) self.category_totals = utilities.execute_sql( sql_code=self.sales_tax_query, args=args, db_name=constants.STATEWIDE_DATASETS_DB, fetchall=True, attach_db=constants.STARS_DB)
def _insert_new_rows(self): column_names = list(self.df) column_string = ','.join(column_names) value_place_holders = ','.join('?' for _ in range(len(column_names))) query = f''' INSERT INTO {self.table_name}({column_string}) VALUES({value_place_holders}) ''' values = self.df.values.tolist() utilities.execute_sql(sql_code=query, args=values, db_name=self.db_name, dml=True, many=True)
def _set_dataframes(self): for name in DATA_NAMES: data = utilities.execute_sql( sql_code=self.queries[name], args=self.args[name], db_name=constants.STATEWIDE_DATASETS_DB, fetchall=True, attach_db=constants.STARS_DB) self.dfs[name] = pd.DataFrame( data, columns=[constants.CATEGORY_COLUMN_NAME] + self.period_headers)
def _delete_current_rows(self): query = f''' DELETE FROM {self.table_name} WHERE {constants.TAC_COLUMN_NAME}=? ''' deleted = utilities.execute_sql(sql_code=query, args=(self.jurisdiction.tac, ), db_name=self.db_name, dml=True) return deleted
def on_execute_click(self): permit_number = self._get_permit_number() if permit_number: business_code_id = self.gui.business_code_id.get() if business_code_id: business_code_id = business_code_id.split(':')[0].strip() business_name = self._get_business_name() values = [business_name, business_code_id] # 1 for insert and 2 for alter execute_type = self.gui.execute_option.get() if execute_type == 1: values.insert(0, permit_number) sql_code = ('INSERT INTO ' 'permits(id, business, business_code_id) ' 'VALUES(?, ?, ?)') message = 'inserted' else: values.append(permit_number) sql_code = ('UPDATE permits ' 'SET business=?, business_code_id=? ' 'WHERE id=?') message = 'altered' executed = utilities.execute_sql( sql_code=sql_code, args=values, db_name=constants.STARS_DB, dml=True, gui=self.gui ) if executed: self.gui.results_message.set(f'{permit_number} {message}.') self._clear_values() # resets the value of the business code widget to the default self._set_default_business_code() # reset the execute option to "Insert" self.gui.execute_option.set(1) else: msg.showerror( self.title, 'Please select a value for (business_code_id).', parent=self.gui) else: self.gui.permit_ent.focus()
def _get_rep_info(self): query = f''' SELECT email, phone FROM {constants.REPS_TABLE} WHERE id=? ''' rep_info = utilities.execute_sql( sql_code=query, args=(self.jurisdiction.rep_id, ), db_name=constants.STARS_DB ) return rep_info
def get_data(self): sql_code = f'ATTACH DATABASE ? AS ?' args = ( str(constants.DB_PATHS[self.values_db]), self.values_db ) con = sql.connect( constants.DB_PATHS[constants.BUSINESSES_DB], uri=True, timeout=constants.DB_TIMEOUT ) business_db_attached = utilities.execute_sql( sql_code=sql_code, args=args, open_con=con, dontfetch=True ) results = [] if business_db_attached: results = utilities.execute_sql( sql_code=self.query, open_con=con, getcursor=True ) data = None if results: column_names = [i[0] for i in results.description] data = results.fetchall() data = {'column_names' : column_names, 'data' : data} con.close() return data
def _get_adjustments_report_attributes(self): report_id = self.check_files_ids['adjustments'] query = f''' SELECT Id, Name, Path, IsDuplex FROM {constants.PACKET_REPORTS_TABLE} WHERE Id=? ''' args = (report_id, ) return utilities.execute_sql( sql_code=query, args=args, db_name=constants.STARS_DB )
def _set_business_codes(self): self.business_codes = [] query = f''' SELECT id, name FROM {constants.BUSINESS_CODES_TABLE} ''' results = utilities.execute_sql( sql_code=query, db_name=constants.STARS_DB, fetchall=True ) if results: self.business_codes = [f'{i[0]} : {i[1]}' for i in results]
def _set_categories(self): self.categories = [] query = f''' SELECT name FROM {constants.CATEGORIES_TABLE} ''' results = utilities.execute_sql(sql_code=query, db_name=constants.STARS_DB, fetchall=True) if results: for i in results: self.categories.append(i[0])
def _set_region_ebi(self): sql_code = 'ATTACH DATABASE ? AS ?' args = (str(constants.DB_PATHS[constants.STARS_DB]), constants.STARS_DB) con = sql.connect(constants.DB_PATHS[constants.STATEWIDE_DATASETS_DB], uri=True, timeout=constants.DB_TIMEOUT) db_attached = utilities.execute_sql(sql_code=sql_code, args=args, open_con=con, dontfetch=True) if db_attached: query = f''' SELECT SUM(ebi) FROM ebi e, {constants.COUNTIES_TABLE} c, {constants.JURISDICTIONS_TABLE} j WHERE e.Id=j.{constants.TAC_COLUMN_NAME} AND j.HasData=1 AND j.{constants.COUNTY_ID_COLUMN_NAME}=c.Id AND c.{constants.REGION_ID_COLUMN_NAME}=? ''' args = (self.jurisdiction.region_id, ) results = utilities.execute_sql(sql_code=query, args=args, open_con=con) if results: self.region_ebi = results[0] // 1000
def _get_geos(self): query = ''' SELECT from_range_id, to_range_id, name FROM geo_names WHERE jurisdiction_id=? ''' results = utilities.execute_sql( sql_code=query, args=(self.jurisdiction.id, ), db_name=constants.STATEWIDE_DATASETS_DB, fetchall=True) geos = {} if results: results.sort() # insert a row with the column names results.insert(0, ['FROM', 'TO', 'NAME']) # formated strings with the geo numbers and name to show in text file text_lines = [ '{:>4} - {:>5} : {}'.format(from_id, to_id, name) for from_id, to_id, name in results ] text_file = f'Export {self.report_name}' exclude_geos = utilities.get_excluded_from_text_file( text_file, text_lines, self.jurisdiction) if exclude_geos: exclude_geos = [ line.rsplit(':', 1)[0].split('-') for line in exclude_geos ] exclude_geos = [(int(x[0].strip()), int(x[1].strip())) for x in exclude_geos] # loops through all result rows minus the row for the column # names that that was inserted at the beginning for from_id, to_id, name in results[1:]: if (from_id, to_id) not in exclude_geos: geos[f'{from_id}-{to_id}'] = name return geos
def _set_naics_codes_dictionary(self): self.naics_codes = {} query = f''' SELECT naics, {constants.BUSINESS_CODE_ID_COLUMN_NAME} FROM {constants.NAICS_TO_BUSINESS_CODE_TABLE} ''' results = utilities.execute_sql( sql_code=query, db_name=constants.STARS_DB, fetchall=True ) if results: for i in range(self.NAICS_DIGITS, 1, -1): naics_codes = self._get_naics_codes_dictionary(results, i) self.naics_codes[i] = naics_codes
def _set_counties(self): query = f''' SELECT {constants.ID_COLUMN_NAME}, name FROM {constants.COUNTIES_TABLE} WHERE {constants.REGION_ID_COLUMN_NAME}=? ''' args = (self.jurisdiction.region_id, ) results = utilities.execute_sql(sql_code=query, args=args, db_name=constants.STARS_DB, fetchall=True) if results: for county_id, name in results: self.counties.append((county_id, name))
def _set_addon_ids(self): query = ( f'SELECT {constants.ID_COLUMN_NAME} ' f'FROM {constants.ADDONS_TABLE} ' f'WHERE JurisdictionId=?' ) results = utilities.execute_sql( sql_code=query, args=(self.jurisdiction.id, ), db_name=constants.STARS_DB, fetchall=True ) if results: if len(results) == 1: results = results[0] for i in results: self.addon_ids.append(i)