def insertDB(information):
# add ID
ID = lastID() + 1
# print ID
IDinfo = []
for indx, item in enumerate(information):
# print "id:", indx, "item", item
IDinfo.append((ID + indx, ) + item)
conn = sqlite3.connect(local_config.get('core', 'data_base_file'))
insertmt = 'INSERT INTO fileInfo VALUES(?, ?, ?, ?, ?, ?, ?)'
# print IDinfo
cursor = conn.cursor()
cursor.execute("begin transaction")
try:
cursor.executemany(insertmt, IDinfo)
except OperationalError as e:
if "database is locked" in e:
print("fun<insertDB>", "OperationalError :", e)
insertDB(information)
if "no such table" in e:
print("OperationalError :", e)
cursor.execute(query)
cursor.executemany(insertmt, IDinfo)
conn.commit()
# except Exception as e:
# print "fun<insertDB>", e
finally:
conn.commit()
cursor.close()
conn.close()
def getAllInf():
"""
get all information from the data base
"""
con = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
cursor = con.cursor()
cursor = con.execute("SELECT * FROM fileInfo")
rows = cursor.fetchall()
return rows
def initDB():
conn = sqlite3.connect(local_config.get('core', 'data_base_file'))
try:
cursor = conn.cursor()
cursor.execute(query)
cursor.execute("CREATE index fileInfoIndex on fileInfo(id) ")
conn.commit()
# except Exception:
# pass
finally:
conn.close()
return
def clearDB():
con = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
delQuery = "DELETE from fileInfo"
try:
cursor = con.cursor()
cursor.execute(delQuery)
con.commit()
# except Exception as e:
# print "fun<clearDB>", e
finally:
con.close()
def get_record_by_id(ID):
"""
get the record with certain ID
"""
con = sqlite3.connect(local_config.get('core', 'data_base_file'))
try:
cursor = con.cursor()
cursor.execute("SELECT * FROM fileInfo WHERE id = {}".format(ID))
rows = cursor.fetchall()
finally:
cursor.close()
con.close()
return rows
def lastID():
conn = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
ID = 0
try:
cursor = conn.cursor()
cursor.execute("SELECT * FROM fileInfo ORDER BY id DESC limit 1")
rows = cursor.fetchall()
if rows:
ID = rows[0][0]
finally:
cursor.close()
conn.close()
return ID
def delByID(Id):
"""
Args:
Id: the Index the the one creod
return:
void
"""
con = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
try:
cursor = con.cursor()
delQuery = "DELETE from fileInfo WHERE id=?"
cursor.execute(delQuery, (Id, ))
con.commit()
# except Exception as e:
# print "fun<delByID>", e
finally:
con.close()
def getLastRecord(n, condition=''):
"""
get the record of last #n
"""
con = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
cursor = con.cursor()
sel_commond = "SELECT * FROM fileInfo"
if condition:
sel_commond += " WHERE {} ".format(condition)
sel_commond += "ORDER BY id DESC limit {}".format(n)
# print("condition {}".format(condition))
# print("execute {}".format(sel_commond))
cursor.execute(sel_commond)
rows = cursor.fetchall()
cursor.close()
con.close()
return rows
Id: the Index the the one creod
return:
void
"""
con = sqlite3.connect(local_config.get('core', 'data_base_file'),
timeout=30.0)
try:
cursor = con.cursor()
delQuery = "DELETE from fileInfo WHERE id=?"
cursor.execute(delQuery, (Id, ))
con.commit()
# except Exception as e:
# print "fun<delByID>", e
finally:
con.close()
# initi the fileInfo.config.db
if not os.path.isfile(local_config.get('core', 'data_base_file')):
initDB()
if __name__ == "__main__":
if not os.path.isfile(local_config.get('core', 'data_base_file')):
initDB()
print(lastID())
exit()
for inf in getAllInf():
print(inf)
print(lastID())
print(get_record_by_id(1))