U0pQYq46

1# Descript: Parse spreadsheets downloaded from the NAICS website, and parse them into a sqlite database.
2# NOTE! This script only works with ODS files. It doesn't work with XLSX, because it gets a date1904 error.
3from pyexcel_ods3 import get_data
4import json
5import sqlite3
6import time
7
8# Set up the database
9fhandle = input('Enter the sqlite database name: ')
10if (len(fhandle) < 1):
11    fhandle = 'naics_2012.sqlite'
12conn = sqlite3.connect(fhandle)
13# if (len(conn) < 1): conn = sqlite3.connect('naics_2012.sqlite')
14cur = conn.cursor()
15
16# For now we'll always want fresh data, but later we'll use something like this:
17# CREATE TABLE IF NOT EXISTS naics (
18#     id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
19#     naics_code  INTEGER,
20#     naics_title TEXT
21# );
22#
23# For when we need fresh data:
24# cur.executescript('''
25# DROP TABLE IF EXISTS naics;
26# CREATE TABLE naics (
27#     id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
28#     naics_code  INTEGER,
29#     naics_title TEXT
30# );
31# ''')
32
33cur.executescript('''
34CREATE TABLE IF NOT EXISTS naics (
35    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
36    naics_code  INTEGER,
37    naics_title TEXT
38);
39''')
40
41# Initialize some variables
42count = 0
43
44# Get search results
45startrow = 3
46partial_data = get_data("2012_to_2017_NAICS.ods",
47                        # Where to start. Choose 1 to skip one row of headers.
48                        start_row=startrow,
49                        # How many rows we want to process.
50                        # row_limit=10,
51                        # At which column we wish to start, listed by index, not name.
52                        # start_column=15,
53                        # How many columns we want to include.
54                        column_limit=2
55                        )
56
57# This makes a JSON string - it "encodes".
58JsonDumps = json.dumps(partial_data)
59
60# This gives us a dictionary - it "decodes". The key is 'Search Results', but we only want the value.
61data = json.loads(JsonDumps)
62
63# Get the value for the key named "Search Results". We get a list full of lists.
64searchresults = data.get("2012 to 2017 NAICS U.S.")
65
66# Go through the results.
67for oneresult in searchresults:
68    if count > 519: break
69    # Give ourselves a couple variables which we may need to use later.
70    naics_code = int(oneresult[0])
71    naics_title = str(oneresult[1])
72
73    # Select the rows we already have in the db, by matching the NAICS Code.
74    # @TODO This should check to see if the naics_title has changed, and update if necessary.
75    cur.execute("SELECT * FROM naics WHERE naics_code = ?", (naics_code,),)
76
77    # Try to get the row. If successful, then show us the row that's already in the db.
78    try:
79        data = cur.fetchone()[0]
80        print('Found in database', naics_code, ' | ', naics_title)
81        continue
82    # I thought this was a bad practice, but this try and except statement is based on the prof's code,
83    # so maybe it's ok?
84    except:
85        pass
86
87    # Increment the counter to count the ones we haven't inserted yet.
88    count += 1
89    # Show us what we're inserting.
90    print(naics_code, '|', naics_title)
91
92    cur.execute('''INSERT INTO naics (naics_code, naics_title) VALUES ( ?, ? )''', oneresult )
93
94    # cur.execute('''INSERT INTO naics (naics_code, naics_title)
95    #         VALUES ( ?, ? )''', ( (naics_code,), (naics_title,), ) )
96    # cur.execute("insert into people values (?, ?)", (who, age))
97    conn.commit()
98    time.sleep(1)