Python - sqlite3

class: split-30 nopadding
background-image: url(https://cloud.githubusercontent.com/assets/4231611/11257838/21abac2a-8e87-11e5-8732-8e83fe61e6e0.jpg)

.column_t2.center[.vmiddle[
.fgtransparent[
# 
]
]]
.column_t2[.vmiddle.nopadding[
.shadelight[.boxtitle1[
# Python - sqlite

### [Eueung Mulyana](https://github.com/eueung)
### http://eueung.github.io/python/sqlite
#### Python CodeLabs | [Attribution-ShareAlike CC BY-SA](https://creativecommons.org/licenses/by-sa/4.0/)
#### 
]]
]]

---
class: split-30 nopadding
background-image: url(https://cloud.githubusercontent.com/assets/4231611/11257838/21abac2a-8e87-11e5-8732-8e83fe61e6e0.jpg)

.column_t2.center[.vmiddle[
.fgtransparent[
# 
]
]]
.column_t2[.vmiddle.nopadding[
.shadelight[.boxtitle1[
# sqlite3 
#### 
]]
]]

---
class: split-50 nopadding

.column_t2[.vmiddle[
## Example #1

```
import sqlite3
*conn = sqlite3.connect('example.db')
*c = conn.cursor()
#-------------------------------------
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
#-------------------------------------
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row
#-------------------------------------
*conn.commit()
*conn.close()
```

]]
.column_t1[.vmiddle[

```
(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
```

]]

---
class: split-50 nopadding

.column_t1[.vmiddle[

```
*(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
(u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0)
(u'2006-04-05', u'BUY', u'MSFT', 1000.0, 72.0)
```

]]
.column_t2[.vmiddle[
## Example #2

```
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
#-------------------------------------
*t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
*print c.fetchone()
#-------------------------------------
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
*c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
#-------------------------------------
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
*       print row
#-------------------------------------
conn.commit()
conn.close()
```

]]

---
class: split-50 nopadding

.column_t2[.vmiddle[

```
import sqlite3
conn = sqlite3.connect('example.db')
*# first, execute without c = conn.cursor()
#-------------------------------------
def execprint(strin):
  res = conn.execute(strin)
  for row in res:
     print "ID = ", row[0]
     print "NAME = ", row[1]
     print "ADDRESS = ", row[2]
     print "SALARY = ", row[3], "\n"
#-------------------------------------
conn.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY    NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);
''')
#-------------------------------------
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
*execprint("SELECT id, name, address, salary  from COMPANY")
```
- See [SQLite Python Tutorial](http://www.tutorialspoint.com/sqlite/sqlite_python.htm)

]]
.column_t1[.vmiddle[
## Example #3

```
*conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
execprint("SELECT id, name, address, salary  from COMPANY where ID=1")
#-------------------------------------
*conn.execute("DELETE from COMPANY where ID=2;")
for row in conn.execute("SELECT id, name, address, salary  from COMPANY;"):
  print row
#-------------------------------------
print "Total number of rows updated :", conn.total_changes
conn.commit()
conn.close()
```

```
ID =  1
...

*ID =  1
*NAME =  Paul
*ADDRESS =  California
*SALARY =  25000.0

(1, u'Paul', u'California', 25000.0)
(3, u'Teddy', u'Norway', 20000.0)
(4, u'Mark', u'Rich-Mond ', 65000.0)
*Total number of rows updated : 6
```

]]

---
class: split-30 nopadding
background-image: url(https://cloud.githubusercontent.com/assets/4231611/11257838/21abac2a-8e87-11e5-8732-8e83fe61e6e0.jpg)

.column_t2.center[.vmiddle[
.fgtransparent[
# 
]
]]
.column_t2[.vmiddle.nopadding[
.shadelight[.boxtitle1[
# Flask SQLite 3 
#### 
]]
]]

---
class: split-50 nopadding

.column_t1[.vmiddle[
# Example #4

]]
.column_t2[.vmiddle[

```
import sqlite3
from flask import Flask, g
#------------------------------------
DATABASE = 'database.db'
app = Flask(__name__)
app.config.from_object(__name__)
#------------------------------------
*def init_db():
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()
#------------------------------------
*def connect_db():
    rv = sqlite3.connect(app.config['DATABASE'])
*   rv.row_factory = sqlite3.Row
    return rv        
#------------------------------------
*def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = connect_db()
    return db
#------------------------------------
*def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv
```

]]

---
class: split-50 nopadding

.column_t2[.vmiddle[

```
*def seed_db():
    with app.app_context():
        db = get_db()
        seedusers = [('ujang',), ('otong',),]
*       db.executemany('INSERT INTO users (username) VALUES (?)', seedusers)
        #db.execute("INSERT INTO users (username) VALUES ('ujang')") 
        #db.execute("INSERT INTO users (username) VALUES ('otong')")
        db.commit()
#------------------------------------
*def print_db():
  with app.app_context():
    for user in query_db('select * from users'):
      print user['username'], 'has the id', user['id']
*def print_db_one(the_username):
  with app.app_context():
    user = query_db('select * from users where username = ?', [the_username], one=True)
    if user is None:
      print 'No such user'
    else:
      print the_username, 'has the id', user['id']
#------------------------------------
*def close_db():
  with app.app_context():
    db = getattr(g, '_database', None)
    if db is not None: db.close()
#------------------------------------
init_db()
seed_db()
*print_db()
*print_db_one('otong')
close_db()
```

]]
.column_t1[.vmiddle[
## Example #4

```
ujang has the id 1
otong has the id 2
otong has the id 2
```

]]

---
class: split-50 nopadding

.column_t1[.vmiddle[
## Example #5
####

```
*[{'username': u'ujang', 'id': 1}, {'username': u'otong', 'id': 2}]
127.0.0.1 - - [28/Nov/2015 15:37:09] "GET / HTTP/1.1" 200 -
```

.figstyle1[
![](images/fig01.jpg)
]

]]
.column_t2[.vmiddle[

```
import sqlite3
*from flask import Flask, g, jsonify
#------------------------------------
DATABASE = 'database.db'
app = Flask(__name__)
app.config.from_object(__name__)
#------------------------------------
def init_db():
    # as previously 
#------------------------------------
*def make_dicts(cur, row):
*   return dict((cur.description[idx][0], value) for idx, value in enumerate(row))
def connect_db():
    rv = sqlite3.connect(app.config['DATABASE'])
    #rv.row_factory = sqlite3.Row
*   rv.row_factory = make_dicts
    return rv        
#------------------------------------
def get_db():
    # as previously 
#------------------------------------
def query_db(query, args=(), one=False):
    # as previously 
```

]]

---
class: split-50 nopadding

.column_t1[.vmiddle[
## Example #5

Another Possibility (cf. JSON security)

```
from flask import Response
import json
#------------------------------------
@app.route('/')
def index():
    res = query_db('select * from users')
*   return Response(json.dumps(res),  mimetype='application/json')
```

]]
.column_t2[.vmiddle[

```
*@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()
#------------------------------------
*@app.route('/')
def index():
    res = query_db('select * from users')
    print res
*   return jsonify(results = res)
#------------------------------------    
if __name__ == '__main__':
    app.run(host='0.0.0.0',debug=True)
```

]]

---
#References
- [sqlite3 - DB-API 2.0 - Python Documentation](https://docs.python.org/2/library/sqlite3.html)
- [Using SQLite 3 with Flask — Flask Documentation](http://flask.pocoo.org/docs/0.10/patterns/sqlite3/#sqlite3)

---
class: split-30 nopadding
background-image: url(https://cloud.githubusercontent.com/assets/4231611/11257838/21abac2a-8e87-11e5-8732-8e83fe61e6e0.jpg)

.column_t2.center[.vmiddle[
.fgtransparent[
# 
]
]]
.column_t2[.vmiddle.nopadding[
.shadelight[.boxtitle1[
# END