Simon Simon - 11 days ago 6
Python Question

Creating tables with a one to many relationship not using primary key

So I am trying to implement a simple one to many relation between these two tables. I've read the docs, scraped the net, put in a lot of work to solve this so I turn to you.

I have full access to the database and are able to create other tables, with similar relationships that do work.

I'm using mariadb, "mysql".

Every row in the table Tradable has an tick_size_id and every row has a tick_size_id. And I want to connect them with that column, I can't seem to figure out how.

base = declarative_base()
class Tradables(base):
__tablename__ = "tradables"

id = Column(Integer, primary_key=True)
tick_size_id = Column(Integer, nullable=False)
ticks = relationship("Ticks")


class Ticks(base):
__tablename__ = "ticks"

id = Column(Integer, primary_key=True)
tick_size_id = Column(Integer, ForeignKey("tradables.tick_size_id"))


def main():
engine = create_engine("mysql+pymysql://user:password@localhost/database?host=localhost?port=3306")
base.metadata.create_all(engine)


if __name__ == '__main__':
main()





This does not work.

and fails with:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1005, 'Can\'t create table
Trading
.
ticks
(errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: '\n

CREATE TABLE ticks (\n\t
id INTEGER NOT NULL AUTO_INCREMENT, \n\t
tick_size_id INTEGER, \n\t
PRIMARY KEY (id), \n\t
FOREIGN KEY(tick_size_id) REFERENCES tradables (tick_size_id)\n)\n\n']


What am I doing wrong?




Edit:

Tried both these creation orders and which both gives the same result.

base.metadata.tables["ticks"].create(bind=engine)
base.metadata.tables["tradables"].create(bind=engine)


and

base.metadata.tables["tradables"].create(bind=engine)
base.metadata.tables["ticks"].create(bind=engine)

Answer

You need to have an index on tradables.tick_size_id. I'm not an alchemist, but I guess it would be something like

...
__tablename__ = "tradables"

id = Column(Integer, primary_key=True)
tick_size_id = Column(Integer, nullable=False, index=True)
ticks = relationship("Ticks")
...
Comments