sqlite3 scrapy

[Thomas Kaufmann]

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. 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