Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 715:a05f79c74d6d dev
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') |