SQLAlchemy Basic Use

Introduced

SQLAlchemy is an ORM framework based on Python implementations. The framework is built on the DB API, using relational object mappings for database operations, in short: converting Classes and objects to SQL, and then using the data API to execute SQL and get execution results.

Role

Help us quickly implement database operations using classes and objects.

Installation

PIP3 Install SQLAlchemy

Part:

• Engine, Frame engines

• Connection Pooling, database connection pool

• Dialect, select the type of DB API to connect to the database

• Schema/types, architecture and type

• SQL exprression language,sql Expression language

Example

-User Login Example
-User Registration Example
-Real-time database Fetch data Update (override construction method)

Using the CREATE DATABASE single table

#!/usr/bin/env python#-*-coding:utf-8-*-import datetimefrom sqlalchemy Import Create_enginefrom Sqlalchemy.ext.declarative Import declarative_basefrom sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, uniqueconstraint, indexbase = Declarative_base () class Users (base): __tablename__ = ' users ' id = Column (Int Eger, primary_key=true) name = Column (string (+), Index=true, nullable=false) # email = column (string), unique=tr UE) # ctime = Column (DateTime, default=datetime.datetime.now) # extra = column (Text, nullable=true) __table_args_ _ = (# UniqueConstraint (' id ', ' name ', ' name= ' Uix_id_name '), # Index (' Ix_id_name ', ' name ', ' email '), Def init_db (): "" "creates a database table from a class: return:" "engine = Create_engine (" Mysql+pymysql://root:[email pro Tected]:3306/s6?charset=utf8 ", #用户名: password @ Native IP: Database port 3306/database name? Character set max_overflow=0, # exceeds the connection pool size to create a maximum of connection pool_size=5, # Connection Pool size pool_timeout=30, # No threads in the pool wait mostTime, otherwise error pool_recycle=-1 # How long after a connection to a thread in the thread pool is recycled (reset)) Base.metadata.create_all (engine) def drop_db (): "" " To delete a database table from a class: return: "" "Engine = Create_engine (" Mysql+pymysql://root:[email protected]:3306/s6?ch Arset=utf8 ", max_overflow=0, # More connections than connection pool size created pool_size=5, # Connection Pool size pool_timeout=30, # No threads in the pool waiting up Time, otherwise error pool_recycle=-1 # How long after a connection to a thread in the thread pool is recycled (reset)) Base.metadata.drop_all (engine) if __name__ = = ' __main__ ': drop_db () init_db ()  

Manipulating database tables

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.orm import sessionmakerfrom sqlalchemy import Create_ Enginefrom Models import Users  engine = Create_engine ("Mysql+pymysql://root:[email protected]:3306/s6", Max_ Overflow=0, pool_size=5) Session = Sessionmaker (bind=engine)  # each time you perform a database operation, you need to create a sessionsession = Session ()  # ############# perform orm operation ############ #obj1 = Users (name= "Alex1") Session.add (obj1)  # COMMIT Transaction Session.commit () # Close Sessionsession.close ()

Basic additions and deletions change

#!/usr/bin/env python#-*-coding:utf-8-*-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 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=" Yaya " ) Session.add (OBJ1) Session.add_all ([Users (Name= "Yaya"), Users (name= "Xiaoqiang"), Hosts (name= "c1.com"),]) session.c Ommit () "" "# ################ 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 = = "Yaya"). 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 ()

Other common operations　　

# ############################## other commonly used ################################ 1. Specify column Select Id,name as CNAME from users; result = Session.query (Users.id,users.name.label (' cname ')). All () for item in Result:print (item[0],item.id,item.cn AME) # 2. The default condition and Session.query (Users). Filter (Users.id > 1, Users.name = = ' Eric '). All () # 3. Between Session.query (Users). Filter (Users.id.between (1, 3), Users.name = = ' Eric '). All () # 4. In Session.query (users). Filter (Users.id.in_ ([1,3,4]). All () session.query (users). Filter (~users.id.in_ ([1,3,4])). All () # 5. Subquery Session.query (Users). Filter (Users.id.in_ (Session.query (users.id). Filter (users.name== ' Eric ')). All () # 6. And and or from SQLAlchemy import And_, Or_ session.query (Users). Filter (Users.id > 3, Users.name = = ' Eric '). All () session . query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All () session.query (users). Filter (Or_ (Users.id < 2, Users.name = = ' Eric '). All () Session.query (Users). Filter (Or_ (Users.id < 2, And_ (Users.name = = ' Eric ', Users.id > 3), Users.extra! = "")). All () # 7. Filter_by session.query (Users). filter_by (name= ' Alex '). All () # 8. wildcard ret = session.query (users). Filter (Users.name.like (' e% ')). All () #e% of all, starting with E, e_ a ret = Session.query (Users) beginning with E. Fi Lter (~users.name.like (' e% ')). All () #~ means not in# 9. Slice (limit) result = Session.query (users) [1:2]# 10. Sort ret = session.query (users). Order_by (Users.name.desc ()). All () ret = Session.query (Users). Order_by (Users.name.desc (), USERS.ID.ASC ()). All () # 11. GROUP by #根据聚合索引进行二次查询要用havingfrom sqlalchemy.sql import func ret = Session.query (users.depart_id, fun C.count (Users.id),). Group_by (users.depart_id). All () as item in the Ret:print (item) from Sqlalchemy.sql import func ret = Session.query (users.depart_id, Func.count (users.id),). Group_by (users.depart_id). Having (Func.count (    Users.id) >= 2). All () for item in Ret:print (item) # 12.union and UNION All "" "Select Id,name from Usersunion      Select Id,name from users, "" "Q1 = session.query (users.name). Filter (Users.id > 2) q2 = Session.query (favor.captio  N). Filter (Favor.nid < 2) ret = q1.union (Q2). All () #union上下拼接去重 q1 = session.query (users.name). Filter (Users.id > 2) Q2 = Session.query (favor.caption). Filter (Favor.nid < 2) ret = Q1.union_all (Q2). All () #union_all the top and bottom splicing does not go to the weight, join left and right stitching

　　　　

SQLAlchemy Basic Use