ZCxz545i

1from scrapy.contrib.exporter import BaseItemExporter
2import sqlite3
3
4
5class SqliteItemExporter(BaseItemExporter):
6
7    def __init__(self, file, **kwargs):
8        self._configure(kwargs)
9        self.conn = sqlite3.connect(file.name)
10        self.conn.text_factory = str
11        self.created_tables = []
12
13    def export_item(self, item):   		
14        item_class_name = type(item).__name__
15
16        if item_class_name not in self.created_tables:
17            keys = None
18            if hasattr(item.__class__, 'keys'):
19                sqlite_keys = item.__class__.sqlite_keys
20            self._create_table(item_class_name, item.fields.iterkeys(), sqlite_keys)
21            self.created_tables.append(item_class_name)
22
23        field_list = []
24        value_list = []
25        for field_name in item.iterkeys():
26            field_list.append('[%s]' % field_name)
27            field = item.fields[field_name]
28            value_list.append(self.serialize_field(field, field_name, item[field_name]))
29
30        sql = 'insert or ignore into [%s] (%s) values (%s)' % (item_class_name, ', '.join(field_list), ', '.join(['?' for f in field_list]))
31        self.conn.execute(sql, value_list)
32        self.conn.commit()
33
34    def _create_table(self, table_name, columns, keys = None):
35        sql = 'create table if not exists [%s] ' % table_name
36        column_define = ['[%s] text' % column for column in columns]
37        print 'type: %s' % type(keys)
38        if keys:
39            if len(keys) > 0:
40                primary_key = 'primary key (%s)' % ', '.join(keys[0])
41                column_define.append(primary_key)
42
43            for key in keys[1:]:
44                column_define.append('unique (%s)' % ', '.join(key))
45
46        sql += '(%s)' % ', '.join(column_define)
47
48        print 'sql: %s' % sql
49        self.conn.execute(sql)
50        self.conn.commit()
51
52    def __del__(self):
53        self.conn.close()