sql文のLIMIT、OFFSET句とリスト内包表記のメモ

表データをN行ずつ分けて処理する。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

↓↓↓↓↓↓ここから　<test用DBの準備>↓↓↓↓↓↓
con = sqlite3.connect(":memory:")
cur = con.cursor()
sql = """CREATE TABLE IF NOT EXISTS user ([user_id] TEXT,
            [user_name] TEXT
            );"""
cur.execute(sql)

sql = "delete from user"
cur.execute(sql)
#test用データ
tplist = ((1,'taro'),(2,'hanako'),(3,'becky'),(4,'alice'),(5,'ken'),(6,'jiro'),(7,'kaori'))
print tplist
ph = "?," * (len(tplist[0]) - 1) + "?"
for t in tplist:
    sql = "INSERT INTO %s VALUES(%s)" % ('user',ph)
    con.execute(sql,t)
    con.commit()
#↑↑↑↑↑↑ここまで　<test用DBの準備>↑↑↑↑↑↑


limit = 2
"""
sql文でLIMIT句OFFSET句を使う
"""
sql = "select count(user_name) from user"
cur.execute(sql)
rowcount = cur.fetchone()[0]
for offset in range(int(rowcount)/limit+1):
    sql = "select * from user order by user_id LIMIT %d OFFSET %d" % \
                                                    (limit,limit*offset)
    cur.execute(sql)
    print cur.fetchall()

"""
リスト内包表記でスライス
"""
sql = "select * from user"
cur.execute(sql)
tplist = cur.fetchall()

for row in [tplist[limit*offset:limit*(offset+1)] for offset in range(len(tplist)/limit+1)]:
    print row
    
cur.close()
con.close()