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.
