def __init__(self, confname, dbname=None, drop=False, exit=True): self.drop = drop self.confname = confname self.exit = exit ### if database name is not config name, e.g., ap212 and appiondata if dbname is not None: self.dbname = dbname dbconf = dbconfig.setConfig(self.confname, db=dbname) else: ### get database config from sinedon.cfg dbconf = dbconfig.getConfig(self.confname) self.dbname = dbconf['db'] dbconf = dbconfig.getConfig(self.confname) if messaging['success'] is True: print "\033[32mconnected to db '%s' on server '%s'\033[0m" % ( dbconf['db'], dbconf['host']) ### connect to db db = MySQLdb.connect(**dbconf) db.autocommit(True) ### create cursor self.cursor = db.cursor() self.defid = 'int(20) NOT NULL auto_increment' self.link = 'int(20) NULL DEFAULT NULL' self.int = 'int(20) NULL DEFAULT NULL' self.bool = 'tinyint(1) NULL DEFAULT 0' self.str = 'text NULL DEFAULT NULL' self.float = 'double NULL DEFAULT NULL' self.timestamp = 'timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'
def __init__(self, confname, dbname=None, drop=False, exit=True): self.drop = drop self.confname = confname self.exit = exit ### if database name is not config name, e.g., ap212 and appiondata if dbname is not None: self.dbname = dbname dbconf = dbconfig.setConfig(self.confname, db=dbname) else: ### get database config from sinedon.cfg dbconf = dbconfig.getConfig(self.confname) self.dbname = dbconf['db'] dbconf = dbconfig.getConfig(self.confname) if messaging['success'] is True: print "\033[32mconnected to db '%s' on server '%s'\033[0m"%(dbconf['db'], dbconf['host']) ### connect to db db = MySQLdb.connect(**dbconf) db.autocommit(True) ### create cursor self.cursor = db.cursor() self.defid = 'int(20) NOT NULL auto_increment' self.link = 'int(20) NULL DEFAULT NULL' self.int = 'int(20) NULL DEFAULT NULL' self.bool = 'tinyint(1) NULL DEFAULT 0' self.str = 'text NULL DEFAULT NULL' self.float = 'double NULL DEFAULT NULL' self.timestamp = 'timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'
def fromFormat(dataclass, alias=None): dbname = dbconfig.getConfig(dataclass.__module__)['db'] tablename = dataclass.__name__ sqlfrom = "FROM %s.%s " % (backquote(dbname), backquote(tablename)) if alias is not None: sqlfrom += "AS %s " % backquote(alias) return sqlfrom
def backupDatabase(self, filename, data=False): if os.path.isfile(filename): print "\033[31merror file exists for backup\033[0m" if self.exit is True: sys.exit(1) return False dbconf = dbconfig.getConfig(self.confname) print dbconf import subprocess cmd = ( "mysqldump --host=%s --user=%s --skip-lock-tables --extended-insert" % (dbconf['host'], dbconf['user'])) if 'passwd' in dbconf: cmd += " --password=%s" % (dbconf['passwd']) if data is False: cmd += " --no-data" cmd += " %s > %s" % (self.dbname, filename) proc = subprocess.Popen(cmd, shell=True) proc.wait() if not os.path.isfile(filename): print "\033[31merror failed to backup database\033[0m" if self.exit is True: sys.exit(1) return False if messaging['success'] is True: backtype = "structure" if data is True: backtype = "data" print "\033[32msuccessfully backed up database %s to file %s\033[0m" % ( backtype, filename) return True
def backupDatabase(self, filename, data=False): if os.path.isfile(filename): print "\033[31merror file exists for backup\033[0m" if self.exit is True: sys.exit(1) return False dbconf = dbconfig.getConfig(self.confname) print dbconf import subprocess cmd = ("mysqldump --host=%s --user=%s --skip-lock-tables --extended-insert" %(dbconf['host'], dbconf['user'])) if 'passwd' in dbconf: cmd += " --password=%s"%(dbconf['passwd']) if data is False: cmd += " --no-data" cmd += " %s > %s"%(self.dbname, filename) proc = subprocess.Popen(cmd, shell=True) proc.wait() if not os.path.isfile(filename): print "\033[31merror failed to backup database\033[0m" if self.exit is True: sys.exit(1) return False if messaging['success'] is True: backtype = "structure" if data is True: backtype = "data" print "\033[32msuccessfully backed up database %s to file %s\033[0m"%(backtype, filename) return True
def joinFormat(field, joinTable): dataclass = joinTable['class'] dbname = dbconfig.getConfig(dataclass.__module__)['db'] dbname = backquote(dbname) tablename = backquote(joinTable['class'].__name__) alias = backquote(joinTable['alias']) sqljoin = " JOIN %s.%s AS %s ON (%s = %s.%s) " % (dbname, tablename, alias, field, alias, backquote('DEF_ID')) return sqljoin
def joinFormat(field, joinTable): dataclass = joinTable['class'] dbname = dbconfig.getConfig(dataclass.__module__)['db'] dbname = backquote(dbname) tablename = backquote(joinTable['class'].__name__) alias = backquote(joinTable['alias']) sqljoin = " JOIN %s.%s AS %s ON (%s = %s.%s) " % ( dbname, tablename, alias, field, alias, backquote('DEF_ID')) return sqljoin
def getConnection(modulename): lock.acquire() try: if not isinstance(modulename, str): modulename = modulename.__name__ modulename = tail(modulename) if modulename not in connections: connectedconf = None else: connectedconf = connections[modulename]['config'] dbconf = dbconfig.getConfig(modulename) if dbconf != connectedconf: #print 'MAKING CONNECTION', modulename, dbconf connections[modulename] = {'config': dbconf, 'connection': DB(**dbconf)} connection = connections[modulename]['connection'] finally: lock.release() return connection
def getConnection(modulename): lock.acquire() try: if not isinstance(modulename, str): modulename = modulename.__name__ modulename = tail(modulename) if modulename not in connections: connectedconf = None else: connectedconf = connections[modulename]['config'] dbconf = dbconfig.getConfig(modulename) if dbconf != connectedconf: #print 'MAKING CONNECTION', modulename, dbconf connections[modulename] = { 'config': dbconf, 'connection': DB(**dbconf) } connection = connections[modulename]['connection'] finally: lock.release() return connection
def flatInsert(self, newdata, force=False, skipinsert=False, fail=True): dbname = dbconfig.getConfig(newdata.__module__)['db'] tablename = newdata.__class__.__name__ table = (dbname, tablename) definition, formatedData = sqldict.dataSQLColumns(newdata, fail) ## check for any new columns that have not been created if table not in columns_created: columns_created[table] = {} fields = [d['Field'] for d in definition] for field in formatedData.keys(): if field not in fields: del formatedData[field] create_table = False for field in fields: if field not in columns_created[table]: columns_created[table][field] = None create_table = True if create_table: self.dbd.createSQLTable(table, definition) myTable = self.dbd.Table(table) if skipinsert is True: return None newid = myTable.insert([formatedData], force=force) return newid