python - 引用 uid 时创建新的 SQLalchemy 对象失败

Question

这里是新的 python 和 SQLAlchemy 用户(是的，我知道 :))无论如何，我正在尝试使用用户、传感器和读数表创建一些表，其中用户和传感器与读数具有一对多关系。

我的类(class)是这样的:

class User(db.Model):
    __tablename__ = 'user'
    uid = db.Column(db.Integer, primary_key=True, index=True)
    firstName = db.Column(db.String(100))
    lastName = db.Column(db.String(100))
    emailAddress = db.Column(db.String(120), unique=True, index=True)
    pwHash = db.Column(db.String(256))
    userLevel = db.Column(db.Integer())
    userAccountType = db.Column(db.Integer())
    isUserActive = db.Column(db.Boolean())
    isUserLockedOut = db.Column(db.Boolean())
    userLastLogin = db.Column(db.DateTime())
    lastInvalidLogin = db.Column(db.DateTime())
    userCreatedAt = db.Column(db.DateTime())
    userConfirmedAt = db.Column(db.DateTime())
    userUpdatedAt = db.Column(db.DateTime(), onupdate=datetime.datetime.now())
    userAddress = db.relationship('Address', backref='user', lazy='dynamic')
    userContactMethod = db.relationship('UserContactMethod', backref='user', lazy='dynamic')
    userSensor = db.relationship('Sensor', backref='user', lazy='dynamic')
    userReading = db.relationship('Reading', backref='user', lazy='dynamic')
    deliveryEvents = db.relationship('logSMTPDeliveryEvents', backref='user', lazy='dynamic')


class Reading(db.Model):
    __tablename__ = 'reading'

    rid = db.Column(db.Integer, primary_key=True)
    uid = db.Column(db.Integer, db.ForeignKey('user.uid'))
    sid = db.Column(db.Integer, db.ForeignKey('sensor.sid'))
    readingTimestamp = db.Column(db.DateTime())
    readingLightValue = db.Column(db.Integer)
    readingLightStatus = db.Column(db.String(6))
    readingTemp1 = db.Column(db.Float)
    readingTemp2 = db.Column(db.Float)
    readingHumidity = db.Column(db.Float)

    def __init__(self, uid, sid, readingTimestamp, readingLightValue, readingLightStatus, readingTemp1, readingTemp2,
                 readingHumidity):

        self.uid = uid
        self.sid = sid
        self.readingTimestamp = readingTimestamp
        self.readingLightValue = readingLightValue
        self.readingLightStatus = readingLightStatus
        self.readingTemp1 = readingTemp1
        self.readingTemp2 = readingTemp2
        self.readingHumidity = readingHumidity

所以，如果我尝试像这样创建对象:

    newReading = Reading(uid=10, sid=123,readingTimestamp = readingTimestamp, readingLightValue=readingLightValue,
                         readingLightStatus=readingLightStatus, readingTemp1=readingTemp1, readingTemp2=readingTemp2,
                         readingHumidity=readingHumidity)
    db.session.add(newReading)
    db.session.commit()

在我对 uid 进行硬编码的地方一切正常。无论如何，如果我在此之前尝试使用我的查询:

    userID = db.session.query(User.uid).filter(User.emailAddress == userEmailAddress).first()

然后我在创建对象中使用 userID 变量，如下所示:

    newReading = Reading(uid=userID, sid=123,readingTimestamp = readingTimestamp, readingLightValue=readingLightValue,
                         readingLightStatus=readingLightStatus, readingTemp1=readingTemp1, readingTemp2=readingTemp2,
                         readingHumidity=readingHumidity)

我收到以下错误:

IntegrityError: (IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`gnomecentral`.`reading`, CONSTRAINT `reading_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`))') 'INSERT INTO reading (uid, sid, `readingTimestamp`, `readingLightValue`, `readingLightStatus`, `readingTemp1`, `readingTemp2`, `readingHumidity`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)' ((10L,), 123, datetime.datetime(2013, 9, 14, 14, 48, 7, 684071), 43, '43', 43.0, 43.0, 43.0)

在此先感谢您的帮助!克雷格

Answer 1

.first() 返回包含单个值的行，而不是该单个值。因此，您正在尝试使用一组值作为新对象中的 uid。您可以在错误中看到这一点；有一个元组应该是一个数字。

您可以使用 .scalar() 获取第一行的第一列，而不是 .first()。