Study the ing between SQLAlchemy databases in the Python ORM framework

Source: Internet
Author: User
This article mainly introduces the ing relationship between the SQLAlchemy library in the Python ORM framework. the SQLAlchemy Library is a common tool for operating databases in Python, if you need a User, you can refer to the User table about the User account described earlier. However, in real life, with the complexity of the problem, the data stored in the database cannot be so simple. let's imagine another table, this table is associated with the User and can be mapped and queried. Therefore, this table can store any number of email addresses associated with an account. This relationship is a typical 1-N (one-to-multiple) relationship in the database theory. a user table corresponds to N email records.

Previously, our user table was called users. now we create another table named addresses to store email addresses. through the Declarative system, we can directly define this table using the Address ing class Address:

>>> from sqlalchemy import ForeignKey>>> from sqlalchemy.orm import relationship, backref >>> class Address(Base):...   __tablename__ = 'addresses'...   id = Column(Integer, primary_key=True)...   email_address = Column(String, nullable=False)...   user_id = Column(Integer, ForeignKey(''))......   user = relationship("User", backref=backref('addresses', order_by=id))......   def __init__(self, email_address):...     self.email_address = email_address......   def __repr__(self):...     return "〈Address('%s')〉" % self.email_address

Let's take a look at the new stuff. The first is the ForeignKey structure of user_id. students who have learned the database know that ForeignKey represents a foreign key. this is one of the core theories of relational databases, this is the users column referenced by the user_id column and its foreign key. the id has a constrained relationship. at the database level, the user_id column of the table users is restricted by the id column of the table users. it is worth noting that, the foreign key must be the primary key of another table.

Next, the new relationship () function will inform the ORM that the Address. userAddress class itself must be linked to the User class. Relationship () uses the foreign key constraints of the two tables to determine the nature of the link. for example, it determines that the Address. user will be a many-to-one relationship.

In addition, there is another function in relationship () called backref (), which provides details for reverse query. for example, the Address object set on the object User is through the User. when the addresses attribute is referenced, the inverse of the many-to-one relationship is always one-to-many ). In addition, the relationship between Address. user and User. addresses is always bidirectional.

If the Declarative system is used, the parameters of relationship () related to remote class can be specified as strings. Once all mappings are successfully loaded, these strings will be computed into Python expressions and then generate actual parameters (in the above case of the User class ). The names of these available strings must be created through the defined base class before being calculated as actual class parameters. to put it bluntly, the class referenced by your string must be a class managed by ORM ING, after these classes are mapped, these strings can be truly translated as references to the corresponding classes.

In the following example, we will also create a sses/User bidirectional relationship that replaces Address with user:

class User(Base):  # ....  addresses = relationship("Address", order_by="", backref="user")

Well, the official documents just now are mostly translated. they are quite blunt. next we will learn a few tips on Foreign keys:

1. foreign key constraints can be linked to primary KEY columns or columns with UNIQUE constraints in most (but not all) relational databases.

2. foreign key can reference multiple primary keys and has multiple columns. it is called composite foreign key ). It can also reference subsets of these columns ). (Note: This is not clear)

3. the foreign key column can automatically update itself as a response to the changes in the referenced columns or rows. for example, CASCADE reference operations are all built-in functions of relational databases.

4. foreign key can reference its own table. this involves the foreign key of self-referential.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.