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:
- User groups, with dba,ddd groups
- 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