Flask_sqlalchemy Notes 1

Flask is not much introduced, is a python written by a very strong micro-framework, is currently learning,

SQLAlchemy a Database abstraction layer, it can provide a unified interface in the upper layer, at the bottom of the different databases can be manipulated, so it is a great middleman.

The 1.06 version currently in use, the following is its overall frame chart:

Under normal circumstances, we are in the ORM level of the database operation, but if the traffic is too large, and also to pursue access efficiency, you can enter the SQLAlchemy core operating database, so that in the possession of sqlalchemy many advantages, you can also improve efficiency. Of course, it can be seen that sqlalchemy is the role of an intermediary between the developer and the database, which can be arbitrary, but the upper operating logic is consistent. Here we only talk about the ORM level of the database operation, because the core level I did not see.

To establish a database:

 fromSQLAlchemyImportcolumn,integer,string#From database import Base fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportScoped_session,sessionmaker#engine is the starting point of the application database operation=create_engine ('sqlite:///yedan.db', echo=True)
The invocation of #db_session通过scope_session () d becomes a similar global variable (the object instantiated by Db_session is the same object), and accessing the database requires only calling it.
#通过scope_session () can not worry about the multi-threaded database, just need to remove the global db_session at the end of request access Db_session=scoped_session (Sessionmaker (autocommit=false,autoflush=false,bind=engine)) #一个创建一个表的元类, provide the appropriate mapping for the newly created table, and the newly created table needs to inherit it
Base=Declarative_base ()
#通过base_query可以查询数据库Base. Query=db_session.query_property () #Session=Sessionmaker ()classPerson (Base):
#命名表__tablename__='Pers'ID=column (integer,primary_key=True) name=column (String), unique=True) Email=column (String), unique=True)def __init__(self,name,email): Self.name=name Self.email=Emaildef __repr__(self):return '<person%r>'%(Self.name) #创建表, Base.metadata.create_all (Bind=engine)

Inter-table relationships:

Many-to-one :

1  fromSQLAlchemyImportColumn,integer,string,foreignkey2 #From database import Base3  fromSqlalchemy.ext.declarativeImportDeclarative_base4  fromSQLAlchemyImportCreate_engine5  fromSqlalchemy.ormImportscoped_session,sessionmaker,relationship6 7 8Engine=create_engine ('sqlite:///dan.db', echo=True)9Db_session=scoped_session (Sessionmaker (autocommit=false,autoflush=false,bind=engine))TenBase=declarative_base () Onebase.query=Db_session.query_property () Asession=Sessionmaker () - #Many-to-oneclass Person (Base): __tablename__= ' pe ' ID =column (integer,primary_key=true)-Name=column (String (80 ), Unique=true) Email=column (String (+), unique=true) son=relationship ("Son", backref= "persons") Son_id=co Lumn (Integer,foreignkey (' son.id ')) +     def __init__(self,name,email,son_id): ASelf.name=name atSelf.email=Email -Self.son_id=son_id -  -     def __repr__(self): -         return '<person%r>'%(Self.name) -          inclass Son (Base): __tablename__= ' son ' id =column (integer,primary_key=true) #per_id =colum N (Integer,foreignkey (' person.id ')) Age=column (integer,unique=true) *  $     def __init__(self,age):Panax NotoginsengSelf.age= Age -  the     def __repr__(self): +         return "<son%r>"%Self.age A  theBase.metadata.create_all (Bind=engine)

When inserting data, you should first insert the son's data, then add () through the session, and then flush (), so that the son's data will generate an ID so that son_id can be passed into the person. Final Unified commit ()

Pair of more:

 fromSQLAlchemyImportColumn,integer,string,foreignkey#From database import Base fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportScoped_session,sessionmaker,relationshipengine=create_engine ('sqlite:///ye.db', echo=True) db_session=scoped_session (Sessionmaker (autocommit=false,autoflush=false,bind=engine)) Base=declarative_base () base.query=Db_session.query_property () session=Sessionmaker ()#Many-to-oneclassPerson (Base):__tablename__='PE'ID=column (integer,primary_key=True) name=column (String), unique=True) Email=column (String), unique=True) son=relationship ("Son", backref=" Person")        def __init__(self,name,email): Self.name=name Self.email=Email#self.son_id=son_id    def __repr__(self):return '<person%r>'%(Self.name)classSon (Base):__tablename__='son'ID=column (integer,primary_key=True) age=column (integer,unique=False)
#外键需要关联其他表的id person_id=column (Integer,foreignkey ('pe.id'))    def __init__(self,age,person_id): Self.age=Age self.person_id=person_iddef __repr__(self):return "<son%r>"%Self.ageBase.metadata.create_all (Bind=engine)

One to one (the following is no longer a detailed example):

As long as the Uselist=false attribute is added to the relationship function, the system will automatically determine

classParent (Base):__tablename__='Parent'ID= Column (Integer, primary_key=True) child_id= Column (Integer, ForeignKey ('child.id')) Child= Relationship (" Child", Backref=backref ("Parent", uselist=False))classChild (Base):__tablename__=' Child'ID= Column (Integer, Primary_key=true)

Many-to-many (connected through the third table):

association_table = Table ('Association', Base.metadata, Column ('left_id', Integer, ForeignKey ('left.id')), Column ('right_id', Integer, ForeignKey ('right.id')))classParent (Base):__tablename__=' Left'ID= Column (Integer, primary_key=True) Children= Relationship (" Child", secondary=association_table, Backref="Parents")classChild (Base):__tablename__=' Right'ID= Column (Integer, Primary_key=true)

Query operation:

K=db_session.query (person). Filter (person.name== ' Wu '). First ()

A=db_session.query (Son). Filter (SON.AGE==32). All ()

K is an object, a is an iterator,

Query for the associated table: A=db_session.query (person). Join (Son). Filter (SON.AGE==32). All ().

The object in query () is the object to be queried

such as: query (person)----> Return Person Object

Query (person.name)-----> return person.name data

Flask_sqlalchemy Notes 1