使用Flask-SQLAlchemy和Python建模

Question

我最近开始学习Flask，之前是做C#开发的。为了更好地练习，我想创建一个项目，这次是从我之前做过的一个C# Webform项目复制过来的。

这是我在Microsoft SQL中数据库的样子：

我在使用ORM（对象关系映射）时遇到了困难，读了一些指南和建议。但是当我尝试创建我的项目时，出现了一些错误。以下是我在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))

我不知道你是否需要一些数据库的描述，但如果需要的话，我可以提供。

数据库描述

City：这是一个包含城市名称和对应邮政编码的表。

Role：这是一个包含角色名称和对应角色ID的表，用于用户管理。

User：这是一个包含所有用户数据的表。

Artistname：如果用户的角色是艺术家（artistuser），我会使用这个表，因为他们需要一个用户名。这里的连接是ArtistID=UserID如果你需要参考，可以查看T-SQL在MS DB中的样子。

Act：这是一个显示表演的表，连接是从Artistname，ArtistID=ArtistID，以查看谁进行了表演。

Performance：这是一个显示表演时间的表，例如，第一场表演在周一和周五进行。

Stage：这是一个显示表演地点的表。

错误信息

当我运行这段代码时（我成功地向两个表插入了数据，Role和Zipcode）。但是当我尝试向第三个表——User插入数据时，出现了错误。

我的插入方式：

 # 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()

我遇到的错误：

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'>

额外信息

在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

错误处理 orm sqlalchemy 用户管理对象关系映射 flask 数据库建模表演管理

使用Flask-SQLAlchemy和Python建模

数据库描述

错误信息

额外信息

1 个回答

撰写回答