python3 sqlite3限制条件查询的操作_Python

python3 sqlite3限制条件查询的操作

2021-10-06 00:072021乐乐 Python

这篇文章主要介绍了python3 sqlite3限制条件查询的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

请注意10,11,24行的代码，是本条博客的精华，逻辑并不难，就是有些小语法问题比较纠结，记录一下。

				?

									import json 

									import sqlite3

									import re

									import argparse

									def Get(db_file):

									    conn = sqlite3.connect(db_file)

									    cur = conn.cursor()

									    print("5555555")

									    value1=(60)# this is must be ()

									    cur.execute("select * from exception where AGV_ID=(%s)" %(value1))

									    #cursor.execute("insert into exception values('%s', '%s','%s' ) " %(start_time ,ID ,infomation))

									    result= cur.fetchall()

									    print("result:",result)

									    for i in result:

									       print(i)  

									    print("******************************888")

									def get_agv_id(db_file):

									  try:

									    conn = sqlite3.connect(db_file)

									    cur = conn.cursor()

									    cur.execute("select * from exception where AGV_ID=51")

									    #print( cur.fetchall())

									    result= cur.fetchall()

									    for i in result:

									       print(i)

									  except sqlite3.Error,e:

									    print(e)

									if __name__ == '__main__': 

									  parser = argparse.ArgumentParser(description='check the information of db')

									  #parser.add_argument('-h', '--help', help='Statistics for abnormal information')

									  parser.add_argument('-n', '--name', help=' the db of name ')

									  args = vars(parser.parse_args())

									  db_name = args['name']

									  print("db_name:",db_name)

									  conn = sqlite3.connect('db_name')

									  cursor = conn.cursor()

									  Get('fitkits.db')

									  get_agv_id('fitkits.db')  

									  conn.commit()

									  conn.close() 

									  print('DONE!')

									  print("666")

补充：python + sqlite3 基本操作

连接数据库

				?

									import sqlite3 

									# 连接数据库(如果不存在则创建)

									conn = sqlite3.connect('test.db')

									print("Opened database successfully")

									# 创建游标

									cursor = conn.cursor() 

									...

									# 关闭游标

									cursor.close()

									# 提交事物

									conn.commit()

									# 关闭连接

									conn.close()

创建表

				?

									...

									# 创建游标

									cursor = conn.cursor()

									# 创建表

									sql = 'CREATE TABLE Student(id integer PRIMARY KEY autoincrement, Name varchar(30), Age integer)'

									cursor.execute(sql)

									# 提交事物

									conn.commit()

									...

插入数据

				?

									...

									# 创建游标

									cursor = conn.cursor()

									# 插入数据

									sql = "INSERT INTO Student(Name, Age) VALUES(\'love\', 22)"

									cursor.execute(sql)

									# 插入数据 2

									data = ('love2', 2221) # or ['love2', 2221]

									sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"

									cursor.execute(sql, data)

									# 提交事物

									conn.commit()

									...

查询数据

				?

									...

									# 创建游标

									cursor = conn.cursor()

									# 查询数据

									sql = "select * from Student"

									values = cursor.execute(sql)

									for i in values:

									 print(i)

									# 查询数据 2

									sql = "select * from Student where id=?"

									values = cursor.execute(sql, (1,))

									for i in values:

									 print('id:', i[0])

									 print('name:', i[1])

									 print('age:', i[2])

									# 提交事物

									conn.commit()

									...

其他操作

自增字段起始位置

				?

									# 设置起始值为1

									update sqlite_sequence SET seq = 0 where name = '表名';

									# 设置全部表起始值为默认值

									delete from sqlite_sequence where name='TableName'; --注意表名区分大小写