First, the object-oriented application scenario:
1, the function has the common parameter, solves the parameter unceasingly to reuse;
2, templates (constraints of the same kind of things, attributes and behavior)
3. Function programming and object-oriented differences:
Object-oriented: Data and logic are grouped together;
Function programming: Data and logic cannot be combined and separated;
Two, ORM Framework (objects, relationships, mappings): SQLALchemy
Concept: The SQLAlchemy framework encapsulates the underlying complex SQL statement, provides a simple calling interface, lets the Python program call, and then the Python process
Sequence-written classes, converted to SQL statements sent to MySQL execution;
Homework:
Class---correspondence table
Column---corresponding property
Data row----Corresponding object
Each table is a class
Column names constrain table data, so the attribute
Every 1 rows of data is a fixed column instantiation of the object;
Third, ORM Framework type
1, DB Frist:
Create DATABASE manually-----ORM framework------automatically generate classes
Code frist:
2, cannot create the database, can only manually create the database and Class-----"ORM Framework------" Generation table (SQL alchemy belongs to code
Frist:)
The ORM Framework in Django: supports both DB Frist and code frist.
Third, sqlalchemy operation database
1.SQLALchemy Architecture:
Connection database: Not SQLAlchemy only do the conversion of class and SQL statements, the connection database is pymysql;
Can be specified in the (Engine=create_engine ("Mysql+pymysql)
To create a table:
###### #增加数据 #######
1, Session.add () increased by 1
2, Session.add_all () add more than one
# objs=[
# usertype (title= "Super User"),
# usertype (title= "Platinum User"),
# usertype (title= "Black Gold user")]
# Session.add_all (OBJS) #增加多条数据
# Obj1=usertype (title= "Normal user")
# Session.add (OBJS) added 1 data
###### #查询数据 #######
1. All () returns so the query results
2. Filter (expression) filters query results
User_type_list=session.query (usertype) # SQL statement
User_type_list=session.query (usertype). All () #返回所有查询结果
For row in User_type_list: #行就是对象
Print (Row.id,row.title)
Select XXX usertype where
User_type_list = Session.query (usertype.id,usertype.title). Filter (Usertype.id > 2)
For row in User_type_list:
Print (Row.id,row.title)
##### #查询到数据再删除 #########
L=session.query (usertype.id). Filter (usertype.id>2). Delete ()
###### #更新数据 ########
1. All modifications
2. Batch Modification
Session.query (Usertype.id,usertype.title). Filter (Usertype.id > 0). Update ({"title": "Black Gold"})
Session.query (Usertype.id,usertype.title). Filter (Usertype.id > 0). Update ({usertype.title:
Usertype.title + "X"}, Synchronize_session=false)
Session.query (Usertype.id,usertype.title). Filter (Usertype.id > 0). Update ({"num": Users.num +
1}, synchronize_session= "Evaluate")
-----Advanced Queries
# conditions
ret = Session.query (Users). filter_by (name= ' Alex '). all () viagra a Q: filter_by () pass the parameter filter () followed by an expression
ret = Session.query (Users). Filter (Users.id > 1, Users.name = = ' Eric '). All ()
ret = Session.query (Users). Filter (Users.id.between (1, 3), Users.name = = ' Eric '). All ()
ret = Session.query (Users). Filter (Users.id.in_ ([1,3,4]). All ()
ret = Session.query (Users). Filter (~users.id.in_ ([1,3,4]). All ()
ret = Session.query (Users). Filter (Users.id.in_ (Session.query (users.id). Filter_by (Name= ' Eric ')). All ()
From SQLAlchemy import And_, or_
ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All ()
ret = Session.query (Users). Filter (Or_ (Users.id < 2, Users.name = = ' Eric ')). All ()
ret = Session.query (Users). Filter (
Or_ (
Users.id < 2,
And_ (Users.name = = ' Eric ', Users.id > 3),
Users.extra! = ""
). All ()
# wildcard characters
ret = Session.query (Users). Filter (Users.name.like (' e% ')). All ()
ret = Session.query (Users). Filter (~users.name.like (' e% ')). All ()
# limit
ret = Session.query (Users) [1:2]
# sort
ret = Session.query (Users). Order_by (Users.name.desc ()). All ()
ret = Session.query (Users). Order_by (Users.name.desc (), USERS.ID.ASC ()). All ()
# Group
From Sqlalchemy.sql import func
ret = Session.query (Users). group_by (Users.extra). All ()
ret = Session.query (
Func.max (Users.id),
Func.sum (Users.id),
Func.min (Users.id)). Group_by (Users.name). All ()
ret = Session.query (
Func.max (Users.id),
Func.sum (Users.id),
Func.min (Users.id)). Group_by (Users.name). have (Func.min (users.id) >2). All ()
# even tables
ret = Session.query (Users, Favor). Filter (Users.id = = Favor.nid). All ()
ret = session.query (person). Join (Favor). All ()
ret = session.query (person). Join (Favor, isouter=true). All ()
# combination
Q1 = session.query (users.name). Filter (Users.id > 2)
Q2 = Session.query (favor.caption). Filter (Favor.nid < 2)
ret = q1.union (Q2). All ()
Q1 = session.query (users.name). Filter (Users.id > 2)
Q2 = Session.query (favor.caption). Filter (Favor.nid < 2)
ret = Q1.union_all (Q2). All ()
Relationship even table
From sqlalchemy.ext.declarative import declarative_base
From SQLAlchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index,char,varchar
From Sqlalchemy.orm import Sessionmaker, relationship
From SQLAlchemy import Create_engine
Base = Declarative_base ()
# Create a single table
"""
1 white gold
2 Black Gold
obj.xx ==> [Obj,obj ...]
"""
Class Usertype (Base):
__tablename__ = ' usertype '
id = Column (Integer, Primary_key=true, Autoincrement=true)
title = Column (VARCHAR (+), Nullable=true, Index=true)
"""
1 Fang Shaowei 1
2 sets 1
3 Small white 2
# positive
UT = relationship (backref= ' xx ')
Obj.ut ==> 1 white gold
"""
Class Users (Base):
__tablename__ = ' users '
Id= Column (Integer, Primary_key=true, Autoincrement=true)
Name = Column (VARCHAR (+), Nullable=true, Index=true)
email = Column (VARCHAR (+), unique=true)
user_type_id = Column (Integer,foreignkey ("Usertype.id"))
User_type = Relationship ("usertype", backref= ' Xxoo ')
ORM Framework's SQLAlchemy