l-dfa/sqlite_fts.py

## sqlite_fts.py
# sqlite_fts.py
#    VERY BASIC: how manage sqlite3 using python AND do Full Text Search on it
#
# author: luciano de falco alfano
# license CC BY-SA 4.0: https://creativecommons.org/licenses/by-sa/4.0/
# date:   2018.08.31
#
# overall logic:
#    - main() open DB and execute an infinite loop to process user commands
#    - cmd_exe() execute user command:
#          - create_table() create a table with fixed record structure, and its
#                            counterpart virtual table even with fixed structure
#          - drop_table() drop a single table
#          - insert()     insert some record
#          - list()       list all records
#          - fts()        full text search on records, list result keys
#
# excerpts from:
#     - pure sqlite example: https://www.pythoncentral.io/introduction-to-sqlite-in-python/
#                            https://www.pythoncentral.io/advanced-sqlite-usage-in-python/
#     - fts3 example:        http://www.rkblog.rk.edu.pl/w/p/fulltext-search-sqlite-and-django-app/
#     - fts3 doc:            https://www.sqlite.org/fts3.html

import pdb
import sqlite3

DB = 'data/db.sqlite3'

# dictionary of commands
DoCMDS = {'c': 'create table',
          'd': 'drop table',
          'fts': 'full text search',
          'h': 'help',
          'i': 'insert persons',
          'l': 'list persons',
          'q': 'quit',
         }
CMDS = tuple([cmd for cmd in DoCMDS.keys()])

# the first 5 presidents of USA, profile incipit from wikipedia
PERSONS = [ ('George Washington',
    '''George Washington (February 22, 1732[b][c] – December 14, 1799) was the first President of the United States, and was among the nation's Founding Fathers. As commander-in-chief of the Continental Army during the American Revolutionary War, he was the principal force in victory, effected with the surrender of the British at Yorktown. In 1787 he presided over the Constitutional Convention which formed the new federal government. Since the late 1780s, Washington has been known as the "Father of His Country" by compatriots. Scholarly and public polling ranks him among the top three Presidents in history.

Washington was born to a moderately prosperous family of planters, who owned slaves in colonial Virginia. He had early opportunities in education, learned mathematics and quickly launched a successful career as a surveyor, which in turn enabled him to make considerable land investments. He then joined the Virginia militia and fought in the French and Indian War. During the war with the British he also fought against one of their allies, the Iroquois nation. His devotion to American Republicanism impelled him to decline further power after victory, and he resigned as commander-in-chief in 1783. ''', ),
    ('John Adams',
    '''John Adams (October 30 [O.S. October 19] 1735 – July 4, 1826) was an American statesman and Founding Father who served as the first Vice President (1789–1797) and second President of the United States (1797–1801). He was a lawyer, diplomat, political theorist, and a leader of the movement for American independence from Great Britain. He was also a dedicated diarist and correspondent, particularly with his wife and closest advisor, Abigail.

Adams collaborated with his cousin, revolutionary leader Samuel Adams, but established his own prominence prior to the American Revolution. Driven by his devotion to the right to counsel and the presumption of innocence, he defied local anti-British sentiment and provided a successful legal defense of the accused British soldiers involved in the Boston Massacre. Adams was sent as a delegate from colonial Massachusetts to the Continental Congress, where he played a leading role in persuading Congress to declare independence. He assisted in drafting the Declaration of Independence in 1776, and was its foremost advocate in Congress. As a diplomat in Europe, he helped negotiate the peace treaty with Great Britain and acquired vital governmental loans from Amsterdam bankers. Adams was the primary author of the Massachusetts Constitution in 1780, which influenced the development of America's own constitution, as did his earlier Thoughts on Government. ''',),
    ('Thomas Jefferson',
    '''Thomas Jefferson (April 13, [O.S. April 2] 1743 – July 4, 1826) was an American Founding Father who was the principal author of the Declaration of Independence and later served as the third president of the United States from 1801 to 1809. Previously, he had been elected the second vice president of the United States, serving under John Adams from 1797 to 1801. He was a proponent of democracy, republicanism, and individual rights motivating American colonists to break from Great Britain and form a new nation; he produced formative documents and decisions at both the state and national level.

Jefferson was mainly of English ancestry, born and educated in colonial Virginia. He graduated from the College of William & Mary and briefly practiced law, with the largest number of his cases concerning land ownership claims.[1] During the American Revolution, he represented Virginia in the Continental Congress that adopted the Declaration, drafted the law for religious freedom as a Virginia legislator, and served as a wartime governor (1779–1781). He became the United States Minister to France in May 1785, and subsequently the nation's first Secretary of State in 1790–1793 under President George Washington. Jefferson and James Madison organized the Democratic-Republican Party to oppose the Federalist Party during the formation of the First Party System. With Madison, he anonymously wrote the controversial Kentucky and Virginia Resolutions in 1798–1799, which sought to strengthen states' rights by nullifying the federal Alien and Sedition Acts. ''',),
    ('James Madison Jr.',
    '''James Madison Jr. (March 16, 1751 – June 28, 1836)[2] was an American statesman and Founding Father who served as the fourth President of the United States from 1809 to 1817. He is hailed as the "Father of the Constitution" for his pivotal role in drafting and promoting the United States Constitution and the Bill of Rights.

Born into a prominent Virginia planting family, Madison served as a member of the Virginia House of Delegates and the Continental Congress during and after the American Revolutionary War. In the late 1780s, he helped organize the Constitutional Convention, which produced a new constitution to supplant the ineffective Articles of Confederation. After the Convention, Madison became one of the leaders in the movement to ratify the Constitution, and his collaboration with Alexander Hamilton produced The Federalist Papers, among the most important treatises in support of the Constitution. ''',),
    ('James Monroe',
    '''James Monroe (/mənˈroʊ/; April 28, 1758 – July 4, 1831) was an American statesman and Founding Father who served as the fifth President of the United States from 1817 to 1825. Monroe was the last president of the Virginia dynasty, and his presidency ushered in what is known as the Era of Good Feelings. Born in Westmoreland County, Virginia, Monroe was of the planter class and fought in the American Revolutionary War. He was wounded in the Battle of Trenton with a musket ball to the shoulder. After studying law under Thomas Jefferson from 1780 to 1783, he served as a delegate in the Continental Congress.[1]

As an anti-federalist delegate to the Virginia convention that considered ratification of the United States Constitution, Monroe opposed ratification, claiming it gave too much power to the central government. Nonetheless, he took an active part in the new government, and in 1790 was elected to the Senate of the first United States Congress, where he joined the Democratic-Republicans. He gained experience as an executive as the Governor of Virginia and rose to national prominence as a diplomat in France when he helped negotiate the Louisiana Purchase in 1803. During the War of 1812, Monroe served in critical roles as Secretary of State and the Secretary of War under President James Madison.[2] ''',), ]

TABLE = ''

def insert(db, tname, persons):
    '''insert a bunch of persons in a (empty) table'''

    try:
        cursor = db.cursor()
        for person in persons:
            cursor.execute(f'INSERT INTO {tname}(name, profile) VALUES(?,?)', person)
            cursor.execute(f'INSERT INTO v_{tname}(name, profile) VALUES(?,?)', person)
        db.commit()
    except Exception as ex:
        db.rollback()
        raise ex


def list(db, tname):
    '''list of all persons in table'''

    try:
        cursor = db.cursor()
        cursor.execute(f'SELECT name, profile FROM {tname}')
        for row in cursor:
            print(f'    {row[0]}: {row[1]}')
    except Exception as ex:
        raise ex


def fts(db, tname, what):
    '''full text search of something in virtual table v_{tname}'''

    try:
        cursor = db.cursor()
        cursor.execute(f'SELECT name FROM v_{tname} WHERE profile MATCH ?', (what,))
        for row in cursor:
            print(f'    {row[0]}')
    except Exception as ex:
        raise ex


def create_table(db, tname):
    '''create a table with id/name/profile record

       note: create even a virtual table named v_{tname}'''

    try:
        cursor = db.cursor()
        cursor.execute(f'CREATE TABLE IF NOT EXISTS {tname}(\
            id INTEGER PRIMARY KEY, \
            name TEXT, \
            profile TEXT)')
        cursor.execute(f'CREATE VIRTUAL TABLE v_{tname} USING fts3(name, profile)')
        db.commit()
    except Exception as ex:
        db.rollback()
        raise ex


def drop_table(db, tname):
    '''drop A SINGLE table

    note: tname and v_{tname} must be dropped separately
      this is because in case of partial drop (one of two tables)
      program could become stuck'''

    try:
        cursor = db.cursor()
        cursor.execute(f'DROP TABLE {tname}')
        db.commit()
    except Exception as ex:
        db.rollback()
        raise ex

def help():
    '''list commands'''

    for cmd in CMDS:
        print(f'    {cmd}: {DoCMDS[cmd]}')
    print()

def cmd_exe(db, cmd):
    ''' execute command, BUT not 'quit' '''

    global TABLE
    try:
        #pdb.set_trace()
        if not cmd in CMDS:
            raise ValueError(f'ERROR: cmd {cmd} unknown')
        elif cmd == 'c': # create table
            tname = input(f'table name to create? (^C to stop) ')
            print(f'    creating table(s) {tname} and v_{tname}')
            create_table(db, tname)
            TABLE = tname[:]
        elif cmd == 'd': # drop table
            tname = input(f'table name to drop? (^C to stop) ')
            print(f'    deleting table {tname}')
            drop_table(db, tname)
        elif cmd == 'fts': # full text search
            what = input(f'full text search of what? (^C to stop) ')
            print(f'    searching {what} in v_{TABLE}')
            fts(db, TABLE, what)
        elif cmd == 'i': # insert persons
            print(f'    inserting persons')
            insert(db, TABLE, PERSONS)
        elif cmd == 'l': # list persons
            print(f'    listing persons')
            list(db, TABLE)
        else:
            raise ValueError(f'WARNING: cmd {cmd} unsupported ')
    except Exception as ex:
        raise ex

def get_table(db):
    '''return 1st table name not starting with v_ prefix'''

    try:
        cursor = db.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
        tables = []
        for row in cursor:
            if row[0].split('_')[0]=='v':
                continue
            else:
                return row[0]
    except Exception as ex:
        raise ex


def main():
    global TABLE
    # Creates or opens a SQLite3 DB
    with sqlite3.connect( DB ) as db:
        TABLE = get_table(db)
        print(f'{ DB } bd opened')
        if TABLE:
            print(f'{TABLE} table exists')

        #command loop
        while True:
            try:
                cmd = input(f'{ CMDS } cmd? ')
                if not cmd in CMDS:
                    print(f'cmd { cmd } unknown; h for help')
                    continue
                elif cmd == 'q': # quit
                    print(f'quitting')
                    break
                else:
                    cmd_exe(db, cmd)
            except Exception as ex:
                raise


if __name__=='__main__':
    main()
	# sqlite_fts.py
	# VERY BASIC: how manage sqlite3 using python AND do Full Text Search on it
	#
	# author: luciano de falco alfano
	# license CC BY-SA 4.0: https://creativecommons.org/licenses/by-sa/4.0/
	# date: 2018.08.31
	#
	# overall logic:
	# - main() open DB and execute an infinite loop to process user commands
	# - cmd_exe() execute user command:
	# - create_table() create a table with fixed record structure, and its
	# counterpart virtual table even with fixed structure
	# - drop_table() drop a single table
	# - insert() insert some record
	# - list() list all records
	# - fts() full text search on records, list result keys
	#
	# excerpts from:
	# - pure sqlite example: https://www.pythoncentral.io/introduction-to-sqlite-in-python/
	# https://www.pythoncentral.io/advanced-sqlite-usage-in-python/
	# - fts3 example: http://www.rkblog.rk.edu.pl/w/p/fulltext-search-sqlite-and-django-app/
	# - fts3 doc: https://www.sqlite.org/fts3.html

	import pdb
	import sqlite3

	DB = 'data/db.sqlite3'

	# dictionary of commands
	DoCMDS = {'c': 'create table',
	'd': 'drop table',
	'fts': 'full text search',
	'h': 'help',
	'i': 'insert persons',
	'l': 'list persons',
	'q': 'quit',
	}
	CMDS = tuple([cmd for cmd in DoCMDS.keys()])

	# the first 5 presidents of USA, profile incipit from wikipedia
	PERSONS = [ ('George Washington',
	'''George Washington (February 22, 1732[b][c] – December 14, 1799) was the first President of the United States, and was among the nation's Founding Fathers. As commander-in-chief of the Continental Army during the American Revolutionary War, he was the principal force in victory, effected with the surrender of the British at Yorktown. In 1787 he presided over the Constitutional Convention which formed the new federal government. Since the late 1780s, Washington has been known as the "Father of His Country" by compatriots. Scholarly and public polling ranks him among the top three Presidents in history.

	Washington was born to a moderately prosperous family of planters, who owned slaves in colonial Virginia. He had early opportunities in education, learned mathematics and quickly launched a successful career as a surveyor, which in turn enabled him to make considerable land investments. He then joined the Virginia militia and fought in the French and Indian War. During the war with the British he also fought against one of their allies, the Iroquois nation. His devotion to American Republicanism impelled him to decline further power after victory, and he resigned as commander-in-chief in 1783. ''', ),
	('John Adams',
	'''John Adams (October 30 [O.S. October 19] 1735 – July 4, 1826) was an American statesman and Founding Father who served as the first Vice President (1789–1797) and second President of the United States (1797–1801). He was a lawyer, diplomat, political theorist, and a leader of the movement for American independence from Great Britain. He was also a dedicated diarist and correspondent, particularly with his wife and closest advisor, Abigail.

	Adams collaborated with his cousin, revolutionary leader Samuel Adams, but established his own prominence prior to the American Revolution. Driven by his devotion to the right to counsel and the presumption of innocence, he defied local anti-British sentiment and provided a successful legal defense of the accused British soldiers involved in the Boston Massacre. Adams was sent as a delegate from colonial Massachusetts to the Continental Congress, where he played a leading role in persuading Congress to declare independence. He assisted in drafting the Declaration of Independence in 1776, and was its foremost advocate in Congress. As a diplomat in Europe, he helped negotiate the peace treaty with Great Britain and acquired vital governmental loans from Amsterdam bankers. Adams was the primary author of the Massachusetts Constitution in 1780, which influenced the development of America's own constitution, as did his earlier Thoughts on Government. ''',),
	('Thomas Jefferson',
	'''Thomas Jefferson (April 13, [O.S. April 2] 1743 – July 4, 1826) was an American Founding Father who was the principal author of the Declaration of Independence and later served as the third president of the United States from 1801 to 1809. Previously, he had been elected the second vice president of the United States, serving under John Adams from 1797 to 1801. He was a proponent of democracy, republicanism, and individual rights motivating American colonists to break from Great Britain and form a new nation; he produced formative documents and decisions at both the state and national level.

	Jefferson was mainly of English ancestry, born and educated in colonial Virginia. He graduated from the College of William & Mary and briefly practiced law, with the largest number of his cases concerning land ownership claims.[1] During the American Revolution, he represented Virginia in the Continental Congress that adopted the Declaration, drafted the law for religious freedom as a Virginia legislator, and served as a wartime governor (1779–1781). He became the United States Minister to France in May 1785, and subsequently the nation's first Secretary of State in 1790–1793 under President George Washington. Jefferson and James Madison organized the Democratic-Republican Party to oppose the Federalist Party during the formation of the First Party System. With Madison, he anonymously wrote the controversial Kentucky and Virginia Resolutions in 1798–1799, which sought to strengthen states' rights by nullifying the federal Alien and Sedition Acts. ''',),
	('James Madison Jr.',
	'''James Madison Jr. (March 16, 1751 – June 28, 1836)[2] was an American statesman and Founding Father who served as the fourth President of the United States from 1809 to 1817. He is hailed as the "Father of the Constitution" for his pivotal role in drafting and promoting the United States Constitution and the Bill of Rights.

	Born into a prominent Virginia planting family, Madison served as a member of the Virginia House of Delegates and the Continental Congress during and after the American Revolutionary War. In the late 1780s, he helped organize the Constitutional Convention, which produced a new constitution to supplant the ineffective Articles of Confederation. After the Convention, Madison became one of the leaders in the movement to ratify the Constitution, and his collaboration with Alexander Hamilton produced The Federalist Papers, among the most important treatises in support of the Constitution. ''',),
	('James Monroe',
	'''James Monroe (/mənˈroʊ/; April 28, 1758 – July 4, 1831) was an American statesman and Founding Father who served as the fifth President of the United States from 1817 to 1825. Monroe was the last president of the Virginia dynasty, and his presidency ushered in what is known as the Era of Good Feelings. Born in Westmoreland County, Virginia, Monroe was of the planter class and fought in the American Revolutionary War. He was wounded in the Battle of Trenton with a musket ball to the shoulder. After studying law under Thomas Jefferson from 1780 to 1783, he served as a delegate in the Continental Congress.[1]

	As an anti-federalist delegate to the Virginia convention that considered ratification of the United States Constitution, Monroe opposed ratification, claiming it gave too much power to the central government. Nonetheless, he took an active part in the new government, and in 1790 was elected to the Senate of the first United States Congress, where he joined the Democratic-Republicans. He gained experience as an executive as the Governor of Virginia and rose to national prominence as a diplomat in France when he helped negotiate the Louisiana Purchase in 1803. During the War of 1812, Monroe served in critical roles as Secretary of State and the Secretary of War under President James Madison.[2] ''',), ]

	TABLE = ''

	def insert(db, tname, persons):
	'''insert a bunch of persons in a (empty) table'''

	try:
	cursor = db.cursor()
	for person in persons:
	cursor.execute(f'INSERT INTO {tname}(name, profile) VALUES(?,?)', person)
	cursor.execute(f'INSERT INTO v_{tname}(name, profile) VALUES(?,?)', person)
	db.commit()
	except Exception as ex:
	db.rollback()
	raise ex


	def list(db, tname):
	'''list of all persons in table'''

	try:
	cursor = db.cursor()
	cursor.execute(f'SELECT name, profile FROM {tname}')
	for row in cursor:
	print(f' {row[0]}: {row[1]}')
	except Exception as ex:
	raise ex


	def fts(db, tname, what):
	'''full text search of something in virtual table v_{tname}'''

	try:
	cursor = db.cursor()
	cursor.execute(f'SELECT name FROM v_{tname} WHERE profile MATCH ?', (what,))
	for row in cursor:
	print(f' {row[0]}')
	except Exception as ex:
	raise ex


	def create_table(db, tname):
	'''create a table with id/name/profile record

	note: create even a virtual table named v_{tname}'''

	try:
	cursor = db.cursor()
	cursor.execute(f'CREATE TABLE IF NOT EXISTS {tname}(\
	id INTEGER PRIMARY KEY, \
	name TEXT, \
	profile TEXT)')
	cursor.execute(f'CREATE VIRTUAL TABLE v_{tname} USING fts3(name, profile)')
	db.commit()
	except Exception as ex:
	db.rollback()
	raise ex


	def drop_table(db, tname):
	'''drop A SINGLE table

	note: tname and v_{tname} must be dropped separately
	this is because in case of partial drop (one of two tables)
	program could become stuck'''

	try:
	cursor = db.cursor()
	cursor.execute(f'DROP TABLE {tname}')
	db.commit()
	except Exception as ex:
	db.rollback()
	raise ex

	def help():
	'''list commands'''

	for cmd in CMDS:
	print(f' {cmd}: {DoCMDS[cmd]}')
	print()

	def cmd_exe(db, cmd):
	''' execute command, BUT not 'quit' '''

	global TABLE
	try:
	#pdb.set_trace()
	if not cmd in CMDS:
	raise ValueError(f'ERROR: cmd {cmd} unknown')
	elif cmd == 'c': # create table
	tname = input(f'table name to create? (^C to stop) ')
	print(f' creating table(s) {tname} and v_{tname}')
	create_table(db, tname)
	TABLE = tname[:]
	elif cmd == 'd': # drop table
	tname = input(f'table name to drop? (^C to stop) ')
	print(f' deleting table {tname}')
	drop_table(db, tname)
	elif cmd == 'fts': # full text search
	what = input(f'full text search of what? (^C to stop) ')
	print(f' searching {what} in v_{TABLE}')
	fts(db, TABLE, what)
	elif cmd == 'i': # insert persons
	print(f' inserting persons')
	insert(db, TABLE, PERSONS)
	elif cmd == 'l': # list persons
	print(f' listing persons')
	list(db, TABLE)
	else:
	raise ValueError(f'WARNING: cmd {cmd} unsupported ')
	except Exception as ex:
	raise ex

	def get_table(db):
	'''return 1st table name not starting with v_ prefix'''

	try:
	cursor = db.cursor()
	cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
	tables = []
	for row in cursor:
	if row[0].split('_')[0]=='v':
	continue
	else:
	return row[0]
	except Exception as ex:
	raise ex


	def main():
	global TABLE
	# Creates or opens a SQLite3 DB
	with sqlite3.connect( DB ) as db:
	TABLE = get_table(db)
	print(f'{ DB } bd opened')
	if TABLE:
	print(f'{TABLE} table exists')

	#command loop
	while True:
	try:
	cmd = input(f'{ CMDS } cmd? ')
	if not cmd in CMDS:
	print(f'cmd { cmd } unknown; h for help')
	continue
	elif cmd == 'q': # quit
	print(f'quitting')
	break
	else:
	cmd_exe(db, cmd)
	except Exception as ex:
	raise


	if __name__=='__main__':
	main()