7.4.13 在SQL中使用Python函数
SQL语法支持在查询中调用函数,可以在“列”列表中调用,也可以在select语句的where子句中调用。利用这个特性,在从查询返回数据之前可以先处理数据。这个特性可以用于在不同格式之间转换,完成一些计算(否则使用纯SQL会很麻烦),以及重用应用代码。
import codecs
import sqlite3
db_filename = 'todo.db'
def encrypt(s):
print('Encrypting {!r}'.format(s))
return codecs.encode(s,'rot-13')
def decrypt(s):
print('Decrypting {!r}'.format(s))
return codecs.encode(s,'rot-13')
with sqlite3.connect(db_filename) as conn:
conn.create_function('encrypt',1,encrypt)
conn.create_function('decrypt',1,decrypt)
cursor = conn.cursor()
# Raw values
print('Original values:')
query = "select id,details from task"
cursor.execute(query)
for row in cursor.fetchall():
print(row)
print('\nEncrypting...')
query = "update task set details = encrypt(details)"
cursor.execute(query)
print('\nRaw encrypted values:')
query = "select id,details from task"
cursor.execute(query)
for row in cursor.fetchall():
print(row)
print('\nDecrypting in query...')
query = "select id,decrypt(details) from task"
cursor.execute(query)
for row in cursor.fetchall():
print(row)
print('\nDecrypting...')
query = "update task set details = decrypt(details)"
cursor.execute(query)
函数通过使用Connection的create_function()方法提供。参数包括函数名(即SQL中使用的函数名),函数的参数个数,以及要提供的Python函数。
运行结果: