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_eu_sanctions_list_table.py
import os
import sqlite3
import pandas as pd
################ NOTE ###############
#
[email protected] sends out updates once or twice a month with the most
# recent links to the sanctions files. If they change the link it will have to be updated in this file
# This file uses the list version 1.1 from the email
#####################################
def create_eu_sanctions_db():
# ------------------------------------ #
# Delete the DB if it exists
# ------------------------------------ #
if os.path.exists("db_eu_sanctions.db"):
os.remove("db_eu_sanctions.db")
# ------------------------------------ #
# Make the db
# ------------------------------------ #
db = sqlite3.connect('db_eu_sanctions.db')
cursor = db.cursor()
cursor.close()
db.close()
# ------------------------------------ #
# Make the dataframe
# ------------------------------------ #
# Create a Dataframe to Read in the CSV
df = pd.read_csv('https://webgate.ec.europa.eu/fsd/fsf/public/files/csvFullSanctionsList_1_1/content?token=n005l51a', delimiter=';', low_memory=False)
assert len(df) > 10,000
df = df.astype(str)
# rename the column titles to remove spaces and camelcase
column_titles = []
for title in df.columns:
column_titles.append(title.replace(' ','_').replace('-','_').replace(',','').replace('.','').replace('/','_').replace('(','').replace(')','').lower())
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_eu_sanctions.db', check_same_thread=False)
df.to_sql(name='eu_sanctions', con=conn, if_exists='append', index=False)
# ------------------------------------ #
# Make the virtual table
# ------------------------------------ #
conn.execute(f'''
CREATE VIRTUAL TABLE v_eu_sanctions
USING FTS5 (
{headers[:-1]}
)
;
'''
)
conn.execute(f'''
INSERT INTO v_eu_sanctions (
{headers[:-1]}
)
SELECT DISTINCT {headers[:-1]}
FROM eu_sanctions
;
'''
)
conn.commit()
conn.execute('''DROP TABLE eu_sanctions;''')
conn.execute('''VACUUM;''')
# ------------------------------------ #
# Close the database
# ------------------------------------ #
conn.close()
# ------------------------------------ #
# print statement
# ------------------------------------ #
print(' ')
print(len(df))
print(' ')
print('--------------------------------------------')
print('--------------------------------------------')
print(' EU Sanctions List Database Build Complete ')
print('--------------------------------------------')
print('--------------------------------------------')
# create_eu_sanctions_db()
```