建議從這裡下載這篇文章對應的.ipynb檔案和相關資源。這樣你就能在Jupyter中邊閱讀，邊測試文中的程式碼。
說明
SQLAlchemy包含SQLAlchemy Core和SQLAlchemy ORM兩部分, 這個系列只包含SQLAlchemy Core的內容。
由於內容較多，教程被分成了上，下兩部分。 Select,Update,Delete本身內容較為豐富，放在sqlalchemy入門(下)行演示講解。
準備¶
安裝sqlalchemy
|
1 |
pip install SQLAlchemy |
安裝postgresql資料庫
如果想執行文中的程式碼,請安裝postgresql資料庫並且建立相應的測試使用者和測試資料庫。
匯入helper.py
為了方便程式碼實現的進行，我編寫了helper.py，裡面提供的函式功能如下
- reset_tables:在名為’test’的schema下重置users,addresses兩張表的資料
- clear_tables:在名為’test’的schema下刪除users和addresses兩張表並返回兩張表對應的object
- clear_schema:刪除名為’test’的schema
- get_table:獲得名為’test’的schema中指定表的資料(DataFrame形式)
- get_select:獲得名為’test’的schema中指定查詢語句得到資料(DataFrame形式)
- print_sql:print sqlalchemy object編譯後對應的sql語句
讀者暫時先不必關心這些函式是怎麼實現的，等完成這份教程後自然有能力自己去實現同樣的功能。
|
1 |
from helper import reset_tables,clear_tables,clear_schema,get_select,get_table,print_sql |
匯入其它程式碼實驗要用到的庫
|
1 |
from IPython.display import display |
建立engine
SQLAlchemy通過engine和目標資料庫建立連線，它是後面所有的資料庫操作都需要使用的object。 我本機的使用的使用者名稱,資料庫名,密碼都是’test’,埠為’5432’。如果不一致請相應的對下面的程式碼做出修改。
|
1 2 3 4 5 6 7 8 |
from sqlalchemy import create_engine user = 'test' password = 'test' port = '5432' dbname = 'test' engine = create_engine('postgresql://{user}:{password}@localhost:{port}/{dbname}'.format(**locals())) |
測試資料
教程中用到的測試資料如下
|
1 |
from helper import users_data, addresses_data |
users表和user是一一對應關係,它包含的測試資料是id為1,2的使用者的name和fullname
|
1 |
display(users_data) |
addresses表和user是一對多的關係,它包含的測試資料是id為1,2的使用者的email_addresses
|
1 |
display(addresses_data) |
| user_id | email_address | |
|---|---|---|
| 0 | 1 | jack@yahoo.com |
| 1 | 1 | jack@msn.com |
| 2 | 2 | www@www.org |
| 3 | 2 | wendy@aol.com |
SQLAlchemy Core初印象
SQLAlchemy Core提供了一套SQL Expression language,它提供了一套用Python construct(Python object)去表達SQL邏輯的體系。下面通過一些程式碼演示一下SQL Expression language的基本特徵。這裡讀者只需要有大致的感覺即可，如果有一些細節不理解不用在意，後面都會有詳細的解釋。
傳統的SQL語句是用文字方式編寫的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
sql = ''' select users.id, users.fullname from ( users join addresses on users.id = addresses.user_id ) group by users.id having count(addresses.email_address)>1 order by users.fullname ''' |
在SQLAlchemy Core中是這樣表達的
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
from sqlalchemy import select, func users, addresses = reset_tables(engine) s = ( select( [ users.c.id, users.c.fullname ] ).select_from( users.join( addresses, users.c.id==addresses.c.user_id ) ).group_by(users.c.id) .having(func.count(addresses.c.email_address)>1) ) print_sql(engine, s) |
|
1 2 3 4 5 6 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.fullname FROM test.users JOIN test.addresses ON test.users.id = test.addresses.user_id GROUP BY test.users.id HAVING count(test.addresses.email_address) > 1 >>> |
上面的SQL邏輯可以看作是很多更基本的元件構成的，包括表,列,條件,join語句等等。整個Select邏輯和這些組成元件，對應的都是sqlalchemy object
|
1 2 3 4 5 6 7 8 9 |
l = [ users, users.c.id, users.c.id==addresses.c.user_id, s ] for obj in l: print(type(obj)) |
由於因此使用SQLAlchemy Core表達SQL邏輯的時候，是一個從代表基本SQL邏輯模組的object逐步組裝成複雜object的過程。這樣做有幾個好處。
容易拆分
當SQL邏輯複雜的時候，可以分階段的構造。先構造簡單的SQL邏輯模組，再逐步組裝成複雜的SQL邏輯。相比一次性構造完整的複雜SQL邏輯相比，頭腦的負擔更低，也不容易出錯。
下面的例子裡，我們可以把前面例子中的要選擇的columns,join語句,having條件先構造好,然後再組裝成完整的SQL邏輯。每一個SQL邏輯模組構造好後我們都可以觀察一下對應的SQL語句是什麼。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
from sqlalchemy import select, func columns_selection = select( [users.c.id, users.c.fullname] ) print_sql(engine,columns_selection) join_clause = users.join( addresses, users.c.id==addresses.c.user_id ) print_sql(engine,join_clause) condition = func.count(addresses.c.email_address)>1 print_sql(engine,condition) s = ( columns_selection .select_from(join_clause) .group_by(users.c.id) .having(condition) ) print_sql(engine,s) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.fullname FROM test.users >>> ***Compiled SQL*** <<< test.users JOIN test.addresses ON test.users.id = test.addresses.user_id >>> ***Compiled SQL*** <<< count(test.addresses.email_address) > 1 >>> ***Compiled SQL*** <<< SELECT test.users.id, test.users.fullname FROM test.users JOIN test.addresses ON test.users.id = test.addresses.user_id GROUP BY test.users.id HAVING count(test.addresses.email_address) > 1 >>> |
容易複用
由於使用SQLAlchemy Core去表達SQL，本質上是使用python語言寫程式碼。 因此我們可以利用python提供的一切工具和手段將重複出現的SQL邏輯抽提成可複用的python程式碼。
例如我們在多個地方要根據fullname的長度，和首字母去篩選user。那麼可以用一個函式生成這個條件，以後直接呼叫這個函式即可。
|
1 2 3 4 5 6 7 8 9 10 |
from sqlalchemy import Table, and_, func, bindparam def email_condition(users, init, length): return and_( users.c.fullname.like('{}%'.format(init)), func.len(users.c.fullname)==length ) c = email_condition(users,'J',5) print_sql(engine,c) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.fullname LIKE 'J%' AND len(test.users.fullname) = 5 >>> |
處理資料庫差異
在用SQLAlchemy Core表達SQL邏輯的時候，只是表達了使用者的意圖，並未生成最終的SQL語句。
同樣的SQL邏輯，在不同的database中語法可能會有變化，因此對應的SQL語句是不同的。 而SQLAlchemy Core會根據database的種類，編譯出和這個database匹配的SQL語句。這樣使用者用SQLAlchemy Core組織一次SQL邏輯，就可以在多個資料庫中複用。
當然每個database都有一些自己獨有的功能，對於這部分差異SQLAlchemy是不能自動處理的。
SQLAlchemy Core使用詳解
檢視編譯後的語句
使用SQLAlchemy Core一個基本的需求是檢視sqlalchemy object編譯後的SQL語句是什麼樣的。這個可以用object提供的compile方法實現。
|
1 2 3 4 5 |
condition = users.c.name == 'jack' compiled = condition.compile() print(compiled) test.users.name = :name_1 |
預設情況下編譯後的SQL語句是帶引數的形式,並沒有把’jack’代入name_1。可以通過呼叫params屬性檢視對應的數值是多少。
|
1 2 3 |
print(compiled.params) {'name_1': 'jack'} |
如果希望編譯後的SQL語句是非引數化的形式，可以新增compile_kwargs={"literal_binds": True}選項。
|
1 2 3 4 |
compiled = condition.compile(engine, compile_kwargs={"literal_binds": True}) print(compiled) test.users.name = 'jack' |
schema操作
建立schema
|
1 2 3 4 5 6 7 8 9 |
from sqlalchemy.schema import CreateSchema clear_schema(engine) schema_name = 'test' obj = CreateSchema(schema_name) engine.execute(obj) print_sql(engine,obj,False) |
|
1 2 3 4 |
***Compiled SQL*** <<< CREATE SCHEMA test >>> |
如果建立已經存在的schema,會導致異常。例如，剛才已經建立了名為’test’的schema，如果再建立一遍的話,會提示schema “test” already exists
|
1 2 3 4 |
try: engine.execute(obj) except Exception as e: print(type(e),e) |
|
1 2 |
(psycopg2.ProgrammingError) schema "test" already exists [SQL: 'CREATE SCHEMA test'] |
有些sqlalchemy object，例如這個例子中的CreateSchema(schema_name)，結果為None。
|
1 2 3 |
type(obj.compile().params) NoneType |
對於這類object,compile的時候新增compile_kwargs={"literal_binds": True}會導致異常。
|
1 2 3 4 |
try: obj.compile(compile_kwargs={"literal_binds": True}) except Exception as e: print(type(e),e) |
|
1 |
visit_create_schema() got an unexpected keyword argument 'literal_binds' |
預設情況print_sql函式會新增"literal_binds": True， 可以將第三個引數設定成False關閉這個設定。
刪除schema
和新建schema類似。不過如果這個schema下有一些依賴於這個schema存在的資源，比如tables,那麼只有先刪除了這些資源後才能刪除這個schema，否則會異常。
這裡有一個有用的引數cascade，設定成True的話會自動刪除所有依賴於這個schema的資源。
|
1 2 3 4 5 6 7 |
from sqlalchemy.schema import DropSchema schema_name = 'test' obj = DropSchema(schema_name, cascade = True) print_sql(engine,obj,False) engine.execute(obj) |
|
1 2 3 4 |
***Compiled SQL*** <<< DROP SCHEMA test CASCADE >>> |
同樣, 如果刪除已經不存在的schema,會報ProgrammingError
同樣，如果刪除並不存在的schema，會報異常，這個不演示了。
table操作
定義ｔａble
定義SQLAlchemy可理解的table資料結構，主要引數是table名,schema名以及相關的column的名稱,型別，是否是primary_key等資訊。
定義table是進行新建表，構建select語句等操作的基礎。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Sequence from sqlalchemy.schema import CreateTable schema='test' table_name = 'users' metadata = MetaData() table = Table( table_name, metadata, Column('id',Integer,primary_key=True), Column('name',String), Column('fullname',String), schema = schema ) |
如果是資料庫中已經存在的表,可以直接使用autoload功能從資料庫中讀取表的列資訊，可以免去很多麻煩。下面reset_db確保test.users表存在後用autoload自動讀取users表的資訊。
|
1 2 3 4 |
reset_tables(engine); metadats = MetaData() users = Table('users', metadata, schema = 'test', autoload=True, autoload_with=engine) |
可以看到users中自動包含了column的定義資訊。
|
1 2 3 4 5 |
users.c.values() [Column('id', Integer(), table=, primary_key=True, nullable=False), Column('name', String(), table=), Column('fullname', String(), table=)] |
如果table中定義了foreign key資訊,SQLAlchemy Core構建join語句的時候能夠自動將foreign key作為join的條件。 但是autoload得到的table會失去這個便利，暫時沒找到解決方法。(見join章節的演示)
新建table
再定義了table後，可以在資料庫中新建這張表。
先清空資料庫
|
1 |
clear_tables(engine) |
新建表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
obj = CreateTable(table) print_sql(engine,obj,False) engine.execute(obj); ***Compiled SQL*** <<< CREATE TABLE test.users ( id SERIAL NOT NULL, name VARCHAR, fullname VARCHAR, PRIMARY KEY (id) ) >>> |
SQLAlchemy會根據資料庫的型別，將String等列型別資訊轉化成資料庫中對應的資訊，例如Oracle中的VARCHAR2。
注意，不同的資料庫對於configs的要求會不同。例如，postgresql只需要寫String,不需要指定長度；而Oracle在定義時，必須指定長度，得改成類似下面的設定才會生效。
|
1 2 3 |
Column('id',Integer,primary_key=True), Column('name',String(20)), Column('fullname',String(20)), |
同樣, 如果嘗試新建已經存在的表，會出錯,這個不演示了。
drop table
drop table的處理方法和create table類似。不過在定義
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
from sqlalchemy.schema import DropTable reset_tables(engine) metadata = MetaData() table = Table( 'users', metadata, schema = 'test' ) print_sql(engine,obj,False) obj = DropTable(table) ***Compiled SQL*** <<< CREATE TABLE test.users ( id SERIAL NOT NULL, name VARCHAR, fullname VARCHAR, PRIMARY KEY (id) ) >>> |
不過執行的話會報錯
|
1 2 3 4 5 6 |
users, addresses = reset_tables(engine) try: engine.execute(obj) except Exception as e: print(type(e),e) |
|
1 2 3 4 |
(psycopg2.InternalError) cannot drop table users because other objects depend on it DETAIL: constraint addresses_user_id_fkey on table addresses depends on table users HINT: Use DROP ... CASCADE to drop the dependent objects too. [SQL: '\nDROP TABLE test.users'] |
這是由於在定義addresses表的時候,定義了addresses的user_id是users表的foreign key,因此foreign key依賴於users表，只有Drop時指定CASCADE選項才能順利的刪除這張表。(它會刪除所有依賴於users表的foreign_key),遺憾的是，我並沒有在sqlalchemy中找到相關的選項啟動CASCADE。
不過SQLAlchemy的一個好處是，它完全可以接受原生的SQL語句去對資料庫進行操作。我們在語句中加上CASCADE和IF EXISTS來進行drop table的操作。
|
1 2 3 4 5 |
table_name = 'users' schema = 'test' sql = "DROP TABLE IF EXISTS {schema}.{table_name} CASCADE".format(table_name = table_name, schema = schema) engine.execute(sql) |
SQLAlchemy的優勢更多的是體現在構造和複用複雜的SQL邏輯上。在刪除table的這個例子裡。SQLAlchemy Core實際上並不如原生的SQL語句好用。我們完全可以針對自己的場景，選擇適合的工作去完成任務。
插入資料
插入單行資料
|
1 2 3 4 5 6 7 8 9 10 11 12 |
users, addresses = reset_tables(engine) ins = users.insert().values(name='Junjie', fullname='Junjie Cai') print_sql(engine, ins) result = engine.execute(ins) ***Compiled SQL*** <<< INSERT INTO test.users (name, fullname) VALUES ('Junjie', 'Junjie Cai') RETURNING test.users.id >>> |
可以用result.insered_primary_key很方便的找到插入記錄的id
|
1 |
result.inserted_primary_key |
驗證一下插入資料後的結果
|
1 |
display(get_table(engine, users)) |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | jack | Jack Jones |
| 1 | 2 | wendy | Wendy Williams |
| 2 | 3 | Junjie | Junjie Cai |
注意也可以在engine.execute中傳入資料
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
users, addresses = reset_tables(engine) ins = users.insert() print_sql(engine, ins) engine.execute(ins,name='jack', fullname='Jack Jones') display(get_table(engine, users)) ***Compiled SQL*** <<< INSERT INTO test.users (id, name, fullname) VALUES (%(id)s, %(name)s, %(fullname)s) >>> |
插入多行資料
如果是插入部分列的話，可以用list of dict的結構。
|
1 2 3 4 5 6 7 8 9 10 |
data = [ {'name':'Junjie','fullname':'CaiJunjie'}, {'name':'Xu','fullname':'ZhangXu'} ] ins = users.insert().values(data) engine.execute(ins) display(get_table(engine,users)) |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | jack | Jack Jones |
| 1 | 2 | wendy | Wendy Williams |
| 2 | 3 | jack | Jack Jones |
| 3 | 4 | Junjie | CaiJunjie |
| 4 | 5 | Xu | ZhangXu |
注意如果要插入dict list,sqlalchemy會以list中第一條記錄的key為準
|
1 2 3 4 5 6 7 8 9 |
data = [ {'name':'Name1'}, {'name':'Name2','fullname':'FULLNAME2'} ] ins = users.insert().values(data) engine.execute(ins) display(get_table(engine,users)) |
如果第一行包含了所有的key,後面的記錄key缺失的話，會直接報錯。
|
1 2 3 4 5 6 7 8 9 10 |
try: data = [ {'name':'Name3','fullname':'FULLNAME3'}, {'name':'Name4'}, ] ins = users.insert().values(data) engine.execute(ins) except Exception as e: print(type(e),e) |
|
1 |
INSERT value for column users.fullname is explicitly rendered as a boundparameter in the VALUES clause; a Python-side value or SQL expression is required |
如果插入資料時會使用所有的列,那麼可以簡化成直接用tuple list插入資料。但是這是就不能利用自動編號id，而是要傳入id。
|
1 2 3 4 5 6 7 8 9 |
data = [ (8,'Cai','Junjie'), (9,'Zhang','Xu') ] ins = users.insert().values(data) engine.execute(ins) display(get_table(engine,users)) |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | jack | Jack Jones |
| 1 | 2 | wendy | Wendy Williams |
| 2 | 3 | jack | Jack Jones |
| 3 | 4 | Junjie | CaiJunjie |
| 4 | 5 | Xu | ZhangXu |
| 5 | 6 | Name1 | None |
| 6 | 7 | Name2 | None |
| 7 | 8 | Cai | Junjie |
| 8 | 9 | Zhang | Xu |
但是用這種方式傳入資料的話，自動id的狀態並不會做出相應的調整,而是繼續從上次終止的地方開始，不會跳過用上面方式插入的id。 如果再利用dict list插入資料，生成id就可能和以後的重複，導致異常。
例如下面的例子裡,最後一次自動id是7，繼續使用自動id的話，會從8開始。可以上面再用tuple list插入資料的時候已經把8佔用了，於是導致異常。
|
1 2 3 4 5 6 7 |
ins = users.insert() print_sql(engine, ins) try: engine.execute(ins,name='jack', fullname='Jack Jones') except Exception as e: print(type(e),e) |
|
1 2 3 4 5 6 7 |
***Compiled SQL*** <<< INSERT INTO test.users (id, name, fullname) VALUES (%(id)s, %(name)s, %(fullname)s) >>> (psycopg2.IntegrityError) duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(8) already exists. [SQL: 'INSERT INTO test.users (name, fullname) VALUES (%(name)s, %(fullname)s) RETURNING test.users.id'] [parameters: {'fullname': 'Jack Jones', 'name': 'jack'}] |
從DataFrame插入資料
pandas DataFrame是資料工作者經常使用的資料結構。
|
1 2 3 |
from pandas import DataFrame df = DataFrame({'name':['Xu','Junjie'],'fullname':['ZhangXu','CaiJunjie']}) display(df) |
| fullname | name | |
|---|---|---|
| 0 | ZhangXu | Xu |
| 1 | CaiJunjie | Junjie |
可以利用to_dict()方法很方便的把dataframe轉成dict list
|
1 2 3 4 |
display(df.to_dict(orient = 'records')) [{'fullname': 'ZhangXu', 'name': 'Xu'}, {'fullname': 'CaiJunjie', 'name': 'Junjie'}] |
儘管list(df.to_records())轉成的結果看上去是tuple list
|
1 2 3 4 5 6 7 8 |
df = DataFrame( {'id':[15,16],'name':['Xu','Junjie'],'fullname':['ZhangXu','CaiJunjie']} ,columns = ['id','name','fullname'] ) display(list(df.to_records(index = False))) [(15, 'Xu', 'ZhangXu'), (16, 'Junjie', 'CaiJunjie')] |
但是直接插入這個資料的話會導致異常
|
1 2 3 4 5 6 7 |
data = list(df.to_records(index = False)) try: ins = users.insert().values(data) engine.execute(ins) except Exception as e: print(type(e),e) |
|
1 |
(psycopg2.ProgrammingError) can't adapt type 'record' [SQL: 'INSERT INTO test.users (id, name) VALUES (%(id)s, %(name)s)'] [parameters: {'id': (15, 'Xu', 'ZhangXu'), 'name': (16, 'Junjie', 'CaiJunjie')}] |
原因是list中的資料型別是numpy.record,不是tuple。
|
1 2 3 |
display(type(data[0])) numpy.record |
即使修復了這個問題
|
1 2 3 4 |
data = [tuple(r) for r in data] display(type(data[0])) tuple |
也依然會因為資料結構型別不一致導致異常
|
1 2 3 4 5 6 7 |
try: ins = users.insert().values(data) engine.execute(ins) except Exception as e: print(type(e),e) (psycopg2.ProgrammingError) can't adapt type 'numpy.int64' [SQL: 'INSERT INTO test.users (id, name, fullname) VALUES (%(id_0)s, %(name_0)s, %(fullname_0)s), (%(id_1)s, %(name_1)s, %(fullname_1)s)'] [parameters: {'name_1': 'Junjie', 'fullname_1': 'CaiJunjie', 'id_0': 15, 'name_0': 'Xu', 'fullname_0': 'ZhangXu', 'id_1': 16}] |
|
1 2 3 4 5 6 |
data = df.to_dict(orient = 'record') try: ins = users.insert().values(data) engine.execute(ins) except Exception as e: print(type(e),e) |
因此建議直接使用to_dict(orient = ‘record’)方式轉化資料。
Select, Update, Delete
這部門內容比較豐富，這裡只演示最基本的應用。更詳細的說明放在下一期的的文章講解。
基本的select結構
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
from sqlalchemy import select s = select( [ users.c.id, users.c.name ] ).select_from( users ).where( users.c.id==1 ) print_sql(engine,s) display(get_select(engine,s)) ***Compiled SQL*** <<< SELECT test.users.id, test.users.name FROM test.users WHERE test.users.id = 1 >>> |
其中select_from相當於SQL中的FROM。 如果不會產生歧義，select_from部分可以省略不寫。SQLAlchemy會自動補齊相關的FROM語句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
from sqlalchemy import select s = select( [ users.c.id, users.c.name ] ).where( users.c.id==1 ) print_sql(engine,s) display(get_select(engine,s)) ***Compiled SQL*** <<< SELECT test.users.id, test.users.name FROM test.users WHERE test.users.id = 1 >>> |
帶引數的SQL邏輯
如果希望生成的SQL邏輯支援引數,有兩種實現方式。
函式生成方式
用函式生成SQL邏輯，用函式的引數去實現SQL邏輯引數可變的效果。例如我們構造一個針對user.id的條件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
def condition(user_id): return users.c.id == user_id print_sql(engine,condition(1)) print_sql(engine,condition(2)) ***Compiled SQL*** <<< test.users.id = 1 >>> ***Compiled SQL*** <<< test.users.id = 2 >>> |
上面這種方式每次執行函式的時候都會構建新的SQLAlchemy object。
用bindparam指定引數
另一種方式是構建SQLAlchemy object時,用bindparam指定引數部分。 然後用.params繫結數值。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
from sqlalchemy.sql import bindparam condition = (users.c.id == bindparam('id')).params({'id':1}) print_sql(engine,condition,False) print_sql(engine,condition) ***Compiled SQL*** <<< test.users.id = %(id)s >>> ***Compiled SQL*** <<< test.users.id = 1 >>> |
實際上,在SQLAlchemy中使用常數的時候,只是把定義引數和繫結資料兩步一起做而已。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
from sqlalchemy.sql import bindparam condition = users.c.id == 1 print_sql(engine,condition,False) print_sql(engine,condition) ***Compiled SQL*** <<< test.users.id = %(id_1)s >>> ***Compiled SQL*** <<< test.users.id = 1 >>> |
如果定義了引數後沒有通過params繫結數值，那麼在execute階段傳入數值也是可以的。
|
1 2 3 4 5 6 7 8 9 10 11 |
s = users.select().where(users.c.id==bindparam('id')) print_sql(engine,s,False) display(engine.execute(s,id=1).fetchone()) ***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users WHERE test.users.id = %(id)s >>> (1, 'jack', 'Jack Jones') |
上面這種方式, obj生成一次後可以反覆被利用,不必重複的生成object。
型別提示
有些場景下，需要指定變數型別，幫助sqlalchemy正確的編譯語句。下面的例子裡,即使後面繫結了string型別的資料，+依然沒能正確的編譯成字串的連線符。應該是”||”。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
from sqlalchemy import text s = users.select(users.c.name.like(bindparam('username') + text("'%'"))) s = s.params({'username':'jack'}) print_sql(engine,s) try: display(get_select(engine, s)) except Exception as e: print(type(e),e) ***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users WHERE test.users.name LIKE NULL + '%%' >>> (psycopg2.ProgrammingError) operator is not unique: unknown + unknown LINE 3: WHERE test.users.name LIKE 'jack' + '%' ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. [SQL: "SELECT test.users.id, test.users.name, test.users.fullname \nFROM test.users \nWHERE test.users.name LIKE %(username)s + '%%'"] [parameters: {'username': 'jack'}] |
這時候，需要主動在bindparam中通過type_指定資料型別，幫助SQLAlchemy正確的編譯
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
from sqlalchemy import text,String s = users.select(users.c.name.like(bindparam('username',type_=String) + text("'%'"))) s = s.params({'username':'jack'}) print_sql(engine,s,compile_kwargs={'literal_binds':True}) display(get_select(engine, s)) ***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users WHERE test.users.name LIKE ('jack' || '%%') >>> |
用text定義sqlalchemy object
除了用純粹的sqlalchemy object去定義SQL邏輯的各種元件,有時候我們希望將文字形式的sql直接轉化成sqlalchemy object。例如下面兩種場景。
- 已經存在現成的sql程式碼片段，不想用SQLAlchemy重寫
- 遇到SQLAlchemy無法表達，只有原生的SQL能表達的場景
例如下面這樣包含待定引數的SQL語句,:id是名為id的引數。在傳入實際的數值前,這個語句是不完整的，如果直接傳入engine.execute的話，會出錯。
|
1 2 3 4 5 6 7 8 9 10 11 |
s = 'select users.id, users.name, users.fullname from test.users where users.id=:user_id' try: engine.execute(s).fetchall() except Exception as e: print(type(e),e) (psycopg2.ProgrammingError) syntax error at or near ":" LINE 1: ...name, users.fullname from test.users where users.id=:user_id ^ [SQL: 'select users.id, users.name, users.fullname from test.users where users.id=:user_id'] |
這時可以用text處理並且用bindparams函式繫結資料
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
s = 'select users.id, name, users.fullname from test.users where users.id=:user_id' s = text(s).bindparams(user_id=1) print_sql(engine,s) print(engine.execute(s).fetchone()) ***Compiled SQL*** <<< select users.id, name, users.fullname from test.users where users.id=1 >>> (1, 'jack', 'Jack Jones') |
繫結引數呼叫的方法是bindparams,不是params,也不是bindparam! 注意區分!
也可以不繫結引數，而是在execute階段傳入資料
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
s = 'select users.id, users.name, users.fullname from test.users where users.id=:user_id' s = text(s) print_sql(engine,s,False) print(engine.execute(s,user_id=1).fetchone()) ***Compiled SQL*** <<< select users.id, users.name, users.fullname from test.users where users.id=%(user_id)s >>> (1, 'jack', 'Jack Jones') |
除了用文字定義大段的SQL邏輯外，也可以用文字SQL的片段去定義部分的SQL元件。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
s = ( select( [ text("users.fullname || ', ' || addresses.email_address AS title"), ] ).select_from( text('test.users, test.addresses'), ).where( text( "users.id = addresses.user_id and " "users.id = :user_id" ) ) ) s = s.params({'user_id':1}) print_sql(engine, s) engine.execute(s).fetchall() ***Compiled SQL*** <<< SELECT users.fullname || ', ' || addresses.email_address AS title FROM test.users, test.addresses WHERE users.id = addresses.user_id and users.id = NULL >>> [('Jack Jones, jack@yahoo.com',), ('Jack Jones, jack@msn.com',)] |
注意上面例子中s構造的時候，用到了text生成的帶引數的SQL邏輯元件，但是本身的資料型別是sqlalchemy.sql.selectable.Select,因此繫結資料的時候呼叫的方法是params，而不是bindparam
|
1 |
print(type(s)) |
如果用文字定義的SQL片段是table,和column, 可以用literal_column, table代替text去處理文字SQL。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
from sqlalchemy import literal_column, String,table,literal users = table('users') users.schema = 'test' #注意指定schema的方式 s = select( [ literal_column('users.id').label('id'), (literal('=<')+literal_column('users.fullname',type_ = String)+literal('>=')).label('name') ] ).select_from ( users ) print_sql(engine,s) ***Compiled SQL*** <<< SELECT users.id AS id, '=<' || users.fullname || '>=' AS name FROM test.users >>> |
注意schema不能在構造table時以字串傳入，否則生成的語句執行時會錯誤。儘管構造出來的SQL看上去是完全正確的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
from sqlalchemy import literal_column, String,table,literal users = table('test.users') #這樣是不行的 s = select( [ literal_column('users.id').label('id'), (literal('=<')+literal_column('users.fullname',type_ = String)+literal('>=')).label('name') ] ).select_from ( users ) print_sql(engine,s) try: display(get_select(engine,s)) except Exception as e: print(type(e),e) ***Compiled SQL*** <<< SELECT users.id AS id, '=<' || users.fullname || '>=' AS name FROM "test.users" >>> (psycopg2.ProgrammingError) relation "test.users" does not exist LINE 2: FROM "test.users" ^ [SQL: 'SELECT users.id AS id, %(param_1)s || users.fullname || %(param_2)s AS name \nFROM "test.users"'] [parameters: {'param_2': '>=', 'param_1': '=<'}] |
用literal_column和table相比text,構造出的object能夠更好的被SQLAlchemy支援。看下面的例子。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
users, addresses = reset_tables(engine) s1 = select( [ users.c.id, text('users.fullname AS name') ] ) print_sql(engine,s1) s2 = select( [ users.c.id, literal_column('users.fullname').label('name') ] ) print_sql(engine,s2) ***Compiled SQL*** <<< SELECT test.users.id, users.fullname AS name FROM test.users >>> ***Compiled SQL*** <<< SELECT test.users.id, users.fullname AS name FROM test.users >>> |
儘管編譯出的語句是一樣的，但是觀察SQLAlchemy識別出的column names,發現SQLAlchemy無法識別text函式構造出的列。
|
1 2 3 4 5 6 |
print(s1.c.keys()) print(s2.c.keys()) ['id'] ['id', 'name'] |
因此應該優先考慮使用literal_column, table等更確切具體的構造方式以獲得SQLAlchemy更好的支援。