How to check and write to Postgres with Python and Sqlalchemy

Question

I'm writing a scraper with scrapy, sqlalchemy and postgres. I'd like the script to check for new items and, if there are any, send an email and write to database. I thought of two tables- one permanent, and one temporary, dropped after its data is processed. I'd like to check if items in the temporary exist in the permanent list, and if not- write them to pernament list. How do I construct the expression to check if the results exist in the other table, with sqlalchemy? I can successfuly write to both of the tables, but I struggle with the next stage is to check for a change and write new items to the permanent table.

Here's the model for table:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine.url import URL
from . import settings
from random import randint

DeclarativeBase = declarative_base()

def db_connect():
    """
    Performs database connection using database settings from settings.py.
    Returns sqlalchemy engine instance
    """
    return create_engine(URL(**settings.DATABASE))

def create_item_table(engine):
    """"""
    DeclarativeBase.metadata.create_all(engine)


class ItemsTemplateTable(object):
    def uid(self):
        return randint(100, 999999999)
    """Sqlalchemy items table model"""
    uid = Column('uid',Integer, default=uid, primary_key=True, unique=True)
    item_name = Column('id', String)
    item_size = Column('title', String)
    item_prize = Column('url', String, nullable=True)

class Items(ItemsTemplateTable, DeclarativeBase):
    __tablename__ = "items"

class AllItems(ItemsTemplateTable, DeclarativeBase):
    __tablename__ = "allitems"

Here's the pipeline

from sqlalchemy.orm import sessionmaker
from sqlalchemy import literal, select, text, exists
from sqlalchemy.sql import exists
from .models import Items, db_connect, create_items_table
from .items import ItemssItem

class ItemsPipeline(object):
    '''Pipeline for storing data from scraped items into a database'''
    def __init__(self):
        '''
        Initialises connection with the database
        Creates a table.
        '''
        engine = db_connect()
        create_items_table(engine)
        self.Session = sessionmaker(bind=engine)

    def process_item(self, item, spider):
        session = self.Session()
        list = Items(**item)
        try:
            session.add(list)
            session.commit()

        except:
            session.rollback()
            raise
        finally:
            session.close()
        return list

What have you tried so far? – Gallaecio Feb 06 '19 at 13:40 — Gallaecio, Feb 06 '19 at 13:40

score 0 · Answer 1 · answered Feb 08 '19 at 09:03

I think rather than handling with two different tables, I will declare an unique index (constraint) spanning multiple columns, in you case if you think two items are equal if tuple (title, url) are equal, then declare a uniqueness constrain on both (title, url). You can simply insert values to your main table, and when you try to save a duplicate item, postgres will throw an exception, which is IntegrityException in SqlAlchemy. Catch that exception and ignore it. Something along the line of[3].

Please do remember that IntegrityException is kind of catch all.

Please see:

[1] https://www.postgresql.org/docs/9.0/indexes-unique.html

[2] https://docs.sqlalchemy.org/en/latest/core/constraints.html#unique-constraint

[3] What is the good approach to check an existance of unique values in database table by SqlAlchemy in python2.7

How to check and write to Postgres with Python and Sqlalchemy

1 Answers1