SQLite Python

发布于 2015-07-25 11:42:20 | 550 次阅读 | 评论: 0 | 来源: 网络整理

sqlite3 可以与Python sqlite3 模块集成是由格哈德·哈林（Gerhard Haring）编写。 PEP249所描述的DB-API2.0规范，它提供了一个SQL接口兼容。不需要单独安装这个模块，因为它默认情况下随着Python版本在2.5.x 一起安装。

要使用sqlite3模块，必须首先创建一个连接对象，表示数据库中，然后可以选择创建游标对象，这将帮助在执行的所有SQL语句。

Python sqlite3 模块API

以下是重要的sqlite3模块程序，它可以足够Python程序SQLite数据库操作工作。如果要寻找一个更复杂的应用程序，那么你可以看看成的Python sqlite3 模块的官方文档。

连接到数据库

Python代码显示了如何连接到一个现有的数据库。如果数据库不存在，那么它就会被创建，终于将返回一个数据库对象。

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully";

在 这里，您还可以提供特殊的名字 :memory: 在RAM中创建一个数据库的数据库名称。现在，让我们运行上面的程序在当前目录中创建数据库 test.db。按要求，你可以改变路径。上面的代码在sqlite.py文件并执行它，如下图所示。如果数据库创建成功，那么它会给以下消息：

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

创建表

以下Python程序将使用以前创建的数据库中创建一个表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.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.close()

上述程序执行时，它会创建表COMPANYtest.db的，它会显示以下消息：

Opened database successfully
Table created successfully

INSERT 操作

Python程序显示了我们如何能够创建表COMPANY 在上面的例子中创建表中的记录：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

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

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

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

conn.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()

上述程序执行时，它会创建COMPANY表中的记录，并显示以下两行：

Opened database successfully
Records created successfully

SELECT 操作

Python程序，表明我们如何能够获取并显示COMPANY 在上面的例子中创建表的记录：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

cursor = conn.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()

当上述程序执行时，它会产生以下结果：

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

UPDATE 操作

Python代码显示如何，我们可以使用UPDATE语句来更新任何记录，然后获取并显示更新的记录，从COMPANY 表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit
print "Total number of rows updated :", conn.total_changes

cursor = conn.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()

当上述程序执行时，它会产生以下结果：

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

DELETE 操作

Python代码显示了如何我们可以使用DELETE语句删除任何记录，然后获取并显示剩余的记录COMPANY 表：

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print "Opened database successfully";

conn.execute("DELETE from COMPANY where ID=2;")
conn.commit
print "Total number of rows deleted :", conn.total_changes

cursor = conn.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()

当上述程序执行时，它会产生以下结果：

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully