Python基础系列讲解——如何使用自带的SQLite数据库

当前流行的数据库有Oracle、MySQL、Microsoft SQL Server、PostgreSQL、MongoDB、Redis、Microsoft Access……SQLite（这个是按某报告调研的受欢迎程度来排名的）。当然每个数据库都有它最适合的应用场合。这里我们推荐一款轻型的关系型数据库SQLite，推荐理由如下：

1. SQLite本身是C写，所以体积小巧，占用资源低
2. SQLite本身是C写，所以处理速度非常快
3. SQLite已经发布SQLite 3 版本
4. SQLite3支持Windows/Linux/Unix等主流操作系统
5. Python 2.5.x 以上版本默认内置SQLite3，无需单独安装和配置，直接使用！！！

当我们仅仅是用于本地的数据管理，无需多用户访问，数据容量小于2T，无需海量数据处理，关键是要求移植方便、使用简单、处理迅速的话， SQLite3确实是个很不错的选择。
在Python 2.5.x以上定义了一套操作SQLite3的API接口，使用时需要导入sqlite3模块，如下所示。

import sqlite3

接下来，我们仅需调用接口就可以，我们用例程来介绍下：

一、连接数据库

要操作SQLite3首先需要连接到数据库，接口函数：

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

该函数会链接到 SQLite3数据库并返回一个连接对象，如果数据库不存在，那么将会自动创建一个数据库。如下所示：

conn = sqlite3.connect('stock-data.db')

SQLite3是文件型数据库，可以看到一个SQLite3就是一个文件，备份这个文件就备份了整个数据库。
在连接到数据库后，需要建立Cursor，通过Cursor去执行SQL语句，接口函数：

connection.cursor([cursorClass])

使用如下所示：

c = conn.cursor()

二、创建表

数据库中是以表的形式存放数据的，我们在先前创建的数据库中创建一个STOCK600123表。可以使用如下接口去执行一个 SQL 语句：

cursor.execute(sql [, optional parameters])

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

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

数据库系统识别到CREATE TABLE关键字后会创建一个新表。CREATE TABLE 语句后跟着表的唯一的名称或标识。如下所示：

c.execute('''CREATE TABLE STOCK600123
       (ID INT PRIMARY KEY   NOT NULL,
       TIME          TEXT    NOT NULL,
       CODE          INT     NOT NULL,
       HIGH          REAL,
       LOW           REAL,
       CLOSE         REAL,
       OPEN          REAL,
       DESCRIPTION CHAR(50));''')

创建表后记得提交当前的操作，接口函数：

connection.commit()

如果未调用该方法，那么所做的任何操作对数据库来说都是无效的。
我们可以查看表结构来验证表是否已成功创建，如下接口可将查询到的结果以列表形式返回所有行：

cursor.fetchall()

整体操作如下所示：

c.execute("PRAGMA table_info(STOCK600123)")
print(c.fetchall())
[(0, 'ID', 'INT', 1, None, 1), (1, 'TIME', 'TEXT', 1, None, 0), (2, 'CODE', 'INT', 1, None, 0), (3, 'HIGH', 'REAL', 0, None, 0), (4, 'LOW', 'REAL', 0, None, 0), (5, 'CLOSE', 'REAL', 0, None, 0), (6, 'OPEN', 'REAL', 0, None, 0), (7, 'DESCRIPTION', 'CHAR(50)', 0, None, 0)]

三、插入表

我们上面创建的 STOCK600123表中插入4行数据，如下所示：

#插入表
c.execute("INSERT INTO STOCK600123 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
      VALUES (1, '2019-1-1', 600123, 10.12, 10.12, 10.12, 10.12,'event1' )")

c.execute("INSERT INTO STOCK600123 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
      VALUES (2, '2019-1-2', 600123, 10.13, 10.13, 10.13, 10.13,'event2' )")

c.execute("INSERT INTO STOCK600123 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
      VALUES (3, '2019-1-3', 600123, 10.14, 10.14, 10.14, 10.14,'event3' )")

c.execute("INSERT INTO STOCK600123 (ID,TIME,CODE,HIGH,LOW,CLOSE,OPEN,DESCRIPTION) \
      VALUES (4, '2019-1-4', 600123, 10.15, 10.15, 10.15, 10.15,'event4' )")
conn.commit()

我们可以查看表的内容来验证是否已成功插入四行，如下所示：

c.execute("select * from STOCK600123")
print(c.fetchall())
conn.commit()
[(1, '2019-1-1', 600123, 10.12, 10.12, 10.12, 10.12, 'event1'), (2, '2019-1-2', 600123, 10.13, 10.13, 10.13, 10.13, 'event2'), (3, '2019-1-3', 600123, 10.14, 10.14, 10.14, 10.14, 'event3'), (4, '2019-1-4', 600123, 10.15, 10.15, 10.15, 10.15, 'event4')]

四、更新表

当我们需要更新表中的第一行的“CODE”列内容时，可以使用UPDATE 语句，如下所示，600123更新为了600888。

# 更新表
c.execute("UPDATE STOCK600123 set CODE = 600888 where ID=1")
conn.commit()
c.execute("select * from STOCK600123")
print(c.fetchall())
conn.commit()

[(1, '2019-1-1', 600888, 10.12, 10.12, 10.12, 10.12, 'event1'), (2, '2019-1-2', 600123, 10.13, 10.13, 10.13, 10.13, 'event2'), (3, '2019-1-3', 600123, 10.14, 10.14, 10.14, 10.14, 'event3'), (4, '2019-1-4', 600123, 10.15, 10.15, 10.15, 10.15, 'event4')]

五、选择表

从当前创建的 STOCK600123 表中获取id, time, code, description这四部分信息，如下所示：

# 选择表
cursor = conn.execute("SELECT id, time, code, description from STOCK600123")
for row in cursor:
  print("ID = {}; TIME = {}; CODE = {}; description = {};\n".format(row[0],row[1],row[2],row[3]))
ID = 1; TIME = 2019-1-1; CODE = 600888; description = event1;
ID = 2; TIME = 2019-1-2; CODE = 600123; description = event2;
ID = 3; TIME = 2019-1-3; CODE = 600123; description = event3;
ID = 4; TIME = 2019-1-4; CODE = 600123; description = event4;

六、删除表

从当前创建的 STOCK600123 表中删除id为2的信息，只剩下id为1、3、4行的内容，如下所示：

# 删除表
c.execute("DELETE from STOCK600123 where ID=2;")
conn.commit()

# 选择表
cursor = conn.execute("SELECT id, time, code, description from STOCK600123")
for row in cursor:
  print("ID = {}; TIME = {}; CODE = {}; description = {};\n".format(row[0],row[1],row[2],row[3]))

当要删除STOCK600123整个表时，如下所示：

# 删除一个表
c.execute("drop table STOCK600123")
conn.commit()

当关闭数据库连接时，可使用如下接口：

connection.close()

该接口不会自动调用 commit()，如果关闭数据库连接前未调用 commit() 方法，所有的更改将丢失，切记！

·······································
欢迎大家订阅《教你用 Python 进阶量化交易》专栏！