0

So, I have the following issue when updating my database:

I have a column in my table that I need to split in three essentially and so I have the following code:

with con:
  cur = con.cursor()
  cur.execute('SELECT Column1 FROM MainTable')
  while True row = cur.fetchone() 
    if row == None:
      break 
for line in row: a, b, c= line.split('-') 
  print (b);

which gives me the values of the second column for instance. However, I need to UPDATE that second column with those values and for this I have tried adding the following at the bottom: 

cur.execute('UPDATE MainTable SET Col_2 = ?' WHERE Id = 1, (row, Id))

con.commit()

However, for some reason this isn't running well.

halfer
  • 19,824
  • 17
  • 99
  • 186
José Urriola
  • 11
  • 4
  • Probably unrelated - you are missing a where clause in your update. please check – Gurwinder Singh Feb 06 '17 at 18:45
  • I started having a go at editing the long line of code and then realised I don't know what is going on here: `while True cur.fetchone == None Break;`. Please format your code correctly so that it's possible to see an accurate representation; that happens to precede the line that you find isn't working. – roganjosh Feb 06 '17 at 18:50
  • Hello, I have edited a bit because you're right and the first line of code was wrong. – José Urriola Feb 06 '17 at 19:01
  • But there are no line breaks in your code or any indentation to see what code belongs in what block. You can't have a horizontal line of code like that. – roganjosh Feb 06 '17 at 19:04
  • Yes, you're right. I'll see if I can edit that because that's not how it really looks. – José Urriola Feb 06 '17 at 19:15
  • That code is still wrong. Just use copy/paste. – CL. Feb 06 '17 at 19:24
  • @GurV I added that clause but I'm not exactly sure giving it an Id works like I want it to. Thx! – José Urriola Feb 06 '17 at 19:37
  • @CL is it actually wrong? this is what I had written down. I'm sure the following works for just one though : with con: cur = con.cursor() cur.execute('SELECT Column1 FROM MainTable') row = cur.fetchone() for line in row : a, b, c = line.split('-') print (b); – José Urriola Feb 06 '17 at 19:41
  • `cur.execute('UPDATE MainTable SET Col_2 = ?' WHERE Id = 1, (row, Id))` this is very wrong. Can you confirm now that your code in the question is an **accurate** reflection of what you have? – roganjosh Feb 06 '17 at 19:47
  • I know that last bit of code is wrong. That's what I had tried to update and failed to. I think the first piece of code is correct though, at least it's what I have written down in paper from when I ran it on the database and it returned the values fine(maybe I wrote some of it down erroneously). – José Urriola Feb 06 '17 at 19:55
  • I think your final point is true. This has lots of syntax errors and probably indentation mistakes (`for line in row:`, and ignoring the rest of that line, does not work in the way the previous `while` loop, also invalid syntax, would imply). This is unanswerable as it is. Unless you can copy/paste from your existing code or are proficient in creating valid syntax, nobody can help you I'm afraid. – roganjosh Feb 06 '17 at 20:03

1 Answers1

0

To update a specific row, you need a way to identify the row. You appear to have an "ID" column, so read that together with the string you want split.

SQL statements have nothing to do with Python; what you do in Python is to construct an SQL string that you then give to the database to execute. (Using ?-style parameters avoids string formatting problems and SQL injection attacks; always use them for values.)

cur.execute('SELECT ID, Column1 FROM MainTable')
for id, col1 in cur.fetchall():
    a, b, c = col1.split('-')
    cur.execute('UPDATE MainTable SET ColA = ?, ColB = ?, ColC = ? WHERE ID = ?',
                [a, b, c, id])
con.commit()
CL.
  • 173,858
  • 17
  • 217
  • 259