SQLite

1
SQLite Python
2
SQLite (wikipedia)

• SQLite is a mostly ACID-compliant relational
  database management system contained in a
  relatively small (500kB) C programming library.
• Unlike client-server database management systems,
  the SQLite engine is not a standalone process
  with which the program communicates. Instead, the
  SQLite library is linked in and thus becomes an
  integral part of the program. The program uses
  SQLite's functionality through simple function
  calls, which reduces latency in database access
  as function calls are more efficient than
  inter-process communication. The entire database
  (definitions, tables, indices, and the data
  itself) is stored as a single cross-platform file
  on a host machine. This simple design is achieved
  by locking the entire database file at the
  beginning of a transaction.

3
Advantage

• Performance
• Faster than other free databases such as MySQL
  and PostgreSQL
• Size
• Has a small memory footprint and only single
  library is required to access databases
• Portability
• Run on many platforms
• Linux and Unix family
• Windows
• Mac
• Program Language Support
• C, C, C, Objective-C
• Python, PHP, Perl, Ruby, Tcl, R, Lua,
• Java,
• Delphi, Smalltalk
• .

4
Reference

• Official Website
• http//www.sqlite.org/docs.html
• Windows version
• SQLite command line
• http//www.sqlite.org/sqlite-3_6_4.zip
• SQLite Expert Professional
• http//www.sqliteexpert.com/SQLiteExpertPersSetup.
  exe
• SQLite Tutorial
• http//souptonuts.sourceforge.net/readme_sqlite_tu
  torial.html
• Book
• Owens, Michael (2006). The Definitive Guide to
  SQLite. Apress. ISBN 978-1-59059-673-9

5
SQLite Expert Professional
6
Structured Query Language (SQL)

• Create table
• CREATE TABLE ltTableNamegt (col1 type, col2 type,)
• CREATE TABLE t1 (word varchar, cnt int)
• Insert data
• INSERT INTO ltTableNamegt VALUES (data1,data2,)
• INSERT INTO t1 VALUES('Hello',1)
• Delete data
• DELETE FROM ltTableNamegt WHERE ltConditiongt
• DELETE FROM t1 WHERE word'world'
• Update data
• UPDATE ltTableNamegt SET ltcolumngtltvaluegt WHERE
  ltConditiongt
• UPDATE t1 SET word'hello' WHERE word'Hello

7
SQL (cont.)

• Data Selection
• SELECT ltColumngt FROM ltTableNamegt WHERE
  ltConditiongt
• SELECT FROM t1
• SELECT word, cnt FROM t1 WHERE word hello

8
SORT

• SELECT FROM t1 ORDER BY Cnt

9
Aggregate Function

• SELECT count() FROM t1
• 7
• SELECT sum(Cnt) FROM t1
• 28
• SELECT avg(Cnt) FROM t1
• 4

10
Aggregate Function

• SELECT Word, sum(Cnt) as no FROM t1 GROUP BY Word
  ORDER BY no

11
JOIN

• SELECT t1.word t2.word as w12, t1.count
  t2.count as sum FROM t1,t2
• WHERE t1.word t2.word

12
SQLite in Python

• import sqlite3
• conn sqlite3.connect(test.db3)
• cursor conn.cursor()
• SQL CREATE TABLE IF NOT EXISTS c1(word
  varchar, count int)
• cursor.execute(SQL)
• cursor.execute(DELETE FROM c1)
• word Hello
• number 3
• SQL INSERT INTO c1 VALUES (s,d) (
  word.replace(,),number)
• cursor.execute(SQL)
• conn.commit()
• conn.close()

13
Adding New SQL Functions

• def lastword(sent)
• wordlist sent.split()
• return wordlistlen(wordlist)-1
• cursor conn.cursor()
• conn.create_function(LW,1,lastword)
• result cursor.execute(select LW(sent) from
  t1)

14
Obtain Data

• conn sqlite.connect(test.db)
• cursor conn.cursor()
• SQL select from t1
• result cursor.execute(SQL).fetchall()
• conn.close()

15
Insert Data

• Larger example
• for t in (('2006-03-28', 'BUY', 'IBM', 1000,
  45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000,
  72.00), ('2006-04-06', 'SELL', 'IBM', 500,
  53.00), )
• c.execute('insert into stocks values
  (?,?,?,?,?)', t)

16
Practice

• Almost the same as Lab03
• Statistical language modeling
• P(the) P(u.s.the) P(militaryu.s.) P(shiitea)
  P(muslimshiite)
• Not need to smooth (suppose that no unseen word
  pairs)
• Do not use dictionary for counting and use list
  for sort, use SQL instead.
• You should use SQL at least
• Count and store the unigram and bigram count
  information
• Use JOIN to calculate the bigram LM probability
• You could also use SQL to do
• Count total number and probability of unigram and
  bigram
• Use SELECT to get LM probability for certain
  bigram

17
Testing Sentence

• Baghdad also says it will pass legislation next
  week outlawing the use of weapons of mass
  destruction .