python3 sqlite3 Operation of Constraint Query

Please note 10, 11, 24 lines of code, is the essence of this blog, logic is not difficult, is some small grammar problems are tangled, record 1.

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")

Supplement: python + sqlite3 Basic Operations

Connect to a database

import sqlite3 
#  Connect to a database ( If it does not exist, create )
conn = sqlite3.connect('test.db')
print("Opened database successfully")
 
#  Create a cursor 
cursor = conn.cursor() 
...
 
#  Close the cursor 
cursor.close()
#  Submit things 
conn.commit()
#  Close the connection 
conn.close()

Create a table

...
#  Create a cursor 
cursor = conn.cursor()
 
#  Create a table 
sql = 'CREATE TABLE Student(id integer PRIMARY KEY autoincrement, Name varchar(30), Age integer)'
cursor.execute(sql)
 
#  Submit things 
conn.commit()
...

Insert data

...
#  Create a cursor 
cursor = conn.cursor()
 
#  Insert data 
sql = "INSERT INTO Student(Name, Age) VALUES(\'love\', 22)"
cursor.execute(sql)
 
#  Insert data  2
data = ('love2', 2221) # or ['love2', 2221]
sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"
cursor.execute(sql, data)
 
#  Submit things 
conn.commit()
...

Query data

...
#  Create a cursor 
cursor = conn.cursor()
 
#  Query data 
sql = "select * from Student"
values = cursor.execute(sql)
for i in values:
 print(i)
 
#  Query data  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])
 
#  Submit things 
conn.commit()
...

Other operations

Starting position of self-increasing field

#  Set the starting value to 1
update sqlite_sequence SET seq = 0 where name = ' Table name ';
#  Set all table starting values to default values 
delete from sqlite_sequence where name='TableName'; -- Note that table names are case sensitive