- SQLite快速入门
- SQLite是什么？
- SQLite历史
- SQLite特性/为什么要使用SQLite？
- SQLite优点和缺点
- SQLite安装
- SQLite命令大全
- SQLite语法大全
- SQLite数据类型
- SQLite运算符
- SQLite表达式
- 数据库和表
- CURD操作
- 子句和条件
- 连接操作
- SQLite时间日期
- 聚合函数
- SQLite触发器
- SQLite主键
- SQLite导出导入
- SQLite连接程序
Python连接SQLite数据库
由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");
在这里，还可以提供数据库名称作为特殊名称: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()
当执行上述程序后，将在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()
当执行上述程序时，它将在py-sqlite.db数据库中创建company表，并显示以下消息：
Opened database successfully Table created successfully
插入操作
以下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()
当执行上述程序时，它将在COMPANY表中插入给定的数据记录，并显示以下结果：
Opened database successfully Table created successfully Records Insert successfully
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()
执行上述程序时，会产生以下结果：
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.
更新操作
以下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()
执行上述程序时，会产生以下结果：
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.
删除操作
以下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()
执行上面语句后，得到以下结果 -
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.
这里只是简单演示如何使用Python来连接SQLite实现CURD操作，关于其它更复杂的操作，建议参考官方API和实例，这里就不是一一讲解了，如有问题欢迎留言。
上一篇：PHP连接SQLite数据库
下一篇：没有了
关注微信小程序
扫描二维码
程序员编程王
