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 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 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
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
