Mercurial Hosting > traffic-intelligence
comparison python/storage.py @ 714:d6c69d3d09e5
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 |