ProgrammingError, Flask with postgres and sqlalchemy

Question

I am trying to get this setup to work, the database is created correctly, but trying to insert data I get the following error:

On sqlite:

sqlalchemy.exc.OperationalError
OperationalError: (sqlite3.OperationalError) no such column: Author [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT Author) AS anon_1']

On postgres:

sqlalchemy.exc.ProgrammingError
ProgrammingError: (psycopg2.ProgrammingError) column "author" does not exist
LINE 2: FROM (SELECT Author) AS anon_1
                     ^
 [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT Author) AS anon_1']

edit: Perhaps this has to do with it: I don't understand why it says "anon_1", as I am using credentials clearly?

I have inspected postgres and sqlite and the tables are created correctly. It seems to be an ORM configuration error, as it only seems to happend on inspecting or creating entries, any suggestion would be welcome!

class Author(CommonColumns):
    __tablename__ = 'author'
    author = Column(String(200))
    author_url = Column(String(2000))
    author_icon = Column(String(2000))
    comment = Column(String(5000))


registerSchema('author')(Author)


SETTINGS = {
    'SQLALCHEMY_TRACK_MODIFICATIONS': True,
    'SQLALCHEMY_DATABASE_URI': 'sqlite:////tmp/test.db',
    # 'SQLALCHEMY_DATABASE_URI': 'postgresql://xxx:xxx@localhost/test',
}


application = Flask(__name__)

# bind SQLAlchemy
db = application.data.driver
Base.metadata.bind = db.engine
db.Model = Base
db.create_all()

if __name__ == "__main__":
    application.run(debug=True)

Answer 1

What is the query you're using to insert data?

I think the error messages may be a bit more opaque than they need to be because you're using Author/author in three very similar contexts:

• the Class name
• the table name
• the column name

For easier debugging, the first thing I'd do is temporarily make each one unique (AuthorClass, author_table, author_column) so you can check which 'Author' is actually being referred to by the error message.

Since you're using the ORM, I suspect the underlying issue is that your insert statement uses Author (the object) when it should actually be using Author.author (the attribute/column name). The SELECT statements are complaining that they can't find the column 'author', but because you use author for both the table and column name, it's unclear what's actually being passed into the SQL statement.