class MongoDBDatabase(BaseDatabase): def __new__(cls, db, *args, **kwargs): obj = BaseDatabase.__new__(cls, db, *args, **kwargs) obj.name = 'mongo' # obj.connect() # obj.cursor = obj.engine return obj def connect(self): if self.connected: return self.engine try: self.kwargs['database'] = self.db self.engine = MongoClient(host="localhost", database="hritik", user="******", password="******") self._connected = True return self.engine except pymongo.OperationalError as e: raise DatabaseException(e) def close(self): if self.connected: self.cursor.close() self.engine.close() self._connected = False def commit(self): self.engine.commit() def execute(self, query, *args, **kwargs): self.cursor.execute(query, *args)
class dbMongoDB(): def __init__(self, host='127.0.0.1', port=27017): try: self.db = MongoClient(host=host, port=port).wren except Exception as e: print e def initdb(self): return def regcond(self, cond_id, key, cond): self.db[self.__TAB_KEY_MAP].find({'key': key}) self.c.execute('select key_id from tab_key_map where key = ?', (key, )) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map, %s' % (key) return None key_id = res[0] self.c.execute( 'select cond_id from tab_cond where key_id = ? and cond = ?', (key_id, cond)) if self.c.fetchone() != None: print '%s is already registered with %s for %s' % (cond, cond_id, key) return None self.c.execute('insert into tab_cond values (?, ?, ?)', (cond_id, key_id, cond)) self.db.commit() def docond(self, cond_id): self.c.execute('select key_id, cond from tab_cond where cond_id = ?', (cond_id, )) res = self.c.fetchone() if res == None: print 'no such cond_id %s' % (cond_id) return [] key_id = res[0] cond = eval(res[1]) # XXX need to be revied. return self.select_direct(key_id, cond, 10) def makekey(self, key, key_id): self.c.execute('select key from tab_key_map where key = ?', (key, )) if self.c.fetchone() != None: print '%s is already registered with %s' % (key, key_id) return self.c.execute('insert into tab_key_map values (?, ?)', (key_id, key)) arg = 'create table %s (time text, value text)' % (key_id, ) self.c.execute(arg) self.db.commit() def insert(self, key, ts, value): self.c.execute('select key_id from tab_key_map where key = ?', (key, )) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map %s' % (key, ) return None key_id = res[0] arg = 'insert into %s (time, value) values (?, ?)' % (key_id, ) self.c.execute(arg, (ts, value)) self.db.commit() # # @return list of values # def select(self, key, cond, cursor_size): self.c.execute('select key_id from tab_key_map where key = ?', (key, )) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map %s' % (key) return [] key_id = res[0] return self.select_direct(key_id, cond, cursor_size) # # @return the latest record # def select_latest(self, key_id): arg = 'select time, value from %s order by time desc limit 1' % ( key_id, ) self.c.execute(arg) return self.c.fetchmany() # # @note the memory allocation issue needs to be considered # def select_direct(self, key_id, cond, cursor_size): m = mongoParseQuery.mongoParseQuery() arg = 'select time, value from %s where %s' % (key_id, m.parse_cond(cond)) self.c.execute(arg) return self.c.fetchmany(size=cursor_size)
class dbMongoDB(): def __init__(self, host='127.0.0.1', port=27017): try: self.db = MongoClient(host=host, port=port).wren except Exception as e: print e def initdb(self): return def regcond(self, cond_id, key, cond): self.db[self.__TAB_KEY_MAP].find({'key': key}) self.c.execute('select key_id from tab_key_map where key = ?', (key,)) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map, %s' % (key) return None key_id = res[0] self.c.execute('select cond_id from tab_cond where key_id = ? and cond = ?', (key_id, cond)) if self.c.fetchone() != None: print '%s is already registered with %s for %s' % (cond, cond_id, key) return None self.c.execute('insert into tab_cond values (?, ?, ?)', (cond_id, key_id, cond)) self.db.commit() def docond(self, cond_id): self.c.execute('select key_id, cond from tab_cond where cond_id = ?', (cond_id,)) res = self.c.fetchone() if res == None: print 'no such cond_id %s' % (cond_id) return [] key_id = res[0] cond = eval(res[1]) # XXX need to be revied. return self.select_direct(key_id, cond, 10) def makekey(self, key, key_id): self.c.execute('select key from tab_key_map where key = ?', (key,)) if self.c.fetchone() != None: print '%s is already registered with %s' % (key, key_id) return self.c.execute('insert into tab_key_map values (?, ?)', (key_id, key)) arg = 'create table %s (time text, value text)' % (key_id,) self.c.execute(arg) self.db.commit() def insert(self, key, ts, value): self.c.execute('select key_id from tab_key_map where key = ?', (key,)) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map %s' % (key,) return None key_id = res[0] arg = 'insert into %s (time, value) values (?, ?)' % (key_id,) self.c.execute(arg, (ts, value)) self.db.commit() # # @return list of values # def select(self, key, cond, cursor_size): self.c.execute('select key_id from tab_key_map where key = ?', (key,)) res = self.c.fetchone() if res == None: print 'no such key in tab_key_map %s' % (key) return [] key_id = res[0] return self.select_direct(key_id, cond, cursor_size) # # @return the latest record # def select_latest(self, key_id): arg = 'select time, value from %s order by time desc limit 1' % (key_id,) self.c.execute(arg) return self.c.fetchmany() # # @note the memory allocation issue needs to be considered # def select_direct(self, key_id, cond, cursor_size): m = mongoParseQuery.mongoParseQuery() arg = 'select time, value from %s where %s' % (key_id, m.parse_cond(cond)) self.c.execute(arg) return self.c.fetchmany(size=cursor_size)
class DatabaseHulk: def __init__(self, dbCredentials): self.dbType = dbCredentials['dbtype'] if self.dbType != 'mongo' and self.dbType != 'mysql': exit('Database type not supported: ', self.dbType) if (self.dbType == 'mongo'): self.db = MongoClient() self.db = self.db[dbCredentials['dbname']] if (self.dbType == 'mysql'): self.db = MySQLdb.connect( host=dbCredentials['host'], user=dbCredentials['user'], passwd=dbCredentials['password'], db=dbCredentials['dbname'], cursorclass=MySQLdb.cursors.SSCursor # this cursor allows one by one fetching # TODO: implement fetching one by one - http://kushaldas.in/posts/fetching-row-by-row-from-mysql-in-python.html ) def createTable( self, newTableName, vars ): # drop table if already exists self.dropTable(newTableName) if (self.dbType == 'mongo'): # TODO: mongo create table exit('NON HAI SCRITTO IL CODICE PER CREARE LA TABELLA IN MONGO') if (self.dbType == 'mysql'): query = 'CREATE TABLE ' + newTableName + ' ( ' for name in vars: if not 'type' in vars[name]: vars[name]['type'] = 'text' query += str(name) + ' ' + vars[name]['type'] + ', ' query = query[:-2] #take away last comma query += ');' # execute creation self.db.cursor().execute( query ) # save changes self.db.commit() def cloneTable( self, originTableName, newTableName, empty=False ): # drop table if already exists self.dropTable(newTableName) if (self.dbType == 'mongo'): # TODO: mongo clone table exit('NON HAI SCRITTO IL CODICE PER CLONARE LA TABELLA IN MONGO') if (self.dbType == 'mysql'): query = 'CREATE TABLE ' + newTableName + ' LIKE ' + originTableName # execute creation self.db.cursor().execute( query ) self.emptyTable(newTableName) # save changes self.db.commit() def dropTable(self, table): if (self.dbType == 'mongo'): # TODO: mongo drop table exit('NON HAI SCRITTO IL CODICE PER CANCELLARE LA TABELLA IN MONGO') if (self.dbType == 'mysql'): # delete data from table query = 'DROP TABLE IF EXISTS ' + table self.db.cursor().execute(query) self.db.commit() def emptyTable(self, table): if (self.dbType == 'mongo'): # TODO: mongo clean table exit('NON HAI SCRITTO IL CODICE PER SVUOTARE LA TABELLA IN MONGO') if (self.dbType == 'mysql'): # delete data from table query = 'TRUNCATE TABLE ' + table self.db.cursor().execute(query) self.db.commit() def insertRecord( self, table, record, commit=True ): # extract keys and values keys = str( tuple( record.keys() ) ) values = str( tuple( record.values() ) ) # take away ' from columns names keys = keys.replace("'", '') if (self.dbType == 'mysql'): # TODO: mysql gives error when there is only one key and value. # build and execute query query = 'INSERT into ' + table + ' ' + keys + ' VALUES ' + values self.db.cursor().execute( query ) # commit=False is used for fast insertion in myql. # in that case using code should call DatabaseHulk.commit() by hand from outside. if( commit ): self.db.commit() if (self.dbType == 'mongo'): # TODO: mongo insert record exit('NON HAI SCRITTO IL CODICE PER INSERIRE IL RECORD IN MONGO') def updateRecord( self, table, newValues, identifiers, commit=True ): if (self.dbType == 'mysql'): # build and query query = 'UPDATE ' + table + ' SET ' for columnName in newValues: query += '`' + columnName + '`="' + str(newValues[columnName]) + '", ' # take away last ', ' query = query[:-2] query += ' WHERE ' for columnName in identifiers: query += '`' + columnName + '`="' + str(identifiers[columnName]) + '" AND ' # take away last 'AND' query = query[:-4] self.db.cursor().execute( query ) # commit=False is used for fast insertion in myql. # in that case using code should call DatabaseHulk.commit() by hand from outside. if( commit ): self.db.commit() if (self.dbType == 'mongo'): # TODO: mongo update record exit('NON HAI SCRITTO IL CODICE PER AGGIORNARE IL RECORD IN MONGO') def commit(self): # TODO: is it necessary for mongo? self.db.commit() def getFromSQL(self, query, getAsDictionary=False): # get data from db cursor = self.db.cursor() cursor.execute(query) data = cursor.fetchall() # get also column names columnNames = [] for col in cursor.description: columnNames.append(col[0]) cursor.close() # clean data (take them away from tuples) return self.cleanSQLdata(data, getAsDictionary, columnNames) def cleanSQLdata(self, data, getAsDictionary, columnNames): cleandata = [] for row in data: if len(row) > 1: cleanrow = [] if getAsDictionary: columnCounter = 0 cleanrow = {} # we need a dict for element in row: if getAsDictionary: columnName = columnNames[columnCounter] cleanrow[columnName] = self.convertToIntIfItIsLong(element) columnCounter += 1 else: cleanrow.append(self.convertToIntIfItIsLong(element)) else: cleanrow = self.convertToIntIfItIsLong(row[0]) cleandata.append(cleanrow) return cleandata def convertToIntIfItIsLong(self, n): # TODO: this method will cause issues if the number is actually long if type(n) is long: return int(n) return n def getDistinctValues(self, table, variable): if (self.dbType == 'mongo'): return self.db[table].distinct(variable) if (self.dbType == 'mysql'): query = 'SELECT DISTINCT `' + variable + '` FROM ' + table return self.getFromSQL(query) def count(self, table, constraints={}): if (self.dbType == 'mongo'): return self.db[table].find(constraints).count() if (self.dbType == 'mysql'): query = 'SELECT COUNT(*) FROM ' + table + ' WHERE' for k, v in constraints.items(): query += ' `' + str(k) + '`="' + str(v) + '" AND' # take away last 'AND' if constraints != {}: query = query[:-4] else: query = query[:-5] print query return self.getFromSQL(query)[0] def getRecords(self, table, constraints={}): if (self.dbType == 'mongo'): return self.db[table].find(constraints, timeout=False) # TODO: we should close the cursor manually after deactivating the timeout if (self.dbType == 'mysql'): query = 'SELECT * FROM ' + table + ' WHERE' for k, v in constraints.items(): query += ' `' + str(k) + '`="' + str(v) + '" AND' # take away last 'AND' query = query[:-4] return self.getFromSQL(query, getAsDictionary=True)
print("connected") except: print("I am unable to connect to the database") cur = conn.cursor() reader = csv.reader(open('data.csv', 'r')) dataText = ','.join( cur.mogrify( "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", tuple(row)) for i, row in enumerate(reader)) cur.execute('INSERT INTO "apple_sensor" VALUES ' + dataText) conn.commit() cur.close() print("Data loaded successfully ... ") #dataText = ','.join( cur.mogrify('(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', tuple(row)) for i,row in enumerate(reader)) #cur.execute('INSERT INTO apple_sensor_test VALUES ' + dataText) #print dataText #reader = csv.reader(open('data.csv', 'r')) #for i, row in enumerate(reader): #print(i, row) #if i == 0: continue # cur.execute('''
class pantheraDB: """ Database support """ panthera = None db = None cursor = None dbType = None escapeFunc = None def __init__(self, panthera): """ Initialize connection """ self.panthera = panthera self.panthera.logging.output("Initializing pantheraDB", "pantheraDB") # Create some default configuration keys self.panthera.config.getKey('databaseHost', 'localhost') self.panthera.config.getKey('databasePassword', '') self.panthera.config.getKey('databaseUser', 'root') self.panthera.config.getKey('databaseDB', 'my-database') self.panthera.config.getKey('databaseFile', self.panthera.filesDir+"/db.sqlite3") if not "MySQLdb" in globals() and not "sqlite3" in globals() and not "peewee" in globals(): self.panthera.logging.output("No MySQL or SQLite3 driver found", "pantheraDB") sys.exit(1) try: dbType = self.panthera.config.getKey('databaseType', 'orm').lower() ### # # Peewee ORM database # ### if dbType == 'orm': if not "peewee" in globals(): self.panthera.logging.output("Peewee module not found, but orm database type selected", "pantheraDB") sys.exit(1) if self.panthera.config.getKey("databaseSocketType", "sqlite3") == "sqlite3": self.db = db = SqliteDatabase(self.panthera.config.getKey('databaseFile', self.panthera.filesDir+"/db.sqlite3")) class BaseModel(Model): class Meta: database = db self.BaseModel = BaseModel self.dbType = "peewee" ### # # SQLite3 native support # ### elif dbType == 'sqlite3': self.db = sqlite3.connect(self.panthera.config.getKey('databaseFile'), check_same_thread=False) self.db.row_factory = dict_factory self.cursor = self.db.cursor() self.dbType = "sqlite3" self.escapeFunc = quoteIdentifier ### # # MySQL (MySQLdb driver) # ### elif dbType == 'mysql': self.db = MySQLdb.connect( host=self.panthera.config.getKey('databaseHost'), user=self.panthera.config.getKey('databaseUser'), passwd=self.panthera.config.getKey('databasePassword'), db=self.panthera.config.getKey('databaseDB'), cursorclass=MySQLdb.cursors.DictCursor ) # escape function self.escapeFunc = self.db.escape_string self.cursor = self.db.cursor() self.dbType = "mysql" ### # # MongoDB # ### elif dbType == 'mongodb': self.db = MongoClient(str(self.panthera.config.getKey('databaseHost')), int(self.panthera.config.getKey('databasePort', 27017))) if self.panthera.config.getKey('databaseUser') and self.panthera.config.getKey('databasePassword'): self.db.the_database.authenticate(self.panthera.config.getKey('databaseUser'), self.panthera.config.getKey('databasePassword')) self.dbType = "mongodb" else: self.panthera.logging.output("Unknown database driver \'"+str(self.panthera.config.getKey('databaseType'))+"\'", "pantheraDB") sys.exit(1) self.panthera.logging.output("Connection estabilished using "+self.dbType+" socket", "pantheraDB") except Exception as e: self.panthera.logging.outputException("Cannot connect to database: "+str(e), "pantheraDB") sys.exit(1) def query(self, query, values=dict(), commit=True): """ Execute a raw query """ ### # Append database prefix and insert values into a query ### # {$db_prefix} insertion support query = query.replace('{$db_prefix}', str(self.panthera.config.getKey('databasePrefix', 'pa_'))) originalQuery = query # inserting escaped values into query query, values = self.applyValues(query, values) if query.strip() != originalQuery.strip(): self.panthera.logging.output("Original: " + query, "pantheraDB") self.panthera.logging.output("SQL: "+ query, "pantheraDB") ### # Make q query and return a resultset ### if self.dbType == "peewee": return self.db.execute_sql(query, values) elif self.dbType == "sqlite3": obj = self.cursor.execute(query, values) if commit: self.db.commit() return pantheraDBSQLite3ResultSet(obj, self.cursor) elif self.dbType == 'mysql': obj = self.cursor.execute(query, values) if commit: self.db.commit() return pantheraDBMySQLResultSet(self.cursor, self, obj) elif self.dbType == 'mongodb': raise Exception('query() is not supported by MongoDB database type') else: self.panthera.logging.output("Cannot connect to databse via unknown socket", "pantheraDB") sys.exit(1) def applyValues(self, query, values): """ Append values from dict to query string """ newValues = [] for value in values: pos = query.find(':' + value) while pos != -1: query = query[:pos] + '?' + query[pos + len(':' + value):] newValues.append(values[value]) pos = query.find(':' + value) return query, newValues