Python 上的 ORM Library: SQLAlchemy

ORM 發展的時間也算不短,像是 Java 上的 Hibernate 或是 C# 搭配 LINQ 語法的 Entity Framework。其明顯的優點在於更換資料庫時可以降低轉換的成本及風險(雖然很少人這樣幹),另一個好處是工程師能直接以物件的方式去操作資料庫,省去寫 SQL 的麻煩(複雜的業務邏輯例外)。而在 Python 上,ORM 最好的選擇就是 SQLAlchemy。

以下文章會列出 SQLAlchemy 中常用到的函式,以及當遇到需串接 where 條件式時該怎麼做。
另外,本文以 SQLite 檔案為測試資料,請到下面的連結下載檔案:

SQLite 測試檔案

在從資料庫取資料前,得先建立連線以及建立 SQLAlchemy 的 Session 物件:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def get_sql_session(path):
    # connection_string = 'sqlite:///sample.sqlite'
    connection_string = 'sqlite:///' + path
    engine = create_engine(connection_string, echo=True)

    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()

    return session

前面的程式碼中,有一個 connection_string 的變數,它是用來跟資料庫連線的連線字串,注意,不同的資料庫會有不同的寫法,使用前記得先到官網查詢連線字串的正確寫法。

建立 Session 物件還不夠,這裡還需要定義資料表跟類別的對應,請參考以下程式碼:

from sqlalchemy import Column, MetaData, Table, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Album(Base):
    __tablename__ = 'Album'

    AlbumId = Column(Integer, primary_key=True)
    Title = Column(String)
    ArtistId = Column(Integer)

    def __init__(self):
        pass

class Artist(Base):
    __tablename__ = 'Artist'

    ArtistId = Column(Integer, primary_key=True)
    Name = Column(String)

    def __init__(self):
        pass  

建立資料表的對應非常簡單,先新增一個 Base 物件,並讓對應資料表的類別去繼承它,而這些對應的類別需在 __tablename__ 這個變數中指定資料表名稱。

接下來示範如果做簡單的 SQL 查詢,假設我們要查詢 Album 底下 ArtistID 為 82 的資料有哪些,程式可以這樣寫:

from sqlalchemy import or_, and_, desc, func

session = get_sql_session('sample.sqlite')
rows = session.query(Album.AlbumId,
        Album.Title,
        Album.ArtistId) \
        .filter_by(ArtistId=82) \
        .order_by(Album.AlbumId.desc())

print('')
for item in rows:
    print(item.Title + ' ' + str(item.AlbumId))

接下來列出各函式的用途:

  • query:相當於 select
  • filter_by:相當於 where 語法,這裡是透過參數指定的方式來指定過濾的欄位
  • order_by:相當於 order by

如果 where 條件中有比較大小的,這時就要從 filter_by 改為使用 filter,程式碼如下:

from sqlalchemy import or_, and_, desc, func

session = get_sql_session('sample.sqlite')
rows = session.query(Artist.ArtistId,
        Artist.Name) \
        .filter(Artist.ArtistId<100) \
        .order_by(Artist.ArtistId.desc())

print('')
for item in rows:
    print(item.Name + ' ' + str(item.ArtistId))

如果條件中有 and 或者是 or 的需求,可用函式 and_ 或 or_,程式碼如下:

from sqlalchemy import or_, and_, desc, func

session = get_sql_session('sample.sqlite')
rows = session.query(Album.AlbumId,
        Album.Title,
        Album.ArtistId) \
        .filter(and_(Album.ArtistId==82,
        Album.AlbumId<76))

print('')
for item in rows:
    print(item.Title + ' ' + str(item.ArtistId))

但假如 ArtistId 及 AlbumId 這兩個欄位要依條件決定要不要加入到 where 條件,可以下列方式來寫:

from sqlalchemy import or_, and_, desc, func

session = get_sql_session('sample.sqlite')
rows = session.query(Album.AlbumId,
        Album.Title,
        Album.ArtistId) \
        .filter(Album.ArtistId==82)
rows = rows.filter(Album.AlbumId<76)

print('')
for item in rows:
    print(item.Title + ' ' + str(item.ArtistId))

而假如商業邏輯太過複雜,有的人可能傾向寫純 SQL,當然在 SQLAlchemy 也是可以做到的:

sql_command = 'SELECT AlbumId, Title, ArtistId FROM Album'
session = get_sql_session('sample.sqlite')
result = session.execute(sql_command)

for item in result:
    print(item['AlbumId'])

如上面程式碼所示,只要執行 Session 物件中的 execute 函式即可。

最後,示範一個可能不常用到的功能,就是先定義好物件,再透過 SQLAlchemy 直接對資料庫產生資料庫。題外話,在 Entity Framework 中這稱為 Code First:

from sqlalchemy import Column, MetaData, Table, Integer, String, DateTime

def get_sql_metadata(path):
    connection_string = 'sqlite:///' + path
    engine = create_engine(connection_string, echo=True)

    metadata = MetaData(engine)

    return metadata

metadata = get_sql_metadata('sample2.sqlite')
table = Table('Example',metadata,
            Column('id',Integer, primary_key=True),
            Column('name',String))
table2 = Table('Example2',metadata,
            Column('id',Integer, primary_key=True),
            Column('name',String))
metadata.create_all()

for _t in metadata.tables:
    print "Table: ", _t

至於這方法實不實用,就見人見智了。

廣告

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s