This chapter mainly introduces python Of orm And the role of the framework ,ORM The type and application of frame , Let's have a look !

One 、 Object oriented application scenarios :

1、 Functions have parameters in common , Solve the problem of parameter reuse ;

2、 Templates ( Binding on the same kind of things , Attributes and behaviors )

3、 The difference between functional programming and object-oriented :

object-oriented : The combination of data and logic ;

Functional programming : Data and logic can't be combined , It's separate ;

Two 、ORM frame ( object 、 Relationship 、 mapping ):SQLALchemy

Concept :SQLALchemy The framework encapsulates the underlying complex SQL sentence , Provide a simple call interface , Give Way Python Program call , And then Python cheng

Class of preface , convert to SQL Sentence to MySQL perform ;

Homework :

class — Corresponding table

Column — Corresponding attribute

data row ---- Corresponding object

Each table is a class

Column names constrain table data , So it's attributes

Every time 1 Row data is , Fixed column instantiated object ;

3、 ... and 、ORM Frame type

1、DB frist:

First, create the database manually -----》ORM frame ------》 Automatically generate classes

code frist :

2、 Cannot create database , You can only manually create the database and class -----》ORM frame ------》 To generate table (SQL ALchemy Belong to code

frist :)

django in ORM frame : Support both DB frist, Also support code frist.

3、 ... and 、SQLALchemy Operating the database

1.SQLALchemy framework :

Connect to database : No SQLALchemy Only do Classes and SQL Sentence conversion , Connecting to the database is pymysql;

Can be in (engine=create_engine("mysql+pymysql) Appoint

Operation table :

1、 establish / Delete 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()

class UserType(Base):

__tablename__ = 'usertype'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(VARCHAR(32), nullable=True, index=True)

class Users(Base):

__tablename__="User" # Set the name of the table
id=Column(Integer,nullable=True,autoincrement=True,primary_key=True)
name=Column(String(32))
email=Column(String(49))
user_type_id = Column(Integer,ForeignKey("usertype.id"))      #   Set up foreign keys 

table_args ,SQLAlcheme There are two rules to set index

table_args = (

    UniqueConstraint('id', 'name', name='uix_id_name'),
    Index('ix_n_ex','name', 'email',),
)

# Connect to the specified database

engine=create_engine(“mysql+pymysql://eric:123123@192.168.182.128:3306/db666?charset=utf8”, max_overflow=5)

# Find all of the class ( form ), Start creating table ;

Base.metadata.create_all(engine)

# Delete table

Base.metadata.drop_all(engine)

Operation data line

1、 Add data

session.add() increase 1 strip

obj1=UserType(title=“ Ordinary users ”)

session.add(objs) # increase 1 Data

session.add_all() Add more

objs=[

UserType(title=“ The super user ”),

UserType(title=“ Platinum users ”),

UserType(title=“ Black gold users ”)]

session.add_all(objs) # Add multiple data

Update data :

First find the data and then update it

1. Batch update

update({“title”:“ Black gold ”})

session.query(UserType).filter(UserType.id>2).update({“title”:“ Black gold ”})

2. On the original basis ( Black gold ) Modified value

ynchronize_session=False

synchronize_session=“evaluate”

Parameter description :SQLALchemy According to the last pass in synchronize_session Parameters , Determine the type of update data , It's numerical calculation ? Or string splicing ?

Update character type data
session.query(UserType).filter(UserType.id>2).update({UserType.title:UserType.title+“VIP”},synchronize_session=False)

Update number type data

session.query(Users).filter(Users.id > 2).update({“num”: Users.num + 1},synchronize_session=“evaluate”)session.commit()


Delete data

First find the data and then delete it

res=session.query(Users).filter(Users.id==6).delete()

Query data :

1、.all() Get all row objects

print(session.query(Users))SQLALchemy That generated it for us SQL Query statement ;

res=session.query(Users).all()# Get all the rows found by the query statement ---->( Object combination )forrowin res:

print(row.id,row.name,row.email)# Get line object   Properties of ( How many columns in a row )

1、.filter() The objects found by conditional filtering

res=session.query(Users).filter(Users.id>3)#filter Filter query results by criteria forrowin res:

print(row.id,row.name,row.email)# Get line object   Properties of ( How many columns in a row )

----- Advanced query

# Conditions

ret = session.query(Users).filter_by(name=‘alex’).all() Viagra asked :filter_by() The parameter filter() It's 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

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()

grouping

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).having(func.min(Users.id) >2).all()

Even the table

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()

Combine

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 the 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 Platinum

2 Black gold

obj.xx ==> [obj,obj…]

“”"

class UserType(Base):

tablename = ‘usertype’

id = Column(Integer, primary_key=True, autoincrement=True)

title = Column(VARCHAR(32), nullable=True, index=True)

“”"

1 Fang Shaowei 1

2 complete set 1

3 The small white 2

positive

ut = relationship(backref=‘xx’)

obj.ut ==> 1 Platinum

“”"

class Users(Base):

tablename = ‘users’

id

= Column(Integer, primary_key=True, autoincrement=True)

name = Column(VARCHAR(32), nullable=True, index=True)

email = Column(VARCHAR(16), unique=True)

user_type_id = Column(Integer,ForeignKey(“usertype.id”))

user_type = relationship(“UserType”,backref=‘xxoo’)