Python-sqlalchemy operation of the table

Two ways to create an ORM

Database precedence: Refers to the creation of a database, including the establishment of tables and fields, and then based on the database generated ORM code, it is to create a database, and then create the relevant program code

Code First: Write the code First, and then build the database structure according to the code.

Code first creates the essence of the database: Gets the class-to-Table object, and then generates the SQL statement based on the Table object--Build the database table structure

Two other points of knowledge:

• Change the way data is exported: You can define a special member in a table's class: __repr__,return a custom data connection by string concatenation.
• In addition to the standard foreign key (ForeignKey) in MySQL, you can create a virtual relationship between table relationships in the database, for example group = relationship("Group",backref=‘uuu‘) , typically this virtual relationship is used with ForeignKey.

SQLAlchemy table Operations 1. One-to-many relationships

Demand:

1. User groups, with dba,ddd groups
2. Users, users can belong to only one user group

The two tables are as follows:

Group table:

User table:

Create the two tables above:

#!/usr/bin/env python#-*-coding:utf-8-*-#auth:pangguoping fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn, Integer, String, ForeignKey, UniqueConstraint, Index fromSqlalchemy.ormImportSessionmaker, Relationship fromSQLAlchemyImportCreate_engineengine= Create_engine ("Mysql+pymysql://root:[email Protected]:3306/s13", max_overflow=5) Base=declarative_base ()#Create a pair of multiple tablesclassGroup (Base):__tablename__='Group'nid= Column (integer,primary_key=true,autoincrement=True) Caption= Column (String (32))classUser (Base):__tablename__='User'nid= Column (integer,primary_key=true,autoincrement=True) Username= Column (String (32)) group_id= Column (Integer,foreignkey ('Group.nid')) Group= Relationship ("Group", backref='UUU')    #What this method outputs, and what the object gets    def __repr__(self): temp='%s-%s:%s'%(self.nid,self.username,self.group_id)returnTempdefinit_db (): Base.metadata.create_all (Engine) init_db ()

View Code

Add data to the two tables above

Session = Sessionmaker (bind=engine) Session=Session ()#adding data to the Group tableSession.add (Group (caption='DBA')) Session.add (Group (Caption='SA') ) Session.commit ()#adding data to the user tableSession.add_all ([User (username='User1', group_id=1), User (username='User2', group_id=1), User (username='User3', group_id=2), User (username='User4', group_id=2)]) session.commit ()

View Code

To query all users in the user table:

# #以left. Join view ret = Session.query (User). Join (group,isouter=True). All ()print= Session.query (User). Join (group,isouter=True)print(SQL)

View Code

Out

[1-user1:1, 2-user2:1, 3-user3:2, 4-user4:2]
Select "User". Nid as User_nid, "user". Username as User_username, "user". group_id as user_group_id
From ' user ' left OUTER JOIN ' group ' on ' group '. Nid = "user". group_id

Query the corresponding group for each user:

General Query Method:

# #映射方式sql = session.query (user.username,group.caption). Join (group,isouter=True)print  = Session.query (user.username,group.caption). Join (group,isouter=True). All ()print(ret )

View Code

Out

Select "User". Username as User_username, "group". Caption as Group_caption
From ' user ' left OUTER JOIN ' group ' on ' group '. Nid = "user". group_id
[(' User1 ', ' DBA '), (' User2 ', ' DBA '), (' User3 ', ' sa '), (' User4 ', ' sa ')]

Python-sqlalchemy operation of the table