Lelsoos Lelsoos - 3 months ago 7
MySQL Question

Flask-SQLAlchemy adds unnecessary row in parents table (relational tables)

I'm recently trying to build a little web-app with Flask. For the database 'stuff' I use Flask-SQLAlchemy and now I'm trying to get a relationship between two objects going.

I have a 'project' table and a 'file' table and it should be a one-to-many relation, so x files can be associated with one project (actually there are more relations coming in the future when I've figured the current problem out).

I've made a input-mask template so a user can upload a file and link it to a project via a dropdown which is populated with the existing projects stored in its table. Thats the corresponding view:

@app.route('/admin/upload/', methods=('GET', 'POST'))
def upload():
form = forms.UploadForm()

if not os.path.isdir(app.config['UPLOAD_FOLDER']):
os.mkdir(app.config['UPLOAD_FOLDER'])
print('Folder created')

form.projectId.choices = []

for g in models.Project.query.order_by('name'):
form.projectId.choices.append((g.id, g.name))

if form.validate_on_submit():
filename = secure_filename(form.fileUpload.data.filename)
filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
assocProject = models.Project(name=models.Project.query.filter_by(id=form.projectId.data).first().name)

form.fileUpload.data.save(filepath)
prepedFile = models.File(path=filepath, project=assocProject)

print(prepedFile)
print(form.projectId.data)

db.session.add(prepedFile)
db.session.commit()

return 'success'
else:
filename = None
return render_template('upload.html', form=form, filename=filename)


The prepared file should be an instance of the File-Class which has the linked Project-instance as an attribute, therefore the commit should work.

class Project(db.Model):
__tablename__ = 'project'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
file = db.relationship('File', backref="projects")
post = db.relationship('Post', backref="projects")

def __init__(self, name):
self.name = name

def __repr__(self):
return '<Project %r>' % self.name


class File(db.Model):
__tablename__ = 'file'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64))
path = db.Column(db.String(64))
type = db.Column(db.String(6))

projectId = db.Column(db.Integer, db.ForeignKey('project.id'))
project = db.relationship('Project', backref='files')

def __init__(self, path, project):
self.path = path
self.project = project

fullName = re.search('[a-zA-Z0-9]*\.[a-zA-Z]{2,}', path)
splitName = fullName.group(0).split('.')
self.name = splitName[0]
self.type = splitName[1]

def __repr__(self):
return '<File %r %r %r %r>' % (self.name, self.type, self.path, self.project)


And now the problem: When I try to upload a file it works and the file information are stored in the file table but it creates a new entry in the project table and link its id to the file entry. E.g., if the project entry looks like: name = TestProj1, id=1 and I try to link the uploaded to the project it will create a second project: name = TestProj1, id=2.

Thats my struggle and I cant figure out whats wrong. Maybe some of you now. I appreciate any help!

P.S. Maybe it is relevant, here the form I wrote:

class UploadForm(Form):
fileUpload = FileField(label='Deine Datei')
projectId = SelectField(u'Projekte', coerce=int)

Answer

You create a new Project each time.

assocProject = models.Project(name=models.Project.query.filter_by(id=form.projectId.data).first().name)

This does two things. First, it finds the first project with the specified id. Second, it passes that project's name to Project(), creating a new instance using that same name.

What you really want is

assocProject = models.Project.query.get(form.projectId.data)
Comments