插入数据库

Question

我有一个使用scrapy的工作脚本，它使用pipelines类将刮下的项插入到数据库中。然而，这似乎大大减缓了擦伤。我正在使用processitem方法将每个被刮下的条目插入到数据库中。将刮下的项目输出到csv文件中，然后使用存储过程将数据插入数据库会更快吗？你知道吗

def process_item(self, item, spider):

    if 'address_line_1' in item:
        sql = """INSERT dbo.PropertyListings (date, url, ad_type, address_line_1, suburb, state, postcode)
        SELECT ?, ?, ?, ?, ?, ?, ?
        WHERE NOT EXISTS
        (   SELECT 1
            FROM dbo.PropertyListings
            WHERE date = ?
            AND address_line_1 = ?
            AND suburb = ?
            AND state = ?
            And postcode = ?
        )
        """
        self.crsr.execute(sql, item['date'], item['url'], item['ad_type'], item['address_line_1'], item['suburb'], \
            item['state'], item['postcode'], item['date'], item['address_line_1'], item['suburb'], item['state'], \
            item['postcode'])
        self.conn.commit()

    else:
        sql = """INSERT dbo.PropertyListings (date, url, ad_type, address_line_1, suburb, state, postcode)
        SELECT ?, ?, ?, ?, ?, ?, ?
        WHERE NOT EXISTS
        (   SELECT 1
            FROM dbo.PropertyListings
            WHERE date = ?
            AND url = ?
        )
        """

        self.crsr.execute(sql, item['date'], item['url'], item['ad_type'], '', item['suburb'], \
            item['state'], item['postcode'], item['date'], item['url'])
        self.conn.commit()

    return item

Answer 1

看起来您正在尝试对每个数据点进行插入。这真是太慢了！！在收集完所有数据之后，或者至少插入到块中，您应该考虑bulk insertions。你知道吗

用这样的东西

def scrape_me_good():
    data = []

    for something in something_else():
        # Process data
        data.append(process_a_something(something)

    bulk_insert(data)

而不是这个

def scrape_bad():
    for something in something_else():
        single_insert(process_a_something(something)

请参阅this answer，以获得SQL server中相当好的性能细分