Difficulty with .replace during formating

This is the place for queries that don't fit in any of the other categories.

Difficulty with .replace during formating

Postby Larz60+ » Thu Apr 03, 2014 5:45 pm

Hello,

First, I am new to python but have been programming professionally since 1965. I believe that Python offers every single requirement that I have for a new project. That being said, I am having difficulty formating a string that I will use with psycopg2 to insert into a postgreSQL managed database. The specific issue is with use of .replace which I am using with a text item iterator. The code, I am showing an original text sample, a desired result and the actual (incorrect) output:

Code: Select all
import re

row = "Jaatal","Lorenzo","B","","O'Leary Cheatum and Howe","","64 H N Pope Street Suite 16"
data = "INSERT INTO Attorney VALUES ('Jaatal', 'Lorenzo', 'B', '', 'O''Leary Cheatum and Howe', '', '64 H N Pope Street Suite 16'"

print('Original row:     ', row)
print('Desired result:    ' + data)

for item in range (len(row)):
    row[item].replace("'","\'\'")

InsertStatement = ("INSERT INTO Attorney VALUES ('"
+ row[0] + "', '"  + row[1]  + "', '" + row[2]  + "', '" + row[3]  + "', '"
+ row[4]  + "', '" + row[5].rstrip('\r\n') + "');")

print('Row after replace: ', row)
print('Generated result:  ' + InsertStatement)



The results I am geting are:
Code: Select all
Original row:      ('Jaatal', 'Lorenzo', 'B', '', "O'Leary Cheatum and Howe", '', '64 H N Pope Street Suite 16')
Desired result:    INSERT INTO Attorney VALUES ('Jaatal', 'Lorenzo', 'B', '', 'O''Leary Cheatum and Howe', '', '64 H N Pope Street Suite 16'
Row after replace:  ('Jaatal', 'Lorenzo', 'B', '', "O'Leary Cheatum and Howe", '', '64 H N Pope Street Suite 16')
Generated result:  INSERT INTO Attorney VALUES ('Jaatal', 'Lorenzo', 'B', '', 'O'Leary Cheatum and Howe', '');


The problem is with the O'Leary. postgresql requires two single quotes, and I expected that my replace statement would do that.

Where is my error?

Thank you,
Larz60+
Last edited by stranac on Thu Apr 03, 2014 6:36 pm, edited 1 time in total.
Reason: First post lock.
Larz60+
 
Posts: 202
Joined: Thu Apr 03, 2014 4:06 pm

Re: Difficulty with .replace during formating

Postby stranac » Thu Apr 03, 2014 6:54 pm

If you've been programming since '65, you probably heard of a little boy called Bobby Tables...
That is to say, don't manually format your SQL queries, every python SQL library gives you a way to do that securely.

That said, strings are immutable, every method on strings returns a new string.
Furthermore, tuples are also immutable, so assigning new values to row wouldn't work either.

The best way to "change" row is to replace it with a new list(or tuple, but you probably don't specifically need a tuple).
The pythonic way to do this is a list comprehension:
Code: Select all
>>> row = "Jaatal", "Lorenzo", "B", "", "O'Leary Cheatum and Howe", "", "64 H N Pope Street Suite 16"
>>> row = [item.replace("'","''") for item in row]
>>> row
['Jaatal', 'Lorenzo', 'B', '', "O''Leary Cheatum and Howe", '', '64 H N Pope Street Suite 16']


But as I said, you won't need that here, because you don't want to manually construct your queries...
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1144
Joined: Thu Feb 07, 2013 3:42 pm

Re: Difficulty with .replace during formating

Postby Larz60+ » Thu Apr 03, 2014 6:56 pm

Thank You
Larz60+
 
Posts: 202
Joined: Thu Apr 03, 2014 4:06 pm


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 8 guests