daily pastebin goal
50%
SHARE
TWEET

Untitled

a guest Jan 25th, 2016 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import relationship, backref, sessionmaker, joinedload
  4.  
  5. # For this example we will use an in-memory sqlite DB.
  6. # Let's also configure it to echo everything it does to the screen.
  7. engine = create_engine('sqlite:///:memory:', echo=True)
  8.  
  9. # The base class which our objects will be defined on.
  10. Base = declarative_base()
  11.  
  12. # Our User object, mapped to the 'users' table
  13. class User(Base):
  14.     __tablename__ = 'users'
  15.  
  16.     # Every SQLAlchemy table should have a primary key named 'id'
  17.     id = Column(Integer, primary_key=True)
  18.  
  19.     name = Column(String)
  20.     fullname = Column(String)
  21.     password = Column(String)
  22.  
  23.     # Lets us print out a user object conveniently.
  24.     def __repr__(self):
  25.        return "<User(name='%s', fullname='%s', password'%s')>" % (
  26.                                self.name, self.fullname, self.password)
  27.  
  28. # The Address object stores the addresses
  29. # of a user in the 'adressess' table.
  30. class Address(Base):
  31.     __tablename__ = 'addresses'
  32.     id = Column(Integer, primary_key=True)
  33.     email_address = Column(String, nullable=False)
  34.  
  35.     # Since we have a 1:n relationship, we need to store a foreign key
  36.     # to the users table.
  37.     user_id = Column(Integer, ForeignKey('users.id'))
  38.  
  39.     # Defines the 1:n relationship between users and addresses.
  40.     # Also creates a backreference which is accessible from a User object.
  41.     user = relationship("User", backref=backref('addresses'))
  42.  
  43.     # Lets us print out an address object conveniently.
  44.     def __repr__(self):
  45.         return "<Address(email_address='%s')>" % self.email_address
  46.  
  47.  
  48. # Create all tables by issuing CREATE TABLE commands to the DB.
  49. Base.metadata.create_all(engine)
  50.  
  51. # Creates a new session to the database by using the engine we described.
  52. Session = sessionmaker(bind=engine)
  53. session = Session()
  54.  
  55. # Let's create a user and add two e-mail addresses to that user.
  56. ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
  57. ed_user.addresses = [Address(email_address='ed@google.com'), Address(email_address='e25@yahoo.com')]
  58.  
  59. # Let's add the user and its addresses we've created to the DB and commit.
  60. session.add(ed_user)
  61. session.commit()
  62.  
  63. # Now let's query the user that has the e-mail address ed@google.com
  64. # SQLAlchemy will construct a JOIN query automatically.
  65. user_by_email = session.query(User)\
  66.     .filter(Address.email_address=='ed@google.com')\
  67.     .first()
  68.  
  69. print user_by_email
  70.  
  71. # This will cause an additional query by lazy loading from the DB.
  72. print user_by_email.addresses
  73.  
  74.  
  75. # To avoid querying again when getting all addresses of a user,
  76. # we use the joinedload option. SQLAlchemy will load all results and hide
  77. # the duplicate entries from us, so we can then get for
  78. # the user's addressess without an additional query to the DB.
  79. user_by_email = session.query(User)\
  80.     .filter(Address.email_address=='ed@google.com')\
  81.     .options(joinedload(User.addresses))\
  82.     .first()
  83.  
  84. print user_by_email
  85. print user_by_email.addresses
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top