一、使用SQLite

SQLite是一種嵌入式數(shù)據(jù)庫，它的數(shù)據(jù)庫就是一個文件。由于SQLite本身是C寫的，而且體積很小，所以，經(jīng)常被集成到各種應(yīng)用程序中，甚至在iOS和Android的App中都可以集成。
在使用SQLite前，我們先要搞清楚幾個概念：

表是數(shù)據(jù)庫中存放關(guān)系數(shù)據(jù)的集合，一個數(shù)據(jù)庫里面通常都包含多個表，比如學(xué)生的表，班級的表，學(xué)校的表，等等。表和表之間通過外鍵關(guān)聯(lián)。

要操作關(guān)系數(shù)據(jù)庫，首先需要連接到數(shù)據(jù)庫，一個數(shù)據(jù)庫連接稱為Connection；

連接到數(shù)據(jù)庫后，需要打開游標，稱之為Cursor，通過Cursor執(zhí)行SQL語句，然后，獲得執(zhí)行結(jié)果。

Python定義了一套操作數(shù)據(jù)庫的API接口，任何數(shù)據(jù)庫要連接到Python，只需要提供符合Python標準的數(shù)據(jù)庫驅(qū)動即可。

由于SQLite的驅(qū)動內(nèi)置在Python標準庫中，所以我們可以直接來操作SQLite數(shù)據(jù)庫。

我們在Python交互式命令行實踐一下：

# 導(dǎo)入SQLite驅(qū)動:
>>> import sqlite3
# 連接到SQLite數(shù)據(jù)庫
# 數(shù)據(jù)庫文件是test.db
# 如果文件不存在，會自動在當(dāng)前目錄創(chuàng)建:
>>> conn = sqlite3.connect('test.db')
# 創(chuàng)建一個Cursor:    # 光標，游標
>>> cursor = conn.cursor()
# 執(zhí)行一條SQL語句，創(chuàng)建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')      # varchar 可變長字符串,varchar(20)表示可存的內(nèi)容大小,execute 履行執(zhí)行
<sqlite3.Cursor object at 0x10f8aa260>
# 繼續(xù)執(zhí)行一條SQL語句，插入一條記錄:
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x10f8aa260>
# 通過rowcount獲得插入的行數(shù):
>>> cursor.rowcount
1
# 關(guān)閉Cursor:
>>> cursor.close()
# 提交事務(wù):
>>> conn.commit()
# 關(guān)閉Connection:
>>> conn.close()

我們再試試查詢記錄：

>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
# 執(zhí)行查詢語句:
>>> cursor.execute('select * from user where id=?', ('1',))    #  * 表示將表格里所有的相關(guān)數(shù)據(jù)選出來 , ? 為占位符，對應(yīng)python的 %s
<sqlite3.Cursor object at 0x10f8aa340>
# 獲得查詢結(jié)果集:
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
>>> cursor.close()
>>> conn.close()

使用Python的DB-API時，只要搞清楚Connection和Cursor對象，打開后一定記得關(guān)閉，就可以放心地使用。

使用Cursor對象執(zhí)行insert，update，delete語句時，執(zhí)行結(jié)果由rowcount返回影響的行數(shù)，就可以拿到執(zhí)行結(jié)果。

使用Cursor對象執(zhí)行select語句時，通過featchall()可以拿到結(jié)果集。結(jié)果集是一個list，每個元素都是一個tuple，對應(yīng)一行記錄。

如果SQL語句帶有參數(shù)，那么需要把參數(shù)按照位置傳遞給execute()方法，有幾個?占位符就必須對應(yīng)幾個參數(shù)，例如：

cursor.execute('select * from user where name=? and pwd=?', ('abc', 'password'))

SQLite支持常見的標準SQL語句以及幾種常見的數(shù)據(jù)類型。具體文檔請參閱SQLite官方網(wǎng)站。

小結(jié)

在Python中操作數(shù)據(jù)庫時，要先導(dǎo)入數(shù)據(jù)庫對應(yīng)的驅(qū)動，然后，通過Connection對象和Cursor對象操作數(shù)據(jù)。

要確保打開的Connection對象和Cursor對象都正確地被關(guān)閉，否則，資源就會泄露。

如何才能確保出錯的情況下也關(guān)閉掉Connection對象和Cursor對象呢？請回憶try:...except:...finally:...的用法。

練習(xí)

在Sqlite中根據(jù)分數(shù)段查找指定的名字

# -*- coding: utf-8 -*-
import os, sqlite3
db_file = os.path.join(os.path.dirname(__file__), 'test.db')
if os.path.isfile(db_file):
    os.remove(db_file)

# 初始數(shù)據(jù):
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')
cursor.execute(r"insert into user values ('A-001', 'Adam', 95)")
cursor.execute(r"insert into user values ('A-002', 'Bart', 62)")
cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)")
cursor.close()
conn.commit()
conn.close()

def get_score_in(low, high):
    ' 返回指定分數(shù)區(qū)間的名字，按分數(shù)從低到高排序 '
    with sqlite3.connect(db_file) as conn:
        try:
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM user WHERE score > ? AND score < ? ORDER BY score',(low,high))    # 篩選
            values = cursor.fetchall()
        finally:
            cursor.close()
    return values
if __name__=='__main__':
    r = get_score_in(60,100)
    print(r)

二、使用MySQL

我們演示如何連接到MySQL服務(wù)器的test數(shù)據(jù)庫：

# 導(dǎo)入MySQL驅(qū)動:
>>> import mysql.connector
# 注意把password設(shè)為你的root口令:
>>> conn = mysql.connector.connect(user='root', password='password', database='test')
>>> cursor = conn.cursor()
# 創(chuàng)建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# 插入一行記錄，注意MySQL的占位符是%s:
>>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
>>> cursor.rowcount
1
# 提交事務(wù):
>>> conn.commit()
>>> cursor.close()
# 運行查詢:
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id = %s', ('1',))
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
# 關(guān)閉Cursor和Connection:
>>> cursor.close()
True
>>> conn.close()

由于Python的DB-API定義都是通用的，所以，操作MySQL的數(shù)據(jù)庫代碼和SQLite類似。
注意：
執(zhí)行INSERT等操作后要調(diào)用commit()提交事務(wù)；
MySQL的SQL占位符是%s。SQLite占位符是 ?

三、使用SQLAlchemy

數(shù)據(jù)庫表是一個二維表，包含多行多列。把一個表的內(nèi)容用Python的數(shù)據(jù)結(jié)構(gòu)表示出來的話，可以用一個list表示多行，list的每一個元素是tuple，表示一行記錄，比如，包含id和name的user表：

[
    ('1', 'Michael'),
    ('2', 'Bob'),
    ('3', 'Adam')
]

Python的DB-API返回的數(shù)據(jù)結(jié)構(gòu)就是像上面這樣表示的。

但是用tuple表示一行很難看出表的結(jié)構(gòu)。如果把一個tuple用class實例來表示，就可以更容易地看出表的結(jié)構(gòu)來：

class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name

[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]

這就是傳說中的ORM技術(shù)：Object-Relational Mapping，把關(guān)系數(shù)據(jù)庫的表結(jié)構(gòu)映射到對象上。是不是很簡單？

但是由誰來做這個轉(zhuǎn)換呢？所以O(shè)RM框架應(yīng)運而生。

在Python中，最有名的ORM框架是SQLAlchemy。我們來看看SQLAlchemy的用法。

首先通過pip安裝SQLAlchemy：

$ pip install sqlalchemy

然后，利用上次我們在MySQL的test數(shù)據(jù)庫中創(chuàng)建的user表，用SQLAlchemy來試試：

第一步，導(dǎo)入SQLAlchemy，并初始化DBSession：

# 導(dǎo)入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 創(chuàng)建對象的基類:
Base = declarative_base()

# 定義User對象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的結(jié)構(gòu):
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

# 初始化數(shù)據(jù)庫連接:
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# 創(chuàng)建DBSession類型:
DBSession = sessionmaker(bind=engine)

以上代碼完成SQLAlchemy的初始化和具體每個表的class定義。如果有多個表，就繼續(xù)定義其他class，例如School：

class School(Base):
    __tablename__ = 'school'
    id = ...
    name = ...

create_engine()用來初始化數(shù)據(jù)庫連接。SQLAlchemy用一個字符串表示連接信息：

'mysql+mysqlconnector://root:password@localhost:3306/test'  # 對應(yīng)下方
'數(shù)據(jù)庫類型+數(shù)據(jù)庫驅(qū)動名稱://用戶名:口令@機器地址:端口號/數(shù)據(jù)庫名'

你只需要根據(jù)需要替換掉用戶名、口令等信息即可。

下面，我們看看如何向數(shù)據(jù)庫表中添加一行記錄。

由于有了ORM，我們向數(shù)據(jù)庫表中添加一行記錄，可以視為添加一個User對象：

# 創(chuàng)建session對象:
session = DBSession()
# 創(chuàng)建新User對象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到數(shù)據(jù)庫:
session.commit()
# 關(guān)閉session:
session.close()

可見，關(guān)鍵是獲取session，然后把對象添加到session，最后提交并關(guān)閉。DBSession對象可視為當(dāng)前數(shù)據(jù)庫連接。

如何從數(shù)據(jù)庫表中查詢數(shù)據(jù)呢？有了ORM，查詢出來的可以不再是tuple，而是User對象。SQLAlchemy提供的查詢接口如下：

# 創(chuàng)建Session:
session = DBSession()
# 創(chuàng)建Query查詢，filter是where條件，最后調(diào)用one()返回唯一行，如果調(diào)用all()則返回所有行:
user = session.query(User).filter(User.id=='5').one()
# 打印類型和對象的name屬性:
print('type:', type(user))
print('name:', user.name)
# 關(guān)閉Session:
session.close()

運行結(jié)果如下：

type: <class '__main__.User'>
name: Bob

可見，ORM就是把數(shù)據(jù)庫表的行與相應(yīng)的對象建立關(guān)聯(lián)，互相轉(zhuǎn)換。

由于關(guān)系數(shù)據(jù)庫的多個表還可以用外鍵實現(xiàn)一對多、多對多等關(guān)聯(lián)，相應(yīng)地，ORM框架也可以提供兩個對象之間的一對多、多對多等功能。

例如，如果一個User擁有多個Book，就可以定義一對多關(guān)系如下：

class User(Base):
    __tablename__ = 'user'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # 一對多:
    books = relationship('Book')

class Book(Base):
    __tablename__ = 'book'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))
    # “多”的一方的book表是通過外鍵關(guān)聯(lián)到user表的:
    user_id = Column(String(20), ForeignKey('user.id'))

當(dāng)我們查詢一個User對象時，該對象的books屬性將返回一個包含若干個Book對象的list。

小結(jié)

ORM框架的作用就是把數(shù)據(jù)庫表的一行記錄與一個對象互相做自動轉(zhuǎn)換。
正確使用ORM的前提是了解關(guān)系數(shù)據(jù)庫的原理。

python中cursor操作數(shù)據(jù)庫

commit() 提交
rollback() 回滾

cursor用來執(zhí)行命令的方法:

callproc(self, procname, args):用來執(zhí)行存儲過程,接收的參數(shù)為存儲過程名和參數(shù)列表,返回值為受影響的行數(shù)

execute(self, query, args):執(zhí)行單條sql語句,接收的參數(shù)為sql語句本身和使用的參數(shù)列表,返回值為受影響的行數(shù)

executemany(self, query, args):執(zhí)行單挑sql語句,但是重復(fù)執(zhí)行參數(shù)列表里的參數(shù),返回值為受影響的行數(shù)

nextset(self):移動到下一個結(jié)果集

cursor用來接收返回值的方法:

fetchall(self):接收全部的返回結(jié)果行.

fetchmany(self, size=None):接收size條返回結(jié)果行.如果size的值大于返回的結(jié)果行的數(shù)量,則會返回cursor.arraysize條數(shù)據(jù).

fetchone(self):返回一條結(jié)果行.

scroll(self, value, mode='relative'):移動指針到某一行.如果mode='relative',則表示從當(dāng)前所在行移動value條,如果 mode='absolute',則表示從結(jié)果集的第一行移動value條.