PyCon 2011 - Hidden Treasures of the Python Standard Library - 将sqlite3的查询输出转化为python列表格式

 

本作品采用知识共享署名-非商业性使用-相同方式共享 3.0 Unported许可协议进行许可。允许非商业转载，但应注明作者及出处。

作者：liuyuan_jq

2011-03-28

---

创建实例sqlite3数据库

 
 
 
#!/bin/sh

rm -f test.db

sqlite3 test.db <<EOF

CREATE TABLE users (
  fullname text,
  username text,
  email    text
);

INSERT INTO users (fullname, username, email) 
VALUES ('Doug Hellmann', 'dhellmann', 'doug.hellmann@gmail.com');

INSERT INTO users (fullname, username, email) 
VALUES ('Guest account, no login', 'guest', 'guest@example.com');

EOF 
查询表格的内容
 
 

  
  

 
 
$ sqlite3 -noheader test.db 'select * from users'
Doug Hellmann|dhellmann|doug.hellmann@gmail.com 
Guest account, no login|guest|guest@example.com  
将查询输出转换为python列表格式
    
    
sqlite3 -noheader test.db 'select * from users' | python csv_pipes.py
['Doug Hellmann', 'dhellmann', 'doug.hellmann@gmail.com'] 
['Guest account, no login', 'guest', 'guest@example.com'] 
csv_pipes.py
    
    
#!/usr/bin/env python
# encoding: utf-8
"""
    Pipe-delimited dialect for parsing sqlite3 output

    Doug Hellmann|dhellmann|doug.hellmann@gmail.com 
    Guest account, no login|guest|guest@example.com

    ->

    ['Doug Hellmann', 'dhellmann', 'doug.hellmann@gmail.com'] 
    ['Guest account, no login', 'guest', 'guest@example.com']
"""

import csv
import sys

csv.register_dialect('pipes', delimiter='|')

reader = csv.reader(sys.stdin, dialect='pipes')
for row in reader:
    print row