????? SQLite 是內(nèi)嵌在 Python 中的輕量級、基于磁盤文件的數(shù)據(jù)庫管理系統(tǒng)，不需要服務(wù)器進程，支持使用 SQL 語句來訪問數(shù)據(jù)庫。
????? 首先創(chuàng)建一個與數(shù)據(jù)庫關(guān)聯(lián)的 Connection 對象。

>>> import sqlite3
>>> con = sqlite3.connect('example.db')

????? 再創(chuàng)建一個 Cursor 對象，并調(diào)用該對象的 execute() 方法來執(zhí)行 SQL 語句。

>>> c = con.cursor()
>>> c.execute("CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")  # 創(chuàng)建表
<sqlite3.Cursor object at 0x03462660>
>>> c.execute("INSERT INTO stocks VALUES ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)")  # 插入記錄
<sqlite3.Cursor object at 0x03462660>
>>> con.commit()  # 提交事務(wù)，保存數(shù)據(jù)
>>> con.close()  # 關(guān)閉數(shù)據(jù)庫連接

????? 查詢表中的數(shù)據(jù)，需要重新創(chuàng)建 Connection 對象和 Cursor 對象，再使用下面代碼：

>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print(row)

    
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)

14.1.1 Connection 對象

35E8014050BDE9B14E31962DB19212E6.jpg

????? 下面代碼演示如何在 sqlite3 連接中創(chuàng)建并調(diào)用自定義函數(shù)：

import sqlite3
import hashlib

def md5sum(t):
    return hashlib.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)   # 創(chuàng)建可在 SQL 語句中調(diào)用的函數(shù)，"md5" 為函數(shù)名，1為參數(shù)個數(shù)，md5sum 表示可調(diào)用對象
cur = con.cursor()
cur.execute("select md5(?)", (b"foo",)) # 在 SQL 語句中調(diào)用自定義函數(shù)
print(cur.fetchone()[0])

14.1.2 Cursor 對象

（1）execute(sql[,parameters])

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")
who = "Dong"
age = 38
# 使用問好作為占位符
cur.execute("insert into people values (?, ?)", (who, age))
# 使用命名變量作為占位符
cur.execute("select * from people where name_last=:who and age=:age", {"who":who, "age":age})
print(cur.fetchone())

（2）executemany(sql,seq_of_parameters)
????? 該方法用來對給定參數(shù)執(zhí)行同一個 SQL 語句，該參數(shù)序列可以使用不同的方式產(chǎn)生。如，下面的代碼使用序列作為 SQL 語句的參數(shù)：

import sqlite3

persons = [("Hugo", "Boss"), ("Calvin", "Klein")]
con = sqlite3.connect(":memory:")
# 創(chuàng)建表
con.execute("create table person(firstname, lastname)")
# 插入數(shù)據(jù)
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)
# 顯示數(shù)據(jù)
for row in con.execute("select firstname, lastname from person"):
    print(row)
print("I just deleted", con.execute("delete from person").rowcount, "rows")

（3）fetchone()、fetchmany(size = cursor.arraysize)、fetchall()
????? 這三個方法用來讀取數(shù)據(jù)。假設(shè)數(shù)據(jù)庫通過下面的方法創(chuàng)建并插入數(shù)據(jù)：

import sqlite3
conn = sqlite3.connect("D://Python//addressBook.db")
cur = conn.cursor()
cur.execute("CREATE TABLE addressList (name text, sex text, phone text, QQ text, address text)") 
cur.execute('''insert into addressList(name, sex, phone, QQ, address) values
            ('王小丫', '女', '13888991234', '341245245', '北京市')''')
conn.commit()
conn.close()

????? 則下面代碼演示了 fetchall() 讀取數(shù)據(jù)的方法：

import sqlite3

conn = sqlite3.connect(r'D:/Python/addressBook.db')
cur = conn.cursor()
cur.execute('select * from addressList')
li = cur.fetchall()
for line in li:
    for item in line:
        if type(item) != str:
            s = str(item)
        else:
            s = item
        print(s + '\t', end = ' ')
    print()
conn.close()

14.1.3 Row 對象

import sqlite3
con = sqlite3.connect('example.db')
con.row_factory = sqlite3.Row
c = con.cursor()
c.execute('select * from stocks')
r = c.fetchone()
print(type(r))
print(tuple(r))
print(len(r))
print(r[2])
print(r.keys())
print(r['qty'])
for member in r:
    print(member)

????? 運行結(jié)果：

<class 'sqlite3.Row'>
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
5
RHAT
['date', 'trans', 'symbol', 'qty', 'price']
100.0
2006-01-05
BUY
RHAT
100.0
35.14

14.2.3 操作 MySQL 數(shù)據(jù)庫

????? 可使用 MySQLdb 模塊訪問 MySQL 數(shù)據(jù)庫。
????? 使用該模塊查詢 MySQL 數(shù)據(jù)庫記錄的方法：

import MySQLdb

try:
    conn = MySQLdb.connect(host = 'localhost', user = 'root', password = '88888888',db = 'student',
                           port = 3306)
    cur = conn.cursor()
    cur.execute('select * from users')
    cur.close()
    conn.close()
except MySQLdb.Error as e:
    print("Mysql Error %d: %s"%(e.args[0], e.args[1]))

????? 插入用法：

import MySQLdb
try:
    conn = MySQLdb.connect(host = 'localhost', user = 'root', password = '88888888',db = 'student',
                           port = 3306)
    cur = conn.cursor()
    cur.execute('create database if not exists python')
    conn.select_db('python')
    cur.execute('create table test(id int, info varchar(20))')  # 執(zhí)行單條 SQL 語句
    value = [1, 'hi rollen']
    cur.execute('insert into test values(%s, %s)', value)
    values = []
    for i in range(20):
        values.append((i, 'hi rollen' + str(i)))
    cur.executemany('insert into test values(%s, %s)', values)
    cur.execute('update test set info = "I am rollen" where id = 3')    # 執(zhí)行多條 SQL 語句
    conn.commit()       # 提交事務(wù)
    cur.close()
    conn.close()
except MySQLdb.Error as e:
    print("MySQL Error %d: %s"%(e.args[0], e.args[1]))