我心中的王者：Python-Python与SQLite数据库

我心中的王者：Python-Python与SQLite数据库

26-1　SQLite基本观念

在先前章节笔者说明了CSV、Json等数据格式，我们可以将数据以这些格式存储，不过我们使用数据时有时候只是取用一个小小的部分，如果每次皆要大费周章打开文件，处理完成再存储文件，其实不是很经济的事。

一个好的解决方式是使用轻量级的数据库程序当作存储媒体，未来我们可以使用数据库语法取得此数据库的部分有用数据，这将是一个很好的想法。本章笔者将介绍如何使用Python建立SQLite数据库，同时也将讲解使用Python插入(insert)、提取(select)、更新(update)、删除(delete)SQLite数据库的内容。

Python 3.x版安装完成后有内附SQLite数据库，这一章将以此为实例讲解，在使用此SQLite前需要导入此SQLite。

 import sqlite3

26-2　安装SQLite数据库

执行Python与数据库连接方法如下：

 conn = sqlite3.connect(“数据库名称”)

上述conn是笔者取的对象名称，读者也可以自行取不一样的名称。上述connect( )方法执行时，如果connect( )内的数据库名称存在，就可以将此Python程序与此数据库名称建立连接，然后我们可以在Python程序内做更进一步的操作。如果数据库名称不存在，就会以此为名称建立一个新的数据库，然后执行数据库连接。

数据库操作结束，我们可以在Python内使用下列方法结束Python程序与数据库的连接。

 conn.close( )

程序实例ch26_1.py：建立一个新的数据库myData.db，笔者习惯使用db当扩展名。

# ch26_1.py
import sqlite3
conn = sqlite3.connect("myData.db")
conn.close()

执行结果

26-3　SQLite数据类型

SQLite数据库内的数据可以是下列类型。

26-4　建立SQLite数据库表

在26-2节我们可以使用connect( )方法建立数据库连接，这时会回传connect对象，笔者在该节使用conn存储此所回传的对象，这个对象可以使用下列常用的方法。

下列是cursor对象的方法。

其实上述execute( )所使用的是SQL数据库指令，下列将以实例解说。

程序实例ch26_2.py：建立一个数据库data26_2.db，此数据库内有一个表，表名称是students。

# ch26_2.py
import sqlite3
conn = sqlite3.connect("data26_2.db")   # 数据库连接
cursor = conn.cursor()
sql = '''Create table students(  
        id int,
        name text,
        gender text)'''
cursor.execute(sql)                     # 执行SQL指令
cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

上述第4行是建立cursor对象，第5～8行是一个字符串，这是SQL语法字符串，意义是建立students表，这个表有3个字段，分别是id、name、gender，每个字段也设定它的数据类型，分别是整数、字符串、字符串。第9行是执行此SQL语法字符串，经上述设定后相当于在data26_2.db的数据库文件内有students表，这个表有3个字段。

需特别注意是，上述ch26_2.py执行完后，如果重复执行会产生students表已经存在的错误，如下所示：

也就是当我们已经在数据库建立表时，无法重新建立相同的表，这样可以防止因为重新建立造成原先的数据库表遗失。

其实除了上述使用cursor( )方法建立对象，然后再启动execute( )方法外，我们也可以省略建立cursor( )方法建立cursor对象的步骤，可以参考下列实例。

程序实例ch26_3.py：省略cursor( )方法建立cursor对象，重新设计ch26_2.py。另外这个程序所建的数据库名称是myInfo.db，未来几节我们将持续使用此数据库。

# ch26_3.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
sql = '''Create table students(  
        id int,
        name text,
        gender text)'''
conn.execute(sql)                       # 执行SQL指令
conn.close()                            # 关闭数据库连接

执行结果　此程序会建立myInfo.db文件。

上述虽然省略了建立cursor对象，其实系统内部有建立一个隐含的cursor对象，协助程序可以继续执行。

26-5　增加SQLite数据库表纪录

在SQL语法中可以使用INSERT指令增加表数据，这个表数据我们称记录(record)，它的相关语法用法可以参考下列实例。

程序实例ch26_4.py：读者可以由屏幕输入students表的内容，笔者将键盘输入内容建立成一个循环，每笔记录输入完成后，按N键可以让输入结束。

# ch26_4.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
print("请输入myInfo数据库students窗体数据")
while True:
    new_id = int(input("请输入id : "))  # 转成整数
    new_name = input("请输入name : ")
    new_gender = input("请输入gender : ")
    x = (new_id, new_name, new_gender)
    sql = '''INSERT into students values(?,?,?)'''  
    cursor.execute(sql,x)
    conn.commit()                       # 更新数据库
    again = input("继续(y/n)? ")
    if again[0].lower() == "n":
        break
cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

请输入myInfo数据库students窗体数据
请输入id : 1
请输入name : A
请输入gender : M
继续(y/n)? y
请输入id : 2
请输入name : B
请输入gender : F
继续(y/n)? y
请输入id : 3
请输入name : C
请输入gender : M
继续(y/n)? n

上述程序第7～9行是读取表记录，插入表最重要的语法格式如下：

上述可以将每笔记录处理成元组(tuple)，然后将SQL语法处理成字符串，最后将元组与字符串当作execute( )方法的参数。

其实在真实世界建立表时，最重要的关键词段id并不一定是数字，甚至更多时候是使用字符串，这时id输入时可以使用001, 002……方式，本实例笔者使用整数int，主要目的是丰富此表的数据类型。

26-6　查询SQLite数据库表

查询表的关键词是SELECT，下列是列出所有表的SQL语法。

 SELECT * from students

程序实例ch26_5.py：列出所有students表属性。

# ch26_5.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
results = cursor.execute("SELECT * from students")
for record in results:
    print("id = ", record[0])
    print("name = ", record[1])
    print("gender = ", record[2])

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

id =  1
name =  A
gender =  M
id =  2
name =  B
gender =  F
id =  3
name =  C
gender =  M

在sqlite3模块内有fetchall( )方法，这个方法可以将所获得的学生数据存储到元组内，可以参考下列实例。

程序实例ch26_6.py：以元组方式列出所有查询到的学生数据。

# ch26_6.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
results = cursor.execute("SELECT * from students")
allstudents = results.fetchall()        # 结果转成元组
for student in allstudents:
    print(student)

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

(1, 'A', 'M')
(2, 'B', 'F')
(3, 'C', 'M')

如果查询数据时，只想列出部分字段数据，在使用SELECT时可以直接列出域名取代“*”符号。

程序实例ch26_7.py：重新设计ch26_6.py，只列出name字段数据。

# ch26_7.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
results = cursor.execute("SELECT name from students")
allstudents = results.fetchall()        # 结果转成元组
for student in allstudents:
    print(student)

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

('A',)
('B',)
('C',)

上述如果要列出2个字段或更多字段数据，可以将第5行的name旁边增加字段即可。如果想要查询符合条件的表属性，则SQL语法如下，为了简单化笔者将此语法字符串分行解说：

 ‘“SELECT 字段, …
  from 表
  where 条件'''

程序实例ch26_8.py：查询所有女生的记录(record)，此程序只列出name和gender字段。

# ch26_8.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
sql = '''SELECT name, gender
        from students
        where gender = "F"'''
results = cursor.execute(sql)
allstudents = results.fetchall()        # 结果转成元组
for student in allstudents:
    print(student)

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

('B', 'F')

26-7　更新SQLite数据库表记录

更新SQLite表记录的关键词是UPDATE，SQL语法如下，为了简单化笔者将此语法字符串分行解说：

 ‘“UPDATE 表
  set 字段＝新内容
  where 标明那一笔记录'''

上述完成后记得需要使用commit( )更新数据库。

程序实例ch26_9.py：将id为1的记录name名字改为Tomy。

# ch26_9.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
sql = '''UPDATE students
        set name = "Tomy"
        where id = 1'''
results = cursor.execute(sql)
conn.commit()                           # 更新数据库

results = cursor.execute("SELECT name from students")
allstudents = results.fetchall()        # 结果转成元组
for student in allstudents:
    print(student)

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

('Tomy',)
('B',)
('C',)

26-8　删除SQLite数据库表记录

删除SQLite表记录的关键词是DELETE，SQL语法如下，为了简单化笔者将此语法字符串分行解说：

 ‘“DELETE
  from 表
  where 标明是哪一笔记录'''

上述完成后记得需要使用commit( )更新数据库。

程序实例ch29_10.py：删除id=2的记录。

# ch26_10.py
import sqlite3
conn = sqlite3.connect("myInfo.db")     # 数据库连接
cursor = conn.cursor()
sql = '''DELETE
        from students
        where id = 2'''
results = cursor.execute(sql)
conn.commit()                           # 更新数据库

results = cursor.execute("SELECT name from students")
allstudents = results.fetchall()        # 结果转成元组
for student in allstudents:
    print(student)

cursor.close()                          # 关闭
conn.close()                            # 关闭数据库连接

执行结果

('Tomy',)
('C',)