1.1 SQLite 和 Python

1.1.1 瀏覽

SQLite是一個強而有力的鑲入型資料庫應用在小的和相容於C 的函式庫.它已經被調整為命令列的工具, 特別是C語言, web 服務, PDAs,和程式語言如 Python.

因為它的大小很小,所有有許多平台都可以使用, liberal copyright license, and solid design, it lends itself to wide range of applications in both open source and commercial software.程式計設師 傳統上是使用簡單的BTree資料庫如gdm可以使用關連式資料庫的優點,不用加入額外的檔頭到它的應用程式.使用者通常寫入簡單的報表到Microsoft Visual Basic和 Access 現在可以簡單的免費使用Python 和SQLite,和產生資料庫和程式不再限制在 Windows作業平台.大概相同的程式碼也可以輕易的執行在 Apple Macintosh或 Sun UltraSPARC.

Python 是世上最好的描述語言 (I say without the least bias). 它清楚,簡單, 漂亮,並且有一個可以延伸的函式庫和使用者資料庫. 像SQLite,它可被使用所有的東西由PDAs到大型主機.它被使用在科學計算機,系統管理,圖形使用者應用程式,搜索引擎 , web 服務, movie studios, and the list goes on. 它不只一個優良的語言給專業的程式設計師,給初學者也不錯. 它已經被移植到不同的作業系統及架構,而且是可以免費的使用.

1.1.2 應用

整合Python和 SQLite使得程式使用一個關連式資料庫,開啟和可利用, spanning作業系統和架構 architectures, 和所有的surprisingly 低價 1.1. PySQLite的應用範圍由both sides of the continuum: Python可以使用管理的語言,使用它使得操作在SQLite資料庫是比較簡單, and likewise SQLite 可以服務一個便利的持續的儲存機制給使用存在的PYTHON應用程式.

1.1.3 容易使用

使用 SQLite和 Python是無法被了解的簡單.SQLite C函式庫只有三函式來執行一個 SQL查詢 和取回它的結果. Python 使得它更簡單scripting language. 一個完整的程式排序一個taxonimic database of plants by species and genus name could be done with the following program:

import sqlite

conn = sqlite.connect(db="db", mode=077)
cursor = conn.cursor()
cursor.execute("""
select genus, species, family, category
from calflora
order by species, genus
""")
for row in cursor.fetchall():
print "%14s, %15s, %19s, %8s, %25s, %i" % ( row.genus,
row.species,
row.family,
row.category)
conn.close()

2.1 API 修改

2.1.1 函式

2.1.1.1 connect()

SQLite是一個檔案為基礎的鑲入型資料庫. As such, you only need to give the path to the database file to the connect function.

2.2 API 額外

2.2.1 資料型態

SQLite是沒有資料型態.所有的資料都被當成文字儲存.然而,所有的資料自 PySQLite 取回會投射到相對宣告的資料庫schema, 提供標準型態的 ANSI SQL type (e.g. VARCHAR, INT, FLOAT, etc.).

額外使用schema, PySQLite可以讓你指定欄位的資料型態在取回時自定.這個使用execute()來做.當 fetchone(),fetchmany()fetchall() 被呼叫, PySQLite轉換欄位在回傳的列成為其指定的型態.例如 ,你有一個表格定義如下:

create table plants
(
id integer primary key,
name varchar(25),
av_height int,
av_lifespan float
);

現在你要選取所有的記錄並讓它們有合適的型態.你將做如下:

cnx = connect('db')
c = cnx.cursor()
c.execute('-- types int,str,int,float')
c.execute('select * from plants')

PySQLite尋找型態在SQL註解中如果找到, stores the list of types internally for later use when rows are fetched from the result set. In this particular example that even without the '-types' mechanism, data types would have still been properly cast since they are ANSI datatypes.

2.2.1.1 Custom Types

While PySQLite supports native Python data types, you can also apply your own data types to result sets as well. When you do this, PySQLite will pass the field value to your type's constructor when converting the column type. The way to do this is to add your type the the converters list, which is done on connect() This is perhaps best illustrated by example:

class my_type:

def __init__(self,value):
self.value = value

def __repr__(self):
return "my_type(%.3f)" % float(self.value)

def __str__(self):
return "%.3f" % float(self.value)

cnx = sqlite.connect(db="db", mode=077, converters={'my_type':my_type})
c = conn.cursor()

SQL = "select genus, species, family, category, author1, upper_elev_ft " \
" from calflora order by genus, species limit 33"

cursor.execute('-- types str,str,str,str,str,my_type')
cursor.execute(SQL)

The resulting rows fetched will have upper_elev_ft formatted with three decimal places.

2.2.2 Result Sets

PySQLite's result set class is taken from pyPgSQL, which adds many niceties not required by the DB API Specification. As such, fetchone() returns more than just a tuple of fields, but a flexible data structure which can behave as a tuple, but also has attributes with the field names from the returned rows. Thus you can do things as in the following example:

c.execute('select last_name, street, city, zip from contacts')

row = c.fetchone()

# By field number (the DB-API way)
print row[1]

# By attribute
print row.city
print row.zip

# As a slice
print row[2:-1]

2.2.3 Aggregates and Functions

The SQLite Extended C API has a nice framework for allowing you to add your own functions and aggregates which can be called from SQL. For example, while SQLite does not support the concept of CURRENT_DATE, you could use the framework to create an equivalent function (current_date()) so that statements like SELECT current_date() or INSERT into orders (cust_id, price, order_date) values (1, 100, current_date() are valid statements within SQLite.

The only problem is that the SQLite framework requires you to implement these extensions in C. Fortunately, PySQLite provides a framework which allows you to implement them in Python. The process of creating user-defined functions and aggregates is very similar to creating custom types. The best way to demonstrate these is by example.

2.2.3.1 Functions

The following is from functions.py which is included in thecalflora example. It creates a function times_two which takes a single argument and appropriately return a multiple of two. The example applies the times_two argument to the upper_elev_ft field in a simple select statement.

# Function def times_two(x): return float(x)*2

conn = sqlite.connect("db", 077)

# Add the aggregate
conn.create_function("times_two", 1, times_two)

cursor = conn.cursor()

# Expect string, then two float values for columns
cursor.execute('-- types str, float, float')

# Execute SQL with custom aggregate
cursor.execute("select family, upper_elev_ft, times_two(upper_elev_ft) from calflora limit 10")

# DB-API extension that requires Python 2.2, else use cursor.fetchall():
for row in cursor:
print row

2.2.3.2 Aggregates

The following is from aggregates.py which is included in the calflora example. It creates an aggregate called variance which computes the variance of a set (or population). This example determines the variance in elevation heights (the upper_elev_ft field) of all plant species listed in the table.

# Aggregate Class class variance:

def __init__(self):
self.X = []

def step(self, x):
self.X.append(float(x))

def reset(self):
self.X = []

def finalize(self):
mu = 0
n = len(self.X)

for x in self.X:
mu += x
mu = float(x)/n

sigma = 0
for x in self.X:
sigma += (x-mu)**2

sigma = sigma / (n - 1)

return sigma

conn = sqlite.connect("db", 077)

# Add the aggregate
conn.create_aggregate("variance", 1, variance)

cursor = conn.cursor()

# Expect float value for column
cursor.execute('-- types float')

# Execute SQL with custom aggregate
cursor.execute("select variance(upper_elev_ft) from calflora")

# Fetch
row = cursor.fetchone()

# Print
print "Upper elevation variance: %.2f" % row[0]

A.1 Calflora

The calflora example comes from data at http://www.calflora.org/calflora. It contains about 100 records included for the sake of example. This project illustrates the use of PySQLite for reading and writing.

A.1.1 Reading

The database must be created first. For UNIX systems, there is are a few convenience shell scripts. For this purpose, you can just run createdb.sh. On windows systems, you can type

> cat data.sql | sqlite db

This will create the database and load the sample data. Now that this is done, you can run any of the select scripts, which are as follows:

A.1.2 Writing

The insert.py illustrates writing to the database. In order to run this script, you need to empty out the database. On UNIX, you can use the cleardb.sh script. On Windows you can type

> echo "delete from calflora;" | sqlite db

The insert script will load records from data.txt, which is a pipe delimited flat file into the calflora table.