SQLAlchemy的基本能使用

標籤：資料庫連接   語句   foreign   lang   .com   sql   date   commit   set   

一、介紹

　　SQLAlchemy是一種ORM（Object-Relational Mapping）架構，用來將關係型資料庫映射到對象上。該架構建立在DB API之上，將類和對象轉化成SQL，然後使用API執行SQL並擷取執行結果。

二、組成

• Schema/Types，架構和類型
• SQL Exprression Language，SQL運算式語言
• Engine，架構的引擎
• Connection Pooling ，資料庫連接池
• Dialect，選擇串連資料庫的DB API種類

三、基本使用　　1、流程：　

　　　　1）使用者通過ORM對象提交命

　　　　2）命令交給SQLAlchemy Core（Schema/Types SQL Expression Language）轉換成SQL

　　　　3）使用 Engine/ConnectionPooling/Dialect 進行資料庫操作

　　　　　　3.1）匹配使用者事先配置好的egine

　　　　　　3.2）egine從串連池中取出一個連結 

　　　　　　3.3）基於該連結通過Dialect調用DB API，將SQL轉交給它去執行

　　　　SQLAlchemy本身無法操作資料庫，其必須以來pymsql等第三方外掛程式，Dialect用於和資料API進行交流，根據設定檔的不同調用不同的資料庫API，從而實現對資料庫的操作，如：

MySQL-Python    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>    pymysql    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]    MySQL-Connector    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>    cx_Oracle    oracle+cx_oracle://user:[email protected]:port/dbname[?key=value&key=value...]

　　2、啟用

　　　　如果我們不依賴於SQLAlchemy的轉換而自己寫好sql語句，就意味著我們可以直接從第3個階段開始執行了，事實上正是如此，我們完全可以只用SQLAlchemy執行純sql語句，如下：

from sqlalchemy import create_engine# 1 準備# 需要事先安裝好pymysql# 配置好要使用的資料庫，這裡用MySQL為例# 需要事先建立好資料庫:create database db1 charset utf8;# 2 建立引擎egine=create_engine(‘mysql+pymysql://[email protected]/db1?charset=utf8‘)# 3 執行sql# egine.execute(‘create table if not EXISTS user(id int PRIMARY KEY auto_increment,name char(32));‘)# cur=egine.execute(‘insert into user values(%(id)s,%(name)s);‘,name=‘ming‘,id=3)# 4 查詢cur=egine.execute(‘select * from user‘)cur.fetchone() #擷取一行cur.fetchmany(2) #擷取多行cur.fetchall() #擷取所有行

　　3、ORM

　　　　1）建立表

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, DateTime, Enum, ForeignKey, UniqueConstraint, ForeignKeyConstraint,     Indexegine = create_engine(‘mysql+pymysql://[email protected]:3306/db1?charset=utf8‘, max_overflow=5)# 建立一個Base類，後面建立的每個表都需要繼承這個類Base = declarative_base()# 建立單表:業務線class Business(Base):    __tablename__ = ‘business‘    id = Column(Integer, primary_key=True, autoincrement=True)    bname = Column(String(32), nullable=False, index=True)# 多對一:多個服務可以屬於一個業務線,多個業務線不能包含同一個服務class Service(Base):    __tablename__ = ‘service‘    id = Column(Integer, primary_key=True, autoincrement=True)    sname = Column(String(32), nullable=False, index=True)    ip = Column(String(15), nullable=False)    port = Column(Integer, nullable=False)    business_id = Column(Integer, ForeignKey(‘business.id‘))    __table_args__ = (        UniqueConstraint(ip, port, name=‘uix_ip_port‘),        Index(‘ix_id_sname‘, id, sname)    )# 一對一:一種角色只能管理一條業務線,一條業務線只能被一種角色管理class Role(Base):    __tablename__ = ‘role‘    id = Column(Integer, primary_key=True, autoincrement=True)    rname = Column(String(32), nullable=False, index=True)    priv = Column(String(64), nullable=False)    business_id = Column(Integer, ForeignKey(‘business.id‘), unique=True)# 多對多:多個使用者可以是同一個role,多個role可以包含同一個使用者class Users(Base):    __tablename__ = ‘users‘    id = Column(Integer, primary_key=True, autoincrement=True)    uname = Column(String(32), nullable=False, index=True)class Users2Role(Base):    __tablename__ = ‘users2role‘    id = Column(Integer, primary_key=True, autoincrement=True)    uid = Column(Integer, ForeignKey(‘users.id‘))    rid = Column(Integer, ForeignKey(‘role.id‘))    __table_args__ = (        UniqueConstraint(uid, rid, name=‘uix_uid_rid‘),    )# 建立所有表def init_db():    Base.metadata.create_all(egine)# 刪除資料庫所有表def drop_db():    Base.metadata.drop_all(egine)if __name__ == ‘__main__‘:    init_db()

　　註：設定外鍵的另一種方式 ForeignKeyConstraint([‘other_id‘], [‘othertable.other_id‘])

　　2）動作表

　　　　表結構

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import sessionmakeregine=create_engine(‘mysql+pymysql://[email protected]:3306/db1?charset=utf8‘,max_overflow=5)Base=declarative_base()#多對一:假設多個員工可以屬於一個部門,而多個部門不能有同一個員工(只有建立公司才把員工當駱駝用,一個員工身兼數職)class Dep(Base):    __tablename__=‘dep‘    id=Column(Integer,primary_key=True,autoincrement=True)    dname=Column(String(64),nullable=False,index=True)class Emp(Base):    __tablename__=‘emp‘    id=Column(Integer,primary_key=True,autoincrement=True)    ename=Column(String(32),nullable=False,index=True)    dep_id=Column(Integer,ForeignKey(‘dep.id‘))def init_db():    Base.metadata.create_all(egine)def drop_db():    Base.metadata.drop_all(egine)drop_db()init_db()Session=sessionmaker(bind=egine)session=Session()

　　　　增

row_obj=Dep(dname=‘銷售‘) #按關鍵字傳參,無需指定id,因其是自增長的session.add(row_obj)session.add_all([    Dep(dname=‘技術‘),    Dep(dname=‘運營‘),    Dep(dname=‘人事‘),])session.commit()

　　　　刪

session.query(Dep).filter(Dep.id > 3).delete()session.commit()

　　　　改

session.query(Dep).filter(Dep.id > 0).update({‘dname‘:‘哇哈哈‘})session.query(Dep).filter(Dep.id > 0).update({‘dname‘:Dep.dname+‘_SB‘},synchronize_session=False)session.query(Dep).filter(Dep.id > 0).update({‘id‘:Dep.id*100},synchronize_session=‘evaluate‘)session.commit()

　　　　查

#查所有,取所有欄位res=session.query(Dep).all() #for row in res:print(row.id,row.dname)#查所有,取指定欄位res=session.query(Dep.dname).order_by(Dep.id).all() #for row in res:print(row.dname)res=session.query(Dep.dname).first()print(res) # (‘哇哈哈_SB‘,)#過濾查res=session.query(Dep).filter(Dep.id > 1,Dep.id <1000) #逗號分隔,預設為andprint([(row.id,row.dname) for row in res])

 

SQLAlchemy的基本能使用