Solved: sqlalchemy session.query...first() crashes if no data found

Question

I always get an excessive traceback error, when querying the database finds no result. Why does this query not simply return None?

userExpireDate = session.query(PayingUser.expireDate).filter(PayingUser.mail == mailaddress).first()

This gives this horror error message when the mailaddress can't be found:

Traceback (most recent call last):
  File "/Volumes/Mac HD/Benutzer/oliver/PycharmProjects/ErsterTest/Praeprocessor.py", line 274, in <module>
    main()
  File "/Volumes/Mac HD/Benutzer/oliver/PycharmProjects/ErsterTest/Praeprocessor.py", line 263, in main
    userExpireDate = checkUser(mail)
  File "/Volumes/Mac HD/Benutzer/oliver/PycharmProjects/ErsterTest/Praeprocessor.py", line 57, in checkUser
    userExpireDate = session.query(PayingUser.expireDate).filter(PayingUser.mail == sender).first()
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3240, in first
    ret = list(self[0:1])
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
    return list(res)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3341, in __iter__
    self.session._autoflush()
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1588, in _autoflush
    util.raise_from_cause(e)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1577, in _autoflush
    self.flush()
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2470, in flush
    self._flush(objects)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2608, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2568, in _flush
    flush_context.execute()
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 589, in execute
    uow,
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    insert,
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 1137, in _emit_insert_statements
    statement, params
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/Volumes/Mac HD/Benutzer/oliver/ErsterTest/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.OperationalError) no such table: mails
[SQL: INSERT INTO mails (sender, subject, received, answer, text, html, category, count, danger) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('anemailaddress@gmx.at', 'An subject', '2019-10-15 23:59:11.219968', 2, '\r\n\r\n> Anfang der weitergeleiteten Nachricht:\r\n>\r\n> Von: Name <anemailaddress@gmx.at>\r\n> Betreff: An subject (87 characters truncated) ... > Kopie: MrX <anemailaddress@gmx.at>\r\n> Antwort an: MrX <anemailaddress@gmx.at>\r\n>\r\n> Ein weiteres Mail\r\n\r\n', '<html><head><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space ... (2865 characters truncated) ... ass=""></span></div><br class=""><div class=""><div class="">Ein weiteres Mail<br class=""></div></div></blockquote></div><br class=""></body></html>', '', 0, 0)]
(Background on this error at: http://sqlalche.me/e/e3q8)

Why?

Interestingly enough it gives no Error when I do this:

for userExpireDate in session.query(PayingUser.expireDate).filter(PayingUser.mail == sender):

But I'd like to detect if there was no result. And if I try it this way (I know that's not elegant but nevertheless):

i = 0
for userExpireDate in session.query(PayingUser.expireDate).filter(PayingUser.mail == sender):
    i += 1
    ...

if i == 0:
    print('No user in database')

then I again get a horribly long traceback error.

Here is the model definition in another file called: payingUser.py

from sqlalchemy import Column, String, Integer, DateTime
from base import Base
from datetime import datetime


class PayingUser(Base):
    __tablename__ = 'payingUsers'

    mailaddress = Column(String, primary_key=True, unique=True)
    firstName = Column(String)
    lastName = Column(String)
    expireDate = Column(DateTime)

    def __init__(self, mailaddress, firstName, lastName):
        self.mailaddress = mailaddress
        self.firstName = firstName
        self.lastName = lastName
        self.expireDate = datetime.now()

What's happening here? Can anybody help? Yes, I'm new to sqlalchemy.

There is another table that's not used in this query, but kind of seems to appear in the traceback.

from sqlalchemy import Column, String, Integer, DateTime, Text, Table
from datetime import datetime

from base import Base



class Mail(Base):
    __tablename__ = 'mails'
    mail_id = Column(Integer, primary_key=True)
    sender = Column(String(100))
    subject = Column(String)
    received = Column(DateTime, nullable=True)
    answer = Column(Integer)
    text = Column(Text)
    html = Column(Text)

    def __init__(self):
        self.sender = ''
        self.subject = ''
        self.received = 0
        self.answer = 0
        self.text = ''
        self.html = ''

Thanks!!

Answer 1

I am an idiot!

The reason for my error was, that I had the model definitions in separated files and I imported one of them AFTER the create_all(engine) statement. So I practically did this:

from testingUser import TestingUser

Base.metadata.create_all(engine)
session = Session()

from mail import Mail

Making it correct solved all my problems.

from testingUser import TestingUser
from mail import Mail

Base.metadata.create_all(engine)
session = Session()

Me butthead!!! This took me 3 days. Arghh.