sqlite3常见操作

数据表样例

id	name	age
1	张三	16

查询数据

import sqlite3

DB_PATH = 'sqlite文件路径'

conn = sqlite3.connect(DB_PATH)
# 查询方式一：获取表中所有列的数据
cur = conn.execute('SELECT * FROM table_name')
# 查询方式二：获取表中某几列的数据
cur = conn.execute('SELECT name,age FROM table_name')
# 查询方式三：根据一个查询条件获取表中某几列的数据
query = (10,)
cur = conn.execute('SELECT name,age FROM table_name WHERE age > ?',query)
# 查询方式三：根据多个查询条件获取表中某几列的数据
query = (10,'张三')
cur = conn.execute('SELECT name,age FROM table_name WHERE age > ? and name = ?',query)

# 获取查询到的数据
values = cur.fetchall()
print(values)
cur.close()
conn.close()

插入一行数据

import sqlite3

DB_PATH = 'sqlite文件路径'
# manyData可以是二维列表、元组或者迭代器
data = ('张三',16)
conn = sqlite3.connect(DB_PATH)
conn.execute('INSERT INTO table_name (name, age) VALUES (?, ?)', data)
conn.commit()
conn.close()

插入多行数据

import sqlite3

DB_PATH = 'sqlite文件路径'

# manyData可以是二维列表、元组或者迭代器
manyData = [('张三',16),('李四',17),('王五',18)]

# 方法一：
conn = sqlite3.connect(DB_PATH)
for x in data:
	conn.execute('INSERT INTO table_name (name, age) VALUES (?, ?)', x)
# commit放在for循环外面，可以减少提交次数，显著提升插入速度。
conn.commit()
conn.close()


#方法二：
conn = sqlite3.connect(DB_PATH)
conn.executemany('INSERT INTO table_name (name, age) VALUES (?, ?)', manyData)
conn.commit()
conn.close()

更新数据

import sqlite3

DB_PATH = 'sqlite文件路径'
# manyData可以是二维列表、元组或者迭代器
conn = sqlite3.connect(DB_PATH)

# 根据条件更新某行数据
data = (16,'张三')
conn.execute('UPDATE table_name SET age = ? WHERE name = ?',query)
conn.commit()
conn.close()

删除数据

import sqlite3

DB_PATH = 'sqlite文件路径'
# manyData可以是二维列表、元组或者迭代器
conn = sqlite3.connect(DB_PATH)
# 删除表中所有行数据
conn.execute('DELETE FROM table_name')
# 根据条件删除某行数据
query = (10,)
conn.execute('DELETE FROM table_name WHERE age > ?',query)
conn.commit()
conn.close()