python sqlite 常用命令

前置代码

import sqlite3

con = sqlite3.connect(self.path)
cursorObj = con.cursor()

1、 插入数据

cursorObj.execute("INSERT INTO employees VALUES(?,?,?)",(t1, t2, t3))

2、选取行
sql=“select * from employees order by id desc limit 0,1” #获取最后一行
sql=“select * from employees order by id desc limit 2,2” #从倒数第二行开始获取2个数据
cursorObj.execute(“select * from employees order by id desc limit %d,%d”%(offset,num)) #从最后一行偏移offset获取num个数据

3、查询
cursorObj.execute(“select id from employees where id =9”)

4、查找最大ID
cursorObj.execute(“SELECT MAX(id) FROM employees”)

5、排序-升序/降序
cursorObj.execute(“SELECT * FROM employees ORDER BY id DESC”) #降序
cursorObj.execute(“SELECT * FROM employees ORDER BY id”) #升序

6、查询ID是否存在

id = cursorObj.execute("select id from employees where id = %d"%sIndex)
if id:
	print("存在")
cursorObj.close()

7、按日期搜索数据

def SearchData(StartDate, EndDate):
	cmd = "select * from employees where Date >= '%s' and Date <= '%s' "%(StartDate, EndDate)
	con, cursorObj = self.Connect()
	cursorObj.execute(cmd)
	ret = cursorObj.fetchall()
	return ret

8、批量插入

con.executemany('INSERT INTO employees VALUES (?,?,?,?)',(DataList))

9、#更新数据

cursorObj.execute("UPDATE employees SET Date = '%s' , Data='%s' WHERE id = %d"%(sTime, data, id))  

10、检查表是否存在，不存在则新建一个表 {table_name} 代表你想要检查表的表名，如果表不存在，则返回0，否则返回1，

cmd = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='employees'"
cursorObj.execute(cmd)
ret = cursorObj.fetchall()
if not ret[0][0]:
	cursorObj.execute("CREATE TABLE employees(id integer PRIMARY KEY, Date text, Time text, Data text)")
	con.commit()

注：转载请注明出处