SQLITE 3 B.

SELECT/UPDATE/DELETE -
2018

Ph.D. / Golden Gate Ave, San

Francisco / Seoul National
Univ / Carnegie Mellon / UC
Berkeley / DevOps / Deep
Learning / Visualization

([Link]
v=250&username=khhong7)
Sponsor Open Source
development activities and
free contents for everyone.

Thank you.

- K Hong
([Link]

[Link] site search:

 Custom Search Search

Select - 1. Retrieving Data

This chapter is a continuation from Connecting to DB,
create/drop table, and insert data into a table
([Link]

In the previous chapter, we had the following output

when we used the sqlite shell command line:

$ sqlite3 [Link]

sqlite> .tables
books

sqlite> .mode column

sqlite> .headers on
sqlite> SELECT * FROM books;
id title author price
---------- --------------- ---------- ---------
1 Learning Python Mark Lutz $36.19
2 Two Scoops of D Daniel Gre $34.68
3 Python Cookbook David Beaz $30.29
4 The Quick Pytho Naomi R. C $16.39
5 Python Testing David Sale $38.20
sqlite>

Python
tutorial
 Python Home
([Link]

Introduction
([Link]

Running Python
Programs (os,
sys, import)
([Link]

Modules and
IDLE (Import,
To retrieve data, we need to execute the query against
Reload, exec)
the cursor object and then use fetchone() to retrieve a
([Link]
single row or fetchall() to retrieve all the rows.

Object Types -
>>> [Link]('''SELECT title, author, price
Numbers,
<[Link] object at 0x7f1d2717d650> Strings, and
None
>>> book1 = [Link]() ([Link]
>>> print book1
(u'Learning Python', u'Mark Lutz', u'$36.19')
Strings - Escape
>>> print(book1[0])
Learning Python Sequence, Raw
String, and
>>> all_cols = [Link]() Slicing
([Link]
>>> print all_cols
[(u'Two Scoops of Django: Best Practices For Djang
Strings -
>>> for col in all_cols: Methods
... print('{0} : {1}, {2}'.format(col[0], col[ ([Link]
...
Two Scoops of Django: Best Practices For Django 1.
Formatting
Python Cookbook : David Beazley, $30.29
The Quick Python Book : Naomi R. Ceder, $16.39 Strings -
Python Testing : David Sale, $38.20 expressions and
method calls
([Link]
The cursor object works as an iterator, invoking
fetchall() automatically: Files and
[Link]
([Link]
>>> [Link]('''SELECT title, author, price,
<[Link] object at 0x7f1d2717d650>
Traversing
>>> for col in cursor:
directories
... # col[0] returns the first column in the q
... print('{0} : {1}, {2}, {3}'.format(col[0], recursively
... ([Link]
Learning Python : Mark Lutz, $36.19, Jul 6, 2013
Two Scoops of Django: Best Practices For Django 1. Subprocess
Python Cookbook : David Beazley, $30.29, May 29, 2
Module
The Quick Python Book : Naomi R. Ceder, $16.39, Ja
Python Testing : David Sale, $38.20, Sep 2, 2014 ([Link]

Regular
Expressions
with Python
([Link]

Select - 2. Retrieving Data with Object Types -

"?" Lists
([Link]
We can use the ? placeholder to retrieve data with
conditions:
Object Types -
Dictionaries and
Tuples
>>> book_id = 3
>>> [Link]('''SELECT title, author, price ([Link]
<[Link] object at 0x7f1d2717d650>
>>> book = [Link]() Functions def,
>>> print(book) *args, **kargs
(u'Python Cookbook', u'David Beazley', u'$30.29')
([Link]

Functions
lambda
([Link]

Built-in
UPDATE Data Functions
([Link]
Now, we want to update data in the table. Let's switch
the price of the 4th book (id = 4).
map, lter, and
reduce
([Link]
>>> newPrice = '$19.99'
>>> book_id = 4
>>> [Link]('''UPDATE books SET price = ? W Decorators
<[Link] object at 0x7f1d2717d650> ([Link]

We can check if the price of the 4th (book_id = 4) book List

has been updated from $16.39 to $19.99: Comprehension
([Link]

>>> [Link]('''SELECT title, author, price Sets

<[Link] object at 0x7f1d2717d650> (union/intersectio
>>> all_books = [Link]() and itertools -
>>> print(all_books)
Jaccard
[(u'Learning Python', u'Mark Lutz', u'$36.19'), (u
coe cient and
shingling to
check plagiarism
([Link]

Hashing (Hash
DELETE data tables and
hashlib)
>>> delete_book_id = 5 ([Link]
>>> [Link]('''DELETE FROM books WHERE id =
<[Link] object at 0x7f1d2717d650> Dictionary
>>> [Link]() Comprehension
with zip
Let's check the last boot (id = 5) is gone: ([Link]

The yield
keyword
([Link]
 Generator
>>> [Link]('''SELECT title, author, price
Functions and
<[Link] object at 0x7f1d2717d650>
>>> print([Link]())
Expressions
[(u'Learning Python', u'Mark Lutz', u'$36.19'), (u ([Link]

[Link]()
Yes, it's not there anymore.
method
([Link]

Iterators
([Link]

Classes and
more Instances
(__init__, __call__,
etc.)
([Link]

Python tutorial if__name__ ==

'__main__'
([Link]

Python Home argparse

([Link] ([Link]

Introduction Exceptions
([Link]
([Link]

Running Python Programs (os, sys, import) @static method

([Link] vs class method
([Link]
Modules and IDLE (Import, Reload, exec)
([Link]
Private
attributes and
Object Types - Numbers, Strings, and None private methods
([Link]
([Link]