0

Alembic keeps giving me this error when I try to migrate my schema even though the initial migration went without a hitch.

sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'dataset.datasetid' on table 'analysis': table 'dataset' has no column named 'datasetid'

Here is a part of my models.py class 

class Dataset(db.Model):
    DatasetID = db.Column(db.Integer, primary_key = True)
    SampleID = db.Column(db.String(50), db.ForeignKey('sample.SampleID', onupdate="cascade",ondelete="restrict"), nullable=False)
    UploadDate = db.Column(db.Date, nullable=False)
    UploadID = db.Column(db.Integer,db.ForeignKey('uploaders.UploadID', onupdate="cascade",ondelete="restrict"), nullable=False)
    UploadStatus = db.Column(db.String(45), nullable=False)
    HPFPath = db.Column(db.String(500))
    DatasetType = db.Column(db.String(45), nullable=False)
    SolvedStatus = db.Column(db.String(30), nullable=False)
    InputFile = db.Column(db.Text)
    RunID = db.Column(db.String(45))
    Notes = db.Column(db.Text)
    analyses = db.relationship('Analysis',backref='dataset',lazy='dynamic')
    data2Cohorts = db.relationship('Dataset2Cohort',backref='dataset',lazy='dynamic')

class Dataset2Cohort(db.Model):
    __tablename__='dataset2Cohort'
    DatasetID = db.Column(db.Integer, db.ForeignKey('dataset.DatasetID', onupdate="cascade",ondelete="cascade"), nullable=False, primary_key = True)
    CohortID = db.Column(db.Integer, db.ForeignKey('cohort.CohortID', onupdate="cascade", ondelete="restrict"),  nullable=False, primary_key = True)

class Analysis(db.Model):
    AnalysisID = db.Column(db.String(100), primary_key = True)
    DatasetID = db.Column(db.Integer, db.ForeignKey('dataset.DatasetID', onupdate="cascade",ondelete="cascade"), nullable=False)
    PipelineVersion = db.Column(db.String(30))
    ResultsDirectory = db.Column(db.Text)
    ResultsBAM = db.Column(db.Text)
    AssignedTo = db.Column(db.String(100), nullable=True)
    analysisStatuses = db.relationship('AnalysisStatus', backref='analysis', lazy='dynamic')

Does anyone know why I keep getting that error even though I have the DatasetID column in the Dataset table?

Thank you, Teja.

Stephen
  • 1,072
  • 1
  • 19
  • 33
viswateja nelakuditi
  • 11
  • 1
  • 1
    I would advise you to use lower case variable/column names. So instead of `DatasetID` use something like `dataset_id`. This can avoid issues like this. Also it's more conventional to name variables and columns using underscores (see the style guide https://www.python.org/dev/peps/pep-0008/) – Jonas Feb 19 '19 at 20:55
  • Thank you Jonas. I will keep that in mind. Is there a way to force alembic/sqlalchemy to recognize my column names as they are now? or is the only way to rename all my column names to lowercase/conventional naming? Teja. – viswateja nelakuditi Feb 19 '19 at 21:58

1 Answers1

1

Found a solution.

This seems to be an issue with how Mysql 8.x versions refer to column names in the foreign key declaration - Mysql 8.x versions always use lowercase when a column is referenced in Foreign Key statements, which cause an incompatibility with sqlalchemy. This issue is discussed here

https://github.com/sqlalchemy/sqlalchemy/issues/4344

Solution is to just upgrade the sqlalchemy to the latest version (>=1.2.x)

Teja.

viswateja nelakuditi
  • 11
  • 1