Instantly share code, notes, and snippets.

@l-dfa l-dfa/
Last active Aug 31, 2018

What would you like to do?
VERY BASIC: how manage sqlite3 using python AND do Full Text Search on it
# 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:
# 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:
# - fts3 example:
# - fts3 doc:
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'''
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)
except Exception as ex:
raise ex
def list(db, tname):
'''list of all persons in table'''
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}'''
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}'''
cursor = db.cursor()
cursor.execute(f'CREATE TABLE IF NOT EXISTS {tname}(\
name TEXT, \
profile TEXT)')
cursor.execute(f'CREATE VIRTUAL TABLE v_{tname} USING fts3(name, profile)')
except Exception as ex:
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'''
cursor = db.cursor()
cursor.execute(f'DROP TABLE {tname}')
except Exception as ex:
raise ex
def help():
'''list commands'''
for cmd in CMDS:
print(f' {cmd}: {DoCMDS[cmd]}')
def cmd_exe(db, cmd):
''' execute command, BUT not 'quit' '''
global TABLE
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)
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'''
cursor = db.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
tables = []
for row in cursor:
if row[0].split('_')[0]=='v':
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')
print(f'{TABLE} table exists')
#command loop
while True:
cmd = input(f'{ CMDS } cmd? ')
if not cmd in CMDS:
print(f'cmd { cmd } unknown; h for help')
elif cmd == 'q': # quit
cmd_exe(db, cmd)
except Exception as ex:
if __name__=='__main__':

This comment has been minimized.

Copy link
Owner Author

l-dfa commented Aug 31, 2018

Using Python 3, and functioning even in Windows 10 (I read sqlite 3 in Windows hadn't FTSx support. By now, August 2018, this is no more True).
Launching python, you could create persons table, insert persons and do a full text query as below:

data/db.sqlite3 bd opened
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? c
table name to create? (^C to stop) persons
    creating table(s) persons and v_persons
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? i
    inserting persons
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? l
    listing persons
    George Washington: George Washington (February 22, 1732[b][c] – December 14, 1799) was the first President of the United States, and
    ... CUT ...
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? fts
full text search of what? (^C to stop) virginia
    searching virginia in v_persons
    George Washington
    Thomas Jefferson
    James Madison Jr.
    James Monroe
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? fts
full text search of what? (^C to stop) virginia convention
    searching virginia convention in v_persons
    George Washington
    James Madison Jr.
    James Monroe
('c', 'd', 'fts', 'h', 'i', 'l', 'q') cmd? fts
full text search of what? (^C to stop) virginia convention executive
    searching virginia convention executive in v_persons
    James Monroe
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment