Mercurial > repos > yufei-luo > s_mart

comparison commons/core/sql/DbSQLite.py @ 6:769e306b7933

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Change the repository level.
author yufei-luo
date Fri, 18 Jan 2013 04:54:14 -0500
parents
children
comparison
equal deleted inserted replaced
5:ea3082881bf8 6:769e306b7933
1 import sqlite3
2 import os
3 import sys
4
5 #TODO: update...compare with DbMySql.py
6 class DbSQLite(object):
7
8 ## Constructor
9 #
10 # @param host string db file path
11 # @param cfgFileName string configuration file name
12 #
13 # @note when a parameter is left blank, the constructor is able
14 # to set attribute values from environment variable: REPET_HOST,
15 #
16 def __init__(self, host = ""):
17 if host != "":
18 self.host = host
19 else:
20 msg = "ERROR: no host specified"
21 sys.stderr.write( "%s\n" % msg )
22 sys.exit(1)
23 # remove open() and cursor from init() use directly outside this class ...
24 self.open()
25 self.cursor = self.db.cursor()
26
27 ## Connect to the DbSQLite database
28 #
29 # @param verbose integer (default = 0)
30 #
31 def open( self, verbose = 0, nb = 0 ):
32 try:
33 #sqlite.connect(":memory:", check_same_thread = False)
34 self.db = sqlite3.connect(self.host, check_same_thread= False, isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES)
35 except sqlite3.Error, e:
36 if verbose > 0:
37 print "ERROR %s" % e
38 sys.stdout.flush()
39 return False
40 return True
41
42 ## Execute a SQL query
43 #
44 # @param qry string SQL query to execute
45 # @param params parameters of SQL query
46 #
47 def execute( self, qry, params=None ):
48 try :
49 if params == None:
50 self.cursor.execute( qry )
51 else:
52 self.cursor.execute( qry, params )
53 except Exception, e:
54 #TODO Must be test
55 try :
56 if params == None:
57 self.cursor.execute( qry )
58 else:
59 self.cursor.execute( qry, params )
60 except Exception, e:
61 print "Erreur : %s" % e
62
63 ## Retrieve the results of a SQL query
64 #
65 def fetchall(self):
66 return self.cursor.fetchall()
67
68 ## Record a new table in the 'info_table' table
69 #
70 # @param tableName string table name
71 # @param info string information on the table origin
72 #
73 def updateInfoTable( self, tableName, info ):
74 if not self.doesTableExist( "info_tables" ):
75 sqlCmd = "CREATE TABLE info_tables ( name varchar(255), file varchar(255) )"
76 self.execute( sqlCmd )
77 sqlCmd = 'INSERT INTO info_tables VALUES ("%s","%s")' % (tableName, info)
78 self.execute( sqlCmd )
79
80 def createTable(self, tableName, dataType, overwrite=False, verbose=0):
81 if verbose > 0:
82 print "creating table '%s' from file '%s' of type '%s'..." % (tableName, dataType)
83 sys.stdout.flush()
84 if overwrite:
85 self.dropTable(tableName)
86 if dataType.lower() in ["job", "jobs"]:
87 self.createJobTable(tableName)
88 else:
89 print "ERROR: unknown type %s" % (dataType)
90 self.close()
91 sys.exit(1)
92 if verbose > 0:
93 print "done!"; sys.stdout.flush()
94
95 ## Create a job table
96 #
97 # @param tablename new table name
98 #
99 def createJobTable( self, tablename ):
100 sqlCmd = "CREATE TABLE %s" % ( tablename )
101 sqlCmd += " ( jobid INT UNSIGNED"
102 sqlCmd += ", jobname VARCHAR(255)"
103 sqlCmd += ", groupid VARCHAR(255)"
104 sqlCmd += ", command TEXT"
105 sqlCmd += ", launcher VARCHAR(1024)"
106 sqlCmd += ", queue VARCHAR(255)"
107 sqlCmd += ", status VARCHAR(255)"
108 sqlCmd += ", time timestamp"
109 sqlCmd += ", node VARCHAR(255) )"
110 self.execute( sqlCmd )
111
112 self.updateInfoTable( tablename, "job table" )
113 sqlCmd = "CREATE INDEX igroupid ON " + tablename + " ( groupid )"
114 self.execute( sqlCmd )
115
116 ## Test if a table exists
117 #
118 # @param table string table name
119 # @return boolean True if the table exists, False otherwise
120 #
121 def doesTableExist( self, table ):
122 qry = "PRAGMA table_info(%s)" % (table)
123 self.execute( qry )
124 results = self.cursor.fetchall()
125 if results:
126 return True
127 return False
128
129 def isEmpty( self, tableName ):
130 return self.getSize( tableName ) == 0
131
132 ## Give the rows number of the table
133 #
134 # @param tableName string table name
135 #
136 def getSize( self, tableName ):
137 qry = "SELECT count(*) FROM %s;" % ( tableName )
138 self.execute( qry )
139 res = self.fetchall()
140 return int( res[0][0] )
141
142 ## Remove a table if it exists
143 #
144 # @param table string table name
145 # @param verbose integer (default = 0)
146 #
147 def dropTable( self, table, verbose = 0 ):
148 if self.doesTableExist( table ):
149 sqlCmd = "DROP TABLE %s" % ( table )
150 self.execute( sqlCmd )
151 sqlCmd = 'DELETE FROM info_tables WHERE name = "%s"' % ( table )
152 self.execute( sqlCmd )
153
154 ## Get a list with the fields
155 #
156 def getFieldList( self, table ):
157 lFields = []
158 sqlCmd = "PRAGMA table_info(%s)" % ( table )
159 self.execute( sqlCmd )
160 lResults = self.fetchall()
161 for res in lResults:
162 lFields.append( res[1] )
163 return lFields
164
165 ## delete this SQLite database session
166 #
167 def delete(self):
168 os.remove(self.host)
169
170 ## Close the connection
171 #
172 def close( self ):
173 self.db.close()