escape string to store in a database?

[Rainy-3]

Hi, I'd like to store chunks of text, some of them may be very large,
in a database, and have them searchable using 'LIKE %something%'
construct. These pieces of text may have single and double quotes in
them, I tried escaping them using re module and string module and
either I did something wrong, or they escape either single quotes or
double quotes, not both of these. So that when I insert that text into
a db record, this causes an error from the database. What's the
accepted way of dealing with this? I have a workaround currently where
I encode the string with b64, and then unencode it when searching for
a string, but that's a dumb way to do this. For my app, searching
quickly is not very crucial, but would be nice to have.. thanks, -ak
--
http://mail.python.org/mailman/listinfo/python-list

[Carsten Haese]

On Wed, 2008-03-12 at 18:18 -0700, andrei.avk@... wrote:
> These pieces of text may have single and double quotes in
> them, I tried escaping them using re module and string module and
> either I did something wrong, or they escape either single quotes or
> double quotes, not both of these. So that when I insert that text into
> a db record, this causes an error from the database. What's the
> accepted way of dealing with this?

The accepted way of dealing with this is to use parameter binding:

conn = somedbmodule.connect(...)
cur = conn.cursor()
cur.execute("insert into sometable(textcolumn) values (?)",
            (stringvar,) )

(Note that the question mark may have to be replaced with %s depending
on which database module you're using.)

For background information on parameter binding see, for example,
http://informixdb.blogspot.com/2007/07/filling-in-blanks.html .

HTH,

--
Carsten Haese
http://informixdb.sourceforge.net


--
http://mail.python.org/mailman/listinfo/python-list

[Rainy-3]

 On Mar 12, 8:32 pm, Carsten Haese <cars...@...> wrote:

> On Wed, 2008-03-12 at 18:18 -0700, andrei....@... wrote:
> > These pieces of text may have single and double quotes in
> > them, I tried escaping them using re module and string module and
> > either I did something wrong, or they escape either single quotes or
> > double quotes, not both of these. So that when I insert that text into
> > a db record, this causes an error from the database. What's the
> > accepted way of dealing with this?
>
> The accepted way of dealing with this is to use parameter binding:
>
> conn = somedbmodule.connect(...)
> cur = conn.cursor()
> cur.execute("insert into sometable(textcolumn) values (?)",
>             (stringvar,) )
>
> (Note that the question mark may have to be replaced with %s depending
> on which database module you're using.)
>
> For background information on parameter binding see, for example,http://informixdb.blogspot.com/2007/07/filling-in-blanks.html.
>
> HTH,
>
> --
> Carsten Haesehttp://informixdb.sourceforge.net

Thanks for the reply, Carsten, how would this work with UPDATE
command? I get this error:

        cmd = "UPDATE items SET content = ? WHERE id=%d" % id

    self.cursor.execute(cmd, content)
pysqlite2.dbapi2.ProgrammingError: Incorrect number of bindings
supplied. The c
rrent statement uses 1, and there are 0 supplied.

Sqlite site doesn't give any details on using parameter bindings in
UPDATE command, I'm
going to look around some more.. -ak
--
http://mail.python.org/mailman/listinfo/python-list

[Bryan Olson]

andrei.avk@... wrote:
> how would this work with UPDATE
> command? I get this error:
>
>         cmd = "UPDATE items SET content = ? WHERE id=%d" % id
>
>     self.cursor.execute(cmd, content)
> pysqlite2.dbapi2.ProgrammingError: Incorrect number of bindings
> supplied. The c
> rrent statement uses 1, and there are 0 supplied.

The error message implies that 'content' is an empty sequence.
Even when the SQL takes exactly one parameter, the second
argument is a sequence containing the parameter. You can use
a one-element list, written [someparam], or a one-tuple
(someparam,).


> Sqlite site doesn't give any details on using parameter bindings in
> UPDATE command, I'm
> going to look around some more..

To make effective use of Python's Sqlite3 module, I need three
references: the Python DB API v2 spec, the Sqlite3 module's doc,
and the Sqlite database doc.

     http://www.python.org/dev/peps/pep-0249/
     http://docs.python.org/lib/module-sqlite3.html
     http://www.sqlite.org/docs.html

With all three, parameter binding is still under-specified, but
only a little.

Those new to the relational model and to SQL will need sources
on those as well. On the model, I think the foundational paper
has held up well over the decades:

   Codd, E.F. "A Relational Model of Data for Large Shared
   Data Banks". /Communications of the ACM/ Volume 13 number
   6, June 1970; pages 377–387.

It is currently available on line at:

   http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf


Anyone have a particularly good and easily accessible
source to recommend on SQL?


--
--Bryan
--
http://mail.python.org/mailman/listinfo/python-list

[jim-on-linux-2]

> > --
> > Carsten
> > Haesehttp://informixdb.sourceforge.net
>
> Thanks for the reply, Carsten, how would
> this work with UPDATE command? I get this
> error:
>
>         cmd = "UPDATE items SET content =
> ? WHERE id=%d" % id

try this;

("update items set contents = (?) where id
=(?)", [ x, y] )
put your data in a list

or

("update items set contents = (?) where id
=%d ", [ x] )


below statement "uses 1" refers to the one
(?) , 0 supplied, means no list or none in
list.

jim-on-linux
http://www.inqvista.com

>
>     self.cursor.execute(cmd, content)
> pysqlite2.dbapi2.ProgrammingError:
> Incorrect number of bindings supplied. The
> c
> rrent statement uses 1, and there are 0
> supplied.
>
> Sqlite site doesn't give any details on
> using parameter bindings in UPDATE
> command, I'm
> going to look around some more.. -ak

--
http://mail.python.org/mailman/listinfo/python-list

[Rainy-3]

On Mar 14, 1:36 am, Dennis Lee Bieber <wlfr...@...> wrote:

> On Thu, 13 Mar 2008 19:55:27 -0700 (PDT), andrei....@... declaimed
> the following in comp.lang.python:
>
>
>
> > Thanks for the reply, Carsten, how would this work with UPDATE
> > command? I get this error:
>
> >         cmd = "UPDATE items SET content = ? WHERE id=%d" % id
>
>                 cmd = "update items set content = ? where id = ?"
>
> >     self.cursor.execute(cmd, content)
>
>                 self.cursor.execute(cmd, (content, id))
>
> would be the preferred method...

Thanks very much - this works perfectly -ak

>
> --
>         Wulfraed        Dennis Lee Bieber               KD6MOG
>         wlfr...@...               wulfr...@...
>                 HTTP://wlfraed.home.netcom.com/
>         (Bestiaria Support Staff:               web-a...@...)
>                 HTTP://www.bestiaria.com/

--
http://mail.python.org/mailman/listinfo/python-list