CODEWITHSUNDEEP
pythonunit-testingsqlalchemypytest
Renato Oliveira
Renato Oliveira

Reputation: 504

SQLAlchemy mismatches column with orm query

I have a simple relationship of a Subscription and Subscriber tables/models in a Flask app in which I'm using SQLAlchemy to query a SQLite database locally during development.

A Subscription has a relationship to a Subscriber through a subscriber_id field defined as follows:

subscriber_id = Column(db.Integer, ForeignKey('subscriber.id'))

Both objects have a field defined as String(36) in which I'm writing a UUID4 string into when they are created in the database. They are subscription_id and subscriber_id, respectively, like this:

@dataclass
class Subscription(db.Model):
    __tablename__ = 'subscription'

    subscription: str
    subscriber: str

    id = Column(db.Integer, primary_key=True)
    subscription_id = Column(
        db.String(36),
        default=generate_uuid,  # a function that returns a uuid4
        unique=True
    )
    subscriber_id = Column(db.Integer, ForeignKey('subscriber.id'))

During test execution using PyTest I'm facing a weird behavior in which the subscriber_id of a Subscription instance is returning not the integer, as defined in the class, but a UUID.

The test runs a method that queries the subscriber, and then attempts to get its subscriptions:

subscriber = Subscriber.query.filter(
    Subscriber.subscriber_id == subscriber_id
).first()

assert subscriber is not None

subscriptions = Subscription.query.filter(
    Subscription.subscriber_id == subscriber_id
).all()

subscriber_id is 1 during execution, and the subscriber is retrieved from the database.

The second query, however, does not work, even though subscriber_id is a foreign key to the subscriber's id.

If I replace the second query with another without filter, it works. But then I see this issue, where the subscriber_id returned is not an integer, but a UUID:

raise Exception(Subscription.query.first().subscriber_id)

        subscriptions = Subscription.query.filter(
            Subscription.subscriber_id == subscriber_id
        ).all()
    
>       raise Exception(Subscription.query.first().subscriber_id)
E       Exception: 59ebfdd6-1cbc-4748-94e1-955ef55c380c

Is there a special behavior of SQLAlchemy that I am not taking into account here? A naming convention for tables/fields, perhaps?

Upvotes: 0

Views: 85

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

As wonderful as SQLite is, it has two features that make it quite "different" from most other SQL implementations:

  1. SQLite does not strictly enforce column types, it only recognizes column "affinities". Therefore it is possible to declare a column as INT, insert a string (like the string representation of a UUID) into it, and SQLite won't necessarily raise an error.

  2. SQLite accepts foreign key constraint declarations but it ignores them by default. That can lead to confusion when working with SQLite and expecting foreign keys to "just work".

Both of these behaviours came into play for this issue. A coding error was inserting (UUID) strings into a column that was intended to be integer, and the lack of enforcement of foreign key constraints caused that error to go unnoticed.

Moral: Use SQLite and appreciate it for what it can offer, but also be aware of its particular "personality" (just like any other SQL dialect).

Upvotes: 1

Related Questions