Can't insert data into sql table from scrapy items

Question

These are the errors:

[scrapy.core.scraper] ERROR: Error processing {'level': None,
 'school': 'Some school name',
 'place': None,
 'subject': None}
Traceback (most recent call last):
  File "/home/reducedgosling/.virtualenvs/data/lib/python3.6/site-packages/twisted/internet/defer.py", line 653, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/home/reducedgosling/Programming/schools/pipelines.py", line 28, in process_item
    self.cur.execute(sql, data)
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

items.py

class SchoolsItem(scrapy.Item):
    subject = scrapy.Field()
    level = scrapy.Field()
    place = scrapy.Field()
    school = scrapy.Field()

spider.py

def parse_school(self, response):
    item = SchoolsItem()
    school = response.css('h1 span.title::text').extract_first()
    table_rows = response.css('tr')
    for x in table_rows:
        item['subject'] = x.css('td.views-field-title a::text').extract_first()
        item['level'] = x.css('td.views-field-field-level').xpath('normalize-space(./text())').extract_first()
        item['place'] = x.css('td.views-field-field-campus').xpath('normalize-space(./text())').extract_first()
        item['school'] = school
        yield item

pipelines.py

def process_item(self, item, spider):
    sql = "INSERT INTO udir_content (subject, level, school, place) VALUES (%s, %s, %s, %s);"
    data = (item['subject'], item['level'], item['school'], item['place'])
    self.cur.execute(sql, data)
    self.connection.commit()
    return item

What am I doing wrong? I suspected the Null values, which Python(or psycopg?) transforms into None values? But PostgreSQL accepts null values unless I specify NOT NULL, right?

The first error that shows in the psql log file is this:

ERROR:  relation "udir_content" does not exist at character 13
STATEMENT:  INSERT INTO udir_content (subject, level, school, place) VALUES (NULL, NULL, 'Some school name', NULL);

The rest just says "transaction aborted".

Related: https://stackoverflow.com/questions/2979369/databaseerror-current-transaction-is-aborted-commands-ignored-until-end-of-tra?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — Tony, Jun 08 '18 at 10:16
Is your table `udir_content` named using all lower case in the DB? You need to match it exactly (case sensitive). — Tony, Jun 08 '18 at 11:42
The next thing to check is if you are using schemas? Is the table in the default schema or a different one you created? — Tony, Jun 08 '18 at 12:10
@Tony, Heh, I just spent two hours only to realize now that i was inside the postgres database when i created the tables, when i fixed it, i just needed to grant permissions to my second user and now everything is fine. — , Jun 08 '18 at 12:25
Good to know you found the answer. When you can, mark your own answer as 'accepted' so others know you found the solution. — Tony, Jun 08 '18 at 12:43

score 0 · Answer 1 · answered Jun 08 '18 at 10:14

0

You have a problem with your INSERT, the error reported is because the insert has failed and you have not rolled back the transaction before issuing another.

The execute should use question marks as placeholders (?) not %s

So for your statement you should use

sql = "INSERT INTO udir_content (subject, level, school, place) VALUES (?, ?, ?, ?);"

answered Jun 08 '18 at 10:14

Tony

9,672
3
47
75

I changed it but get this error: psycopg2.ProgrammingError: syntax error at or near "," LINE 1: ...dir_content (subject level, school, place) VALUES (?, ?, ?, ?)... – Jun 08 '18 at 10:34
Apologies, I'm using SQLite and that is happy with `?`, for Postgres it seems you were correct with `%s`. However, you still need to test your SQL statement for errors. Call `mogrify()` to get the sql statement being executed and test it against your database to see what error you are getting - http://initd.org/psycopg/docs/cursor.html#cursor.mogrify – Tony Jun 08 '18 at 11:12
Thanks! I updated my question with error from psql log file. – Jun 08 '18 at 11:16

score 0 · Answer 2 · answered Jun 08 '18 at 12:33

Learned a little about PostgreSQL the hard way by spending two hours just to figure out I created the table inside the postgres database.. not the one I created for this. Then I had to grant my second user privileges and now everything is fine.

Can't insert data into sql table from scrapy items

2 Answers2