SQLAlchemy Basic Operation Three

multithreaded Example

Import timeimport threadingfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipfrom SQLAlchemy Import create_enginefrom db Import usersengine = Create_engine ("Mysql+pymysql://root:[email protected]:3306 /s6 ", Max_overflow=0, pool_size=5) session = Sessionmaker (bind=engine) def task (ARG):    session = Session ()    obj1 = Users (name= "alex1")    Session.add (obj1)    session.commit () for I in range:    t = Threading. Thread (Target=task, args= (i,))    T.start ()

Basic additions and deletions to check the import time

Import threadingfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import Create_ Enginefrom sqlalchemy.sql Import textfrom db import Users, hostsengine = Create_engine ("Mysql+pymysql://root:[email Protected]:3306/s6 ", Max_overflow=0, pool_size=5) session = Sessionmaker (bind=engine) session = Session () # ############ # # # # Add ################ "" "Obj1 = Users (name=" Wupeiqi ") Session.add (OBJ1) Session.add_all ([Users (name=" Wupeiqi "), use RS (name= "Alex"), Hosts (name= "c1.com"),]) Session.commit () "" "
# ################ Delete ################
"""
Session.query (Users). Filter (Users.id > 2). Delete ()
Session.commit ()
"""
# ################ Modify ################
"""
Session.query (Users). Filter (Users.id > 0). Update ({"name": "099"})
Session.query (Users). Filter (Users.id > 0). Update ({Users.name:Users.name + "099"},
Synchronize_session=false) Session.query (Users). Filter (Users.id > 0). Update ({"Age": Users.age + 1},
synchronize_session= "Evaluate") Session.commit ()
"""
# ################ Query ################
"" "R1 = Session.query (Users). All () r2 = session.query (Users.name.label (' xx '),
Users.age). All ()
R3 = Session.query (Users). Filter (Users.name = = "Alex"). All (),
R4 = Session.query (Users). filter_by (name= ' Alex '). All ()
R5 = Session.query (Users). filter_by (name= ' Alex '). First ()
R6 = Session.query (Users). Filter (Text ("Id<:value and Name=:name"). Params (value=224, name= ' Fred '). Order_by ( users.id). All ()
R7 = Session.query (users). From_statement (Text ("SELECT * from Users where Name=:name")). Params (name= ' Ed '). All ()
"""
Session.close ()

Common Operations

# conditional ret = session.query (users). filter_by (name= ' Alex '). All () 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 ')). SQLAlchemy import and_, Or_ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All () ret = Sessi        On.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 character ret = Sessio N.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 Funcret = Session.query (Users). group_by (Users.extra). All () ret = Session.query (Func.max (U sers.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). All () ret = Session.query (Func.max (Users.i D), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). have (Func.min (users.id) >2). All () # even table ret = ses Sion.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 () # Combo 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 ()

　　

SQLAlchemy Basic Operation Three