一期虾、使用SQLite

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

表是數(shù)據(jù)庫(kù)中存放關(guān)系數(shù)據(jù)的集合聋涨，一個(gè)數(shù)據(jù)庫(kù)里面通常都包含多個(gè)表晾浴，比如學(xué)生的表，班級(jí)的表牍白，學(xué)校的表脊凰，等等。表和表之間通過(guò)外鍵關(guān)聯(lián)茂腥。

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

連接到數(shù)據(jù)庫(kù)后帕胆，需要打開(kāi)游標(biāo)朝捆，稱之為Cursor，通過(guò)Cursor執(zhí)行SQL語(yǔ)句懒豹，然后芙盘，獲得執(zhí)行結(jié)果。

Python定義了一套操作數(shù)據(jù)庫(kù)的API接口歼捐，任何數(shù)據(jù)庫(kù)要連接到Python，只需要提供符合Python標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)驅(qū)動(dòng)即可晨汹。

由于SQLite的驅(qū)動(dòng)內(nèi)置在Python標(biāo)準(zhǔn)庫(kù)中豹储，所以我們可以直接來(lái)操作SQLite數(shù)據(jù)庫(kù)。

我們?cè)赑ython交互式命令行實(shí)踐一下：

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

我們?cè)僭囋嚥樵冇涗洠?/p>

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

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

使用Cursor對(duì)象執(zhí)行insert宁脊，update断国，delete語(yǔ)句時(shí)，執(zhí)行結(jié)果由rowcount返回影響的行數(shù)榆苞，就可以拿到執(zhí)行結(jié)果稳衬。

使用Cursor對(duì)象執(zhí)行select語(yǔ)句時(shí)，通過(guò)featchall()可以拿到結(jié)果集坐漏。結(jié)果集是一個(gè)list薄疚，每個(gè)元素都是一個(gè)tuple，對(duì)應(yīng)一行記錄赊琳。

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

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

SQLite支持常見(jiàn)的標(biāo)準(zhǔn)SQL語(yǔ)句以及幾種常見(jiàn)的數(shù)據(jù)類型莱坎。具體文檔請(qǐng)參閱SQLite官方網(wǎng)站。

小結(jié)

在Python中操作數(shù)據(jù)庫(kù)時(shí)寸士，要先導(dǎo)入數(shù)據(jù)庫(kù)對(duì)應(yīng)的驅(qū)動(dòng)檐什，然后碴卧，通過(guò)Connection對(duì)象和Cursor對(duì)象操作數(shù)據(jù)。

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

如何才能確保出錯(cuò)的情況下也關(guān)閉掉Connection對(duì)象和Cursor對(duì)象呢瓮具？請(qǐng)回憶try:...except:...finally:...的用法荧飞。

練習(xí)

在Sqlite中根據(jù)分?jǐn)?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):
    ' 返回指定分?jǐn)?shù)區(qū)間的名字，按分?jǐn)?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ù)庫(kù)：

# 導(dǎo)入MySQL驅(qū)動(dòng):
>>> 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()
# 運(yùn)行查詢:
>>> 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ù)庫(kù)代碼和SQLite類似耳幢。
注意：
執(zhí)行INSERT等操作后要調(diào)用commit()提交事務(wù)；
MySQL的SQL占位符是%s欧啤。SQLite占位符是 ?

三睛藻、使用SQLAlchemy

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

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

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

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

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

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

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

但是由誰(shuí)來(lái)做這個(gè)轉(zhuǎn)換呢盹牧？所以O(shè)RM框架應(yīng)運(yùn)而生俩垃。

在Python中，最有名的ORM框架是SQLAlchemy汰寓。我們來(lái)看看SQLAlchemy的用法口柳。

首先通過(guò)pip安裝SQLAlchemy：

$ pip install sqlalchemy

然后，利用上次我們?cè)贛ySQL的test數(shù)據(jù)庫(kù)中創(chuàng)建的user表有滑，用SQLAlchemy來(lái)試試：

第一步跃闹，導(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)建對(duì)象的基類:
Base = declarative_base()

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

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

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

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

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

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

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

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

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

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

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

可見(jiàn)风钻，關(guān)鍵是獲取session顷蟀，然后把對(duì)象添加到session，最后提交并關(guān)閉骡技。DBSession對(duì)象可視為當(dāng)前數(shù)據(jù)庫(kù)連接鸣个。

如何從數(shù)據(jù)庫(kù)表中查詢數(shù)據(jù)呢？有了ORM哮兰，查詢出來(lái)的可以不再是tuple毛萌，而是User對(duì)象苟弛。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()
# 打印類型和對(duì)象的name屬性:
print('type:', type(user))
print('name:', user.name)
# 關(guān)閉Session:
session.close()

運(yùn)行結(jié)果如下：

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

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

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

例如帅刀，如果一個(gè)User擁有多個(gè)Book让腹，就可以定義一對(duì)多關(guān)系如下：

class User(Base):
    __tablename__ = 'user'

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

class Book(Base):
    __tablename__ = 'book'

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

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

小結(jié)

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

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

commit() 提交
rollback() 回滾

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

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

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

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

nextset(self):移動(dòng)到下一個(gè)結(jié)果集

cursor用來(lái)接收返回值的方法:

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

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

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

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