#kyc #data #pe_utils #_2022
The Foreign Agents Registration Act (FARA) is a U.S. law enacted in 1938 that requires individuals and entities working as agents of foreign governments, organizations, or individuals to disclose their activities and relationships to the Department of Justice (DOJ). The purpose of FARA is to promote transparency by ensuring that the American public and government officials are aware of foreign influence efforts within the United States.
Useful for KYC and diligence.
Link:
Script:
```py
# migrate_fara.py
import sqlite3
import pandas as pd
import requests
import os
def create_fara_db():
# ------------------------------------ #
# Delete the DB if it exists
# ------------------------------------ #
if os.path.exists("db_fara.db"):
os.remove("db_fara.db")
# ------------------------------------ #
# Make the db
# ------------------------------------ #
db = sqlite3.connect('db_fara.db')
cursor = db.cursor()
cursor.close()
db.close()
# ------------------------------------ #
# Make the dataframe
# ------------------------------------ #
# make the dataframe
fara_df = pd.read_csv('https://efile.fara.gov/api/v1/Registrants/csv/Active', encoding='cp1252')
fara_df = fara_df.astype(str)
# rename the column titles to remove spaces and camelcase
column_titles = []
for title in fara_df.columns:
column_titles.append(title.replace(' ','_').replace('-','_').replace(',','').replace('.','').replace('/','_').replace('(','').replace(')','').lower())
fara_df.columns = column_titles
# make a formatted string of the column headers for later use
headers = ''
for title in column_titles:
headers += title
headers += ','
# ------------------------------------ #
# Insert data to db
# ------------------------------------ #
conn = sqlite3.connect('db_fara.db', check_same_thread=False)
fara_df.to_sql(name='fara', con=conn, if_exists='append', index=False)
# ------------------------------------ #
# Make the virtual table
# ------------------------------------ #
conn.execute(f'''
CREATE VIRTUAL TABLE v_fara
USING FTS5 (
{headers[:-1]}
)
;
'''
)
conn.execute(f'''
INSERT INTO v_fara (
{headers[:-1]}
)
SELECT DISTINCT {headers[:-1]}
FROM fara
;
'''
)
conn.commit()
conn.execute('''DROP TABLE fara;''')
conn.execute('''VACUUM;''')
# ------------------------------------ #
# Close the database
# ------------------------------------ #
conn.close()
# ------------------------------------ #
# print statement
# ------------------------------------ #
print(' ')
print(len(fara_df))
print(' ')
print('--------------------------------------------')
print('--------------------------------------------')
print(' Fara Database Build Complete ')
print('--------------------------------------------')
print('--------------------------------------------')
# create_fara_db()
```