Example of using ORM mode for SQLAlchemy

Know: What is the experience of manipulating a database using SQLAlchemy orm?

Answer: Cool!

This article is based on: Win10 + python3.4 + sqlAlchemy 1.0.13

The following steps are shown:

1. Declaring a base class

 from Import  = Declarative_base ()

2. Defining tables

 fromSQLAlchemyImportColumn, Integer, ForeignKey fromSqlalchemy.ormImportRelationshipclassParent (Base):__tablename__='Parent'ID= Column (Integer, primary_key=True) Children= Relationship (" Child")        def __repr__(self):return "<parent (id= ' {} ', children= ' {} ') >". Format (self.id, Self.children)classChild (Base):__tablename__=' Child'ID= Column (Integer, primary_key=True) parent_id= Column (Integer, ForeignKey ('parent.id'))        def __repr__(self):return "<child (id= ' {} ', parent_id= ' {} ') >". Format (self.id, self.parent_id)

3. Establish a connection

 from Import  = create_engine ('sqlite:///:memory:', echo=true)

4. Establishing a session

 from Import  = Session (engine)

5. Create a table

# production environment only need to run once!!! 

6. Add a record

 for inch  for  in range (2)]) session.commit ()

7. Enquiry

#lazy Loading (lazyload)#load everything, no eager loading. forParentinchsession.query (Parent):Print(Parent.children)#Federated Loading (joinedload)#load everything, joined eager loading. forParentinchSession.query (Parent). Options (Joinedload ("Children")): Parent.children#Child Query Loading (subqueryload)#load everything, subquery eager loading. forParentinchSession.query (Parent). Options (Subqueryload ("Children")): Parent.children

8. Complete code

 fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn, Integer, ForeignKey fromSqlalchemy.ormImportRelationship fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportSession fromSqlalchemy.ormImportJoinedload, Subqueryload#declaring base classesBase =declarative_base ()#Defining TablesclassParent (Base):__tablename__='Parent'ID= Column (Integer, primary_key=True) Children= Relationship (" Child")        def __repr__(self):return "<parent (id= ' {} ', children= ' {} ') >". Format (self.id, Self.children)classChild (Base):__tablename__=' Child'ID= Column (Integer, primary_key=True) parent_id= Column (Integer, ForeignKey ('parent.id'))        def __repr__(self):return "<child (id= ' {} ', parent_id= ' {} ') >". Format (self.id, self.parent_id)#Establish a connectionEngine = Create_engine ('sqlite:///:memory:', echo=True)#Establish a sessionSession =Session (Engine)#Generating TablesBase.metadata.drop_all (Engine) Base.metadata.create_all (engine )#production environment only need to run once!!! #Add a recordSession.add_all ([Parent (Children=[child () forJinchRange (5)]) forIinchRange (2)]) session.commit ()#Enquiry#Lazyload#load everything, no eager loading. forParentinchsession.query (Parent):Print(Parent.children)#Joinedload#load everything, joined eager loading. forParentinchSession.query (Parent). Options (Joinedload ("Children")): Parent.children#Subqueryload#load everything, subquery eager loading. forParentinchSession.query (Parent). Options (Subqueryload ("Children")): Parent.children

Example of using ORM mode for SQLAlchemy