python - 使用 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))

我不知道您是否需要一些数据库描述，但如果您需要的话，这里就可以了。

数据库描述

城市包含城市名称和相应邮政编码的表格。

角色包含角色名称和相应角色 ID 的表，用于用户。

用户包含用户所有数据的表格

艺术家姓名如果用户具有角色(artistuser)并且因此是需要用户名的用户，我会使用此表。连接为 ArtistID=UserID如果您需要引用，请参阅 T-SQL 了解它在 MS DB 中的外观

行动一个表格，其中显示了连接来自Artistname的行为，ArtistID=ArtistID，以查看是谁做出的行为

性能显示演出时间的表格，例如第一幕在周一和周五直播。

舞台显示演出地点的表格。

错误

当我运行这段代码时(我设法插入两个表，角色和邮政编码)。但是当我尝试插入第三个表时， - 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

Answer 1

您需要先提交角色和邮政编码，然后才能在用户中使用它们。在那之前，这些对象不会有整数 ID。您还必须将用户对象更改为类似

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

邮政编码和角色字段需要整数 ID，而不是对象。