· 9 years ago · Oct 09, 2016, 08:16 PM
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 data from the spreadsheet
45# sheetname = '"' + input('Paste the file name of the spreadsheet (ex: 2012_to_2017_NAICS.ods): ') + '"'
46startrow = 3
47collimit = 2
48# @TODO: I'd like to use a variable for the ODS file, but am getting errors so far.
49partial_data = get_data("2012_to_2017_NAICS.ods",
50 # Where to start. Choose 1 to skip one row of headers.
51 start_row=startrow,
52 # How many rows we want to process.
53 # row_limit=10,
54 # At which column we wish to start, listed by index, not name.
55 # start_column=15,
56 # How many columns we want to include.
57 column_limit=collimit
58 )
59
60# json.dumps - This takes the spreadsheet data and makes a JSON string - it "encodes".
61# json.loads - This takes the JSON string we got, and "decodes", returning a dictionary.
62data = json.loads(json.dumps(partial_data))
63
64# Since we only have ONE key value pair, we can use .values to get our search results.
65# We then make this into a list, and take the first element of the list. It's our iterable list! Yay!
66searchresults = list(data.values())[0]
67
68# Go through the results.
69for oneresult in searchresults:
70 if count > 9: break
71 # Give ourselves a couple variables which we may need to use later.
72 naics_code = int(oneresult[0])
73 naics_title = str(oneresult[1])
74
75 # Select the rows we already have in the db, by matching the NAICS Code.
76 # @TODO This should check to see if the naics_title has changed, and update if necessary.
77 cur.execute("SELECT * FROM naics WHERE naics_code = ?", (naics_code,),)
78
79 # Try to get the row. If successful, then show us the row that's already in the db.
80 try:
81 data = cur.fetchone()[0]
82 print('Found in database', naics_code, ' | ', naics_title)
83 continue
84 # I thought this was a bad practice, but this try and except statement is based on the prof's code,
85 # so maybe it's ok?
86 except:
87 pass
88
89 # Increment the counter to count the ones we haven't inserted yet.
90 count += 1
91 # Show us what we're inserting.
92 print(naics_code, '|', naics_title)
93
94 cur.execute('''INSERT INTO naics (naics_code, naics_title) VALUES ( ?, ? )''', oneresult )
95
96 # @TODO Get this code working so that we can insert individual values, because I like knowing what's going where.
97 # cur.execute('''INSERT INTO naics (naics_code, naics_title)
98 # VALUES ( ?, ? )''', ( (naics_code,), (naics_title,), ) )
99 # cur.execute("insert into people values (?, ?)", (who, age))
100 conn.commit()
101 time.sleep(1)