SQLite Cheatsheet

SQLite是一个轻量级的数据库，它以文件的形式存储在硬盘中，适合小型的简单应用。有些应用也会采用SQLite来做产品原型，然后把它迁移到其他功能更强大的数据库，比如：MySQL、PostgreSQL等。

Python标准库 —— sqlite3

参考：Python-Docs/sqlite3

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create tables
c.execute('''CREATE TABLE stocks          (date text, trans text, symbol text, qty real, price real)''')
          (date text, trans text, symbol text, qty real, price real)'''

# Insert a row of database
c.execute("INSERT INTO stocks VALUES ('2016-06-27', 'BUY', 'APPL', 100, 100)")

# Save (commit) the changes
conn.commit()

conn.close()

# Read from database
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Put ? as a placeholder and then provide a tuple of values
t = ('APPL',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

# Larger example that inserts many records at a timestamp
purchases = [('2016-06-01', 'BUY', 'IBM', 100, 45.00),
             ('2016-06-01', 'BUY', 'GOOG', 100, 450.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

# Treat the cursor as an iterator，or fetchall()
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print row

在Flask中使用SQLite

参考：Flask-Docs/sqlite3

• Simple Example

import sqlite3
from flask import g

DATABASE = '/path/to/database.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

@app.route('/')
def index():
    cur = get_db().cursor()

with app.app_context():
    # now you can use get_db()

• Easy Querying

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    return (rv[0] if rv else None) if one else rv

for user in query_db('select * from users'):
    print user['username'], 'has the id', user['user_id']

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['user_id']

• Initial Schemas

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()

>>> from yourapplication import init_db
>>> init_db()

@app.cli.command('initdb')
def initdb_command():
    """Creates the database tables."""
    init_db()
    print('Initialized the database.')

SQL语句

参考：flask example

• schema

drop table if exists message;
create table message (
  message_id integer primary key autoincrement,
  author_id integer not null,
  text text not null,
  pub_date integer
);

• select

messages=query_db('''    select message.*, user.* from message, user    where message.author_id = user.user_id and (        user.user_id = ? or        user.user_id in (select whom_id from follower                                where who_id = ?))    order by message.pub_date desc limit ?''',
    select message.*, user.* from message, user
    where message.author_id = user.user_id and (
        user.user_id = ? or
        user.user_id in (select whom_id from follower
                                where who_id = ?))
    order by message.pub_date desc limit ?'''
    [session['user_id'], session['user_id'], PER_PAGE])

followed = query_db('''select 1 from follower where    follower.who_id = ? and follower.whom_id = ?''',
    follower.who_id = ? and follower.whom_id = ?'''
    [session['user_id'], profile_user['user_id']],
    one=True)

• insert

db.execute('insert into follower (who_id, whom_id) values (?, ?)',
    [session['user_id'], whom_id])
db.commit()

• delete

db.execute('delete from follower where who_id=? and whom_id=?',
    [session['user_id'], whom_id])
db.commit()

• update

db.execute('update message set text=? where message_id=?',
    [new_text, message_id])
db.commit()