from pathlib import Path
import os
import sqlite3


class SimpleQuery:
    def __init__(self):
        os.chdir(os.path.abspath(os.path.dirname(__file__)))
        self.homepath = Path('.')
        self.dbpath = self.homepath / '../..' / 'data' / 'National' / 'database'
        self.db = self.dbpath / 'Elections.db'

        self.dbcon = None
        self.dbcur = None

    def try_query(self):
        self.db_connect()
        sqlstr = "SELECT CandidateLName, CandidateFName, CandidateMInit, CandidateSuffix, Town, PlaceId, Name, StateId " \
            " FROM ElectionResults2012, Places WHERE Places.Name like  ElectionResults2012.Town + '%';"
        self.dbcon.execute(sqlstr)
        self.db_close()

    def db_connect(self):
        print(f'\nConnecting to: {self.db}')
        self.dbcon = sqlite3.connect(self.db)
        self.dbcur = self.dbcon.cursor()

    def db_close(self, rollback=False):
        if rollback:
            self.dbcon.rollback()
        else:
            self.dbcon.commit()
        self.dbcon.close()

    def db_commit(self):
        self.dbcon.commit()

if __name__ == '__main__':
    sq = SimpleQuery()
    sq.try_query()

...
def db_connect(self):

    #print(f'\nConnecting to: {self.db}') ## Py >= 3.6
    print('\nConnecting to: {}'.format(self.db))

    #self.dbcon = sqlite3.connect(self.db) ## TypeError: argument 1 must be str, not PosixPath
    self.dbcon = sqlite3.connect(str(self.db))
...

Quote:Maybe it's the like operator? Can you clean up your data so that you can use an equality instead?

Quote:You are combining two tables here: FROM ElectionResults2012, Places
but you only give the length of one table. What is the length of each table? The join then multiples those lengths together, and that could be millions of records of course.

Quote:Places.Name like ElectionResults2012.Town + '%'

CREATE TABLE table01 (id, name countrycode, comment );
INSERT INTO table01 SELECT id, name, countrycode, comment FROM table;
DROP TABLE table;
CREATE TABLE table (
   id       integer PRIMARY KEY AUTOINCREMENT,
   name     text COLLATE NOCASE
   country  integer,
   comment  text COLLATE NOCASE
);
INSERT INTO table (id, name, countrycode, comment)
    SELECT id, name, countrycode, comment FROM table01;
DROP TABLE table01;

Quote:Is the name the only way the two tables are related? There's no ids or anything?

Quote:Depending on how much control you have, rebuilding the table to use a fulltext search engine would make it work as expected, if you use the keyword 'match' instead of 'like'

Quote:Or, you can use pragma declarations to let sql know you don't care about case sensitivity, then it might decide to use the index:

Quote:sqlite apparently doesn't let you alter tables that already exist, so you can't modify the collation of a preexisting table. But you can create a temporary table to hold the contents, drop the table, recreate it using the proper collation, then put the data back in:https://stackoverflow.com/a/47469890