#_2022 #kyc
Swiss sanctions for OSINT / KYC searches. Setup here for full text search in SQLite.
Link:
Script:
```py
# migrate_swiss_sanctions.py
import sqlite3
import pandas as pd
import requests
import os
# --------------------------------------------------------------- #
# https://www.seco.admin.ch/seco/en/home/Aussenwirtschaftspolitik_Wirtschaftliche_Zusammenarbeit/Wirtschaftsbeziehungen/exportkontrollen-und-sanktionen/sanktionen-embargos/sanktionsmassnahmen/suche_sanktionsadressaten.html
# this file must be manually downloaded, then placed in the static
# data directory as SwissSanctionsSearchResults.csv. This doesn't take
# long at all, but it super annoying.
# TODO: Make an RPA script that downloads this data and moves it
# into the static_data directory so this can be totally automated
# --------------------------------------------------------------- #
def create_swiss_sanctions_db():
# ------------------------------------ #
# Delete the DB if it exists
# ------------------------------------ #
if os.path.exists("db_swiss_sanctions.db"):
os.remove("db_swiss_sanctions.db")
# ------------------------------------ #
# Make the db
# ------------------------------------ #
db = sqlite3.connect('db_swiss_sanctions.db')
cursor = db.cursor()
cursor.close()
db.close()
# ------------------------------------ #
# Make the dataframe
# ------------------------------------ #
# make dataframe from the extract
swiss_df = pd.read_csv('static_data/SwissSanctionsSearchResults.csv')
swiss_df = swiss_df.astype(str)
# rename the column titles to remove spaces and camelcase
column_titles = []
for title in swiss_df.columns:
column_titles.append(title.replace(' ','_').replace('-','_').replace(',','').replace('.','').replace('/','_').replace('(','').replace(')','').lower())
swiss_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_swiss_sanctions.db', check_same_thread=False)
swiss_df.to_sql(name='swiss_sanctions', con=conn, if_exists='replace', index=False)
# ------------------------------------ #
# Make the virtual table
# ------------------------------------ #
conn.execute(f'''
CREATE VIRTUAL TABLE v_swiss_sanctions
USING FTS5 (
{headers[:-1]}
)
;
'''
)
conn.execute(f'''
INSERT INTO v_swiss_sanctions (
{headers[:-1]}
)
SELECT
{headers[:-1]}
FROM swiss_sanctions
;
'''
)
conn.commit()
conn.execute('''DROP TABLE swiss_sanctions;''')
conn.execute('''VACUUM;''')
# # ------------------------------------ #
# # Close the database
# # ------------------------------------ #
# conn.close()
# ------------------------------------ #
# print statement
# ------------------------------------ #
print(' ')
print(len(swiss_df))
print(' ')
print('--------------------------------------------')
print('--------------------------------------------')
print(' Swiss Sanctions Database Build Complete ')
print('--------------------------------------------')
print('--------------------------------------------')
# create_swiss_sanctions_db()
```