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
