I'm working with a Python program to insert / update textual data into a
PostgreSQL database. The text has single and double quotes in it, and I
wonder: What is the easiest way to escape quotes in Python, similar to
the Perlism "$str =~ s/(['"])/\\$1/g;"?
I tried the re.escape() method, but it escapes far too much, including
spaces and accented characters. I only want to escape single and double
quotes, everything else should be acceptable to the database.
--
Leif Biberg Kristensen http://solumslekt.org/ 7 22714
> I'm working with a Python program to insert / update textual data into a PostgreSQL database. The text has single and double quotes in it, and I wonder: What is the easiest way to escape quotes in Python, similar to the Perlism "$str =~ s/(['"])/\\$1/g;"?
I tried the re.escape() method, but it escapes far too much, including spaces and accented characters. I only want to escape single and double quotes, everything else should be acceptable to the database.
You don't need to escape text when using the Python DB-API.
DB-API will do everything for you.
For example:
SQL = 'INSERT into TEMP data = %s'
c.execute(SQL, """ text containing ' and ` and all other stuff we might
read from the network""")
You see, the SQL string contains a %s placeholder, but insetad of executing
the simple string expansion SQL % """....""", I call the execute method
with the text as a second *parametar*. Everything else is magic :).
--
damjan
Hey there,
str.replace('"', '\\"').replace("'", "\\'")
HTH, jbar
Damjan skrev: You don't need to escape text when using the Python DB-API. DB-API will do everything for you. For example: SQL = 'INSERT into TEMP data = %s' c.execute(SQL, """ text containing ' and ` and all other stuff we might read from the network""")
You see, the SQL string contains a %s placeholder, but insetad of executing the simple string expansion SQL % """....""", I call the execute method with the text as a second *parametar*. Everything else is magic :).
Sure, but does this work if you need more than one placeholder? FWIW,
here's the whole script. It will fetch data from the table name_parts
and pump them into the "denormalized" table names ( a real SQL guru
would probably do the same thing with one single monster query):
import psycopg
from re import escape
connection = psycopg.connect("dbname=slekta", serialize=0)
sql = connection.cursor()
sql.execute("select * from name_parts")
result = sql.fetchall()
for row in result:
if row[2] == 1: # name part = 'prefix'
query = ("update names set prefix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 2: # name part = 'given'
query = ("update names set given='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 3: # name part = 'surname'
query = ("update names set surname='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 4: # name part = 'suffix'
query = ("update names set suffix='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 5: # name part = 'patronym'
query = ("update names set patronym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
elif row[2] == 6: # name part = 'toponym'
query = ("update names set toponym='%s' where name_id=%s" % \
(escape(row[4]), row[1]))
sql.execute(query)
sql.commit()
connection.close()
--
Leif Biberg Kristensen http://solumslekt.org/
Leif B. Kristensen wrote: Damjan skrev:For example: SQL = 'INSERT into TEMP data = %s' c.execute(SQL, """ text containing ' and ` and all other stuff we might read from the network""")
Sure, but does this work if you need more than one placeholder?
Yup.
FWIW, here's the whole script. It will fetch data from the table name_parts and pump them into the "denormalized" table names ( a real SQL guru would probably do the same thing with one single monster query):
import psycopg
from re import escape
connection = psycopg.connect("dbname=slekta", serialize=0)
cursor = connection.cursor()
cursor.execute("select * from name_parts")
result = cursor.fetchall()
kind = 'prefix', 'given', 'surname', 'suffix', 'patronym', 'toponym'
for row in result:
if 0 < row[2] <= 6:
cursor.execute("update names set " + kind[row[2] - 1] +
" = %s where name_id = %s",
(row[4], row[1]))
cursor.commit()
connection.close()
1) I would prefer "SELECT name_id, part, name FROM name_parts", rather
than relying on * to return the field names in an expected order
and size as your database evolves. I generally do SQL keywords in
all-caps as documentation for those reading the code later.
2) I suspect that last line of the second execute might need to be:
[(row[4], row[1])])
I don't really remember; I'd just try both and see which works.
3) It is not really clear to when you want to do the commits.
I might be tempted to do the first query with "ORDER BY name_id"
and do a commit after each distinct name_id is finished. This
strategy would keep data for individuals coherent.
4) In fact, I'd leave the data in the database. Perhaps more like a
set of queries like:
UPDATE names
SET names.prefix = name_parts.name
FROM name_parts
WHERE names.name_id = name_parts.name_id
AND name_parts.name_kind = 1
You really need to think about commits when you adopt this strategy.
--Scott David Daniels Sc***********@Acm.Org
In <Gq******************@news2.e.nsc.no>, Leif B. Kristensen wrote: Damjan skrev:
You don't need to escape text when using the Python DB-API. DB-API will do everything for you. For example: SQL = 'INSERT into TEMP data = %s' c.execute(SQL, """ text containing ' and ` and all other stuff we might read from the network""")
You see, the SQL string contains a %s placeholder, but insetad of executing the simple string expansion SQL % """....""", I call the execute method with the text as a second *parametar*. Everything else is magic :). Sure, but does this work if you need more than one placeholder?
Yes it works with more than one placeholder.
FWIW, here's the whole script. It will fetch data from the table name_parts and pump them into the "denormalized" table names ( a real SQL guru would probably do the same thing with one single monster query):
import psycopg from re import escape
connection = psycopg.connect("dbname=slekta", serialize=0) sql = connection.cursor()
sql.execute("select * from name_parts") result = sql.fetchall() for row in result: if row[2] == 1: # name part = 'prefix' query = ("update names set prefix='%s' where name_id=%s" % \ (escape(row[4]), row[1])) elif row[2] == 2: # name part = 'given' query = ("update names set given='%s' where name_id=%s" % \ (escape(row[4]), row[1])) elif row[2] == 3: # name part = 'surname' query = ("update names set surname='%s' where name_id=%s" % \ (escape(row[4]), row[1])) elif row[2] == 4: # name part = 'suffix' query = ("update names set suffix='%s' where name_id=%s" % \ (escape(row[4]), row[1])) elif row[2] == 5: # name part = 'patronym' query = ("update names set patronym='%s' where name_id=%s" % \ (escape(row[4]), row[1])) elif row[2] == 6: # name part = 'toponym' query = ("update names set toponym='%s' where name_id=%s" % \ (escape(row[4]), row[1])) sql.execute(query) sql.commit() connection.close()
A lot of redundant code. Try something like the following instead of the
``elif`` sequence::
name_part = ['prefix', 'given', 'surname', 'suffix', 'patronym', 'toponym']
for row in result:
query = 'update names set %s=%%s where name_id=%%s' % name_part[row[2]-1]
sql.execute(query, (row[4], row[1]))
sql.commit()
Ciao,
Marc 'BlackJack' Rintsch
First, thanks to all who have replied. I learned a lot more than I had
expected :-)
This is a small part of a major project; converting my genealogy
database from a commercial FoxPro application to my homegrown Python /
PostgreSQL app. I'm still in a phase where I'm experimenting with
different models, hence the need for shuffling data between two tables.
Now, the script in its refined form looks like this:
#! /usr/bin/env python
# name_convert.py - populate "names" with values from "name_parts"
import psycopg
name_part = ('prefix','given','surname','suffix','patronym','t oponym')
connection = psycopg.connect("dbname=slekta", serialize=0)
sql = connection.cursor()
sql.execute("select name_id, name_part_type, name_part from name_parts")
result = sql.fetchall()
for row in result:
query = "update names set %s=%%s where name_id=%%s" % \
name_part[row[1]-1]
sql.execute(query, (row[2], row[0]))
sql.commit()
connection.close()
--
Leif Biberg Kristensen http://solumslekt.org/
Leif B. Kristensen wrote: I'm working with a Python program to insert / update textual data into a PostgreSQL database. The text has single and double quotes in it, and I wonder: What is the easiest way to escape quotes in Python, similar to the Perlism "$str =~ s/(['"])/\\$1/g;"?
Just for the record (even though it's not the right solution to your problem), the Python equivalent is
re.sub('''(['"])''', r'\\\1', s)
Kent This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: TheKeith |
last post by:
Hi, I'm just learning php now for the first time and I'm having a little
trouble understanding something.
In the following example:
...
|
by: JehanNYNJ |
last post by:
I have to put some html into a variable like so....
var html = '<TABLE cellspacing="5" border="0" .......
But within this html string I also need to have the code for a button
that traps the...
|
by: Greg |
last post by:
I keep getting an error when I have a tick mark in a text value that I am
searching for in my XPath Query.
Example:
<Authors>
<Author LastName="O'Donnel">
<Author LastName="Smith">...
|
by: Jeff S |
last post by:
In a VB.NET code behind module, I build a string for a link that points to a
JavaScript function. The two lines of code below show what is relevant.
PopupLink = "javascript:PopUpWindow(" &...
|
by: Axel Dahmen |
last post by:
Hi,
within a DataGrid control I'm using a DataTable containing a string column
to fill a Hyperlink's href attribute. Unfortunately HttpUtility.UrlEncode()
doesn't escape the apostroph character,...
|
by: Lawrence D'Oliveiro |
last post by:
The "escape" function in the "cgi" module escapes characters with special
meanings in HTML. The ones that need escaping are '<', '&' and '"'.
However, cgi.escape only escapes the quote character if...
|
by: Marina Levit [MVP] |
last post by:
I've scoured google, but apparently none of the suggestions actually work.
I have the following. type of XPATH query
"SomeNode[SomeAttribute = 'abc's search'"
Now, I've tried doing this:
...
|
by: Confused but working on it |
last post by:
Hi all,
I'm trying to do something simple and grabbed a snippet from the php
manual for reading files in a directory. The snippet echos out a nice
list of files.
<?php
//Open images directory...
|
by: crybaby |
last post by:
I wrote a python code in linux text pad and copied to thumb drive and
try to ran the file by changing the path to windows:
sys.path = sys.path +
I get the following error:
ValueError:...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |