sqlalchemy messes up names with "_1" suffix

[Xander Cage]

hi,

i have this litte flask-admin game running, now out of nowwhere sqlalchemy has begun to add strange "_1" suffixes to the column names. i know sqlalchemy does this to keep names unique, but in my case the queries are failing

and my naming is unique.

my models:

### DB models

# Base model that for other models to inherit from
class Base(db.Model):
    __abstract__ = True

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    ts = db.Column(db.TIMESTAMP, default=db.func.current_timestamp(),
                              onupdate=db.func.current_timestamp())

    def __str__(self):
        attrs = db.class_mapper(self.__class__).attrs # show also relationships
        if 'name' in attrs:
            return self.name
        elif 'parent' in attrs:
            return self.parent
        elif 'value' in attrs:
            return self.value
        else:
            return "<%s(%s)>" % (self.__class__.__name__,
                                 ', '.join('%s=%r' % (k.key, getattr(self, k.key))
                                           for k in sorted(attrs)
                                          )
                                )

class Attrib(Base):
    __tablename__ = 'attribs'
    name = Column(String(256, u'utf8_unicode_ci'), nullable=False)
    persistent = Column(Integer, server_default=FetchedValue())
    parent = Column(String(256, u'utf8_unicode_ci'), server_default=FetchedValue())

class Entry(Base):
    __tablename__ = 'entries'
    node_id = Column(ForeignKey(u'nodes.id', ondelete=u'CASCADE', onupdate=u'CASCADE'), nullable=False, index=True)
    attrib_id = Column(ForeignKey(u'attribs.id', ondelete=u'CASCADE', onupdate=u'CASCADE'), nullable=False, index=True)
    value = Column(String(256, u'utf8_unicode_ci'), nullable=False)
    attrib = relationship(u'Attrib', primaryjoin='Entry.attrib_id == Attrib.id', backref=u'entries')
    node = relationship(u'Node', primaryjoin='Entry.node_id == Node.id', backref=u'entries')

class Node(Base):
    __tablename__ = 'nodes'
    name = Column(String(256, u'utf8_unicode_ci'), nullable=False)

error:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'attribs_1.ts' in 'field list'")
[SQL: SELECT entries.id AS entries_id, entries.ts AS entries_ts, entries.node_id AS entries_node_id, entries.attrib_id AS entries_attrib_id, entries.value AS entries_value, attribs_1.id AS attribs_1_id, attribs_1.ts AS attribs_1_ts, attribs_1.name AS attribs_1_name, attribs_1.persistent AS attribs_1_persistent, attribs_1.parent AS attribs_1_parent, nodes_1.id AS nodes_1_id, nodes_1.ts AS nodes_1_ts, nodes_1.name AS nodes_1_name
FROM entries LEFT OUTER JOIN attribs AS attribs_1 ON entries.attrib_id = attribs_1.id LEFT OUTER JOIN nodes AS nodes_1 ON entries.node_id = nodes_1.id
 LIMIT %(param_1)s]
[parameters: {'param_1': 20}]

any idea whats going on here?

[Mike Bayer]

Hi there-

this depends on how you are emitting this query.  Can you supply a complete minimal reproducing example?  most notably, what code you are running which produces this SQL statement.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0983ac9b-0ecd-416e-bf6c-1ee5e73c95aeo%40googlegroups.com.

[Ben]

Not sure if this will help but are you using FlaskWTF?  If you have repeating fields on a form, some of its data structures will append a _1, _2... to each instance in your response to keep them unique. So, just a guess, but perhaps your problem is related to Flask / WTForms?

[Jonathan Vanasco]

> i have this litte flask-admin game running, now out of nowwhere sqlalchemy has begun to add strange "_1" suffixes to the column names. i know sqlalchemy does this to keep names unique, but in my case the queries are failing

SQLAlchemy does do this, for those reasons, and to the columns... but note those exact error:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1054, "Unknown column 'attribs_1.ts' in 'field list'")

It's not finding the `.ts` on the `attribs` table, which was mapped to `attribs_1` in the query.

I think the best thing do to is what mike said - create a complete executable example you can share. the model + the query.  My first guess is that you have a typo on the column/table name in the model or query.  There could also be an inheritance issue because of a typo too.