def consolidate_entities(read_only):
db = DatabaseConnection(path_config='utils/db_config.yaml',
search_path='mysql')
ids1, eids1, edges = consolidate_people(db)
ids2, eids2 = consolidate_companies(db)
cur = db.dict_cursor()
# 1. Reset eids to be equal to ids in entities.
print "Reset eids"
cur.execute("update entities set eid = id;")
# 2. Consolidate people
print "Update DB with people eids"
update_eids_of_ids(cur, ids1, eids1)
# 3. Consolidate companies
print "Update DB with companies eids"
update_eids_of_ids(cur, ids2, eids2)
# 4. Remove neighbour edges
print "Delete neighbour edges"
cur.execute("DELETE from related where source=%s", ("neighbour", ))
# 5. Add new neighbour edges
add_neighbour_edges(cur, edges)
# 6. Update related table
print "Updating related eids"
cur.execute(
"UPDATE related SET eid1=entities.eid FROM entities WHERE related.id1=entities.id;"
)
cur.execute(
"UPDATE related SET eid2=entities.eid FROM entities WHERE related.id2=entities.id;"
)
cur.close()
if not read_only:
db.commit()
db.close()
def ProcessSource(db_prod, geocoder, entities, config, test_mode):
""" Process one source table (read from db_source) using the config and
performing normalization using the given geocoder and entities lookup.
The produced data are written into db_prod connection. The function writes
new entities and addresses in to the Entities and Address tables. It also
creates and populates supplementary tables as specified by a config.
"""
# Connect to the most recent schema from the current source
db_source = DatabaseConnection(path_config='db_config_update_source.yaml')
source_schema_name = db_source.get_latest_schema('source_' +
config["source_schema"])
print "Processing source_schema_name", source_schema_name
db_source.execute('SET search_path="' + source_schema_name + '";')
columns_for_table = {}
with db_prod.dict_cursor() as cur:
# Create supplementaty tables using the provided command.
# Also store the columns of the table for later use.
for table in config["tables"]:
table_config = config["tables"][table]
columns_for_table[table] = table_config["columns"]
cur.execute(table_config["create_command"])
def AddValuesToTable(columns, values, eid, supplier_eid=None):
if eid is not None:
columns += ["eid"]
values += [eid]
if supplier_eid is not None:
columns += ["supplier_eid"]
values += [supplier_eid]
if all(v is None for v in values):
# Ignore this entry, all meaningful values are None
return
# TODO: find out how to build SQL statement properly
column_names = ",".join(columns)
values_params = ",".join(["%s"] * (len(columns)))
command = ("INSERT INTO %s (" + column_names + ") " + "VALUES (" +
values_params + ") " + "ON CONFLICT DO NOTHING")
with db_prod.dict_cursor() as cur:
cur.execute(command, [AsIs(table)] + values)
def AddToTable(row, table, eid, years, supplier_eid=None):
""" Add values for the given row into the supplementary table 'table'.
It reads the corresponding values from the row and adds them into the
table with the corresponding eid.
"""
columns = list(columns_for_table[table])
if years:
for year in years:
values = []
columns_per_year = columns[:]
for column in columns:
col_name = column + "_" + str(year)
if col_name in row:
values.append(row[col_name])
else:
values.append(None)
columns_per_year.append("year")
values.append(year)
AddValuesToTable(columns_per_year, values, eid)
else:
values = [row[column] for column in columns]
AddValuesToTable(columns, values, eid, supplier_eid)
with db_source.dict_cursor() as cur:
# Read data using the given command.
print "Executing SQL command ..."
suffix_for_testing = ""
if test_mode:
suffix_for_testing = " LIMIT 1000"
cur.execute(config["command"] + suffix_for_testing)
print "Done."
missed = 0
found = 0
empty = 0
missed_eid = 0
found_eid = 0
missed_addresses = set([])
for row in cur:
# Read entries one by one and try to geocode them. If the address
# lookup succeeds, try to normalize the entities. If it succeeds,
# insert into Entities and supplementary tables.
address = ""
if "address" in row:
address = row["address"]
if address is None: continue
name = ""
if "name" in row:
name = row["name"]
if name is None: continue
# Sometimes FirstName and Surname are joined. Lets try the simplest splitting on Capital
# letters.
if (len(name.split()) == 1):
name = ' '.join(re.findall('[A-Z][^A-Z]*', name))
addressId = geocoder.GetAddressId(address.encode("utf8"))
if addressId is None:
if address == "":
empty += 1
else:
if test_mode and missed < 10:
print "MISSING ADDRESS", address.encode("utf8")
missed_addresses.add(address)
missed += 1
continue
found += 1
eid = None
if config.get("no_entity_id"):
# TODO(rasto): is the address lookup necessary here?
eid = None
else:
eid = entities.GetEntity(row["ico"], name, addressId)
if found % 20000 == 0:
print "Progress:", found
sys.stdout.flush()
if config.get("save_org_id"):
entities.AddOrg2Eid(row["org_id"], eid)
if config.get("use_org_id_as_eid_relation"):
eid2 = entities.GetEidForOrgId(row["eid_relation"])
if eid2 is None:
continue
row["eid_relation"] = eid2
if config.get("extract_description_from_body"):
row["body"] = ExtractDescriptionFromBody(row["body"])
supplier_eid = None
if config.get("supplier_eid"):
supplier_address_id = None
if "supplier_address" in row and not row[
"supplier_address"] is None:
supplier_address = row["supplier_address"]
if supplier_address:
supplier_address_id = geocoder.GetAddressId(
supplier_address.encode("utf8"))
if supplier_address_id is None:
missed_addresses.add(supplier_address)
missed += 1
continue
else:
empty += 1
supplier_name = ""
if "supplier_name" in row and not row["supplier_name"] is None:
supplier_name = row["supplier_name"]
supplier_eid = entities.GetEntity(row["supplier_ico"],
supplier_name,
supplier_address_id)
if table_config.get("strip_html"):
for strip_html_column in table_config["strip_html"]:
if row.get(strip_html_column):
row[strip_html_column] = StripHtml(
row[strip_html_column])
if eid is None: missed_eid += 1
found_eid += 1
AddToTable(row, table, eid, table_config.get("years"),
supplier_eid)
print "FOUND", found
print "MISSED", missed
print "EMPTY", empty
print "MISSED UNIQUE", len(missed_addresses)
print "FOUND EID", found_eid
print "MISSED EID", missed_eid
db_source.close()