On Fri, May 31, 2013 at 10:28 AM, sajuptpm <[email protected]> wrote:
> How to update PickleType column using "DBSession.execute()"
>
>
> class MyTable(DeclarativeBase):
> __tablename__ = 'mytable'
> context = Column(PickleType)
>
>
> Attempt 1
> ========
>
> context = {"k1":{"n1":"bbla"}, "k2":{"n2":"bbla"}}
> context = pickle.dumps(context)
> DBSession.execute("update mytable set context='%s' where t_id=%s;"
> %(context, id))
> DBSession.execute("commit;")
>
> Error
> --------
> ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near 'shortD'\np1\nS'bla bla
> bla%s'\np2\nsS'child'\np3\n(dp4\nS'im' at line 1") u"update mytable set
> context='(dp0\nS'shortD'\np1\nS'blabla
> %%s'\np2\nsS'child'\np3\n(dp4\nS'import_disk'\np5\n(dp6\nS't_id'\np7\nL11092L\nsssS'shortDP'\np8\n(V2\np9\ntp10\nsS'description'\np11\ng2\nsS'descParams'\np12\n(V2\np13\ntp14\ns.'
> where t_id=11091;"
>
>
> Attempt 2
> ========
>
> context = {"k1":{"n1":"bbla"}, "k2":{"n2":"bbla"}}
> context = re.escape(str(context))
> context = pickle.dumps(context)
> DBSession.execute("update mytable set context='%s' where t_id=%s;"
> %(context, id))
> DBSession.execute("commit;")
>
> Error
> --------
> ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near 'shortDesc\\\\'\\\\:\\\\ \\\\'bla\\\\ bla\\\\
> bla\\\\ \\\\%s\\\\'\\\\,\\\\ \\\\'child\\\\_' at line 1") u'update mytable
> set context=\'S"\\\\{\\\\\'shortD\\\\\'\\\\:\\\\ \\\\\'bla\\\\ bla\\\\
> bla\\\\ \\\\%%s\\\\\'\\\\,\\\\ \\\\\'child\\\\_s\\\\\'\\\\:\\\\
> \\\\{\\\\\'import\\\\_disk\\\\\'\\\\:\\\\ \\\\{\\\\\'t\\\\_id\\\\\'\\\\:\\\\
> 11145L\\\\}\\\\}\\\\,\\\\ \\\\\'shortDP\\\\\'\\\\:\\\\
> \\\\(u\\\\\'2\\\\\'\\\\,\\\\)\\\\,\\\\ \\\\\'description\\\\\'\\\\:\\\\
> \\\\\'bla\\\\ Virtual\\\\ bla\\\\ \\\\%%s\\\\\'\\\\,\\\\
> \\\\\'descP\\\\\'\\\\:\\\\ \\\\(u\\\\\'2\\\\\'\\\\,\\\\)\\\\}"\np0\n.\'
> where t_id=11144;'
>
>
> Note:
> =====
> * I want to use only DBSession.execute("").
>
> * I know that following code will works, But i want to use
> DBSession.execute("").
> DBSession.query(MyTable).filter(MyTable.t_id==id).update(values=dict(context=context))
>
Don't use string interpolation (Python's "%" operator) with SQL
strings. It means you have to take care of all the quoting yourself,
and potentially open yourself up to SQL injection attacks.
Instead, you should use bind parameters, something like this:
context = {"k1":{"n1":"bbla"}, "k2":{"n2":"bbla"}}
context = pickle.dumps(context)
DBSession.execute("update mytable set context=:context where
t_id=:id", {'context': context, 'id': id})
See the documentation at:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#sqlalchemy.orm.session.Session.execute
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
