def create(self, grant=None, owner=None): cmds = [] for extra in self.extras: sql = extra.pre_create_sql() if sql is not None: cmds.append(sql) if not self.db.db_has_relation(self.name): col_sql = [] for col in self.columns: col.create_sql(col_sql) if self.primary_keys: pkey_cols = [coldesc.name for coldesc in self.primary_keys] col_sql.append('PRIMARY KEY (%s)' % ', '.join(pkey_cols)) table_sql = [ 'CREATE TABLE %s (' % self.name, ',\n'.join([' %s' % c for c in col_sql]), ') WITH OIDS', '', ] cmds.append('\n'.join(table_sql)) for extra in self.extras: sql = extra.post_create_sql() if sql is not None: cmds.append(sql) curs = self.db.cursor() try: for cmd in cmds: execute(curs, cmd) if cmds and grant: self.grant(curs, grant) if cmds and owner: self.owner(curs, owner) finally: curs.close()
def nextval(self, colname): col_desc = self.get_column(colname) curs = self.db.cursor() try: execute(curs, col_desc.nextval_sql()) return curs.fetchone()[0] finally: curs.close()
def rename(self, new_name): misc.valid_identifier(new_name, 'table identifier', strict=False) curs = self.db.cursor() try: execute(curs, 'ALTER TABLE %s RENAME TO %s' % (self.name, new_name)) self.name = new_name finally: curs.close()
def lock_table(self, table, mode, wait=True): cmd = 'LOCK %s IN %s MODE' % (table, mode) if not wait: cmd += ' NOWAIT' curs = self.cursor() try: execute(curs, cmd) finally: curs.close()
def make_user(self, username): curs = self.cursor() try: execute(curs, 'SELECT usename FROM pg_user' ' WHERE usename=%s', (username, )) if not curs.fetchone(): execute(curs, 'CREATE USER "%s"' % username) self.commit() finally: curs.close()
def make_user(self, username): curs = self.cursor() try: execute(curs, 'SELECT usename FROM pg_user' ' WHERE usename=%s', (username,)) if not curs.fetchone(): execute(curs, 'CREATE USER "%s"' % username) self.commit() finally: curs.close()
def _connect_db(self): self.db = self.dsn.connect() curs = self.db.cursor() try: execute(curs, "SET datestyle TO 'ISO,European'") # May potentially open us up to an SQL injection attack: # http://www.postgresql.org/docs/techdocs.50 # execute(curs, "SET client_encoding TO unicode") execute(curs, "SET client_encoding TO SQL_ASCII") finally: curs.close()
def delete(self): query = ['DELETE FROM %s' % self.table_desc.name] query_args = () if self.where_expr: where_expr, query_args = self.where_expr.build_expr() query.append('WHERE %s' % where_expr) curs = self.table_desc.db.cursor() try: execute.execute(curs, ' '.join(query), query_args) finally: curs.close()
def update(self, set, *args): query = ['UPDATE %s SET %s' % (self.table_desc.name, set)] args = list(args) if self.where_expr: where_expr, query_args = self.where_expr.build_expr() query.append('WHERE %s' % where_expr) args.extend(query_args) curs = self.table_desc.db.cursor() try: execute.execute(curs, ' '.join(query), args) finally: curs.close()
def aggregate(self, *op): query_expr, query_args = self.build_expr(op) curs = self.table_desc.db.cursor() try: execute.execute(curs, query_expr, query_args) result = curs.fetchone() if len(result) != len(op): raise dbapi.ProgrammingError('aggregate function returned ' '%d results, expected %d' % (len(result), len(op))) if len(result) == 1: return result[0] else: return result finally: curs.close()
def get_ref(self, col_name): col_desc, col_value = self._colpair(col_name) ref_col_desc = col_desc.target_column() ref_table_desc = ref_col_desc.table_desc row = ref_table_desc.get_row() curs = self.db().cursor() try: value = col_value.raw_value() if value is not None: execute(curs, 'SELECT * FROM %s WHERE %s = %%s' % \ (ref_table_desc.name, ref_col_desc.name), (col_desc.to_sql(value),)) result = curs.fetchone() if result: row.from_fetch(curs.description, result) finally: curs.close() return row
def template_exec(dsn, cmd): """ Connect to the template1 database, retrying if necessary """ template_dsn = DSN(dsn, database='template1') db = template_dsn.connect() try: db.autocommit = True curs = db.cursor() n = 0 while 1: try: execute(curs, cmd) break except dbapi.DatabaseError, e: if n < 3 and 'is being accessed by other users' in str(e): n += 1 time.sleep(1) continue else: raise finally: db.close()
def drop(self): if self.db.db_has_relation(self.name): curs = self.db.cursor() try: for extra in self.extras: sql = extra.pre_drop_sql() if sql is not None: execute(curs, sql) execute(curs, 'DROP TABLE %s' % self.name) # PG drops objects related to the table for extra in self.extras: sql = extra.post_drop_sql() if sql is not None: execute(curs, sql) finally: curs.close()
def owner(self, curs, owner): execute(curs, 'ALTER TABLE %s OWNER TO "%s"' % (self.name, owner)) for extra in self.extras: extra.owner(curs, owner) self.owned_by = owner
def reset_sequences(self, curs): execute(curs, "SELECT setval('%s',(SELECT max(%s) FROM %s))" %\ (self.seq.name, self.name, self.table_desc.name)) curs.fetchone()
def owner(self, curs, owner): execute(curs, 'ALTER TABLE %s OWNER TO "%s"' % (self.name, owner))
def grant(self, curs, user): objs = [self.name] + [e.name for e in self.extras if e.needs_grant] execute( curs, 'GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES' ' ON %s TO "%s"' % (', '.join(objs), user))
def revoke(self, curs, user): objs = [self.name] + [e.name for e in self.extras if e.needs_grant] execute( curs, 'REVOKE SELECT, UPDATE, INSERT, DELETE, REFERENCES ' ' ON %s FROM "%s"' % (', '.join(objs), user))
def db_update(self, refetch=True): changed_cols, new_values = [], [] no_change = True for col_desc in self._table_desc.get_columns(): col_value = self._columns[col_desc.name] value = col_value.raw_value() changed = ((self._new and value is not None) or col_value.has_changed()) if changed: no_change = False elif col_desc.auto_timestamp: # Not "changed", but auto-updating with other updates value = DateTime.now() changed = True if changed: changed_cols.append(col_desc.name) new_values.append(col_desc.to_sql(value)) if no_change: return table = self._table_desc.name curs = self.db().cursor() try: if self._new: cmd = _CmdBuilder() cmd.append('INSERT INTO %s' % table) cmd.append_list_of_names(changed_cols) cmd.append('VALUES') cmd.append_list_of_values(new_values) cmd.execute(curs) if not refetch: return execute(curs, 'SELECT * FROM %s WHERE oid=%%s' % table, (curs.oidValue,)) else: pkey_names, pkey_values = self._get_pkey() if not pkey_names: raise dbapi.ProgrammingError('No primary key defined for "%s"' % table) cmd = _CmdBuilder() cmd.append('UPDATE %s SET' % table) cmd.append_name_value_expr(', ', changed_cols, new_values) cmd.append('WHERE') cmd.append_name_value_expr(' and ', pkey_names, pkey_values) cmd.execute(curs) if curs.rowcount != 1: raise dbapi.RecordDeleted('Record has been deleted') pkey_names, pkey_values = self._get_pkey(initial = False) if not refetch: return cmd = _CmdBuilder() cmd.append('SELECT * FROM %s WHERE' % table) cmd.append_name_value_expr(' and ', pkey_names, pkey_values) cmd.execute(curs) for col_value in self._columns.values(): col_value.save() self._saved_new = self._new self.db().add_pending(self) result = curs.fetchmany(2) assert len(result) == 1 self.from_fetch(curs.description, result[0]) finally: curs.close()
def execute(self, curs): execute(curs, ' '.join(self.cmd), self.args)
def execute(self, curs, columns=None): query_expr, query_args = self.build_expr(columns) execute.execute(curs, query_expr, query_args)