sqlite3 scrapy

[Peter Otten]

Thomas Kaufmann wrote:

> Am Donnerstag, 7. Juli 2016 16:05:14 UTC+2 schrieb Thomas Kaufmann:
>> Hi,
>> 
>> I have a little scrapy-script: scrapy gets the links from a webpage; this
>> works fine. Then I want to write these links in a sqlite3-table. There is
>> no error-note. But in the database are not any records. What is the
>> problem here? My code:
>> 
>>  # -*- coding: utf-8 -*-
>>   2
>>   3 import scrapy
>>   4 import sqlite3
>>   5
>>   6 class MySpider(scrapy.Spider):
>>   7
>>   8     name            = "frisch2"
>>   9     allowed_domains = ["frischblau.de"]
>>  10     start_urls      = ["http://www.frischblau.de/",]
>>  11
>>  12     def parse(self, response):
>>  13
>>  14         for href in response.xpath("//ul/li/a/@href"):
>>  15
>>  16             url = response.urljoin(href.extract())
>>  17             yield scrapy.Request(url,
>>  callback=self.parse_dir_contents) 18
>>  19     def parse_dir_contents(self, response):
>>  20
>>  21         sql  = u' '
>>  22         conn = sqlite3.connect('frisch.db')
>>  23         cur  = conn.cursor()
>>  24         counter = 3;
>>  25
>>  26         try:
>>  27             cur.execute("SELECT SQLITE_VERSION()")
>>  28             print "-> SQLite version: %s" % cur.fetchone()
>>  29
>>  30             item = response.xpath('//a/@href').extract()
>>  31             for el in item:
>>  32
>>  33                 #print("----------> ", type(el))
>>  34                 sql = "INSERT INTO Frisch VALUES(" + unicode(counter)
>>  + ", " + "'" + el.strip() + "');" 35
>>  36                 print (sql)
>>  37                 cur.execute(sql)
>>  38                 conn.commit
>>  39                 counter = int(counter)
>>  40                 counter += 1
>>  41
>>  42         except sqlite3.Error as e:
>>  43             print "Error %s:" % e.args[0]
>>  44             conn.close()
>>  45
>>                                                                                                                                                                                                                                            
>> 
>> Thanks for help.
>> 
>> o-o
>> 
>> Thomas
> 
> Hi Chris,
> 
> I changed my code in the way you suggested. 
> There is no effect in the db.

Did you replace the line

conn.commit

with

con.commit()

? If not, can you provide the updated code?


> I start this script on the commandline with:
> 
> tk at Hamlet:~/myscrapy/tutorial/tutorial/spiders$ scrapy crawl webbot
> 
> When I call the sql-statement in the db it works.
> 
> sqlite> INSERT INTO Frisch VALUES(4, '//:Üs.chi/hhh\n');
> 
> sqlite> SELECT * FROM Frisch;
> 1|Üschi/hhh
> 2|Üschi/hhh\n
> 3|//:Üs.chi/hhh\n
> 4|//:Üs.chi/hhh\n
> 
> 
> 
> 
> 
> 
>