嵌入式数据库SQLite本身是一个文件，由C写成，具有体积小的优点，便于集成到各种应用程序中。

主要学习调用Python中的sqlite3模块使用SQLite数据库，Python 2.5.x 以上版本默认自带了该模块。



tips:

  1. 表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表.表和表之间通过外键关联。

  2. 要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；

  3. 连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。



sqlite3模块API

  • sqlite3.connect(database [,timeout, other optional arguments])

    • 当一个数据库被多个连接访问，且其中一个修改了数据库，此时 SQLite 数据库被锁定，直到事务提交。timeout 参数表示连接等待锁定的持续时间，直到发生异常断开连接。timeout 参数默认是 5.0（5 秒）

    • 如果给定的数据库名称 filename 不存在，则该调用将创建一个数据库。

  • connection.cursor([cursorClass])

    • 创建一个 cursor

  • cursor.execute(sql [, optional parameters])

    • 执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。
    • e.g.cursor.execute(“insert into people values (?, ?)”, (who, age))

  • connection.commit()

    • 该方法提交当前的事务。如果未调用该方法，那么自上一次调用 commit() 以来所做的任何动作对其他数据库连接来说是不可见的。

  • connection.close()

    • 该方法关闭数据库连接。不会自动调用 commit()。如果未调用 commit() 方法就直接关闭数据库连接，所做的所有更改将全部丢失。



常用语句及操作

创建表

CREATE TABLE 语句的基本语法如下：

1
2
3
4
5
6
7
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

e.g. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import sqlite3

conn = sqlite3.connect('test.db')  # 访问或创建数据库
print "Opened database successfully"
c = conn.cursor()  # 创建cursor
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print "Table created successfully"
conn.commit()  # 提交事务
conn.close()  # 关闭连接

该实例创建了一个 COMPANY 表，ID 作为主键，NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL.


Insert语句

基本语法：

1
2
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

也可直接为表中的所有列添加值（需确保值的顺序与列在表中的顺序一致）：

1
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

e.g. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

conn.commit()
print "Records created successfully"
conn.close()

SELECT语句

基本语法：

1
SELECT column1, column2, columnN FROM table_name;

column1, column2…是表的字段。如果需要获取所有可用的字段，那么可以使用下面的语法：

1
SELECT * FROM table_name;

e.g. 
1
2
3
4
5
6
7
8
9
10
11
12
13
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Opened database successfully"

cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully"
conn.close()



WHERE子句

SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。用于过滤记录，可用在 SELECT、UPDATE、DELETE 等语句中。



带有 WHERE 子句的 SELECT 语句的基本语法如下：

1
2
3
SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

e.g.

1.运用比较运算符指定条件

1
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000

2.运用逻辑运算符NOT

如列出 AGE 不为 NULL 的所有记录

1
SELECT * FROM COMPANY WHERE AGE IS NOT NULL

3.运用逻辑运算符LIKE

如列出 NAME 以 ‘Ki’ 开始的所有记录，’Ki’ 之后的字符不做限制：

1
SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'

4.
列出 AGE 的值为 25 或 27 的所有记录： 
1
SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 )

5.

列出 AGE 的值在 25 与 27 之间的所有记录：

1
SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27

UPDATE语句