By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,144 Members | 872 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,144 IT Pros & Developers. It's quick & easy.

How to quote text before inserting into database?

P: n/a
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)?

TIA,
Scott

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.