The SQLAlchemy of ORM Framework

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