#_2022 #kyc
The Australian Sanctions List is a record maintained by the Australian Department of Foreign Affairs and Trade (DFAT). It contains individuals, entities, and vessels subject to sanctions under Australia's autonomous sanctions regimes or United Nations Security Council (UNSC) sanctions. These sanctions are part of Australia's commitment to international peace, security, and its foreign policy objectives.
This is one list I consult when doing KYC checks for individuals affiliated to a deal. Accordingly, I like to prep it for full text search by using SQLite.
Link:
Script:
```py
# migrate_australia_sanctions_list_table.py
import sqlite3
import pandas as pd
import requests
import os
def create_australia_sanctions_db():
# ------------------------------------ #
# Delete the DB if it exists
# ------------------------------------ #
if os.path.exists("db_aus_sanctions.db"):
os.remove("db_aus_sanctions.db")
# ------------------------------------ #
# Make the db
# ------------------------------------ #
db = sqlite3.connect('db_aus_sanctions.db')
cursor = db.cursor()
cursor.close()
db.close()
# ------------------------------------ #
# Make the dataframe
# ------------------------------------ #
aus_df = pd.read_excel('https://www.dfat.gov.au/sites/default/files/regulation8_consolidated.xls')
aus_df.columns=[
'reference',
'name_of_individual_or_entity',
'type',
'name_type',
'date_of_birth',
'place_of_birth',
'citizenship',
'address',
'additional_info',
'listing_info',
'committees',
'control_date'
]
# ------------------------------------ #
# Insert data to db
# ------------------------------------ #
conn = sqlite3.connect('db_aus_sanctions.db', check_same_thread=False)
aus_df.to_sql(name='aus_sanctions', con=conn, if_exists='replace', index=False)
# ------------------------------------ #
# Make the virtual table
# ------------------------------------ #
conn.execute('''
CREATE VIRTUAL TABLE v_aus_sanctions
USING FTS5 (
reference,
name_of_individual_or_entity,
type,
name_type,
date_of_birth,
place_of_birth,
citizenship,
address,
additional_info,
listing_info,
committees,
control_date
)
;
'''
)
conn.execute('''
INSERT INTO v_aus_sanctions (
reference,
name_of_individual_or_entity,
type,
name_type,
date_of_birth,
place_of_birth,
citizenship,
address,
additional_info,
listing_info,
committees,
control_date
)
SELECT reference,
name_of_individual_or_entity,
type,
name_type,
date_of_birth,
place_of_birth,
citizenship,
address,
additional_info,
listing_info,
committees,
control_date
FROM aus_sanctions
;
'''
)
conn.commit()
conn.execute('''DROP TABLE aus_sanctions;''')
conn.execute('''VACUUM;''')
# ------------------------------------ #
# Close the database
# ------------------------------------ #
conn.close()
# ------------------------------------ #
# print statement
# ------------------------------------ #
print(' ')
print(len(aus_df))
print(' ')
print('--------------------------------------------')
print('--------------------------------------------')
print(' AUS Sanctions Database Build Complete ')
print('--------------------------------------------')
print('--------------------------------------------')
# create_australia_sanctions_db()
```