I occasional write simple yet long Python scripts using Sqlite3 (I'm not proficient with functional or object oriented programming... yet)
Usually my Sqlite3 is blazing fast. I'm used to looping through lists of SELECT
statements with each SELECT
taking a split second, and writing tables to text files in HTML markup. But, this is the first time I've done a LEFT JOIN
in Sqlite3, and its taking much longer to complete the script than I had thought. I figured a couple of minutes perhaps, but it's taking several hours to run though all the queries.
I added print statements to narrow down that this LEFT JOIN
, within a loop, is where the script hangs up for several minutes, instead taking a split second to perform. It's definitely between the SELECT
and the .fetchall
.
I have a couple of questions:
- Is it normal for Sqlite3 to hang on
LEFT JOIN
(or any otherJOIN
)? - Did I code something the wrong way?
I need the code to work fast. If the code appears professional that's definitely a bonus.
print "selecting...",
cur.execute("SELECT \"b\".\"Disposals\" AS \"Disposals\", \
\"a\".\"ASSET NUMBER\" AS \"Record\", \
\"a\".\"DESCRIPTION\" AS \"DESCRIPTION\", \
\"a\".\"NAME_2012\" AS \"GIS_NAME_2012\", \
\"a\".\"Notes\" AS \"GIS_Notes\", \
\"a\".\"Other\" AS \"GIS_Other\", \
\"a\".\"QC_place\" AS \"GIS_place\", \
\"a\".\"QC_county\" AS \"GIS_county\", \
\"a\".\"lon_\" AS \"Longitude\", \
\"a\".\"lat_\" AS \"Latitude\" \
FROM \"resultsPoints\" AS \"a\" \
LEFT JOIN \"html_Tables\" AS \"b\" \
ON (\"a\".\"ASSET NUMBER\" = \"b\".\"Assetnum\") \
WHERE \"a\".\"DEPARTMENT NUMBER\" LIKE ? \
OR \"a\".\"AGENCY_BoB\" LIKE ? \
GROUP BY \"a\".\"ASSET NUMBER\" \
ORDER BY \"a\".\"ASSET NUMBER\"",(sqlHunt, sqlHuntBoB))
tableData = cur.fetchall()
print "fetching..",
for r, row in enumerate(tableData):
print " - ",
for c, column in enumerate(row):
print ".",
mysheets[bobAgency].write (r+2, c, str(column))