(Python) How to edit existing rows in a SQL DB?

Question

I am trying to iterate through each row in my database and edit a single column for each of them. The column is called "name" (a varchar) and it is in the company table.

I am trying to remove the commas and then update it in my database.

So far what I have is this:

cursor = db.cursor()

companynames = cursor.execute("SELECT name FROM company;")

for row in cursor:

    row.replace(",", "")

This does not work properly.

print row returns ('Syntel, Inc. ',) ... A tuple

What can I do to properly retrieve the varchar field and then update it? `

Haleemur Ali · Accepted Answer · 2015-04-17 02:05:21Z

1

Mysql has a REPLACE function.

The most efficient way to do this would be:

cursor.execute("UPDATE company SET name = replace(name, ',', '')")
db.commit()

If you want to pull the rows into python and then edit the string, you can do so like this:

corrected_names = []
for row in cursor.execute('SELECT name FROM company'):
    corrected_names.append(row[0].replace(',', ''))

this puts all the corrected names in a new list.

however, this approach can still be improved upon.

corrected_rows = []
for row in cursor.execute('SELECT id, name FROM company'):
    corrected_rows.append((row[0], row[1].replace(',', '')))

Here we're querying the primary key, as well as the field we're interested in, and then adding the edited row to our list. So now corrected_rows is a list of tuples.

After we're done editing the fields as we please, we can update the some or all of the rows in the database because we have the primary key.

This method gives the greatest flexibility.

edited Apr 17, 2015 at 2:05

answered Apr 17, 2015 at 1:49

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Haleemur Ali Over a year ago

You can wrap the replace function on itself multiple times (replace(replace(name, ',', ''), 'acne', 'acme') but that gets ugly quickly. instead, just execute multiple update statements and commit.

c1phr · Accepted Answer · 2015-04-17 01:52:30Z

1

Haleemur's answer is the correct one for a MySQL only approach (which honestly is likely the best approach, but I don't know your needs).

As far as your python is concerned, row is just coming back with the tuple of your data. Since the only column you're selecting is in the first position, you can extract that string with index notation: row[0]. In your case:

removedCommas = row[0].replace(",", "")
# Do work with the string removedCommas

answered Apr 17, 2015 at 1:52

c1phr

5905 silver badges14 bronze badges

Collectives™ on Stack Overflow

(Python) How to edit existing rows in a SQL DB?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related