def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) # Add the hash column, start with allowing NULLs hash_column = Column('hash', String(32), nullable=True, default=None, unique=True) hash_column.create(records_table, unique_name='unique_record') sync_domains = [] # Fill out the hash values. We need to do this in a way that lets us track # which domains need to be re-synced, so having the DB do this directly # won't work. for record in records_table.select().execute(): try: records_table.update()\ .where(records_table.c.id == record.id)\ .values(hash=_build_hash(record))\ .execute() except IntegrityError: if record.domain_id not in sync_domains: sync_domains.append(record.domain_id) LOG.warn("Domain '%s' needs to be synchronised" % record.domain_id) records_table.delete()\ .where(records_table.c.id == record.id)\ .execute() # Finally, the column should not be nullable. records_table.c.hash.alter(nullable=False)
def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) # Add the hash column, start with allowing NULLs hash_column = Column('hash', String(32), nullable=True, default=None, unique=True) hash_column.create(records_table, unique_name='unique_record') sync_domains = [] # Fill out the hash values. We need to do this in a way that lets us track # which domains need to be re-synced, so having the DB do this directly # won't work. for record in records_table.select().execute(): try: records_table.update()\ .where(records_table.c.id == record.id)\ .values(hash=_build_hash(record))\ .execute() except IntegrityError: if record.domain_id not in sync_domains: sync_domains.append(record.domain_id) LOG.warn(_LW("Domain '%s' needs to be synchronised") % record.domain_id) records_table.delete()\ .where(records_table.c.id == record.id)\ .execute() # Finally, the column should not be nullable. records_table.c.hash.alter(nullable=False)
def fix_opening_balance_errors(): data = request.get_json(force=True) opening_balance_id = int(data['id']) new_artist_id = int(data['artist_id']) print(data) print(opening_balance_id) print(new_artist_id) opening_balance_table = Table('opening_balance', metadata, autoload=True, autoload_with=db.engine) update = (opening_balance_table.update().where( opening_balance_table.c.id == opening_balance_id).values( artist_id=new_artist_id)) db.session.execute(update) db.session.commit() db.session.execute(""" UPDATE opening_balance SET artist_name = artist.artist_name FROM artist WHERE opening_balance.artist_id = artist.id """) db.session.commit() return jsonify({'success': 'true'})
def upgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine ip_addresses = Table('ip_addresses', meta, autoload=True) allocated = Column('allocated', Boolean(), default=False) ip_addresses.create_column(allocated) migrate_engine.execute(ip_addresses.update().values(allocated=True))
class TableHandler(object): """ Used by automatically generated objects such as datasets and dimensions to generate, write and clear the table under its management. """ def _init_table(self, meta, namespace, name, id_type=Integer): """ Create the given table if it does not exist, otherwise reflect the current table schema from the database. """ name = namespace + '__' + name self.table = Table(name, meta) if id_type is not None: col = Column('id', id_type, primary_key=True) self.table.append_column(col) def _generate_table(self): """ Create the given table if it does not exist. """ # TODO: make this support some kind of migration? if not db.engine.has_table(self.table.name): self.table.create(db.engine) def _upsert(self, bind, data, unique_columns): """ Upsert a set of values into the table. This will query for the set of unique columns and either update an existing row or create a new one. In both cases, the ID of the changed row will be returned. """ key = and_(*[self.table.c[c] == data.get(c) for c in unique_columns]) q = self.table.update(key, data) if bind.execute(q).rowcount == 0: q = self.table.insert(data) rs = bind.execute(q) return rs.inserted_primary_key[0] else: q = self.table.select(key) row = bind.execute(q).fetchone() return row['id'] def _flush(self, bind): """ Delete all rows in the table. """ q = self.table.delete() bind.execute(q) def _drop(self, bind): """ Drop the table and the local reference to it. """ if db.engine.has_table(self.table.name): self.table.drop() del self.table
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine tasks_table = Table('tasks', meta, autoload=True) task_info_table = Table('task_info', meta, autoload=True) for col_name in TASKS_MIGRATE_COLUMNS: column = Column(col_name, Text()) column.create(tasks_table) task_info_records = task_info_table.select().execute().fetchall() for task_info in task_info_records: values = { 'input': task_info.input, 'result': task_info.result, 'message': task_info.message } tasks_table.update(values=values).where( tasks_table.c.id == task_info.task_id).execute() drop_tables([task_info_table])
def downgrade(migrate_engine): meta = MetaData() meta.bind = migrate_engine tasks_table = Table('tasks', meta, autoload=True) task_info_table = Table('task_info', meta, autoload=True) for col_name in TASKS_MIGRATE_COLUMNS: column = Column(col_name, Text()) column.create(tasks_table) task_info_records = task_info_table.select().execute().fetchall() for task_info in task_info_records: values = { 'input': task_info.input, 'result': task_info.result, 'message': task_info.message } tasks_table.update(values=values).where( tasks_table.c.id == task_info.task_id).execute() drop_tables([task_info_table])
class TableHandler(object): """ Used by automatically generated objects such as datasets and dimensions to generate, write and clear the table under its management. """ def _init_table(self, meta, namespace, name, id_type=Integer): """ Create the given table if it does not exist, otherwise reflect the current table schema from the database. """ name = namespace + '__' + name self.table = Table(name, meta) if id_type is not None: col = Column('id', id_type, primary_key=True) self.table.append_column(col) def _generate_table(self): """ Create the given table if it does not exist. """ # TODO: make this support some kind of migration? if not db.engine.has_table(self.table.name): self.table.create(db.engine) def _upsert(self, bind, data, unique_columns): """ Upsert a set of values into the table. This will query for the set of unique columns and either update an existing row or create a new one. In both cases, the ID of the changed row will be returned. """ key = and_(*[self.table.c[c] == data.get(c) for c in unique_columns]) q = self.table.update(key, data) if bind.execute(q).rowcount == 0: q = self.table.insert(data) rs = bind.execute(q) return rs.inserted_primary_key[0] else: q = self.table.select(key) row = bind.execute(q).fetchone() return row['id'] def _flush(self, bind): """ Delete all rows in the table. """ q = self.table.delete() bind.execute(q) def _drop(self, bind): """ Drop the table and the local reference to it. """ if db.engine.has_table(self.table.name): self.table.drop() del self.table
def import_opening_balance(): file = request.files['CSV'] check_statement = (db.session.query(StatementGenerated).filter( StatementGenerated.statement_balance_table == 'opening_balance').all()) if len(check_statement) == 0: """Create table.""" statement_balance_table = ge.create_statement_balance_table('opening') db.session.commit() """Add to statement balance table""" statement_generated_entry = StatementGenerated( statement_balance_table='opening_balance') db.session.add(statement_generated_entry) db.session.commit() opening_balance_table = Table('opening_balance', metadata, autoload=True, autoload_with=db.engine) """Clear current values.""" db.session.execute("""DELETE FROM opening_balance""") db.session.commit() """Add new values from CSV.""" df = pd.read_csv(file) df.to_sql('opening_balance', con=db.engine, if_exists='append', index=False) update = (opening_balance_table.update().where( opening_balance_table.c.artist_name == Artist.artist_name).values( artist_id=Artist.id)) db.session.execute(update) db.session.commit() # sel = ( # db.session.query(opening_balance_table, Artist.artist_name) # .outerjoin(Artist, Artist.artist_name == opening_balance_table.c.artist_name).all() # ) errors = (db.session.query(opening_balance_table).outerjoin( Artist, Artist.artist_name == opening_balance_table.c.artist_name).filter( Artist.artist_name == None).all()) if len(errors) > 0: return jsonify({'errors': len(errors)}) return jsonify({'success': 'true'})
def downgrade(migrate_engine): meta.bind = migrate_engine rs_table = Table('recordsets', meta, autoload=True) records_table = Table('records', meta, autoload=True) recordsets = _get_recordsets(rs_table) col = Column('priority', Integer, default=None, nullable=True) col.create(records_table) record_cols = [ records_table.c.id, records_table.c.priority, records_table.c.data] for rs in recordsets: records = select(columns=record_cols)\ .where(records_table.c.recordset_id == rs[0])\ .execute().fetchall() for record in records: priority, _, data = record[2].partition(" ") # Old style hashes are <rs_id>:<data>:<priority> new_hash = _build_hash(rs[0], data, priority) update = records_table.update()\ .where(records_table.c.id == record[0])\ .values(priority=int(priority), data=data, hash=new_hash) update.execute() dialect = migrate_engine.url.get_dialect().name if dialect.startswith('sqlite'): # Add missing unique index constraint = UniqueConstraint('hash', name='unique_recordset', table=records_table) constraint.create()
def downgrade(migrate_engine): meta.bind = migrate_engine rs_table = Table('recordsets', meta, autoload=True) records_table = Table('records', meta, autoload=True) recordsets = _get_recordsets(rs_table) col = Column('priority', Integer, default=None, nullable=True) col.create(records_table) record_cols = [ records_table.c.id, records_table.c.priority, records_table.c.data ] for rs in recordsets: records = select(columns=record_cols)\ .where(records_table.c.recordset_id == rs[0])\ .execute().fetchall() for record in records: priority, _, data = record[2].partition(" ") # Old style hashes are <rs_id>:<data>:<priority> new_hash = _build_hash(rs[0], data, priority) update = records_table.update()\ .where(records_table.c.id == record[0])\ .values(priority=int(priority), data=data, hash=new_hash) update.execute() dialect = migrate_engine.url.get_dialect().name if dialect.startswith('sqlite'): # Add missing unique index constraint = UniqueConstraint('hash', name='unique_recordset', table=records_table) constraint.create()
def upgrade(migrate_engine): meta.bind = migrate_engine rs_table = Table('recordsets', meta, autoload=True) records_table = Table('records', meta, autoload=True) recordsets = _get_recordsets(rs_table) record_cols = [ records_table.c.id, records_table.c.priority, records_table.c.data] for rs in recordsets: query = select(columns=record_cols)\ .where(records_table.c.recordset_id == rs[0])\ .where(records_table.c.priority != None) # noqa records = query.execute().fetchall() for record in records: new_data = '%s %s' % (int(record[1]), record[2]) # New style hashes are <rs_id>:<data> since prio is baked into data new_hash = _build_hash(rs[0], new_data) update = records_table.update()\ .where(records_table.c.id == record[0])\ .values(data=new_data, hash=new_hash) migrate_engine.execute(update) records_table.c.priority.drop() dialect = migrate_engine.url.get_dialect().name if dialect.startswith('sqlite'): # Add missing unique index constraint = UniqueConstraint('hash', name='unique_recordset', table=records_table) constraint.create()
def upgrade(migrate_engine): meta.bind = migrate_engine rs_table = Table('recordsets', meta, autoload=True) records_table = Table('records', meta, autoload=True) recordsets = _get_recordsets(rs_table) record_cols = [ records_table.c.id, records_table.c.priority, records_table.c.data ] for rs in recordsets: query = select(columns=record_cols)\ .where(records_table.c.recordset_id == rs[0])\ .where(records_table.c.priority != None) # noqa records = query.execute().fetchall() for record in records: new_data = '%s %s' % (int(record[1]), record[2]) # New style hashes are <rs_id>:<data> since prio is baked into data new_hash = _build_hash(rs[0], new_data) update = records_table.update()\ .where(records_table.c.id == record[0])\ .values(data=new_data, hash=new_hash) migrate_engine.execute(update) records_table.c.priority.drop() dialect = migrate_engine.url.get_dialect().name if dialect.startswith('sqlite'): # Add missing unique index constraint = UniqueConstraint('hash', name='unique_recordset', table=records_table) constraint.create()
def merge(nombre_tabla, id_destino, otros_ids, session): """ Fusiona uno o más registros (otros_ids) en otro (id_destino), siendo todos ellos de la tabla 'nombre_tabla'. IN id_usuario <int>: Identificador del usuario que se conecta nombre_tabla <str>: Nombre de la tabla a la que pertenecen los registros id_destino <int>: Identificador del registro donde se guardará la información otros_ids <list> [<int>, ...]: Lista de ids desde donde se obtendrá la información OUT un JSON de la forma: { 'id_destino': 123, 'id_origen': 123, 'num_campos': 123 } EXC ENoExisteRegistro: Cuando no existe alguno de los registros (origen o destino) """ meta = MetaData(bind=session.bind, reflect=True) tabla = Table(nombre_tabla, meta, autoload=True) # comprobar que existe el registro "destino" alumno = session.execute(select([tabla], tabla.c.id == id_destino)).fetchone() if alumno is None: raise ENoExisteRegistro(id_destino) # comprobar que existen los "otros" registros for id_otro in otros_ids: otro = session.execute(select([tabla], tabla.c.id == id_otro)).fetchone() if otro is None: raise ENoExisteRegistro(id_otro) # fusionar resultado = {} resultado['id_destino'] = id_destino resultado['num_campos'] = 0 for id_otro in otros_ids: if id_otro == id_destino: continue resultado['id_origen'] = id_otro # obtener datos de los dos registros alumno = session.execute(select([tabla], tabla.c.id == id_destino)).fetchone() otro = session.execute(select([tabla], tabla.c.id == id_otro)).fetchone() # claves foráneas que hacen referencia a esta tabla for t in meta.sorted_tables: for fk in t.foreign_keys: if fk.references(tabla): # actualizar los registros que apuntan a "origen" -> "destino" qry_update = t.update(fk.parent == id_otro, values={fk.parent: id_destino}) session.execute(qry_update) session.commit() # cambiar datos de la tabla datos = {} for k in alumno.keys(): if k != 'id' and k != 'busqueda': #print 'Tratando %s: %s - %s' % (k, alumno[k] or '<NULL>', otro[k] or '<NULL>') # if alumno[k] != None and otro[k] != None: # if lista_atributos and str(k) in lista_atributos: # merge('clientes', alumno[k], [otro[k]], conector=conector) if alumno[k] is None and otro[k] != None: #print 'cambiando...' datos[k] = otro[k] resultado['num_campos'] += 1 if datos != {}: # actualizar "destino" qry_update = tabla.update(tabla.c.id == id_destino, values=datos) session.execute(qry_update) # borrar "otro" qry_delete = tabla.delete(tabla.c.id == id_otro) session.execute(qry_delete) session.commit() return resultado
class SqlAlchemyFdw(ForeignDataWrapper): """An SqlAlchemy foreign data wrapper. The sqlalchemy foreign data wrapper performs simple selects on a remote database using the sqlalchemy framework. Accepted options: db_url -- the sqlalchemy connection string. schema -- (optional) schema name to qualify table name with tablename -- the table name in the remote database. """ def __init__(self, fdw_options, fdw_columns): super(SqlAlchemyFdw, self).__init__(fdw_options, fdw_columns) if 'db_url' not in fdw_options: log_to_postgres('The db_url parameter is required', ERROR) if 'tablename' not in fdw_options: log_to_postgres('The tablename parameter is required', ERROR) self.engine = create_engine(fdw_options.get('db_url')) self.metadata = MetaData() schema = fdw_options['schema'] if 'schema' in fdw_options else None tablename = fdw_options['tablename'] self.table = Table(tablename, self.metadata, schema=schema, *[Column(col.column_name, ischema_names[col.type_name]) for col in fdw_columns.values()]) self.transaction = None self._connection = None self._row_id_column = fdw_options.get('primary_key', None) def execute(self, quals, columns): """ The quals are turned into an and'ed where clause. """ statement = select([self.table]) clauses = [] for qual in quals: operator = OPERATORS.get(qual.operator, None) if operator: clauses.append(operator(self.table.c[qual.field_name], qual.value)) else: log_to_postgres('Qual not pushed to foreign db: %s' % qual, WARNING) if clauses: statement = statement.where(and_(*clauses)) if columns: columns = [self.table.c[col] for col in columns] else: columns = self.table.c.values() statement = statement.with_only_columns(columns) log_to_postgres(str(statement), DEBUG) for item in self.connection.execute(statement): yield dict(item) @property def connection(self): if self._connection is None: self._connection = self.engine.connect() return self._connection def begin(self, serializable): self.transaction = self.connection.begin() def pre_commit(self): if self.transaction is not None: self.transaction.commit() self.transaction = None def commit(self): # Pre-commit hook does this on 9.3 if self.transaction is not None: self.transaction.commit() self.transaction = None def rollback(self): if self.transaction is not None: self.transaction.rollback() self.transaction = None @property def rowid_column(self): if self._row_id_column is None: log_to_postgres( 'You need to declare a primary key option in order ' 'to use the write features') return self._row_id_column def insert(self, values): self.connection.execute(self.table.insert(values=values)) def update(self, rowid, newvalues): self.connection.execute( self.table.update() .where(self.table.c[self._row_id_column] == rowid) .values(newvalues)) def delete(self, rowid): self.connection.execute( self.table.delete() .where(self.table.c[self._row_id_column] == rowid))
class SQLTable(Component): _selects = 0 _inserts = 0 _updates = 0 _finalized = False STORE_MODE_LOOKUP = "lookup" STORE_MODE_INSERT = "insert" STORE_MODE_UPSERT = "upsert" _pk = False columns = [] create = True _unicode_errors = 0 _lookup_changed_fields = None def __init__(self, name, connection, columns, label=None): super(SQLTable, self).__init__() self.sa_table = None self.sa_metadata = None self.name = name self.connection = connection self.label = label if label else name self.columns = columns or [] for col in columns: col.sqltable = self def _get_sa_type(self, column): if (column.type == "Integer"): return Integer elif (column.type == "String"): #if (column.length is None): column.length = 128 return Unicode(length=128) elif (column.type == "Float"): return Float elif (column.type == "Boolean"): return Boolean elif (column.type == "AutoIncrement"): return Integer elif (column.type == "Date"): return Date elif (column.type == "Time"): return Time elif (column.type == "DateTime"): return DateTime elif (column.type == "Binary"): return Binary else: raise Exception("Invalid data type (%s): %s" % (column, column.type)) def finalize(self, ctx): if (not SQLTable._finalized): SQLTable._finalized = True if (SQLTable._inserts + SQLTable._selects > 0): logger.info( "SQLTable Totals ins/upd/sel: %d/%d/%d " % (SQLTable._inserts, SQLTable._updates, SQLTable._selects)) if (self._inserts + self._selects > 0): logger.info( "SQLTable %-18s ins/upd/sel: %6d/%6d/%-6d " % (self.name, self._inserts, self._updates, self._selects)) if (self._unicode_errors > 0): logger.warning( "SQLTable %s found %d warnings assigning non-unicode fields to unicode columns" % (self.name, self._unicode_errors)) ctx.comp.finalize(self.connection) super(SQLTable, self).finalize(ctx) def initialize(self, ctx): super(SQLTable, self).initialize(ctx) if self._lookup_changed_fields == None: self._lookup_changed_fields = [] ctx.comp.initialize(self.connection) logger.debug("Loading table %s on %s" % (self.name, self)) self.sa_metadata = MetaData() self.sa_table = Table(self.name, self.sa_metadata) self._selects = 0 self._inserts = 0 self._updates = 0 self._unicode_errors = 0 # Drop? columns_ex = [] for column in self.columns: logger.debug("Adding column to %s: %s" % (self, column)) column.sqltable = self # Check for duplicate names if (column.name in columns_ex): raise ETLConfigurationException( "Duplicate column name '%s' in %s" % (column.name, self)) columns_ex.append(column.name) # Configure column if isinstance(column, SQLColumnFK): if column.fk_sqlcolumn.sqltable.sa_table is None: logger.warning( "Column %s foreign key %s table (%s) has not been defined in backend (ignoring).", column, column.fk_sqlcolumn, column.fk_sqlcolumn.sqltable) continue self.sa_table.append_column( Column(column.name, self._get_sa_type(column), ForeignKey( column.fk_sqlcolumn.sqltable.sa_table.columns[ column.fk_sqlcolumn.name]), primary_key=column.pk, nullable=column.nullable, autoincrement=(True if column.type == "AutoIncrement" else False))) else: self.sa_table.append_column( Column(column.name, self._get_sa_type(column), primary_key=column.pk, nullable=column.nullable, autoincrement=(True if column.type == "AutoIncrement" else False))) # Check schema: # Create if doesn't exist if (not self.connection.engine().has_table(self.name)): logger.info("Creating table %s" % self.name) self.sa_table.create(self.connection.connection()) # TODO:? Extend? (unsafe, allow read-only connections and make them default?) # TODO:? Delete columns (unsafe, allow read-only connections and make them default?) def pk(self, ctx): """ Returns the primary key column definitToClauion, or None if none defined. """ #if (self._pk == False): if True: pk_cols = [] for col in self.columns: if col.pk: pk_cols.append(col) if (len(pk_cols) > 1): raise Exception("Table %s has multiple primary keys: %s" % (self.name, pk_cols)) elif (len(pk_cols) == 1): self._pk = pk_cols[0] else: self._pk = None return self._pk def _attribsToClause(self, attribs): clauses = [] for k, v in attribs.items(): if isinstance(v, (list, tuple)): clauses.append(self.sa_table.c[k].in_(v)) else: clauses.append(self.sa_table.c[k] == v) return and_(*clauses) def _rowtodict(self, row): d = {} for column in self.columns: #print column d[column.name] = getattr(row, column.name) return d def _find(self, ctx, attribs): self._selects = self._selects + 1 SQLTable._selects = SQLTable._selects + 1 query = self.sa_table.select(self._attribsToClause(attribs)) rows = self.connection.connection().execute(query) for r in rows: # Ensure we return dicts, not RowProxys from SqlAlchemy yield self._rowtodict(r) def lookup(self, ctx, attribs, find_function=None): logger.debug("Lookup on '%s' attribs: %s" % (self, attribs)) if (len(attribs.keys()) == 0): raise Exception( "Cannot lookup on table '%s' with no criteria (empty attribute set)" % self.name) find_function = find_function or self._find rows = find_function(ctx, attribs) rows = list(rows) if (len(rows) > 1): raise Exception( "Found more than one row when searching for just one in table %s: %s" % (self.name, attribs)) elif (len(rows) == 1): row = rows[0] else: row = None logger.debug("Lookup result on %s: %s = %s" % (self.name, attribs, row)) return row def upsert(self, ctx, data, keys=[]): """ Upsert checks if the row exists and has changed. It does a lookup followed by an update or insert as appropriate. """ # TODO: Check for AutoIncrement in keys, shall not be used # If keys qfilter = {} if (len(keys) > 0): for key in keys: try: qfilter[key] = data[key] except KeyError as e: raise Exception( "Could not find attribute '%s' in data when storing row data: %s" % (key, data)) else: pk = self.pk(ctx) qfilter[pk.name] = data[pk.name] # Do lookup if len(qfilter) > 0: row = self.lookup(ctx, qfilter) if (row): # Check row is identical for c in self.columns: if c.type != "AutoIncrement": v1 = row[c.name] v2 = data[c.name] if c.type == "Date": v1 = row[c.name].strftime('%Y-%m-%d') v2 = data[c.name].strftime('%Y-%m-%d') if (isinstance(v1, str) or isinstance(v2, str)): if (not isinstance(v1, str)): v1 = str(v1) if (not isinstance(v2, str)): v2 = str(v2) if (v1 != v2): if (c.name not in self._lookup_changed_fields): logger.warning( "%s updating an entity that exists with different attributes, overwriting (field=%s, existing_value=%s, tried_value=%s)" % (self, c.name, v1, v2)) #self._lookup_changed_fields.append(c["name"]) # Update the row row = self.update(ctx, data, keys) return row row_with_id = self.insert(ctx, data) return row_with_id def _prepare_row(self, ctx, data): row = {} for column in self.columns: if column.type != "AutoIncrement": try: row[column.name] = data[column.name] except KeyError as e: raise Exception( "Missing attribute for column %s in table '%s' while inserting row: %s" % (e, self.name, data)) # Checks if (column.type == "String") and (not isinstance( row[column.name], str)): self._unicode_errors = self._unicode_errors + 1 if (ctx.debug): logger.warning( "Unicode column %r received non-unicode string: %r " % (column.name, row[column.name])) return row def insert(self, ctx, data): row = self._prepare_row(ctx, data) logger.debug("Inserting in table '%s' row: %s" % (self.name, row)) res = self.connection.connection().execute(self.sa_table.insert(row)) pk = self.pk(ctx) if pk: row[pk.name] = res.inserted_primary_key[0] self._inserts = self._inserts + 1 SQLTable._inserts = SQLTable._inserts + 1 if pk is not None: return row else: return row # None def update(self, ctx, data, keys=[]): row = self._prepare_row(ctx, data) # Automatically calculate lookup if necessary qfilter = {} if (len(keys) > 0): for key in keys: try: qfilter[key] = data[key] except KeyError as e: raise Exception( "Could not find attribute '%s' in data when storing row data: %s" % (key, data)) else: pk = self.pk(ctx) qfilter[pk.name] = data[pk.name] logger.debug("Updating in table '%s' row: %s" % (self.name, row)) res = self.connection.connection().execute( self.sa_table.update(self._attribsToClause(qfilter), row)) self._updates = self._updates + 1 SQLTable._updates = SQLTable._updates + 1 if pk is not None: return row else: return None
select([track]).where(and_(track.c.album_id == 1, track.c.genre_id == 1))) # In[114]: for row in trackResult: print(row) # Update # In[115]: from sqlalchemy import update # In[116]: conn.execute(track.update().values(genre_id=2).where(track.c.id == 2)) # In[117]: conn.execute(track.update().values(genre_id=1).where(track.c.id == 3)) # Where # In[118]: trackResult = conn.execute( select([track]).where( and_(track.c.album_id == 1, or_(track.c.genre_id == 1, track.c.genre_id == 2)))) # In[119]:
class SqlAlchemyFdw(ForeignDataWrapper): """An SqlAlchemy foreign data wrapper. The sqlalchemy foreign data wrapper performs simple selects on a remote database using the sqlalchemy framework. Accepted options: db_url -- the sqlalchemy connection string. schema -- (optional) schema name to qualify table name with tablename -- the table name in the remote database. """ def __init__(self, fdw_options, fdw_columns): super(SqlAlchemyFdw, self).__init__(fdw_options, fdw_columns) if 'tablename' not in fdw_options: log_to_postgres('The tablename parameter is required', ERROR) self.metadata = MetaData() url = _parse_url_from_options(fdw_options) self.engine = create_engine(url) schema = fdw_options['schema'] if 'schema' in fdw_options else None tablename = fdw_options['tablename'] sqlacols = [] for col in fdw_columns.values(): col_type = self._get_column_type(col.type_name) sqlacols.append(Column(col.column_name, col_type)) self.table = Table(tablename, self.metadata, schema=schema, *sqlacols) self.transaction = None self._connection = None self._row_id_column = fdw_options.get('primary_key', None) def _need_explicit_null_ordering(self, key): support = SORT_SUPPORT[self.engine.dialect.name] default = support['default'] no = None if key.is_reversed: no = nullsfirst if default == 'higher' else nullslast else: no = nullslast if default == 'higher' else nullsfirst if key.nulls_first: if no != nullsfirst: return nullsfirst return None else: if no != nullslast: return nullslast return None def can_sort(self, sortkeys): if SORT_SUPPORT.get(self.engine.dialect.name) is None: # We have no idea about defaults return [] can_order_null = SORT_SUPPORT[self.engine.dialect.name]['support'] if (any((self._need_explicit_null_ordering(x) is not None for x in sortkeys)) and not can_order_null): return [] return sortkeys def explain(self, quals, columns, sortkeys=None, verbose=False): sortkeys = sortkeys or [] statement = self._build_statement(quals, columns, sortkeys) return [str(statement)] def _build_statement(self, quals, columns, sortkeys): statement = select([self.table]) clauses = [] for qual in quals: operator = OPERATORS.get(qual.operator, None) if operator: clauses.append(operator(self.table.c[qual.field_name], qual.value)) else: log_to_postgres('Qual not pushed to foreign db: %s' % qual, WARNING) if clauses: statement = statement.where(and_(*clauses)) if columns: columns = [self.table.c[col] for col in columns] else: columns = self.table.c statement = statement.with_only_columns(columns) orders = [] for sortkey in sortkeys: column = self.table.c[sortkey.attname] if sortkey.is_reversed: column = column.desc() if sortkey.collate: column = column.collate('"%s"' % sortkey.collate) null_ordering = self._need_explicit_null_ordering(sortkey) if null_ordering: column = null_ordering(column) statement = statement.order_by(column) return statement def execute(self, quals, columns, sortkeys=None): """ The quals are turned into an and'ed where clause. """ sortkeys = sortkeys or [] statement = self._build_statement(quals, columns, sortkeys) log_to_postgres(str(statement), DEBUG) rs = (self.connection .execution_options(stream_results=True) .execute(statement)) # Workaround pymssql "trash old results on new query" # behaviour (See issue #100) if self.engine.driver == 'pymssql' and self.transaction is not None: rs = list(rs) for item in rs: yield dict(item) @property def connection(self): if self._connection is None: self._connection = self.engine.connect() return self._connection def begin(self, serializable): self.transaction = self.connection.begin() def pre_commit(self): if self.transaction is not None: self.transaction.commit() self.transaction = None def commit(self): # Pre-commit hook does this on 9.3 if self.transaction is not None: self.transaction.commit() self.transaction = None def rollback(self): if self.transaction is not None: self.transaction.rollback() self.transaction = None @property def rowid_column(self): if self._row_id_column is None: log_to_postgres( 'You need to declare a primary key option in order ' 'to use the write features') return self._row_id_column def insert(self, values): self.connection.execute(self.table.insert(values=values)) def update(self, rowid, newvalues): self.connection.execute( self.table.update() .where(self.table.c[self._row_id_column] == rowid) .values(newvalues)) def delete(self, rowid): self.connection.execute( self.table.delete() .where(self.table.c[self._row_id_column] == rowid)) def _get_column_type(self, format_type): """Blatant ripoff from PG_Dialect.get_column_info""" # strip (*) from character varying(5), timestamp(5) # with time zone, geometry(POLYGON), etc. attype = re.sub(r'\(.*\)', '', format_type) # strip '[]' from integer[], etc. attype = re.sub(r'\[\]', '', attype) is_array = format_type.endswith('[]') charlen = re.search('\(([\d,]+)\)', format_type) if charlen: charlen = charlen.group(1) args = re.search('\((.*)\)', format_type) if args and args.group(1): args = tuple(re.split('\s*,\s*', args.group(1))) else: args = () kwargs = {} if attype == 'numeric': if charlen: prec, scale = charlen.split(',') args = (int(prec), int(scale)) else: args = () elif attype == 'double precision': args = (53, ) elif attype == 'integer': args = () elif attype in ('timestamp with time zone', 'time with time zone'): kwargs['timezone'] = True if charlen: kwargs['precision'] = int(charlen) args = () elif attype in ('timestamp without time zone', 'time without time zone', 'time'): kwargs['timezone'] = False if charlen: kwargs['precision'] = int(charlen) args = () elif attype == 'bit varying': kwargs['varying'] = True if charlen: args = (int(charlen),) else: args = () elif attype in ('interval', 'interval year to month', 'interval day to second'): if charlen: kwargs['precision'] = int(charlen) args = () elif charlen: args = (int(charlen),) coltype = ischema_names.get(attype, None) if coltype: coltype = coltype(*args, **kwargs) if is_array: coltype = ARRAY(coltype) else: coltype = sqltypes.NULLTYPE return coltype @classmethod def import_schema(self, schema, srv_options, options, restriction_type, restricts): """ Reflects the remote schema. """ metadata = MetaData() url = _parse_url_from_options(srv_options) engine = create_engine(url) dialect = PGDialect() if restriction_type == 'limit': only = restricts elif restriction_type == 'except': only = lambda t, _: t not in restricts else: only = None metadata.reflect(bind=engine, schema=schema, only=only) to_import = [] for _, table in sorted(metadata.tables.items()): ftable = TableDefinition(table.name) ftable.options['schema'] = schema ftable.options['tablename'] = table.name for c in table.c: # Force collation to None to prevent imcompatibilities setattr(c.type, "collation", None) # If the type is specialized, call the generic # superclass method if type(c.type) in CONVERSION_MAP: class_name = CONVERSION_MAP[type(c.type)] old_args = c.type.__dict__ c.type = class_name() c.type.__dict__.update(old_args) if c.primary_key: ftable.options['primary_key'] = c.name ftable.columns.append(ColumnDefinition( c.name, type_name=c.type.compile(dialect))) to_import.append(ftable) return to_import
class SqlAlchemyFdw(ForeignDataWrapper): """An SqlAlchemy foreign data wrapper. The sqlalchemy foreign data wrapper performs simple selects on a remote database using the sqlalchemy framework. Accepted options: db_url -- the sqlalchemy connection string. schema -- (optional) schema name to qualify table name with tablename -- the table name in the remote database. """ def __init__(self, fdw_options, fdw_columns): super(SqlAlchemyFdw, self).__init__(fdw_options, fdw_columns) if 'tablename' not in fdw_options: log_to_postgres('The tablename parameter is required', ERROR) self.metadata = MetaData() if fdw_options.get('db_url'): url = make_url(fdw_options.get('db_url')) else: if 'drivername' not in fdw_options: log_to_postgres('Either a db_url, or drivername and other ' 'connection infos are needed', ERROR) url = URL(fdw_options['drivername']) for param in ('username', 'password', 'host', 'database', 'port'): if param in fdw_options: setattr(url, param, fdw_options[param]) self.engine = create_engine(url) schema = fdw_options['schema'] if 'schema' in fdw_options else None tablename = fdw_options['tablename'] sqlacols = [] for col in fdw_columns.values(): col_type = self._get_column_type(col.type_name) sqlacols.append(Column(col.column_name, col_type)) self.table = Table(tablename, self.metadata, schema=schema, *sqlacols) self.transaction = None self._connection = None self._row_id_column = fdw_options.get('primary_key', None) def execute(self, quals, columns): """ The quals are turned into an and'ed where clause. """ statement = select([self.table]) clauses = [] for qual in quals: operator = OPERATORS.get(qual.operator, None) if operator: clauses.append(operator(self.table.c[qual.field_name], qual.value)) else: log_to_postgres('Qual not pushed to foreign db: %s' % qual, WARNING) if clauses: statement = statement.where(and_(*clauses)) if columns: columns = [self.table.c[col] for col in columns] else: columns = self.table.c.values() statement = statement.with_only_columns(columns) log_to_postgres(str(statement), DEBUG) rs = (self.connection .execution_options(stream_results=True) .execute(statement)) for item in rs: yield dict(item) @property def connection(self): if self._connection is None: self._connection = self.engine.connect() return self._connection def begin(self, serializable): self.transaction = self.connection.begin() def pre_commit(self): if self.transaction is not None: self.transaction.commit() self.transaction = None def commit(self): # Pre-commit hook does this on 9.3 if self.transaction is not None: self.transaction.commit() self.transaction = None def rollback(self): if self.transaction is not None: self.transaction.rollback() self.transaction = None @property def rowid_column(self): if self._row_id_column is None: log_to_postgres( 'You need to declare a primary key option in order ' 'to use the write features') return self._row_id_column def insert(self, values): self.connection.execute(self.table.insert(values=values)) def update(self, rowid, newvalues): self.connection.execute( self.table.update() .where(self.table.c[self._row_id_column] == rowid) .values(newvalues)) def delete(self, rowid): self.connection.execute( self.table.delete() .where(self.table.c[self._row_id_column] == rowid)) def _get_column_type(self, format_type): """Blatant ripoff from PG_Dialect.get_column_info""" ## strip (*) from character varying(5), timestamp(5) # with time zone, geometry(POLYGON), etc. attype = re.sub(r'\(.*\)', '', format_type) # strip '[]' from integer[], etc. attype = re.sub(r'\[\]', '', attype) is_array = format_type.endswith('[]') charlen = re.search('\(([\d,]+)\)', format_type) if charlen: charlen = charlen.group(1) args = re.search('\((.*)\)', format_type) if args and args.group(1): args = tuple(re.split('\s*,\s*', args.group(1))) else: args = () kwargs = {} if attype == 'numeric': if charlen: prec, scale = charlen.split(',') args = (int(prec), int(scale)) else: args = () elif attype == 'double precision': args = (53, ) elif attype == 'integer': args = () elif attype in ('timestamp with time zone', 'time with time zone'): kwargs['timezone'] = True if charlen: kwargs['precision'] = int(charlen) args = () elif attype in ('timestamp without time zone', 'time without time zone', 'time'): kwargs['timezone'] = False if charlen: kwargs['precision'] = int(charlen) args = () elif attype == 'bit varying': kwargs['varying'] = True if charlen: args = (int(charlen),) else: args = () elif attype in ('interval', 'interval year to month', 'interval day to second'): if charlen: kwargs['precision'] = int(charlen) args = () elif charlen: args = (int(charlen),) coltype = ischema_names.get(attype, None) if coltype: coltype = coltype(*args, **kwargs) if is_array: coltype = ARRAY(coltype) else: coltype = sqltypes.NULLTYPE return coltype
def rename_vo(old_vo, new_vo, insert_new_vo=False, description=None, email=None, commit_changes=False, skip_history=False): """ Updates rows so that entries associated with `old_vo` are now associated with `new_vo` as part of multi-VO migration. :param old_vo: The 3 character string for the current VO (for a single-VO instance this will be 'def'). :param new_vo: The 3 character string for the new VO. :param insert_new_vo: If True then an entry for `new_vo` is created in the database. :param description: Full description of the new VO, unused if `insert_new_vo` is False. :param email: Admin email for the new VO, unused if `insert_new_vo` is False. :param commit_changes: If True then changes are made against the database directly. If False, then nothing is commited and the commands needed are dumped to be run later. :param skip_history: If True then tables without FKC containing historical data will not be converted to save time. """ success = True engine = session.get_engine() conn = engine.connect() trans = conn.begin() inspector = reflection.Inspector.from_engine(engine) metadata = MetaData(bind=conn, reflect=True) dialect = engine.dialect.name # Gather all the columns that need updating and all relevant foreign key constraints all_fks = [] tables_and_columns = [] for table_name in inspector.get_table_names(): if skip_history and ('_history' in table_name or '_hist_recent' in table_name): continue fks = [] table = Table(table_name, metadata) for column in table.c: if 'scope' in column.name or column.name == 'account': tables_and_columns.append((table, column)) for fk in inspector.get_foreign_keys(table_name): if not fk['name']: continue if 'scope' in fk['referred_columns'] or 'account' in fk['referred_columns']: fks.append(ForeignKeyConstraint(fk['constrained_columns'], [fk['referred_table'] + '.' + r for r in fk['referred_columns']], name=fk['name'], table=table, **fk['options'])) all_fks.extend(fks) try: bound_params = {'old_vo': old_vo, 'new_vo': new_vo, 'old_vo_suffix': '' if old_vo == 'def' else old_vo, 'new_vo_suffix': '' if new_vo == 'def' else '@%s' % new_vo, 'split_character': '@', 'int_1': 1, 'int_2': 2, 'new_description': description, 'new_email': email, 'datetime': datetime.utcnow()} bound_params_text = {} for key in bound_params: if isinstance(bound_params[key], int): bound_params_text[key] = bound_params[key] else: bound_params_text[key] = "'%s'" % bound_params[key] if insert_new_vo: table = Table('vos', metadata) insert_command = table.insert().values(vo=bindparam('new_vo'), description=bindparam('new_description'), email=bindparam('new_email'), updated_at=bindparam('datetime'), created_at=bindparam('datetime')) print(str(insert_command) % bound_params_text + ';') if commit_changes: conn.execute(insert_command, bound_params) # Drop all FKCs affecting InternalAccounts/Scopes for fk in all_fks: print(str(DropConstraint(fk)) + ';') if commit_changes: conn.execute(DropConstraint(fk)) # Update columns for table, column in tables_and_columns: update_command = table.update().where(split_vo(dialect, column, return_vo=True) == bindparam('old_vo_suffix')) if new_vo == 'def': update_command = update_command.values({column.name: split_vo(dialect, column)}) else: update_command = update_command.values({column.name: split_vo(dialect, column) + cast(bindparam('new_vo_suffix'), CHAR(4))}) print(str(update_command) % bound_params_text + ';') if commit_changes: conn.execute(update_command, bound_params) table = Table('rses', metadata) update_command = table.update().where(table.c.vo == bindparam('old_vo')).values(vo=bindparam('new_vo')) print(str(update_command) % bound_params_text + ';') if commit_changes: conn.execute(update_command, bound_params) # Re-add the FKCs we dropped for fkc in all_fks: print(str(AddConstraint(fkc)) + ';') if commit_changes: conn.execute(AddConstraint(fkc)) except: success = False print(format_exc()) print('Exception occured, changes not committed to DB.') if commit_changes and success: trans.commit() trans.close() return success
class SqlAlchemyFdw(ForeignDataWrapper): """An SqlAlchemy foreign data wrapper. The sqlalchemy foreign data wrapper performs simple selects on a remote database using the sqlalchemy framework. Accepted options: db_url -- the sqlalchemy connection string. schema -- (optional) schema name to qualify table name with tablename -- the table name in the remote database. """ def __init__(self, fdw_options, fdw_columns): super(SqlAlchemyFdw, self).__init__(fdw_options, fdw_columns) if 'tablename' not in fdw_options: log_to_postgres('The tablename parameter is required', ERROR) self.metadata = MetaData() url = _parse_url_from_options(fdw_options) self.engine = create_engine(url) schema = fdw_options['schema'] if 'schema' in fdw_options else None tablename = fdw_options['tablename'] sqlacols = [] for col in fdw_columns.values(): col_type = self._get_column_type(col.type_name) sqlacols.append(Column(col.column_name, col_type)) self.table = Table(tablename, self.metadata, schema=schema, *sqlacols) self.transaction = None self._connection = None self._row_id_column = fdw_options.get('primary_key', None) def _need_explicit_null_ordering(self, key): support = SORT_SUPPORT[self.engine.dialect.name] default = support['default'] no = None if key.is_reversed: no = nullsfirst if default == 'higher' else nullslast else: no = nullslast if default == 'higher' else nullsfirst if key.nulls_first: if no != nullsfirst: return nullsfirst return None else: if no != nullslast: return nullslast return None def can_sort(self, sortkeys): if SORT_SUPPORT.get(self.engine.dialect.name) is None: # We have no idea about defaults return [] can_order_null = SORT_SUPPORT[self.engine.dialect.name]['support'] if (any((self._need_explicit_null_ordering(x) is not None for x in sortkeys)) and not can_order_null): return [] return sortkeys def explain(self, quals, columns, sortkeys=None, verbose=False): sortkeys = sortkeys or [] statement = self._build_statement(quals, columns, sortkeys) return [str(statement)] def _build_statement(self, quals, columns, sortkeys): statement = select([self.table]) clauses = [] for qual in quals: operator = OPERATORS.get(qual.operator, None) if operator: clauses.append( operator(self.table.c[qual.field_name], qual.value)) else: log_to_postgres('Qual not pushed to foreign db: %s' % qual, WARNING) if clauses: statement = statement.where(and_(*clauses)) if columns: columns = [self.table.c[col] for col in columns] else: columns = self.table.c statement = statement.with_only_columns(columns) orders = [] for sortkey in sortkeys: column = self.table.c[sortkey.attname] if sortkey.is_reversed: column = column.desc() if sortkey.collate: column = column.collate('"%s"' % sortkey.collate) null_ordering = self._need_explicit_null_ordering(sortkey) if null_ordering: column = null_ordering(column) statement = statement.order_by(column) return statement def execute(self, quals, columns, sortkeys=None): """ The quals are turned into an and'ed where clause. """ sortkeys = sortkeys or [] statement = self._build_statement(quals, columns, sortkeys) log_to_postgres(str(statement), DEBUG) rs = (self.connection.execution_options( stream_results=True).execute(statement)) # Workaround pymssql "trash old results on new query" # behaviour (See issue #100) if self.engine.driver == 'pymssql' and self.transaction is not None: rs = list(rs) for item in rs: yield dict(item) @property def connection(self): if self._connection is None: self._connection = self.engine.connect() return self._connection def begin(self, serializable): self.transaction = self.connection.begin() def pre_commit(self): if self.transaction is not None: self.transaction.commit() self.transaction = None def commit(self): # Pre-commit hook does this on 9.3 if self.transaction is not None: self.transaction.commit() self.transaction = None def rollback(self): if self.transaction is not None: self.transaction.rollback() self.transaction = None @property def rowid_column(self): if self._row_id_column is None: log_to_postgres( 'You need to declare a primary key option in order ' 'to use the write features') return self._row_id_column def insert(self, values): self.connection.execute(self.table.insert(values=values)) def update(self, rowid, newvalues): self.connection.execute(self.table.update().where( self.table.c[self._row_id_column] == rowid).values(newvalues)) def delete(self, rowid): self.connection.execute(self.table.delete().where( self.table.c[self._row_id_column] == rowid)) def _get_column_type(self, format_type): """Blatant ripoff from PG_Dialect.get_column_info""" # strip (*) from character varying(5), timestamp(5) # with time zone, geometry(POLYGON), etc. attype = re.sub(r'\(.*\)', '', format_type) # strip '[]' from integer[], etc. attype = re.sub(r'\[\]', '', attype) is_array = format_type.endswith('[]') charlen = re.search('\(([\d,]+)\)', format_type) if charlen: charlen = charlen.group(1) args = re.search('\((.*)\)', format_type) if args and args.group(1): args = tuple(re.split('\s*,\s*', args.group(1))) else: args = () kwargs = {} if attype == 'numeric': if charlen: prec, scale = charlen.split(',') args = (int(prec), int(scale)) else: args = () elif attype == 'double precision': args = (53, ) elif attype == 'integer': args = () elif attype in ('timestamp with time zone', 'time with time zone'): kwargs['timezone'] = True if charlen: kwargs['precision'] = int(charlen) args = () elif attype in ('timestamp without time zone', 'time without time zone', 'time'): kwargs['timezone'] = False if charlen: kwargs['precision'] = int(charlen) args = () elif attype == 'bit varying': kwargs['varying'] = True if charlen: args = (int(charlen), ) else: args = () elif attype in ('interval', 'interval year to month', 'interval day to second'): if charlen: kwargs['precision'] = int(charlen) args = () elif charlen: args = (int(charlen), ) coltype = ischema_names.get(attype, None) if coltype: coltype = coltype(*args, **kwargs) if is_array: coltype = ARRAY(coltype) else: coltype = sqltypes.NULLTYPE return coltype @classmethod def import_schema(self, schema, srv_options, options, restriction_type, restricts): """ Reflects the remote schema. """ metadata = MetaData() url = _parse_url_from_options(srv_options) engine = create_engine(url) dialect = PGDialect() if restriction_type == 'limit': only = restricts elif restriction_type == 'except': only = lambda t, _: t not in restricts else: only = None metadata.reflect(bind=engine, schema=schema, only=only) to_import = [] for _, table in sorted(metadata.tables.items()): ftable = TableDefinition(table.name) ftable.options['schema'] = schema ftable.options['tablename'] = table.name for c in table.c: # Force collation to None to prevent imcompatibilities setattr(c.type, "collation", None) # If the type is specialized, call the generic # superclass method if type(c.type) in CONVERSION_MAP: class_name = CONVERSION_MAP[type(c.type)] old_args = c.type.__dict__ c.type = class_name() c.type.__dict__.update(old_args) if c.primary_key: ftable.options['primary_key'] = c.name ftable.columns.append( ColumnDefinition(c.name, type_name=c.type.compile(dialect))) to_import.append(ftable) return to_import
def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) # We need to autoload the domains table for the FK to succeed. Table('domains', meta, autoload=True) # Prepare an empty dict to cache (domain_id, name, type) tuples to # RRSet id's cache = {} # Create the recordsets_table table recordsets_table.create() # NOTE(kiall): Since we need a unique UUID for each recordset, and need # to maintain cross DB compatibility, we're stuck doing this # in code rather than an # INSERT INTO recordsets_table SELECT (..) FROM records; results = select( columns=[ records_table.c.tenant_id, records_table.c.domain_id, records_table.c.name, records_table.c.type, func.min(records_table.c.ttl).label('ttl'), func.min(records_table.c.created_at).label('created_at'), func.max(records_table.c.updated_at).label('updated_at') ], group_by=[ records_table.c.tenant_id, records_table.c.domain_id, records_table.c.name, records_table.c.type ] ).execute() for result in results: # Create the new RecordSet and remember it's id pk = recordsets_table.insert().execute( tenant_id=result.tenant_id, domain_id=result.domain_id, name=result.name, type=result.type, ttl=result.ttl, created_at=result.created_at, updated_at=result.updated_at ).inserted_primary_key[0] # Cache the ID for later cache_key = "%s.%s.%s" % (result.domain_id, result.name, result.type) cache[cache_key] = pk # Add the recordset column to the records table record_recordset_id = Column('recordset_id', UUID, default=None, nullable=True) record_recordset_id.create(records_table, populate_default=True) # Fetch all the records # TODO(kiall): Batch this.. results = select( columns=[ records_table.c.id, records_table.c.domain_id, records_table.c.name, records_table.c.type, records_table.c.data, records_table.c.priority ] ).execute() # Update each result with the approperiate recordset_id, and refresh # the hash column to reflect the removal of several fields. for result in results: cache_key = "%s.%s.%s" % (result.domain_id, result.name, result.type) recordset_id = cache[cache_key] new_hash = _build_hash(recordset_id, result) records_table.update()\ .where(records_table.c.id == result.id)\ .values(recordset_id=cache[cache_key], hash=new_hash)\ .execute() # Now that the records.recordset_id field is populated, lets ensure the # column is not nullable and is a FK to the records table. records_table.c.recordset_id.alter(nullable=False) ForeignKeyConstraint(columns=[records_table.c.recordset_id], refcolumns=[recordsets_table.c.id], ondelete='CASCADE', name='fkey_records_recordset_id').create() # Finally, drop the now-defunct columns from the records table records_table.c.name.drop() records_table.c.type.drop() records_table.c.ttl.drop()
def upgrade(migrate_engine): meta.bind = migrate_engine records_table = Table('records', meta, autoload=True) # We need to autoload the domains table for the FK to succeed. Table('domains', meta, autoload=True) # Prepare an empty dict to cache (domain_id, name, type) tuples to # RRSet id's cache = {} # Create the recordsets_table table recordsets_table.create() # NOTE(kiall): Since we need a unique UUID for each recordset, and need # to maintain cross DB compatibility, we're stuck doing this # in code rather than an # INSERT INTO recordsets_table SELECT (..) FROM records; results = select(columns=[ records_table.c.tenant_id, records_table.c.domain_id, records_table.c.name, records_table.c.type, func.min(records_table.c.ttl).label('ttl'), func.min(records_table.c.created_at).label('created_at'), func.max(records_table.c.updated_at).label('updated_at') ], group_by=[ records_table.c.tenant_id, records_table.c.domain_id, records_table.c.name, records_table.c.type ]).execute() for result in results: # Create the new RecordSet and remember it's id pk = recordsets_table.insert().execute( tenant_id=result.tenant_id, domain_id=result.domain_id, name=result.name, type=result.type, ttl=result.ttl, created_at=result.created_at, updated_at=result.updated_at).inserted_primary_key[0] # Cache the ID for later cache_key = "%s.%s.%s" % (result.domain_id, result.name, result.type) cache[cache_key] = pk # Add the recordset column to the records table record_recordset_id = Column('recordset_id', UUID, default=None, nullable=True) record_recordset_id.create(records_table, populate_default=True) # Fetch all the records # TODO(kiall): Batch this.. results = select(columns=[ records_table.c.id, records_table.c.domain_id, records_table.c.name, records_table.c.type, records_table.c.data, records_table.c.priority ]).execute() # Update each result with the approperiate recordset_id, and refresh # the hash column to reflect the removal of several fields. for result in results: cache_key = "%s.%s.%s" % (result.domain_id, result.name, result.type) recordset_id = cache[cache_key] new_hash = _build_hash(recordset_id, result) records_table.update()\ .where(records_table.c.id == result.id)\ .values(recordset_id=cache[cache_key], hash=new_hash)\ .execute() # Now that the records.recordset_id field is populated, lets ensure the # column is not nullable and is a FK to the records table. records_table.c.recordset_id.alter(nullable=False) ForeignKeyConstraint(columns=[records_table.c.recordset_id], refcolumns=[recordsets_table.c.id], ondelete='CASCADE', name='fkey_records_recordset_id').create() # Finally, drop the now-defunct columns from the records table records_table.c.name.drop() records_table.c.type.drop() records_table.c.ttl.drop()
class SQLTable(Component): _selects = 0 _inserts = 0 _updates = 0 _finalized = False STORE_MODE_LOOKUP = "lookup" STORE_MODE_INSERT = "insert" STORE_MODE_UPSERT = "upsert" _pk = False columns = [] create = True _unicode_errors = 0 _lookup_changed_fields = None def __init__(self, name, connection, columns, label=None): super(SQLTable, self).__init__() self.sa_table = None self.sa_metadata = None self.name = name self.connection = connection self.label = label if label else name self.columns = columns or [] for col in columns: col.sqltable = self def _get_sa_type(self, column): if (column.type == "Integer"): return Integer elif (column.type == "String"): #if (column.length is None): column.length = 128 return Unicode(length = 128) elif (column.type == "Float"): return Float elif (column.type == "Boolean"): return Boolean elif (column.type == "AutoIncrement"): return Integer elif (column.type == "Date"): return Date elif (column.type == "Time"): return Time elif (column.type == "DateTime"): return DateTime elif (column.type == "Binary"): return Binary else: raise Exception("Invalid data type (%s): %s" % (column, column.type)) def finalize(self, ctx): if (not SQLTable._finalized): SQLTable._finalized = True if (SQLTable._inserts + SQLTable._selects > 0): logger.info("SQLTable Totals ins/upd/sel: %d/%d/%d " % (SQLTable._inserts, SQLTable._updates, SQLTable._selects)) if (self._inserts + self._selects > 0): logger.info("SQLTable %-18s ins/upd/sel: %6d/%6d/%-6d " % (self.name, self._inserts, self._updates, self._selects)) if (self._unicode_errors > 0): logger.warning("SQLTable %s found %d warnings assigning non-unicode fields to unicode columns" % (self.name, self._unicode_errors)) ctx.comp.finalize(self.connection) super(SQLTable, self).finalize(ctx) def initialize(self, ctx): super(SQLTable, self).initialize(ctx) if self._lookup_changed_fields == None: self._lookup_changed_fields = [] ctx.comp.initialize(self.connection) logger.debug("Loading table %s on %s" % (self.name, self)) self.sa_metadata = MetaData() self.sa_table = Table(self.name, self.sa_metadata) self._selects = 0 self._inserts = 0 self._updates = 0 self._unicode_errors = 0 # Drop? columns_ex = [] for column in self.columns: logger.debug("Adding column to %s: %s" % (self, column)) column.sqltable = self # Check for duplicate names if (column.name in columns_ex): raise ETLConfigurationException("Duplicate column name '%s' in %s" % (column.name, self)) columns_ex.append(column.name) # Configure column if isinstance(column, SQLColumnFK): if column.fk_sqlcolumn.sqltable.sa_table is None: logger.warning("Column %s foreign key %s table (%s) has not been defined in backend (ignoring).", column, column.fk_sqlcolumn, column.fk_sqlcolumn.sqltable) continue self.sa_table.append_column(Column(column.name, self._get_sa_type(column), ForeignKey(column.fk_sqlcolumn.sqltable.sa_table.columns[column.fk_sqlcolumn.name]), primary_key=column.pk, nullable=column.nullable, autoincrement=(True if column.type == "AutoIncrement" else False))) else: self.sa_table.append_column(Column(column.name, self._get_sa_type(column), primary_key=column.pk, nullable=column.nullable, autoincrement=(True if column.type == "AutoIncrement" else False))) # Check schema: # Create if doesn't exist if (not self.connection.engine().has_table(self.name)): logger.info("Creating table %s" % self.name) self.sa_table.create(self.connection.connection()) # TODO:? Extend? (unsafe, allow read-only connections and make them default?) # TODO:? Delete columns (unsafe, allow read-only connections and make them default?) def pk(self, ctx): """ Returns the primary key column definitToClauion, or None if none defined. """ #if (self._pk == False): if True: pk_cols = [] for col in self.columns: if col.pk: pk_cols.append(col) if (len(pk_cols) > 1): raise Exception("Table %s has multiple primary keys: %s" % (self.name, pk_cols)) elif (len(pk_cols) == 1): self._pk = pk_cols[0] else: self._pk = None return self._pk def _attribsToClause(self, attribs): clauses = [] for k, v in attribs.items(): if isinstance(v, (list, tuple)): clauses.append(self.sa_table.c[k].in_(v)) else: clauses.append(self.sa_table.c[k] == v) return and_(*clauses) def _rowtodict(self, row): d = {} for column in self.columns: #print column d[column.name] = getattr(row, column.name) return d def _find(self, ctx, attribs): self._selects = self._selects + 1 SQLTable._selects = SQLTable._selects + 1 query = self.sa_table.select(self._attribsToClause(attribs)) rows = self.connection.connection().execute(query) for r in rows: # Ensure we return dicts, not RowProxys from SqlAlchemy yield self._rowtodict(r) def lookup(self, ctx, attribs, find_function=None): logger.debug ("Lookup on '%s' attribs: %s" % (self, attribs)) if (len(attribs.keys()) == 0): raise Exception("Cannot lookup on table '%s' with no criteria (empty attribute set)" % self.name) find_function = find_function or self._find rows = find_function(ctx, attribs) rows = list(rows) if (len(rows) > 1): raise Exception("Found more than one row when searching for just one in table %s: %s" % (self.name, attribs)) elif (len(rows) == 1): row = rows[0] else: row = None logger.debug("Lookup result on %s: %s = %s" % (self.name, attribs, row)) return row def upsert(self, ctx, data, keys = []): """ Upsert checks if the row exists and has changed. It does a lookup followed by an update or insert as appropriate. """ # TODO: Check for AutoIncrement in keys, shall not be used # If keys qfilter = {} if (len(keys) > 0): for key in keys: try: qfilter[key] = data[key] except KeyError as e: raise Exception("Could not find attribute '%s' in data when storing row data: %s" % (key, data)) else: pk = self.pk(ctx) qfilter[pk.name] = data[pk.name] # Do lookup if len(qfilter) > 0: row = self.lookup(ctx, qfilter) if (row): # Check row is identical for c in self.columns: if c.type != "AutoIncrement": v1 = row[c.name] v2 = data[c.name] if c.type == "Date": v1 = row[c.name].strftime('%Y-%m-%d') v2 = data[c.name].strftime('%Y-%m-%d') if (isinstance(v1, str) or isinstance(v2, str)): if (not isinstance(v1, str)): v1 = str(v1) if (not isinstance(v2, str)): v2 = str(v2) if (v1 != v2): if (c.name not in self._lookup_changed_fields): logger.warn("%s updating an entity that exists with different attributes, overwriting (field=%s, existing_value=%s, tried_value=%s)" % (self, c.name, v1, v2)) #self._lookup_changed_fields.append(c["name"]) # Update the row row = self.update(ctx, data, keys) return row row_with_id = self.insert(ctx, data) return row_with_id def _prepare_row(self, ctx, data): row = {} for column in self.columns: if column.type != "AutoIncrement": try: row[column.name] = data[column.name] except KeyError as e: raise Exception("Missing attribute for column %s in table '%s' while inserting row: %s" % (e, self.name, data)) # Checks if (column.type == "String") and (not isinstance(row[column.name], str)): self._unicode_errors = self._unicode_errors + 1 if (ctx.debug): logger.warn("Unicode column %r received non-unicode string: %r " % (column.name, row[column.name])) return row def insert(self, ctx, data): row = self._prepare_row(ctx, data) logger.debug("Inserting in table '%s' row: %s" % (self.name, row)) res = self.connection.connection().execute(self.sa_table.insert(row)) pk = self.pk(ctx) if pk: row[pk.name] = res.inserted_primary_key[0] self._inserts = self._inserts + 1 SQLTable._inserts = SQLTable._inserts + 1 if pk is not None: return row else: return row # None def update(self, ctx, data, keys = []): row = self._prepare_row(ctx, data) # Automatically calculate lookup if necessary qfilter = {} if (len(keys) > 0): for key in keys: try: qfilter[key] = data[key] except KeyError as e: raise Exception("Could not find attribute '%s' in data when storing row data: %s" % (key, data)) else: pk = self.pk(ctx) qfilter[pk.name] = data[pk.name] logger.debug("Updating in table '%s' row: %s" % (self.name, row)) res = self.connection.connection().execute(self.sa_table.update(self._attribsToClause(qfilter), row)) self._updates = self._updates +1 SQLTable._updates = SQLTable._updates + 1 if pk is not None: return row else: return None