Error Messages

    SQLAlchemy normally raises errors within the context of a SQLAlchemy-specificexception class. For details on these classes, see and ORM Exceptions.

    SQLAlchemy errors can roughly be separated into two categories, theprogramming-time error and the runtime error. Programming-timeerrors are raised as a result of functions or methods being called withincorrect arguments, or from other configuration-oriented methods such asmapper configurations that can’t be resolved. The programming-time error istypically immediate and deterministic. The runtime error on the other handrepresents a failure that occurs as a program runs in response to somecondition that occurs arbitrarily, such as database connections beingexhausted or some data-related issue occurring. Runtime errors are morelikely to be seen in the logs of a running application as the programencounters these states in response to load and data being encountered.

    Since runtime errors are not as easy to reproduce and often occur in responseto some arbitrary condition as the program runs, they are more difficult todebug and also affect programs that have already been put into production.

    Within this section, the goal is to try to provide background on some of themost common runtime errors as well as programming time errors.

    This is possibly the most common runtime error experienced, as it directlyinvolves the work load of the application surpassing a configured limit, onewhich typically applies to nearly all SQLAlchemy applications.

    The following points summarize what this error means, beginning with themost fundamental points that most SQLAlchemy users should already befamiliar with.

    • The SQLAlchemy Engine object uses a pool of connections by default - Whatthis means is that when one makes use of a SQL database connection resourceof an object, and then releases that resource,the database connection itself remains connected to the database andis returned to an internal queue where it can be used again. Even thoughthe code may appear to be ending its conversation with the database, in manycases the application will still maintain a fixed number of database connectionsthat persist until the application ends or the pool is explicitly disposed.

    • Because of the pool, when an application makes use of a SQL databaseconnection, most typically from either making use of or when making queries using an ORM Session, this activitydoes not necessarily establish a new connection to the database at themoment the connection object is acquired; it instead consults theconnection pool for a connection, which will often retrieve an existingconnection from the pool to be re-used. If no connections are available,the pool will create a new database connection, but only if thepool has not surpassed a configured capacity.

    • The default pool used in most cases is called . Whenyou ask this pool to give you a connection and none are available, itwill create a new connection if the total number of connections in playare less than a configured value. This value is equal to thepool size plus the max overflow. That means if you have configuredyour engine as:

    The above Engine will allow at most 30 connections to be inplay at any time, not including connections that were detached from theengine or invalidated. If a request for a new connection arrives and30 connections are already in use by other parts of the application,the connection pool will block for a fixed period of time,before timing out and raising this error message.

    In order to allow for a higher number of connections be in use at once,the pool can be adjusted using the and create_engine.max_overflowparameters as passed to the function. The timeoutto wait for a connection to be available is configured using thecreate_engine.pool_timeout parameter.

    • The pool can be configured to have unlimited overflow by setting to the value “-1”. With this setting,the pool will still maintain a fixed pool of connections, however it willnever block upon a new connection being requested; it will instead unconditionallymake a new connection if none are available.

    However, when running in this way, if the application has an issue where itis using up all available connectivity resources, it will eventually hit theconfigured limit of available connections on the database itself, which willagain return an error. More seriously, when the application exhausts thedatabase of connections, it usually will have caused a greatamount of resources to be used up before failing, and can also interferewith other applications and database status mechanisms that rely upon beingable to connect to the database.

    Given the above, the connection pool can be looked at as a safety valvefor connection use, providing a critical layer of protection againsta rogue application causing the entire database to become unavailableto all other applications. When receiving this error message, it is vastlypreferable to repair the issue using up too many connections and/orconfigure the limits appropriately, rather than allowing for unlimitedoverflow which does not actually solve the underlying issue.

    What causes an application to use up all the connections that it has available?

    • The application is fielding too many concurrent requests to do work basedon the configured value for the pool - This is the most straightforwardcause. If you havean application that runs in a thread pool that allows for 30 concurrentthreads, with one connection in use per thread, if your pool is not configuredto allow at least 30 connections checked out at once, you will get thiserror once your application receives enough concurrent requests. Solutionis to raise the limits on the pool or lower the number of concurrent threads.

    • The application is not returning connections to the pool - This is thenext most common reason, which is that the application is making use of theconnection pool, but the program is failing to release theseconnections and is instead leaving them open. The connection pool as wellas the ORM do have logic such that when the session and/orconnection object is garbage collected, it results in the underlyingconnection resources being released, however this behavior cannot be reliedupon to release resources in a timely manner.

    A common reason this can occur is that the application uses ORM sessions anddoes not call Session.close() upon them one the work involving thatsession is complete. Solution is to make sure ORM sessions if using the ORM,or engine-bound objects if using Core, are explicitlyclosed at the end of the work being done, either via the appropriate.close() method, or by using one of the available context managers (e.g.“with:” statement) to properly release the resource.

    • The application is attempting to run long-running transactions - Adatabase transaction is a very expensive resource, and should never beleft idle waiting for some event to occur. If an application is waitingfor a user to push a button, or a result to come off of a long running jobqueue, or is holding a persistent connection open to a browser, don’tkeep a database transaction open for the whole time. As the applicationneeds to work with the database and interact with an event, open a short-livedtransaction at that point and then close it.

    • The application is deadlocking - Also a common cause of this error andmore difficult to grasp, if an application is not able to complete its useof a connection either due to an application-side or database-side deadlock,the application can use up all the available connections which then leads toadditional requests receiving this error. Reasons for deadlocks include:

      • Using an implicit async system such as gevent or eventlet withoutproperly monkeypatching all socket libraries and drivers, or whichhas bugs in not fully covering for all monkeypatched driver methods,or less commonly when the async system is being used against CPU-boundworkloads and greenlets making use of database resources are simply waitingtoo long to attend to them. Neither implicit nor explicit asyncprogramming frameworks are typicallynecessary or appropriate for the vast majority of relational databaseoperations; if an application must use an async system for some areaof functionality, it’s best that database-oriented business methodsrun within traditional threads that pass messages to the async partof the application.

      • A database side deadlock, e.g. rows are mutually deadlocked

      • Threading errors, such as mutexes in a mutual deadlock, or callingupon an already locked mutex in the same thread

    Keep in mind an alternative to using pooling is to turn off pooling entirely.See the section Switching Pool Implementations for background on this. However, notethat when this error message is occurring, it is always due to a biggerproblem in the application itself; the pool just helps to reveal the problemsooner.

    See also

    Working with Engines and Connections

    DBAPI Errors

    The Python database API, or DBAPI, is a specification for database driverswhich can be located at .This API specifies a set of exception classes that accommodate the full rangeof failure modes of the database.

    SQLAlchemy does not generate these exceptions directly. Instead, they areintercepted from the database driver and wrapped by the SQLAlchemy-providedexception DBAPIError, however the messaging within the exception isgenerated by the driver, not SQLAlchemy.

    InterfaceError

    This error is a and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    The InterfaceError is sometimes raised by drivers in the contextof the database connection being dropped, or not being able to connectto the database. For tips on how to deal with this, see the sectionDealing with Disconnects.

    DatabaseError

    Exception raised for errors that are related to the database itself, and notthe interface or data being passed.

    This error is a and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    DataError

    Exception raised for errors that are due to problems with the processed datalike division by zero, numeric value out of range, etc.

    This error is a DBAPI Error and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    OperationalError

    Exception raised for errors that are related to the database’s operation andnot necessarily under the control of the programmer, e.g. an unexpecteddisconnect occurs, the data source name is not found, a transaction could notbe processed, a memory allocation error occurred during processing, etc.

    This error is a and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    The OperationalError is the most common (but not the only) error class usedby drivers in the context of the database connection being dropped, or notbeing able to connect to the database. For tips on how to deal with this, seethe section Dealing with Disconnects.

    Exception raised when the relational integrity of the database is affected,e.g. a foreign key check fails.

    This error is a and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    InternalError

    Exception raised when the database encounters an internal error, e.g. thecursor is not valid anymore, the transaction is out of sync, etc.

    This error is a DBAPI Error and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    The InternalError is sometimes raised by drivers in the contextof the database connection being dropped, or not being able to connectto the database. For tips on how to deal with this, see the section.

    ProgrammingError

    Exception raised for programming errors, e.g. table not found or alreadyexists, syntax error in the SQL statement, wrong number of parametersspecified, etc.

    This error is a DBAPI Error and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    The ProgrammingError is sometimes raised by drivers in the contextof the database connection being dropped, or not being able to connectto the database. For tips on how to deal with this, see the section.

    NotSupportedError

    Exception raised in case a method or database API was used which is notsupported by the database, e.g. requesting a .rollback() on a connection thatdoes not support transaction or has transactions turned off.

    This error is a DBAPI Error and originates fromthe database driver (DBAPI), not SQLAlchemy itself.

    Compiler StrSQLCompiler can’t render element of type <element type>

    This error usually occurs when attempting to stringify a SQL expressionconstruct that includes elements which are not part of the default compilation;in this case, the error will be against the class.In less common cases, it can also occur when the wrong kind of SQL expressionis used with a particular type of database backend; in those cases, otherkinds of SQL compiler classes will be named, such as SQLCompiler or. The guidance below ismore specific to the “stringification” use case but describes the generalbackground as well.

    Normally, a Core SQL construct or ORM Query object can be stringifieddirectly, such as when we use print():

    1. >>> from sqlalchemy import column
    2. >>> print(column('x') == 5)
    3. x = :x_1

    When the above SQL expression is stringified, the compiler class is used, which is a special statement compiler that is invokedwhen a construct is stringified without any dialect-specific information.

    However, there are many constructs that are specific to some particular kindof database dialect, for which the StrSQLCompiler doesn’t know howto turn into a string, such as the PostgreSQL construct:

    1. >>> from sqlalchemy import table, column
    2. >>> my_table = table('my_table', column('x'), column('y'))
    3. >>> insert_stmt = insert(my_table).values(x='foo')
    4. >>> insert_stmt = insert_stmt.on_conflict_do_nothing(
    5. ...     index_elements=['y']
    6. ... )
    7. >>> print(insert_stmt)
    8. Traceback (most recent call last):
    9.  
    10. ...
    11.  
    12. sqlalchemy.exc.UnsupportedCompilationError:
    13. Compiler <sqlalchemy.sql.compiler.StrSQLCompiler object at 0x7f04fc17e320>
    14. can't render element of type
    15. <class 'sqlalchemy.dialects.postgresql.dml.OnConflictDoNothing'>

    In order to stringify constructs that are specific to particular backend,the ClauseElement.compile() method must be used, passing either an or a Dialect object which will invoke the correctcompiler. Below we use a PostgreSQL dialect:

    1. >>> from sqlalchemy.dialects import postgresql
    2. >>> print(insert_stmt.compile(dialect=postgresql.dialect()))
    3. INSERT INTO my_table (x) VALUES (%(x)s) ON CONFLICT (y) DO NOTHING

    For an ORM object, the statement can be accessed using thestatement accessor:

    1. statement = query.statement
    2. print(statement.compile(dialect=postgresql.dialect()))

    See the FAQ link below for additional detail on direct stringification /compilation of SQL elements.

    See also

    How do I render SQL expressions as strings, possibly with bound parameters inlined?

    Above, the cprop attribute is used inline before it has been mapped,however this cprop attribute is not a ,it’s a ColumnProperty, which is an interim object and thereforedoes not have the full functionality of either the objector the InstrmentedAttribute object that will be mapped onto theBar class once the declarative process is complete.

    While the ColumnProperty does have a clause_element() method,which allows it to work in some column-oriented contexts, it can’t work in anopen-ended comparison context as illustrated above, since it has no Python method that would allow it to interpret the comparison to thenumber “5” as a SQL expression and not a regular Python comparison.

    The solution is to access the directly using theColumnProperty.expression attribute:

    1. class Bar(Base):
    2.     __tablename__ = 'bar'
    3.  
    4.     id = Column(Integer, primary_key=True)
    5.     cprop = deferred(Column(Integer))
    6.  
    7.     __table_args__ = (
    8.         CheckConstraint(cprop.expression > 5),
    9.     )

    This Compiled object is not bound to any Engine or Connection

    This error refers to the concept of “bound metadata”, described atConnectionless Execution, Implicit Execution. The issue occurs when one invokes the method directly off of a Core expression objectthat is not associated with any Engine:

    1. metadata = MetaData()
    2. table = Table('t', metadata, Column('q', Integer))
    3.  
    4. stmt = select([table])
    5. result = stmt.execute()   # <--- raises

    What the logic is expecting is that the object hasbeen bound to a Engine:

    1. engine = create_engine("mysql+pymysql://user:pass@host/db")

    Where above, any statement that derives from a whichin turn derives from that MetaData will implicitly make use ofthe given in order to invoke the statement.

    Note that the concept of bound metadata is a legacy pattern and in mostcases is highly discouraged. The best way to invoke the statement isto pass it to the Connection.execute() method of a :

    1. with engine.connect() as conn:
    2.   result = conn.execute(stmt)

    When using the ORM, a similar facility is available via the Session:

    See also

    A value is required for bind parameter <x> (in parameter group <y>)

    This error occurs when a statement makes use of bindparam() eitherimplicitly or explicitly and does not provide a value when the statementis executed:

    1. stmt = select([table.c.column]).where(table.c.id == bindparam('my_param'))
    2.  
    3. result = conn.execute(stmt)

    Above, no value has been provided for the parameter “my_param”. The correctapproach is to provide a value:

    1. result = conn.execute(stmt, my_param=12)

    When the message takes the form “a value is required for bind parameter <x>in parameter group <y>”, the message is referring to the “executemany” styleof execution. In this case, the statement is typically an INSERT, UPDATE,or DELETE and a list of parameters is being passed. In this format, thestatement may be generated dynamically to include parameter positions forevery parameter given in the argument list, where it will use thefirst set of parameters to determine what these should be.

    For example, the statement below is calculated based on the first parameterset to require the parameters, “a”, “b”, and “c” - these names determinethe final string format of the statement which will be used for eachset of parameters in the list. As the second entry does not contain “b”,this error is generated:

    1. m = MetaData()
    2. t = Table(
    3.     't', m,
    4.     Column('a', Integer),
    5.     Column('b', Integer),
    6.     Column('c', Integer)
    7. )
    8.  
    9. e.execute(
    10.     t.insert(), [
    11.         {"a": 1, "b": 2, "c": 3},
    12.         {"a": 2, "c": 4},
    13.         {"a": 3, "b": 4, "c": 5},
    14.     ]
    15. )
    16.  
    17. sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError)
    18. A value is required for bind parameter 'b', in parameter group 1
    19. [SQL: u'INSERT INTO t (a, b, c) VALUES (?, ?, ?)']
    20. [parameters: [{'a': 1, 'c': 3, 'b': 2}, {'a': 2, 'c': 4}, {'a': 3, 'c': 5, 'b': 4}]]

    Since “b” is required, pass it as None so that the INSERT may proceed:

    1. e.execute(
    2.     t.insert(), [
    3.         {"a": 1, "b": 2, "c": 3},
    4.         {"a": 2, "b": None, "c": 4},
    5.         {"a": 3, "b": 4, "c": 5},
    6.     ]
    7. )

    See also

    Executing Multiple Statements

    Object Relational Mapping

    Parent instance <x> is not bound to a Session; (lazy load/deferred load/refresh/etc.) operation cannot proceed

    This is likely the most common error message when dealing with the ORM, and itoccurs as a result of the nature of a technique the ORM makes wide use of knownas . Lazy loading is a common object-relational patternwhereby an object that’s persisted by the ORM maintains a proxy to the databaseitself, such that when various attributes upon the object are accessed, theirvalue may be retrieved from the database lazily. The advantage to thisapproach is that objects can be retrieved from the database without havingto load all of their attributes or related data at once, and instead only thatdata which is requested can be delivered at that time. The major disadvantageis basically a mirror image of the advantage, which is that if lots of objectsare being loaded which are known to require a certain set of data in all cases,it is wasteful to load that additional data piecemeal.

    Another caveat of lazy loading beyond the usual efficiency concerns is thatin order for lazy loading to proceed, the object has to remain associatedwith a Session in order to be able to retrieve its state. This error messagemeans that an object has become de-associated with its Session andis being asked to lazy load data from the database.

    The most common reason that objects become detached from their is that the session itself was closed, typically via the Session.close()method. The objects will then live on to be accessed further, very oftenwithin web applications where they are delivered to a server-side templatingengine and are asked for further attributes which they cannot load.

    Mitigation of this error is via two general techniques:

    • Don’t close the session prematurely - Often, applications will closeout a transaction before passing off related objects to some other systemwhich then fails due to this error. Sometimes the transaction doesn’t needto be closed so soon; an example is the web application closes outthe transaction before the view is rendered. This is often done in the nameof “correctness”, but may be seen as a mis-application of “encapsulation”,as this term refers to code organization, not actual actions. The template thatuses an ORM object is making use of the which keeps database logic encapsulated from the caller. If theSession can be held open until the lifespan of the objects are done,this is the best approach.

    • Load everything that’s needed up front - It is very often impossible tokeep the transaction open, especially in more complex applications that needto pass objects off to other systems that can’t run in the same contexteven though they’re in the same process. In this case, the applicationshould try to make appropriate use of eager loading to ensurethat objects have what they need up front. As an additional measure,special directives like the option can ensure thatsystems don’t call upon lazy loading when its not expected.

    See also

    Relationship Loading Techniques - detailed documentation on eager loading and otherrelationship-oriented loading techniques

    This Session’s transaction has been rolled back due to a previous exception during flush

    The flush process of the , described atFlushing, will roll back the database transaction if an error isencountered, in order to maintain internal consistency. However, once thisoccurs, the session’s transaction is now “inactive” and must be explicitlyrolled back by the calling application, in the same way that it would otherwiseneed to be explicitly committed if a failure had not occurred.

    This is a common error when using the ORM and typically applies to anapplication that doesn’t yet have correct “framing” around its operations. Further detail is described in the FAQ at“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar).

    ORM Exception Classes

    See for ORM exception classes.