Get the SEC CIK of company by Ticker

1. create a table to save ciks:

schema.sql

DROP TABLE IF EXISTS sec_cik;

CREATE TABLE sec_cik (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ticker TEXT UNIQUE NOT NULL,
  cik INTEGER NOT NULL
);

2. generate the ticker-cik mapping:

sec_cik.sql

import requests

headers = {'User-Agent': 'miaowa.pro', 'Accept-Encoding': 'gzip,deflate'}
content = requests.get("https://www.sec.gov/files/company_tickers.json", headers=headers).json()

ticker_cik_dict = {}
for value in content.values():
    ticker_cik_dict[value['ticker']] = value['cik_str']
    print("INSERT INTO sec_cik (ticker, cik) VALUES ('{ticker}', {cik});".format(ticker=value['ticker'],
                                                                                 cik=value['cik_str']))

print(ticker_cik_dict)

3. init sec cik using flask

db.py import to flask app __init__.py

import sqlite3

import click
from flask import current_app, g
from flask.cli import with_appcontext


def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row

    return g.db


def close_db(e=None):
    db = g.pop('db', None)

    if db is not None:
        db.close()


def init_db():
    db = get_db()

    with current_app.open_resource('schema.sql') as f:
        db.executescript(f.read().decode('utf8'))


def init_app(app):
    app.teardown_appcontext(close_db)
    app.cli.add_command(init_db_command)
    app.cli.add_command(init_cik_command)

def init_cik():
    db = get_db()

    with current_app.open_resource('sec_cik.sql') as f:
        db.executescript(f.read().decode('utf-8'))


@click.command('init-db')
@with_appcontext
def init_db_command():
    """Clear the existing data and create new tables."""
    init_db()
    click.echo('Initialized the database.')


@click.command('init-cik')
@with_appcontext
def init_cik_command():
    init_cik()
    click.echo('Initialized the sec_cik table data.')

4. create table and insert data:

run in a terminal

$ export FLASK_APP=miaowa
$ export FLASK_ENV=development
$ flask init-db
$ flask init-cik