Scrapy: How to insert data into SQLite

Question

I'm trying to write a dictionary into sql database, but without success giving me:

cursor.execute('INSERT INTO posts(url, First_and_Last_Name) VALUES("%s", "%s")' % (url, First_and_Last_Name))
NameError: name 'url' is not defined

spider:

from scrapy.crawler import CrawlerProcess
import scrapy
import sqlite3

class Wellness(scrapy.Spider):
name = "wellness"
start_urls = ['https://www.wellness.com/dir/6022571/acupuncturist/ri/east-greenwich/hwasook-lee-phoenix-fertility-center-dac']

def parse(self, response):
    item = {
            'url' : response.request.url,
            'First_and_Last_Name' : response.css('h1::text').get(),
    }       
    directions_link = response.css('#directions_tab a::attr(href)').get()
    yield scrapy.Request(response.urljoin(directions_link), callback=self.parse_directions, meta={'item': item})

def parse_directions(self, response):
    item = response.meta['item']
    item['Phone'] = response.css('.tel::text').get()
    yield item

    db = sqlite3.connect('posts.db')
    db.execute('''CREATE TABLE IF NOT EXISTS posts(id INTEGER PRIMARY KEY AUTOINCREMENT,url TEXT, First_and_Last_Name TEXT);''') 
    db.commit()

    cursor = db.cursor()
    cursor.execute('INSERT INTO posts(url, First_and_Last_Name) VALUES("%s", "%s")' % (url, First_and_Last_Name))

    db.commit()
    db.close()

if __name__ == '__main__':
    process = CrawlerProcess()
    process.crawl(Wellness)
    process.start()

What am I doing wrong here. Any and all help is appreciated. Thanks!

Your using url but where have u defined it ??

sushanth
– sushanth

2020-05-30 15:44:34 +00:00
Commented May 30, 2020 at 15:44 — sushanth
– sushanth, Commented May 30, 2020 at 15:44

heittpr · Accepted Answer · 2020-05-30 15:46:55Z

1

You want to use item['url'] instead of url, same for First_and_Last_Name:

cursor.execute('INSERT INTO posts(url, First_and_Last_Name) VALUES("%s", "%s")' % (item['url'], item['First_and_Last_Name']))

Also, don't use string formatting with SQL statements, as it makes your code vulnerable to SQL injection attacks. Let the cursor.execute method do the escaping for you:

cursor.execute('INSERT INTO posts(url, First_and_Last_Name) VALUES(?, ?)', (item['url'], item['First_and_Last_Name']))

answered May 30, 2020 at 15:46

heittpr

6195 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Scrapy: How to insert data into SQLite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related