SQL Syntax error when column name is "read"

The following table:

    ... = Table("sim_usermsg", metadata,
        Column("id", Integer, primary_key=True),
        Column("toUser_id", Integer, ForeignKey("sim_user.id"), nullable=False),
        Column("fromUser_id", Integer, ForeignKey("sim_user.id"), nullable=False),
        Column("inReplyTo_id", Integer, ForeignKey("sim_usermsg.id")),
        Column("content", Unicode, nullable=False),
        Column("createTime", DateTime, default=datetime.now(), nullable=False),
        Column("gcTime", DateTime, default=None),
        Column("read", Boolean, default=False, nullable=False), #already read?
        Column("tan", SmallInteger),
        UniqueConstraint=("toUser_id","fromUser_id","tan"),
        )

caused a SQL syntax error, when saving+flushing a new row: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, tan)

The generated SQL was:

INSERT INTO sim_usermsg (`toUser_id`, `fromUser_id`, `inReplyTo_id`, content, `createTime`, `gcTime`, read, tan) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    -> INFO:sqlalchemy.engine.base.Engine.0x..d0:[1L, None, 'a test message', datetime.datetime(2006, 12, 9, 20, 17, 17, 231944), None, 0, 18487](2L,)

The problem was the "read" column name. I renamed it into "isRead", and now it is working.

The INSERT statement would be working if the "read" were in apostrophes, like: INSERT INTO sim_usermsg (toUser_id, fromUser_id, inReplyTo_id, content, createTime, gcTime, read, tan) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) [BR] Anybody else knows that a column (trivially) cannot be named "read" ? (No joke, it's late and it was a hard day ;-) but I assume it to be a bug. Ruben

Comments (3)