Issue in creating a city record when running model.py in interactive mode

[itsahsiao]

Re: Issue #3 - Received these errors when running python -i model.py and trying to create a record in the cities table doing city = City(city_name="Sunnyvale"):

InvalidRequestError: When initializing mapper Mapper|Restaurant|restaurants, expression 'Cities' failed to locate a name ("name 'Cities' is not defined"). If this is a class name, consider adding this relationship() to the <class 'model.Restaurant'> class after both dependent classes have been defined.

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Connection.user - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

[itsahsiao]

Fixed the InvalidRequestError by realizing that the relationships defined used the plural form of "cities" instead of "city" for the model name.

Instead of: city = db.relationship("Cities")

It should be: city = db.relationship("City")

Still getting the AmbiguousForeignKeysError when trying to create a city record

[itsahsiao]

AmbiguousForeignKeysError resulted from handling multiple join paths.

Connection model only defined one relationship, whereas two was required, for the two user_id's. Also needed to specify which column the relationship was pointing to in the square brackets []:

class Connection(db.Model):
    """Connection between two users to establish a friendship and can see each other's info."""

    __tablename__ = "connections"

    connection_id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    first_user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False)
    added_user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False)
    status = db.Column(db.String(100), nullable=True)

    # Define relationships
    first_user = db.relationship("User", foreign_keys=[first_user_id])
    added_user = db.relationship("User", foreign_keys=[added_user_id])

Solutions here:

• http://stackoverflow.com/questions/22355890/sqlalchemy-multiple-foreign-keys-to-the-same-primary-id-in-another-table
• http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#handling-multiple-join-paths

[itsahsiao]

While still in the python interactive mode, city = City(city_name="Sunnyvale") works; when I do db.session.add(city), no error appears, but when I commit the change doing db.session.commit(), I get an IntegrityError:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "updated_At" violates not-null constraint
DETAIL:  Failing row contains (1, Sunnyvale, null).
 [SQL: 'INSERT INTO cities (city_name, "updated_At") VALUES (%(city_name)s, %(updated_At)s) RETURNING cities.city_id'] [parameters: {'city_name': 'Sunnyvale', 'updated_At': None}]

[itsahsiao]

Looking at the City model, updated_At is set to nullable=False. While I don't want this column to be null, what I really wanted was a timestamp for when the record gets generated.

Per solution at Stack Overflow (http://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime), needed to set a default key and used utcnow, as recalled utc time zone being preferred:

updated_At = db.Column(db.DateTime, default=datetime.datetime.utcnow)

Note: Double check with the help queue tomorrow on utcnow for datetime.

No longer getting error when I make a City object and add/commit - Record gets generated when I check psql db:

Applied same default to uploaded_At field under Image model.

[itsahsiao]

Checked with Bonnie regarding UTC time zone being preferred. Due to issues surrounding time and time zones, good to set default time zone as UTC in the database. Then when displaying to the user, can modify to display in user's time zone on page.