def __init__(self, cfg, dbname): object.__init__(self) self.conn = QuickConn(cfg) self.cursor = StatementCursor(self.conn, name='AdminConnection') self.stmt = Statement('select') self.dbname = dbname self.set_path(cfg.get('database', 'export_path'))
def select_multisuite_union(suites, table, fields=None, clause=None): stmt = Statement() if fields is not None: stmt.fields = fields if clause is not None: stmt.set_clause(clause) stmts = [stmt.select(table='%s_%s' % (s, table)) for s in suites] return ' UNION '.join(stmts)
class AdminConnection(object): def __init__(self, cfg, dbname): object.__init__(self) self.conn = QuickConn(cfg) self.cursor = StatementCursor(self.conn, name='AdminConnection') self.stmt = Statement('select') self.dbname = dbname self.set_path(cfg.get('database', 'export_path')) def set_path(self, directory): self.path = directory makepaths(self.path) os.system('chmod 777 %s' % self.path) def to_tsv(self, table, key=None): self.stmt.table = table query = self.stmt.select(order=key) tsv = file(join(self.path, table + '.tsv'), 'w') self.cursor.execute(query) fields = [x[0] for x in self.cursor.description] tsv.write('\t'.join(map(quote, fields))+'\n') row = self.cursor.fetchone() while row: line = [] for field in row: if field == None: field = 'NULL' else: field = str(field) line.append(quote(field)) tsv.write('\t'.join(line)+'\n') row = self.cursor.fetchone() tsv.close() def set_table(self, table): self.stmt.set_table(table) def insert(self, table): self.cursor.set_table(table) tsv = Parser(join(self.path, table + '.tsv')) for row in tsv: self.cursor.insert(data=row) def copyto(self, table): path = join(self.path, table + '.bkup') self.cursor.copyto(table, path) def copyfrom(self, table): path = join(self.path, table + '.bkup') self.cursor.copyfrom(table, path) def backup(self): map(self.copyto, self.cursor.tables())
class AdminConnection(object): def __init__(self, cfg, dbname): object.__init__(self) self.conn = QuickConn(cfg) self.cursor = StatementCursor(self.conn, name='AdminConnection') self.stmt = Statement('select') self.dbname = dbname self.set_path(cfg.get('database', 'export_path')) def set_path(self, directory): self.path = directory makepaths(self.path) os.system('chmod 777 %s' % self.path) def to_tsv(self, table, key=None): self.stmt.table = table query = self.stmt.select(order=key) tsv = file(join(self.path, table + '.tsv'), 'w') self.cursor.execute(query) fields = [x[0] for x in self.cursor.description] tsv.write('\t'.join(map(quote, fields)) + '\n') row = self.cursor.fetchone() while row: line = [] for field in row: if field == None: field = 'NULL' else: field = str(field) line.append(quote(field)) tsv.write('\t'.join(line) + '\n') row = self.cursor.fetchone() tsv.close() def set_table(self, table): self.stmt.set_table(table) def insert(self, table): self.cursor.set_table(table) tsv = Parser(join(self.path, table + '.tsv')) for row in tsv: self.cursor.insert(data=row) def copyto(self, table): path = join(self.path, table + '.bkup') self.cursor.copyto(table, path) def copyfrom(self, table): path = join(self.path, table + '.bkup') self.cursor.copyfrom(table, path) def backup(self): map(self.copyto, self.cursor.tables())
def approve_disk(self, diskname): clause = Eq('diskname', diskname) workspace = 'partition_workspace' sql = Statement('select') sql.table = workspace sql.clause = clause new_rows = sql.select(order='partition') if diskname not in [r.diskname for r in self.cursor.select(table='disks')]: self.cursor.insert(table='disks', data=dict(diskname=diskname)) else: self.cursor.delete(table='partitions', clause=clause) self.cursor.execute('insert into partitions %s' % new_rows)
def approve_disk(self, diskname): clause = Eq('diskname', diskname) workspace = 'partition_workspace' sql = Statement('select') sql.table = workspace sql.clause = clause new_rows = sql.select(order='partition') if diskname not in [ r.diskname for r in self.cursor.select(table='disks') ]: self.cursor.insert(table='disks', data=dict(diskname=diskname)) else: self.cursor.delete(table='partitions', clause=clause) self.cursor.execute('insert into partitions %s' % new_rows)
def __init__(self, dsn, name, parent=None, objname=None): QSqlDatabase.__init__(self, "QPSQL7", name, parent, objname) self.conn = BasicConnection(**dsn) self.setDatabaseName(dsn["dbname"]) self.setHostName(dsn["host"]) self.dbuser = dsn["user"] self.setUserName(self.dbuser) self.stmt = Statement() self.mcursor = StatementCursor(self.conn)
def __init__(self, conn, name=None): CommandCursor.__init__(self, conn, name=name) self.stmt = Statement('select')
class StatementCursor(CommandCursor): def __init__(self, conn, name=None): CommandCursor.__init__(self, conn, name=name) self.stmt = Statement('select') def set_table(self, table): self.stmt.table = table def set_clause(self, items, cmp='=', join='and'): self.stmt.set_clause(items, cmp=cmp, join=join) def set_data(self, data): self.stmt.set(data) def set_fields(self, fields): self.stmt.fields = fields def delete(self, table=None, clause=None): query = self.stmt.delete(table=table, clause=clause) self.execute(query) def insert(self, table=None, data=None): query = self.stmt.insert(table=table, data=data) self.execute(query) def update(self, table=None, data=None, clause=None): query = self.stmt.update(table=table, data=data, clause=clause) self.execute(query) def select(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) return self.fetchall() def iselect(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) self._already_selected = True return self def select_row(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) rows = len(self) if rows == 1: return self.next() elif rows == 0: raise NoExistError else: raise Error, 'bad row count %s' % rows def delete_file(self, conn, field, clause): row = self.select_row(fields=[field], clause=clause) conn.removefile(int(row[field].name)) def update_file(self, conn, field, clause, fileobj): self.delete_file(conn, field, clause) newfile = self.file(conn) update = {field :newfile.name} newfile.write(fileobj.read()) newfile.close() self.update(data=update, clause=clause) def open_file(self, conn, field, clause): row = self.select_row(fields=[field], clause=clause) return self.openfile(conn, row[field].name) def clear(self, **args): self.stmt.clear(**args)
class StatementCursor(CommandCursor): def __init__(self, conn, name=None): CommandCursor.__init__(self, conn, name=name) self.stmt = Statement('select') def set_table(self, table): self.stmt.table = table def set_clause(self, items, cmp='=', join='and'): self.stmt.set_clause(items, cmp=cmp, join=join) def set_data(self, data): self.stmt.set(data) def set_fields(self, fields): self.stmt.fields = fields def delete(self, table=None, clause=None): query = self.stmt.delete(table=table, clause=clause) self.execute(query) def insert(self, table=None, data=None): query = self.stmt.insert(table=table, data=data) self.execute(query) def update(self, table=None, data=None, clause=None): query = self.stmt.update(table=table, data=data, clause=clause) self.execute(query) def select(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) return self.fetchall() def iselect(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) self._already_selected = True return self def select_row(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.execute(query) rows = len(self) if rows == 1: return self.next() elif rows == 0: raise NoExistError else: raise Error, 'bad row count %s' % rows def delete_file(self, conn, field, clause): row = self.select_row(fields=[field], clause=clause) conn.removefile(int(row[field].name)) def update_file(self, conn, field, clause, fileobj): self.delete_file(conn, field, clause) newfile = self.file(conn) update = {field: newfile.name} newfile.write(fileobj.read()) newfile.close() self.update(data=update, clause=clause) def open_file(self, conn, field, clause): row = self.select_row(fields=[field], clause=clause) return self.openfile(conn, row[field].name) def clear(self, **args): self.stmt.clear(**args) def fields(self, table=None): if table is None: table = self.stmt.table return CommandCursor.fields(self, table) def commit(self): CommandCursor.commit(self)
class BaseDatabase(QSqlDatabase): def __init__(self, dsn, name, parent=None, objname=None): QSqlDatabase.__init__(self, "QPSQL7", name, parent, objname) self.conn = BasicConnection(**dsn) self.setDatabaseName(dsn["dbname"]) self.setHostName(dsn["host"]) self.dbuser = dsn["user"] self.setUserName(self.dbuser) self.stmt = Statement() self.mcursor = StatementCursor(self.conn) def set_table(self, table): self.stmt.table = table def set_clause(self, items, cmp="=", join="and"): self.stmt.set_clause(items, cmp=cmp, join=join) def set_data(self, data): self.stmt.set(data) def set_fields(self, fields): self.stmt.fields = fields def qdelete(self, table=None, clause=None): query = self.stmt.delete(table=table, clause=clause) return self.execStatement(query) def qinsert(self, table=None, data=None): query = self.stmt.insert(table=table, data=data) return self.execStatement(query) def qupdate(self, table=None, data=None, clause=None): query = self.stmt.update(table=table, data=data, clause=clause) return self.execStatement(query) def qselect(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) return self.execStatement(query) def delete(self, table=None, clause=None): query = self.stmt.delete(table=table, clause=clause) return self.mcursor.execute(query) def insert(self, table=None, data=None): query = self.stmt.insert(table=table, data=data) return self.mcursor.execute(query) def update(self, table=None, data=None, clause=None): query = self.stmt.update(table=table, data=data, clause=clause) return self.mcursor.execute(query) def select(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.mcursor.execute(query) return self.mcursor.fetchall() def select_row(self, fields=None, table=None, clause=None, group=None, having=None, order=None): query = self.stmt.select(fields=fields, table=table, clause=clause, group=group, having=having, order=order) self.mcursor.execute(query) rows = len(self.mcursor) if rows == 1: return self.mcursor.next() elif rows == 0: raise NoExistError else: raise Error, "bad row count %s" % rows def clear(self, **args): self.stmt.clear(**args) def stdclause(self, data): return reduce(and_, [Eq(k, v) for k, v in data.items()]) def insertData(self, idcol, table, data, commit=True): clause = self.stdclause(data) try: self.mcursor.select_row(fields=[idcol], table=table, clause=clause) except NoExistError: self.mcursor.insert(table=table, data=data) if commit: self.conn.commit() def identifyData(self, idcol, table, data, commit=True): clause = self.stdclause(data) self.insertData(idcol, table, data, commit=commit) return self.mcursor.select_row(fields=["*"], table=table, clause=clause)
def __init__(self, conn): self.cmd = StatementCursor(conn, name='TableBrowser') CList.__init__(self, 'Tables', name='TableBrowser') self.set_rows(self.cmd.tables(), ['table']) self.set_row_select(self.__hello__) self.statement = Statement('select')
dbdriver = 'QPSQL7' cfg = PaellaConfig('database') app = QApplication(sys.argv) db = QSqlDatabase.addDatabase(dbdriver) if db: db.setHostName(cfg['dbhost']) db.setDatabaseName(cfg['dbname']) db.setUserName(cfg['dbusername']) db.open() else: raise Error, 'bad db' s = Statement() cursor = QSqlCursor(None, True, QSqlDatabase.database(dbdriver, True)) #s.table = 'suites' s.table = 'gunny_templates' #query = QSqlQuery(q) #cursor = query print cursor.execQuery(str(s)) #print q print cursor.size() print dir(cursor) fields = cursor.driver().recordInfo(cursor) for f in fields: cursor.append(f) cursor.setName(s.table) #cursor.setMode(QSqlCursor.ReadOnly) #cursor.execQuery(str(s))