Many thanks. Great help. On Jun 9, 4:44 pm, Conor <[email protected]> wrote: > On 06/09/2010 07:58 AM, bartomas wrote: > > > > > Hi, > > > I'm new to SqlAlchemy. I'd like to find the simplest way of updating > > the fields of a table by applying a transformation function to the > > current value of the field. > > I've tried the following: > > > ## Code snippet > > > engine = create_engine('mysql://root:t...@localhost:3306/Test1') > > metadata = MetaData() > > metadata.bind = engine > > employees = Table('employees', metadata, autoload=True) > > > upd = > > employees.update(values={employees.c.fullname:transform2(employees.c.fullname)}) > > > engine.execute(upd) > > > def transform2(currentValue): > > return re.sub('Peter','Paul',currentValue) > > > def transform1(currentValue): > > return 'Mr ' + currentValue > > > ## end code snippet > > > When applying the function transform1 which just concatenates another > > string to the current value, it works fine. However if I apply > > transform2 that does a regular expression substitution on the value I > > get an error message saying that the re.sub function expects a string > > argument. > > How can I retrieve the current value from a Column object to transform > > it? Or is there a better way to this problem? > > > Many thanks for any help. > > In both transform functions, currentValue is the column object, not the > value for a particular row. transform1 happens to work because column > objects can turn the + operator into a SQL expression. To get the same > effect for transform2, you have to use database functions, e.g. for > PostgreSQL: > > upd = employees.update(values={employees.c.fullname: > func.regex_replace(employees.c.fullname, 'Peter', 'Paul')}) > > If the transform function really does have to be run client-side, you > have no choice but to SELECT all the rows, apply the transform, and > issue many UPDATEs back to the database. Using the ORM can make this easier. > > -Conor
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
