Modelling with Flask-SQLAlchemy and Python

Question

I recently started learning Flask, been a C# developer before. Well as a good practice I wanted to create a project but this time a copy from a C# - Webform project I already did before.

Here is how the DB looks like in Microsoft SQL

I am having a hard time with ORM, and have read some guides and tips. But it seems that when I try to create my project I am getting some errors. Here is how the model looks like in Python

class User(db.Model):
    __tablename__ = 'users'
    userID = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True)
    firstName = db.Column(db.String(64))
    lastName = db.Column(db.String(64))
    address = db.Column(db.String(64))
    zipcode = db.Column(db.Integer, db.ForeignKey('zipcodes.zipcode'))
    roleID = db.Column(db.Integer, db.ForeignKey('roles.roleID'))
    password = db.Column(db.String(64))

class Role(db.Model):
    __tablename__ = 'roles'
    roleID = db.Column(db.Integer, primary_key=True)
    roleName = db.Column(db.String(64), unique=True, index=True)

    users = db.relationship('User', backref='role_role')

class Zipcode(db.Model):
    __tablename__ = 'zipcodes'
    zipcode = db.Column(db.Integer, primary_key=True, autoincrement=False)
    city = db.Column(db.String(64))

    users = db.relationship('User', backref='zipcode_zipcode')


class Artistname(db.Model):
    __tablename__ = 'artistnames'
    artistID = db.Column(db.Integer, db.ForeignKey('users.userID'), primary_key=True, autoincrement=False)
    artistname = db.Column(db.String(64), unique=True)

    a_users = db.relationship('User', backref='artistname')

class Act(db.Model):
    __tablename__ = 'acts'
    actID = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64), unique=True)
    artistID = db.Column(db.Integer, db.ForeignKey('artistnames.artistID'))
    description = db.Column(db.Text)
    duration = db.Column(db.Integer)
    imageURL = db.Column(db.String(64))

    a_artistnames = db.relationship('Artistname', backref='act')

class Performance(db.Model):
    __tablename__ = 'performances'
    performanceID = db.Column(db.Integer, primary_key=True)
    actID = db.Column(db.Integer, db.ForeignKey('acts.actID'))
    date = db.Column(db.Date)
    stageID = db.Column(db.Integer, db.ForeignKey('stages.stageID'))

class Stage(db.Model):
    __tablename__ ='stages'
    stageID = db.Column(db.Integer, primary_key=True)
    stageName = db.Column(db.String(64))

I don't know if you need a little Database description, but here it goes if you need it.

Database description

City A table with the city names and the corresponding zipcode.

Role A table with role names and the corresponding roleID, used for User.

User A table with all the data on the users

Artistname I use this table if the user is having role (artistuser) and therefore is a user who needs a username. Where the connection is ArtistID=UserIDsee T-SQL for how it would look like in MS DB, if you need the reference

Act A table with which shows the acts where the connection is from Artistname, ArtistID=ArtistID to see who made the act

Performance A table that shows when the act is rolling, for example act 1 is live monday and friday.

Stage A table that shows where the performance is being played.

The errors

Well when I run this code(I managed to insert into two tables, Role and Zipcode). But when I try to insert into the third table, - User I am getting errors. How I insert

 # Role
    r1 = Role(roleName='Role 1')
    r2 = Role(roleName='Role 2')
    r3 = Role(roleName='Role 3')
    db.session.add_all([r1, r2, r3])

    # Zipcode
    zip1 = Zipcode(zipcode=1, city='zip 1')
    zip2 = Zipcode(zipcode=2, city='zip 2')
    zip3 = Zipcode(zipcode=3, city='zip 3')
    db.session.add_all([zip1, zip2, zip3])

    # User
    u1 = User(
        email='a@a.dk',
        firstName='John',
        lastName='Doe',
        address='test street 1',
        zipcode=zip2,
        roleID=r1,
        password='john')
    u2 = User(
        email='b@b.dk',
        firstName='Jane',
        lastName='Doe',
        address='test street 1',
        zipcode=zip1,
        roleID=r2,
        password='jane')
    u3 = User(
        email='c@c.dk',
        firstName='Jack',
        lastName='Doe',
        address='test street 1',
        zipcode=zip3,
        roleID=r3,
        password='jack')

    db.session.add_all([u1, u2, u3])

    # commit db
    db.session.commit()

and the error I am getting

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\var\test\las_vegas\__program\test.py", line 52, in t
    db.session.commit()
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\scoping.py", line
 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 768, in commit
    self.transaction.commit()
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 370, in commit
    self._prepare_impl()
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 350, in _prepare_impl
    self.session.flush()
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 1907, in flush
    self._flush(objects)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 2025, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\util\langhelpers.py",
 line 57, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\util\compat.py", line
 172, in reraise
    raise value
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\session.py", line
 1989, in _flush
    flush_context.execute()
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\unitofwork.py", l
ine 371, in execute
    rec.execute(self)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\unitofwork.py", l
ine 524, in execute
    uow
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\persistence.py",
line 64, in save_obj
    mapper, table, insert)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\orm\persistence.py",
line 600, in _emit_insert_statements
    execute(statement, params)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\base.py", line
 727, in execute
    return meth(self, multiparams, params)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\sql\elements.py", lin
e 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\base.py", line
 824, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\base.py", line
 954, in _execute_context
    context)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\base.py", line
 1119, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\util\compat.py", line
 172, in reraise
    raise value
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\base.py", line
 947, in _execute_context
    context)
  File "C:\var\test\las_vegas\lib\site-packages\sqlalchemy\engine\default.py", l
ine 435, in do_execute
    cursor.execute(statement, parameters)
  File "C:\var\test\las_vegas\lib\site-packages\pymysql\cursors.py", line 130, i
n execute
    query = query % self._escape_args(args, conn)
  File "C:\var\test\las_vegas\lib\site-packages\pymysql\cursors.py", line 96, in
 _escape_args
    return tuple(conn.escape(arg) for arg in args)
  File "C:\var\test\las_vegas\lib\site-packages\pymysql\cursors.py", line 96, in
 <genexpr>
    return tuple(conn.escape(arg) for arg in args)
  File "C:\var\test\las_vegas\lib\site-packages\pymysql\connections.py", line 69
0, in escape
    return escape_item(obj, self.charset)
  File "C:\var\test\las_vegas\lib\site-packages\pymysql\converters.py", line 24,
 in escape_item
    encoder = encoders[type(val)]
KeyError: <class 'manage.Zipcode'>

Extra

Insert user with artistname in MS

ALTER PROCEDURE [dbo].[sp_INSERT_MAGICIAN]
    @newArtistName nvarchar(50),
    @newFirstName nvarchar(50),
    @newLastName nvarchar(50), 
    @newEmail nvarchar(50),
    @newPassword varchar(50),
    @newAddress nvarchar(50),
    @newZipcode int
AS
    declare @inserr int
    declare @maxerr int

    set @maxerr = 0

BEGIN TRANSACTION

--add user
INSERT INTO [User] 
    ([User].Firstname, [User].Lastname, [User].Email, [User].[Password], [User].[Address], [User].Zipcode, [User].[Role])
VALUES
    (@newFirstName, @newLastName, @newEmail, @newPassword, @newAddress, @newZipcode, 3)

INSERT INTO [Artistname]    
    ([Artistname].ArtistId, [Artistname].Artistname)
VALUES
    (SCOPE_IDENTITY(), @newArtistName)

-- Save error number returned from Insert statement
SET  @inserr = @@error
IF @inserr > @maxerr
    SET @maxerr = @inserr

-- If an error occured, roll back
IF @maxerr <> 0
    BEGIN
        rollback transaction
        print 'Transaction rolled back'
    END
ELSE
    BEGIN
        COMMIT transaction
        PRINT 'Transaction comimitted'
    END
PRINT 'INSERT error number:' + cast(@inserr as nvarchar(8))

RETURN @maxerr

Answer 1

You need to commit the roles and zip codes before you use them in the users. Those objects won't have integer ids until that point. You'll also have to change the user objects to something like

u1 = User(
    email='a@a.dk',
    firstName='John',
    lastName='Doe',
    address='test street 1',
    zipcode=zip2.zipcode,
    roleID=r1.roleID,
    password='john')

The zip codes and roles fields expect integer ids, not the objects.