Mercurial > hg > nsaunier > traffic-intelligence

comparison python/storage.py @ 714:d6c69d3d09e5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
developing python performance tests for SQLite
author Nicolas Saunier <nicolas.saunier@polymtl.ca>
date Fri, 24 Jul 2015 17:58:59 -0400
parents 21aeadcfbabb
children 2cd245cb780d
comparison
equal deleted inserted replaced
713:b0cb644950aa 714:d6c69d3d09e5
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: 34 except sqlite3.OperationalError as error:
35 printDBError(error) 35 printDBError(error)
36 36
37 def createIndex(filename, tableName, columnName, createAnyway = False): 37 def createIndex(connection, tableName, columnName, unique = False):
38 '''Creates an index for the column in the table 38 '''Creates an index for the column in the table
39 I will make querying with a condition on this column faster''' 39 I will make querying with a condition on this column faster'''
40 try: 40 try:
41 connection = sqlite3.connect(filename) 41 #connection = sqlite3.connect(filename)
42 cursor = connection.cursor() 42 cursor = connection.cursor()
43 if createAnyway: 43 s = "CREATE "
44 notExists = "" 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)
45 else: 60 else:
46 notExists = "IF NOT EXISTS " 61 cursor.execute("SELECT COUNT(DISTINCT "+columnName+") from "+tableName)
47 cursor.execute("CREATE INDEX "+notExists+tableName+"_"+columnName+"_index ON "+tableName+"("+columnName+")") 62 return cursor.fetchone()[0]
48 connection.commit() 63 except sqlite3.OperationalError as error:
49 connection.close() 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]
50 except sqlite3.OperationalError as error: 78 except sqlite3.OperationalError as error:
51 printDBError(error) 79 printDBError(error)
52 80
53 # TODO: add test if database connection is open 81 # TODO: add test if database connection is open
54 # IO to sqlite 82 # IO to sqlite