Mercurial > repos > yufei-luo > s_mart
diff smart_toolShed/commons/core/sql/DbSQLite.py @ 0:e0f8dcca02ed
Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data.
author | yufei-luo |
---|---|
date | Thu, 17 Jan 2013 10:52:14 -0500 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/smart_toolShed/commons/core/sql/DbSQLite.py Thu Jan 17 10:52:14 2013 -0500 @@ -0,0 +1,173 @@ +import sqlite3 +import os +import sys + +#TODO: update...compare with DbMySql.py +class DbSQLite(object): + + ## Constructor + # + # @param host string db file path + # @param cfgFileName string configuration file name + # + # @note when a parameter is left blank, the constructor is able + # to set attribute values from environment variable: REPET_HOST, + # + def __init__(self, host = ""): + if host != "": + self.host = host + else: + msg = "ERROR: no host specified" + sys.stderr.write( "%s\n" % msg ) + sys.exit(1) + # remove open() and cursor from init() use directly outside this class ... + self.open() + self.cursor = self.db.cursor() + + ## Connect to the DbSQLite database + # + # @param verbose integer (default = 0) + # + def open( self, verbose = 0, nb = 0 ): + try: + #sqlite.connect(":memory:", check_same_thread = False) + self.db = sqlite3.connect(self.host, check_same_thread= False, isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES) + except sqlite3.Error, e: + if verbose > 0: + print "ERROR %s" % e + sys.stdout.flush() + return False + return True + + ## Execute a SQL query + # + # @param qry string SQL query to execute + # @param params parameters of SQL query + # + def execute( self, qry, params=None ): + try : + if params == None: + self.cursor.execute( qry ) + else: + self.cursor.execute( qry, params ) + except Exception, e: + #TODO Must be test + try : + if params == None: + self.cursor.execute( qry ) + else: + self.cursor.execute( qry, params ) + except Exception, e: + print "Erreur : %s" % e + + ## Retrieve the results of a SQL query + # + def fetchall(self): + return self.cursor.fetchall() + + ## Record a new table in the 'info_table' table + # + # @param tableName string table name + # @param info string information on the table origin + # + def updateInfoTable( self, tableName, info ): + if not self.doesTableExist( "info_tables" ): + sqlCmd = "CREATE TABLE info_tables ( name varchar(255), file varchar(255) )" + self.execute( sqlCmd ) + sqlCmd = 'INSERT INTO info_tables VALUES ("%s","%s")' % (tableName, info) + self.execute( sqlCmd ) + + def createTable(self, tableName, dataType, overwrite=False, verbose=0): + if verbose > 0: + print "creating table '%s' from file '%s' of type '%s'..." % (tableName, dataType) + sys.stdout.flush() + if overwrite: + self.dropTable(tableName) + if dataType.lower() in ["job", "jobs"]: + self.createJobTable(tableName) + else: + print "ERROR: unknown type %s" % (dataType) + self.close() + sys.exit(1) + if verbose > 0: + print "done!"; sys.stdout.flush() + + ## Create a job table + # + # @param tablename new table name + # + def createJobTable( self, tablename ): + sqlCmd = "CREATE TABLE %s" % ( tablename ) + sqlCmd += " ( jobid INT UNSIGNED" + sqlCmd += ", jobname VARCHAR(255)" + sqlCmd += ", groupid VARCHAR(255)" + sqlCmd += ", command TEXT" + sqlCmd += ", launcher VARCHAR(1024)" + sqlCmd += ", queue VARCHAR(255)" + sqlCmd += ", status VARCHAR(255)" + sqlCmd += ", time timestamp" + sqlCmd += ", node VARCHAR(255) )" + self.execute( sqlCmd ) + + self.updateInfoTable( tablename, "job table" ) + sqlCmd = "CREATE INDEX igroupid ON " + tablename + " ( groupid )" + self.execute( sqlCmd ) + + ## Test if a table exists + # + # @param table string table name + # @return boolean True if the table exists, False otherwise + # + def doesTableExist( self, table ): + qry = "PRAGMA table_info(%s)" % (table) + self.execute( qry ) + results = self.cursor.fetchall() + if results: + return True + return False + + def isEmpty( self, tableName ): + return self.getSize( tableName ) == 0 + + ## Give the rows number of the table + # + # @param tableName string table name + # + def getSize( self, tableName ): + qry = "SELECT count(*) FROM %s;" % ( tableName ) + self.execute( qry ) + res = self.fetchall() + return int( res[0][0] ) + + ## Remove a table if it exists + # + # @param table string table name + # @param verbose integer (default = 0) + # + def dropTable( self, table, verbose = 0 ): + if self.doesTableExist( table ): + sqlCmd = "DROP TABLE %s" % ( table ) + self.execute( sqlCmd ) + sqlCmd = 'DELETE FROM info_tables WHERE name = "%s"' % ( table ) + self.execute( sqlCmd ) + + ## Get a list with the fields + # + def getFieldList( self, table ): + lFields = [] + sqlCmd = "PRAGMA table_info(%s)" % ( table ) + self.execute( sqlCmd ) + lResults = self.fetchall() + for res in lResults: + lFields.append( res[1] ) + return lFields + + ## delete this SQLite database session + # + def delete(self): + os.remove(self.host) + + ## Close the connection + # + def close( self ): + self.db.close()