Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 715:a05f79c74d6d dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged default with dev
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Sat, 25 Jul 2015 22:36:39 -0400
parents d6c69d3d09e5
children 2cd245cb780d
comparison
equal deleted inserted replaced
709:29daabe094fe 715:a05f79c74d6d
29 'deletes the table with names in tableNames' 29 'deletes the table with names in tableNames'
30 try: 30 try:
31 cursor = connection.cursor() 31 cursor = connection.cursor()
32 for tableName in tableNames: 32 for tableName in tableNames:
33 cursor.execute('DROP TABLE IF EXISTS '+tableName) 33 cursor.execute('DROP TABLE IF EXISTS '+tableName)
34 except sqlite3.OperationalError as error:
35 printDBError(error)
36
37 def createIndex(connection, tableName, columnName, unique = False):
38 '''Creates an index for the column in the table
39 I will make querying with a condition on this column faster'''
40 try:
41 #connection = sqlite3.connect(filename)
42 cursor = connection.cursor()
43 s = "CREATE "
44 if unique:
45 s += "UNIQUE "
46 cursor.execute(s+"INDEX IF NOT EXISTS "+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")")
47 connection.commit()
48 #connection.close()
49 except sqlite3.OperationalError as error:
50 printDBError(error)
51
52 def getNumberRowsTable(connection, tableName, columnName = None):
53 '''Returns the number of rows for the table
54 If columnName is not None, means we want the number of distinct values for that column
55 (otherwise, we can just count(*))'''
56 try:
57 cursor = connection.cursor()
58 if columnName is None:
59 cursor.execute("SELECT COUNT(*) from "+tableName)
60 else:
61 cursor.execute("SELECT COUNT(DISTINCT "+columnName+") from "+tableName)
62 return cursor.fetchone()[0]
63 except sqlite3.OperationalError as error:
64 printDBError(error)
65
66 def getMinMax(connection, tableName, columnName, minmax):
67 '''Returns max/min or both for given column in table
68 minmax must be string max, min or minmax'''
69 try:
70 cursor = connection.cursor()
71 if minmax == 'min' or minmax == 'max':
72 cursor.execute("SELECT "+minmax+"("+columnName+") from "+tableName)
73 elif minmax == 'minmax':
74 cursor.execute("SELECT MIN("+columnName+"), MAX("+columnName+") from "+tableName)
75 else:
76 print("Argument minmax unknown: {}".format(minmax))
77 return cursor.fetchone()[0]
34 except sqlite3.OperationalError as error: 78 except sqlite3.OperationalError as error:
35 printDBError(error) 79 printDBError(error)
36 80
37 # TODO: add test if database connection is open 81 # TODO: add test if database connection is open
38 # IO to sqlite 82 # IO to sqlite
320 logging.debug(queryStatement) 364 logging.debug(queryStatement)
321 elif trajectoryType == 'object': 365 elif trajectoryType == 'object':
322 queryStatement = 'SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id' 366 queryStatement = 'SELECT OF.object_id, P.frame_number, avg(P.x_coordinate), avg(P.y_coordinate) from '+tableName+' P, objects_features OF where P.trajectory_id = OF.trajectory_id'
323 if objectNumbers is not None: 367 if objectNumbers is not None:
324 queryStatement += ' and OF.object_id '+objectCriteria 368 queryStatement += ' and OF.object_id '+objectCriteria
325 queryStatement += ' group by OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number' 369 queryStatement += ' GROUP BY OF.object_id, P.frame_number ORDER BY OF.object_id, P.frame_number'
326 cursor.execute(queryStatement) 370 cursor.execute(queryStatement)
327 logging.debug(queryStatement) 371 logging.debug(queryStatement)
328 elif trajectoryType in ['bbtop', 'bbbottom']: 372 elif trajectoryType in ['bbtop', 'bbbottom']:
329 if trajectoryType == 'bbtop': 373 if trajectoryType == 'bbtop':
330 corner = 'top_left' 374 corner = 'top_left'
331 elif trajectoryType == 'bbbottom': 375 elif trajectoryType == 'bbbottom':
332 corner = 'bottom_right' 376 corner = 'bottom_right'
333 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName 377 queryStatement = 'SELECT object_id, frame_number, x_'+corner+', y_'+corner+' FROM '+tableName
334 if objectNumbers is not None: 378 if objectNumbers is not None:
335 queryStatement += ' where object_id '+objectCriteria 379 queryStatement += ' WHERE object_id '+objectCriteria
336 queryStatement += ' ORDER BY object_id, frame_number' 380 queryStatement += ' ORDER BY object_id, frame_number'
337 cursor.execute(queryStatement) 381 cursor.execute(queryStatement)
338 logging.debug(queryStatement) 382 logging.debug(queryStatement)
339 else: 383 else:
340 print('no trajectory type was chosen') 384 print('no trajectory type was chosen')