CODEWITHSUNDEEP
postgresqlsqlalchemy
Casey
Casey

Reputation: 2719

Sqlalchemy intersect and except

Does anybody know how to convert these raw SQL queries into sqlalchemy?

new_records = db.session.execute('SELECT issn_l, issn FROM issn_temp EXCEPT SELECT issn_l, issn FROM issn_to_issnl;')

removed_records = db.session.execute('SELECT issn_l, issn FROM issn_temp INTERSECT SELECT issn_l, issn FROM issn_to_issnl;')

My models are below:

class ISSNTemp(db.Model):
    __tablename__ = "issn_temp"
    issn_l = db.Column(db.String(9), nullable=False, primary_key=True)
    issn = db.Column(db.String(9), nullable=False, primary_key=True)


class ISSNToISSNL(db.Model):
    __tablename__ = "issn_to_issnl"

    issn_l = db.Column(db.String(9), nullable=False, primary_key=True)
    issn = db.Column(db.String(9), nullable=False, primary_key=True)
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

I tried using union but it is not working well.

Upvotes: 0

Views: 355

Answers (1)

djkern
djkern

Reputation: 482

I've built a layered query recently that is in the neighborhood:

differential_select = union(
    select( *( [ deltakey ] + sncolumns + [ literal( 'SN' ).label( 'op' ) ] ) ).except_(
        select( *( [ deltakey ] + tncolumns  + [ literal( 'SN' ).label( 'op' ) ] ) )
    ),
    select(*( [ deltakey ] + tncolumns + [ literal( 'TN' ).label( 'op' ) ] ) ).except_(
        select( *( [ deltakey ] + sncolumns  + [ literal( 'TN' ).label( 'op' ) ] ) ) 
    )
)

Applying this to your 1st query, I get:

select(
    issn_temp.c.issn_l,
    issn_temp.c.issn
).except_(
    select(
        issn_to_issn.c.issn_l,
        issn_to_issn.c.issn
    )
)

Where issn_temp and issn_to_issn point to instances of sqlalchemy.Table.

Also note that there is more detail here and here.

Upvotes: 2

Related Questions