python sqlite3,读取视频信息并存入数据库

要操作关系数据库，首先需要连接到数据库，建立connection。

其次打开游标cursor，通过cursor执行SQL语句。

导入sqlite驱动：

import sqlite3

1、refresh sqlite3：

def RefreshDB(self, path):
		#delete the table created before and then create a new table to refresh the database
		self.CreateDB()
		filepathList = GetMediaInfo.GetFilePathList(path)
		InfoList = GetMediaInfo.GetInfoList(filepathList)
		GetMediaInfo.InsertInfo(InfoList)

2、create

def CreateDB(self):

		conn = sqlite3.connect('./MediaInfo.db')　＃链接ｄｂ文件
		cursor = conn.cursor()

		if os.path.exists('./MediaInfo.db'):　
		# delete the table existed
			cursor.executescript('drop table if exists MediaInfo;')
		#create a new table
			cursor.execute('create table MediaInfo (name varchar(20) primary key,\
					fps float(8), framenum float(8),duration float(8),\
					width int(8), height varchar(8))')
		else:
			cursor.execute('create table MediaInfo (name varchar(20) primary key,\
					fps float(8), framenum float(8),duration float(8),\
					width int(8), height varchar(8))')

3、获取视频文件列表：

def GetFilePathList(path):

	filepathList = []
	MediaDBPath = path
	MediaDBPath = MediaDBPath.replace('\\','/')
	for root, dirs, files, in os.walk(MediaDBPath):
		for filename in files:
			# filepath = root + '/' + filename
			filepath = os.path.join(root,filename)
			filepath = filepath.replace('\\','/')
			if os.path.isfile(filepath):
				filepathList.append(filepath) #filepath: the fullpath of file
	return filepathList 

4、获取视频信息

def GetInfoList(filepathList):
	t = (6,)
	InfoList = []

	for MediaFile in filepathList:
		videocapture = cv2.VideoCapture(MediaFile) #用videocapture获取视频信息
		if videocapture.isOpened():

			folder, name = str(MediaFile).split('MediaDB')

			if name[0] == '/':
				name = name[1:]# using name as key

			fps = videocapture.get(cv2.cv.CV_CAP_PROP_FPS)#frames per second
			framenum = videocapture.get(cv2.cv.CV_CAP_PROP_FRAME_COUNT) 
			duration = framenum / fps 
			size = (int(videocapture.get(cv2.cv.CV_CAP_PROP_FRAME_WIDTH)), \
						int(videocapture.get(cv2.cv.CV_CAP_PROP_FRAME_HEIGHT)))

			width = size[0]
			height = size[1]

			t = (name, fps, framenum, duration, width, height)
			InfoList.append(t)

	return InfoList

5、insert

def InsertInfo(InfoList):

	conn = sqlite3.connect('MediaInfo.db')#建立连接
	cursor = conn.cursor()#打开游标

	for t in InfoList:
		# print "----------t:",t
		cursor.execute('insert into MediaInfo values (?,?,?,?,?,?)', t) ￥#执行

	cursor.close()#关闭游标
	conn.commit()#提交事务
	conn.close()#关闭connection

6、fetch

def fetchone(data):

	conn = sqlite3.connect('MediaInfo.db')
	cursor = conn.cursor()

	cursor.execute('select * from MediaInfo where name=?', (data,))

	values =cursor.fetchall()

	cursor.close()
	conn.close()
	return values

备注：

Cursor对象执行：insert、update、delete、

执行select时，fetchall可拿到结果集，结果集为list，每个元素为tuple，对应一行记录。

connection 和cursor打开后记得关闭，否则资源会泄露。