Python连接SQLite数据库

编辑时间: 2018-02-07 13:03:03    关键字:

 由Gerhard Haring编写的sqlite3模块与Python进行集成。 它提供了符合由PEP 249描述的DB-API 2.0规范的SQL接口。所以不需要单独安装此模块,因为默认情况下随着Python 2.5.x以上版本一起发布运行。

要使用sqlite3模块,必须首先创建一个表示数据库的连接对象,然后可以选择创建的游标对象来执行SQL语句。

连接到数据库

以下Python代码显示了如何连接到一个指定的数据库。 如果数据库不存在,那么它将被创建,最后将返回一个数据库对象。

注意:在本示例中,使用的是 python 3.5.1

#!/usr/bin/python  import sqlite3  conn = sqlite3.connect('pydb.db') print ("Opened database successfully"); 
Python

在这里,还可以提供数据库名称作为特殊名称:memory:, 在RAM中创建数据库。 现在,运行上面的程序在当前目录中创建数据库:pydb.db

可以根据需要更改路径。 在F:\worksp\sqlite\py-sqlite.py文件中保留以上代码,并按如下所示执行。 如果数据库成功创建,则会提供以下消息:

创建表

以下Python程序将用于在先前创建的数据库(py-sqlite.py)中创建一个表:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 创建一个表 - company 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() 
Python

当执行上述程序后,将在py-sqlite.db中创建company表,并显示以下消息:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 创建一个表 - company 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() 
Python

当执行上述程序时,它将在py-sqlite.db数据库中创建company表,并显示以下消息:

Opened database successfully Table created successfully 
Shell

插入操作

以下Python程序显示如何在上述示例中创建的COMPANY表中插入数据记录:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 清除已存在的表 - company conn.execute('''DROP TABLE company'''); conn.commit()  ## 创建一个表 - company 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.commit()  ## 插入数据 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");  conn.commit() print ("Records Insert successfully");  conn.close() 
Python

当执行上述程序时,它将在COMPANY表中插入给定的数据记录,并显示以下结果:

Opened database successfully Table created successfully Records Insert successfully 
Shell

SELECT/查询操作

以下Python程序显示如何从上述示例中创建的COMPANY表中获取并显示数据记录:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 清除已存在的表 - company conn.execute('''DROP TABLE company'''); conn.commit()  ## 创建一个表 - company 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.commit()  ## 插入数据 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");  conn.commit() print ("Records Insert successfully"); print ('--------------------------- start fetch data from company --------------------------');  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 ("Select Operation done successfully.");  conn.close() 
Python

执行上述程序时,会产生以下结果:

Opened database successfully Table created successfully Records Insert successfully --------------------------- start fetch data from company -------------------------- ID =  1 NAME =  Maxsu ADDRESS =  Haikou SALARY =  20000.0   ID =  2 NAME =  Allen ADDRESS =  Shenzhen SALARY =  35000.0   ID =  3 NAME =  Weiwang ADDRESS =  Guangzhou SALARY =  22000.0   ID =  4 NAME =  Marklee ADDRESS =  Beijing SALARY =  45000.0   Select Operation done successfully. 
Shell

更新操作

以下Python代码演示如何使用UPDATE语句来更新指定记录,然后再从COMPANY表中获取并显示更新的记录:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 清除已存在的表 - company conn.execute('''DROP TABLE company'''); conn.commit()  ## 创建一个表 - company 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.commit()  ## 插入数据 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");  conn.commit()  ## 更新数据 conn.execute("UPDATE COMPANY set SALARY = 29999.00 where ID=1") conn.commit() print ("Total number of rows updated :", conn.total_changes)   print ("Records Insert successfully"); print ('--------------------------- start fetch data from company --------------------------');  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 ("Select Operation done successfully.");  conn.close() 
Python

执行上述程序时,会产生以下结果:

Opened database successfully Table created successfully Total number of rows updated : 5 Records Insert successfully --------------------------- start fetch data from company -------------------------- ID =  1 NAME =  Maxsu ADDRESS =  Haikou SALARY =  29999.0   ID =  2 NAME =  Allen ADDRESS =  Shenzhen SALARY =  35000.0   ID =  3 NAME =  Weiwang ADDRESS =  Guangzhou SALARY =  22000.0   ID =  4 NAME =  Marklee ADDRESS =  Beijing SALARY =  45000.0   Select Operation done successfully. 
Shell

删除操作

以下Python代码演示如何使用DELETE语句来删除记录,然后从COMPANY表中获取并显示剩余的记录:

#!/usr/bin/python  import sqlite3  ## 打开数据库连接 conn = sqlite3.connect('py-sqlite.db') print ("Opened database successfully");  ## 清除已存在的表 - company conn.execute('''DROP TABLE company'''); conn.commit()  ## 创建一个表 - company 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.commit()  ## 插入数据 conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \       VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");  conn.commit()  ## 删除ID值小于等于2的数据 conn.execute("DELETE from COMPANY where ID<=2;") conn.commit()  print ("Total number of rows updated :", conn.total_changes)   print ("Records Insert successfully"); print ('--------------------------- start fetch data from company --------------------------');  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 ("Select Operation done successfully.");  conn.close() 
Python

执行上面语句后,得到以下结果 -

Opened database successfully Table created successfully Total number of rows updated : 6 Records Insert successfully --------------------------- start fetch data from company -------------------------- ID =  3 NAME =  Weiwang ADDRESS =  Guangzhou SALARY =  22000.0   ID =  4 NAME =  Marklee ADDRESS =  Beijing SALARY =  45000.0   Select Operation done successfully. 
Shell

这里只是简单演示如何使用Python来连接SQLite实现CURD操作,关于其它更复杂的操作,建议参考官方API和实例,这里就不是一一讲解了,如有问题欢迎留言。

推荐热图

合作推荐

2010-2018 可思数据版权所有 About SYKV | ICP备案:京ICP备14056871号