連接SQLite

要操作關系數據庫，首先需要連接到數據庫，一個數據庫連接稱為Connection；

連接到數據庫后，需要打開游標，稱之為Cursor，通過Cursor執行SQL語句，然后，獲得執行結果。

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

由于SQLite的驅動內置在Python標準庫中，可以直接來操作SQLite數據庫。

# 導入SQLite驅動:

import sqlite3

# 連接到SQLite數據庫
# 數據庫文件是test.db
# 如果文件不存在，會自動在當前目錄創建:
conn = sqlite3.connect('test.db')

# 創建一個Cursor:
cursor = conn.cursor()

# 執行一條SQL語句，創建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

# 繼續執行一條SQL語句，插入一條記錄:
cursor.execute('insert into user (id, name) values ("1", "vigilr")')

# 通過rowcount獲得插入的行數:
count=cursor.rowcount

# 關閉Cursor:
cursor.close()

# 提交事務:
conn.commit()

# 關閉Connection:
conn.close()

查詢數據庫數據

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 執行查詢語句:
cursor.execute('select * from user where id=?',('1',))

# 獲得查詢結果集:
values = cursor.fetchall()

cursor.close()
conn.close()

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

使用`Cursor`對象執行`insert`，`update`，`delete`語句時，執行結果由`rowcount`返回影響的行數，就可以拿到執行結果。

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

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

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

# -*- coding: utf-8 -*-
import sqlite3
# 初始數據:
conn = sqlite3.connect('test1.db')
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):
      '''返回指定分數區間的名字，按分數從低到高排序'''
      conn = sqlite3.connect('test1.db')
      cursor = conn.cursor()
      cursor.execute('select name from user where score>=? and score<=? ORDER BY score',(low,high))
      temp=cursor.fetchall()
      result=[]
      for t in temp:
      for i in t:
      result.Append(i)
      cursor.close()
      conn.close()
      return result

# 測試:
assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)
print('Pass')

連接MySQL

安裝MySQL驅動

由于MySQL服務器以獨立的進程運行，并通過網絡對外服務，所以，需要支持Python的MySQL驅動來連接到MySQL服務器。

MySQL官方提供了‘mysql-connector-python’驅動，但是安裝的時候需要給pip命令加上參數‘--allow-external’：

pip install mysql-connector-python --allow-external mysql-connector-python

如果上面的命令安裝失敗，可以試試另一個驅動：

‘pip install mysql-connector’

除了使用`mysql.connector`還可以使用`pymysql`

# 導入pymysql模塊
import pymysql

# 連接database
conn = pymysql.connect(host="127.0.0.1",port=3308,user="root",password="123456",database="test",charset="utf8")
cursor = conn.cursor()

# 創建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

cursor.close()
conn.close()

# 連接database
conn = pymysql.connect(host="127.0.0.1",port=3308,user="root",password="123456",database="test",charset="utf8")
cursor = conn.cursor()

# 插入一行記錄，注意MySQL的占位符是%s:
cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'wasd'])
cursor.execute('insert into user (id, name) values (%s, %s)', ['2', 'zxc'])
print('受影響行數：',cursor.rowcount)
# 提交事務:
conn.commit()
cursor.close()
conn.close()

使用SQLAlchemy

ORM技術：Object-Relational Mapping，把關系數據庫的表結構映射到對象上。

在Python中，最有名的ORM框架是SQLAlchemy。

首先通過pip安裝SQLAlchemy：`pip install sqlalchemy`

# 第一步，導入SQLAlchemy，并初始化DBSession：

# 導入:
from sqlalchemy import Column, String, create_engine,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.ext.declarative import declarative_base

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

# 定義User對象:
class User(Base):
      # 表的名字:
      __tablename__ = 'users'
      # 表的結構:
      id = Column(String(20), primary_key=True)
      name = Column(String(20))

# 以上代碼完成SQLAlchemy的初始化和具體每個表的class定義。如果有多個表，
# 就繼續定義其他class，例如Scho
class School(Base):
      __tablename__ = 'school'
      id = Column(String(20), primary_key=True)
      name = Column(String(20))

# 初始化數據庫連接:mysqlconnector和pymysql都可以用
# engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3308/test')
engine = create_engine('mysql+pymysql://root:123456@localhost:3308/test')

# 創建DBSession類型:
DBSession = sessionmaker(bind=engine)

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

`數據庫類型+數據庫驅動名稱://用戶名:密碼@數據庫地址:端口號/數據庫名`

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

# 創建所有定義的表到數據庫中
Base.metadata.create_all(engine)
# 創建session對象:
session = DBSession()
# 創建新User對象:
user1 = User(id='1', name='wasd')
user2 = User(id='2', name='zxc')
user3 = User(id='3', name='qwe')
user4 = User(id='4', name='rty')
user5 = User(id='5', name='vbn')
user6 = User(id='6', name='fgh')
# 添加到session:
session.add(user1)
session.add(user2)
session.add(user3)
session.add(user4)
session.add(user5)
session.add(user6)
# 提交即保存到數據庫:
session.commit()
# 關閉session:
session.close()
# 關鍵是獲取session，然后把對象添加到session，最后提交并關閉。DBSession對象可視為當前數據庫連接。

查詢數據

# 查詢數據
# 創建Session:
session = DBSession()
# 創建Query查詢，filter是where條件，最后調用one()返回唯一行，如果調用all()則返回所有行:
user = session.query(User).filter(User.id=='1').one()
users = session.query(User).filter(User.id!='1').all()
# 打印類型和對象的name屬性:
print('type:', type(user))
print('name:', user.name)
for user in users:
print('type:', type(user))
print('name:', user.name)
# 關閉Session:
session.close()

運行結果：

type: <class '__main__.User'>
name: wasd
type: <class '__main__.User'>
name: zxc
type: <class '__main__.User'>
name: qwe
type: <class '__main__.User'>
name: rty
type: <class '__main__.User'>
name: vbn
type: <class '__main__.User'>
name: fgh

ORM就是把數據庫表的行與相應的對象建立關聯，互相轉換。

由于關系數據庫的多個表還可以用外鍵實現一對多、多對多等關聯，相應地，ORM框架也可以提供兩個對象之間的一對多、多對多等功能。

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

class User(Base):
      __tablename__ = 'user'
      __table_args__ = {'extend_existing': True}
      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表是通過外鍵關聯到user表的:
      user_id = Column(String(20), ForeignKey('user.id'))
# 當我們查詢一個User對象時，該對象的books屬性將返回一個包含若干個Book對象的list。
# 創建所有定義的表到數據庫中
Base.metadata.create_all(engine)

更多用法可參考：這篇文章 https://www.jianshu.com/p/65903a69d61d