Python中應用SQL及SQLAlchemy（一）

標籤：插入   類型   isp   .sql   man   record   cep   sys   from   

以SQLit3為例：

import sqlite3conn = sqlite3.connect(‘db.sqlite3‘)#擷取遊標對象cur = conn.cursor()#執行一系列SQL語句#建立一張表#cur.execute("create table demo(num int, str vachar(20));")#插入一些記錄cur.execute("insert into demo values(%d, ‘%s‘)" % (1, ‘aaa‘))cur.execute("insert into demo values(%d, ‘%s‘)" % (2, ‘bbb‘))#更新一條記錄cur.execute("update demo set str=‘%s‘ where num =%d" % (‘ddd‘,3))#查詢cur.execute("select * from demo;")rows = cur.fetchall()print("number of records:", len(rows))for i in rows:    print(i)    #提交事務conn.commit()#關閉遊標對象cur.close()#關閉資料庫連接conn.close()

運行結果：

 

SQLAlchemy

SQLAlchemy是一款開源軟體，提供了SQL工具包及對象關係映射（ORM）工具，它採用python語言，為高效和高效能的資料庫訪問設計，實現了完整的企業級持久模型，sqlalchemy非常關注資料庫的量級和效能。

使用SQLAlchemy至少需要三部分代碼，這們分別是定義表，定義資料庫連接，進行增、刪、改、查等操作。

建立表的例子：

from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, StringBase = declarative_base()   #定義一個執行個體，所有表必須繼承該執行個體class Account(Base):    __tablename__ = ‘account‘   #表名        #欄位名    id = Column(Integer, primary_key=True)    user_name = Column(String(50), nullable=False)    password = Column(String(200), nullable=False)    title = Column(String(50))    salary = Column(Integer)        def is_active(self):        #假設所有使用者都是活躍使用者        return True        def get_id(self):        #返回帳戶id,該方法返回屬性值提高了表的封裝性        return self.id            def is_authenticated(self):        #假設已通過驗證        return True            def is_anonymous(self):        #具有登入名稱和密碼的帳戶不是匿名使用者        return False

定義資料庫連接的程式碼範例：

from sqlalchemy import create_enginefrom sqlalchemy.orm import scoped_session, sessionmaker#定義串連資料庫用到的資料庫字串db_connect_string = ‘mysql://root:[email protected]:3306/sqlalchemy_db?charset=utf8‘#如果資料庫開啟了ssl串連，定義ssl字串ssl_args = {    ‘ssl‘:{        ‘cert‘: ‘/home/ssl/client-cert.pem‘,        ‘key‘: ‘/home/shouse/ssl/client-key.pem‘,        ‘ca‘: ‘/home/shouse/ssl/ca-cert.pem‘        }    }#如果資料庫開啟了ssl串連，需要傳入ssl    engine = create_engine(db_connect_string, connect_args=ssl_args)#定義會話類型SessionType = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))def GetSession():    #建立SessionType的執行個體，用於資料庫操作    return SessionType()from contextlib import contextmanager#定義上下文函數，使能夠自動進行交易處理，#定義上下檔案函數的方法就是加上contextmanager裝飾器#執行邏輯：在函數開始時建立資料庫會話，此時會自動建立一個資料庫事務；當發生異常時復原（rollback）事務，當#退出時關閉(close)串連@contextmanagerdef session_scope():    session = GetSession()        try:        yield session        session.commit()    except:        session.rollback()        raise    finally:        session.close()

進行資料庫操作的範例程式碼

import ormfrom sqlalchemy import or_def InsertAccount(user, password, title, salary):    with session_scope() as session:     #新增操作        account = orm.Account(user_name=user, password=password, title=title, salary=salary)        session.add(account)def CetAccount(id=None, user_name=None):       #查詢操作，查詢結果是一個對象集合，同樣可以用all()擷取所有資料    with session_scope() as session:        return session.query(orm.Account).filter(            or_(orm.Account.id==id, orm.Account.user_name==user_name)        ).first()        def DeleteAccount(user_name):       #刪除操作    with session_scope() as session:        account = GetAccount(user_name=user_name)        if account:            session.delete(account)def UpdateAccount(id, user_name, password, title, salary):     #更新操作    with session_scope() as session:        account = session.query(orm.Account).filter(orm.Account.id=id).first()        if not account: return        account.user_name = user_name        account.password = password        account.title = title        account.salary = salary        #調用新增操作InsertAccount(‘David Li‘, "123", "System Manager", 3000)InsertAccount(‘Rebeca Li‘, ‘‘, ‘Accountant‘, 3000)#查詢操作GetAccount(2)#刪除操作DeleteAccount(‘David Li‘)#更新操作UpdateAccount(1, "David Li", "none", "System Manager", 2000)

代碼解釋：

• 用import 引入資料庫表Account所在的包orm(orm.py)， 引入多條件查詢時的 或串連 or_
• 每個函數通過with語句啟用上下文函數session_scope(), 通過它擷取到session對象，並自動開啟事務
• 在InsertAccount中，通過建立一個表account執行個體，並通過session.add將其添加到資料庫中，由於上下文函數退出時會自動認可事務，把以無須顯示地調用session.commit()使新增生

 

主流資料庫的串連方式

資料庫	連接字串
Microsoft SQLServer	‘mssql+pymssql://username:p[email protected]:port/dbname’
MySQL	‘mysql://username:[email protected]:port/dbname’
oracle	‘orcle://username:[email protected]:port/dbname’
PostgreSQL	‘postgresql://username:[email protected]:port/dbname’
SQLite	‘sqlite://file_pathname’

 

查詢條件設定：

在實際編程過程中需要根據各種不同的條件查詢資料庫記錄， SQLAlchemy查詢條件被稱為過濾器。

1. 等值過濾器

session.query(Account).filter(Account.user_name==‘Jack‘)session.query(Account).filter(Account.salary==2000)

2. 不等於過濾器（!=, <, >, <=, >=）

session.query(Account).filter(Account.user_name != ‘Jack‘)session.query(Account).filter(Account.salary != 2000)session.query(Account).filter(Account.salary > 3000)

3. 模糊查詢（like）

模糊查詢只適用於查詢字串類型，不適用於數實值型別

#查詢所有名字中包含字母i的使用者session.query(Account).filter(Account.user_name.like(‘%i%‘))#查詢所有title中以Manager結尾的使用者session.query(Account).filter(Account.title.like(‘%Manager‘))#查詢的有名字中以Da開頭的使用者session.query(Account).filter(Account.user_name.like(‘Da%‘))

4. 包括過濾器(in_)

#查詢id不為1，3，5的記錄session.query(Account).filter(~Account.id.in_([1,3,5]))#查詢工資不為2000，3000，4000的記錄session.query(Account).filter(~Account.salary.in_([2000,3000,4000]))#查詢所有title不為Engineer和Accountant的記錄session.query(Account).filter(~Account.title.in_([‘Account‘,‘Engineer‘]))

5. 判斷是否為空白（is NULL,  is not NULL）

#查詢salary為空白值的記錄session.query(Account).filter(Account.salary.is_(None))session.query(Account).filter(Account.salary == None)#查詢salary不為空白值的記錄session.query(Account).filter(Account.salary.isnot(None))session.query(Account).filter(Account.salary != None)

6. 非邏輯 ~

#查詢id不為1，3，5的記錄session.query(Account).filter(~Account.id.in_([1,3,5]))

7. 與邏輯 （and_）

#直接多個條件查詢session.query(Account).filter(Account.title=‘Engineer‘, Account.salary==3000)#用關鍵字and_進行與邏輯查詢from sqlalchemy import and_session.query(Account).filter(and_(Account.title==‘Engineer‘, Account.salary==3000))#通過多個filter連結查詢session.query(Account).filter(Account.title==‘Engineer‘).filter(Account.salary==3000)

8. 或邏輯（or_）

from sqlalchemy import or_#查詢title是Engineer或者salary為3000的記錄session.query(Account).filter(or_(Account.title==‘Engineer‘, Account.salary==3000))

Python中應用SQL及SQLAlchemy（一）