Re: How to quote text before inserting into database?

Hi Scott,

Scott Chapman wrote:
> I am working with Python (psycopg). I have HTML with embedded Python that I'm
> inserting into a database and it could contain any character.
>
> Single quotes, at least, must be escaped (to two single quotes, right?) before
> inserting it into Postgres.
>
> This poses a problem when I get the data out of the table. It could have
> originally contained two single quotes together and I replace them with one
> single quote in the unescaping process.
>
> How do you properly escape the special characters (and what all are they)?

This is supported by psycopg. See

http://www.python.org/peps/pep-0249.html

Especially:

.execute(operation[,parameters])

Prepare and execute a database operation (query or
command). Parameters may be provided as sequence or
mapping and will be bound to variables in the operation.
Variables are specified in a database-specific notation
(see the module's paramstyle attribute for details). [5]

A reference to the operation will be retained by the
cursor. If the same operation object is passed in again,
then the cursor can optimize its behavior. This is most
effective for algorithms where the same operation is used,
but different parameters are bound to it (many times).

For maximum efficiency when reusing an operation, it is
best to use the setinputsizes() method to specify the
parameter types and sizes ahead of time. It is legal for
a parameter to not match the predefined information; the
implementation should compensate, possibly with a loss of
efficiency.

The parameters may also be specified as list of tuples to
e.g. insert multiple rows in a single operation, but this
kind of usage is depreciated: executemany() should be used
instead.

Return values are not defined.

This means, if you have to handle strings, you can use

cursor.execute("SELECT value FROM table WHERE key=%s",("your'key",))

for example.

HTH
Tino Wildenhain