It’s a bit long time that i haven’t dealed this such of jobs until i met the related issue today. and it’s very necessary to do something marked here i think.
here i consoldated some useful operation points for SQLite with under python environment, and hopefully it would be useful for your reference -
Let’s take a look what on earth the operations need to be highlighted here -
1. 创建数据库及其表单-
# cursor - 游标对象用于执行查询和获取结果
# 注意游标方法 fechone() / fechmany(3) / fechall() 的用法
import sqlite3
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = '''create table students (
Name text,
Sex text,
Age int,
ID int,
Address text,
Tel text)
'''
cursor.execute(sql)
cursor.close()1.1 复制上面的表单来创建第二个表单，因不能创建同名的两个表单
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "create table students2 as SELECT * from students" # 按照students表单来创建新的表单students2， 即复制students表单并命名为students2
cursor.execute(sql)
cursor.close()1.2 向数据库表单中插入一条记录也可以这样写，请注意和上面1.1中的写法区别 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = '''insert into students(Name, Sex, Age, ID, Address, Tel)
VALUES ('Alan.Yuan', 'Male', '38', '19993600002', '中国上海浦东新区张江高科技园区IBM中国研发中心', '13800000000')
'''
cursor.execute(sql)
conn.commit()
## 使用另一个游标来查看执行结果，是值得推荐的一种做法，因为用同一个游标有时会带来不准确的结果
print("\n")
curson2 = conn.cursor()
sql = "select * from students"
results = curson2.execute(sql)
for each in results:
print(each)1.3 判断数据库中是否存在指定的表单 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'students' " # 可以通过查询 sqlite_master 这个系统表来实现。下面 sql 执行后，判断返回的 count。如果 count 为 0 则说明查询的表不存在，大于 0 则说明存在。
results = cursor.execute(sql)
if results.rowcount <= 0:
print(results.rowcount)
# cursor.execute("alter table userInfo rename to students")2. 向数据库中添加记录 -
conn = sqlite3.connect('pioltRun_student.db')
cursor = conn.cursor()
print("Let's input some students records.")
while True:
var1 = input("Please enter student's Name: ")
var2 = input("Please enter student's Sex: ")
var3 = input("Please enter student's Age: ")
var4 = input("Please enter student's ID: ")
var5 = input("Please enter student's Address: ")
var6 = input("Please enter student's Tel: ")
# 注意sql语句中的具名变量和table字段的对应关系，在sql中的注释符号是 -- 或者 /* */
sql = '''insert into students (Name, Sex, Age, ID, Address, Tel) -- 这一行是指明哪个table表，哪些字段需要插入记录
VALUES (:st_name, :st_sex, :st_age, :st_id, :st_address, :st_tel) /* 这行语句里的变量叫具名参数，需要和上面的字段一一对应，参数名自定义，目的就是它告诉SQLite库，在这里有我们想要插入的一个项。
注意前面的冒号：不能少*/
'''
cursor.execute(sql, {'st_name':var1, 'st_sex': var2, 'st_age': var3, 'st_id': var4, 'st_address': var5, 'st_tel': var6})
conn.commit() # 提交数据记录到数据库，如果没有这一步，用户输入的数据会丢失；注意提交是conn变量而不是游标变量
cont = input("Another student?")
if cont[0].lower() == 'n':
break
cursor.close()3. 向数据库中查询数据记录 -
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students"
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.1 向数据库中查询数据记录使用where条件查询 -
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE SEX = 'Female' " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.2 向数据库中查询数据记录使用where条件查询 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE SEX != 'Female' " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.3 向数据库中查询数据记录使用like模糊匹配 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE SEX LIKE 'Fe%' " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.4 向数据库中查询数据记录使用like模糊匹配 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE SEX NOT LIKE 'Fe%' " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.5 向数据库中查询数据记录使用> 或 <条件过滤 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE Age = 36" # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.6 向数据库中查询数据记录使用ORDER BY 子句进行排序 DESC 逆序排序 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE Age <= 38 ORDER BY Name DESC " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.7 向数据库中查询数据记录使用ORDER BY 子句进行排序，不带DESC顺序排序 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select * from students WHERE Age <= 38 ORDER BY Name " # sqlite 的column大小写不敏感
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.8 向数据库中查询数据记录使用Distinct关键字去重 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "select DISTINCT * from students ORDER BY Name " # 请注意distinct的位置，必须放置在select后面，搜索字段的前面，请务必注意
results = cursor.execute(sql)
all_students = results.fetchall()
for each in all_students:
print(each)3.9 修改数据库表中的记录，使用Update关键字 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "update students set age=26 where age=38" # 请注意distinct的位置，必须放置在select后面，搜索字段的前面，请务必注意
results = cursor.execute(sql)
conn.commit()
conn.close()3.10 删除数据库表中的记录，使用Delete关键字 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "delete from students where name like 'yuan%' " # 请注意distinct的位置，必须放置在select后面，搜索字段的前面，请务必注意
results = cursor.execute(sql)
conn.commit()
curson2 = conn.cursor()
sql = "select * from students"
results = curson2.execute(sql)
for each in results:
print(each)
conn.close()3.11 针对表单的操作（修改表名，添加字段，去除字段-不能drop操作，可以复制的表单并生成新的表单来实现，删除表单等操作实现）
####### 修改表名 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "alter table students rename to userInfo" # 请注意distinct的位置，必须放置在select后面，搜索字段的前面，请务必注意
results = cursor.execute(sql)
conn.commit()
conn.close()给数据库表添加字段 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "alter table students add COLUMN Remark text" # remark可以加引号，也可以不加，不影响结果
# sql = "alter table students drop column remark " # SQLite 不支持这个删除字段的操作，本写法没有错误；我们可以通过复制原表单并且去除需要删除的字段来方法来进行
cursor.execute(sql)
conn.commit()
conn.close()删除表单 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "drop table students3" # remark可以加引号，也可以不加，不影响结果
# sql = "alter table students drop column remark " # SQLite 不支持这个删除字段的操作，本写法没有错误；我们可以通过复制原表单并且去除需要删除的字段来方法来进行
cursor.execute(sql)
conn.commit()
conn.close()删除数据库 -
SQLite 并不支持删除一个数据库，所以没有相应的语法，事实上这么做没有实际意义，请看下面官方文档说明
SQLite does not use the DROP DATABASE statement like many other database management systems do.
SQLite stores its databases as a normal file within the computer’s file system, so creating and dropping databases is not really applicable.
但可以直接删除db文件来删除数据库 -import os path = 'myPioltRunDatabase.db' if os.path.exists(path): os.remove(path) # os.unlink(path) ## 和remove函数功能相同，unlink是remove在unix中的叫法 print("The file {} is deleted successfully!" .format(path)) else: print("Sorry, there is no file {} being there!" .format(path))
查询表单结构 -
print("\n")
conn = sqlite3.connect("pioltRun_student.db")
cursor = conn.cursor()
sql = "pragma table_info ('students2')" # 注意后面的括号（）不可少, 表名带不带引号无所谓
# sql = "alter table students drop column remark " # SQLite 不支持这个删除字段的操作，本写法没有错误；我们可以通过复制原表单并且去除需要删除的字段来方法来进行
results = cursor.execute(sql)
# conn.commit()
for each in results:
print(each)
conn.close()
