How to modify and save a column in the sqlite3 database using python?

[laithsky1]

I have a CSV file (A.csv) has many columns. The question is how to modify and save a column in this file and database (data.db). I would like to replace the value 1 in column y based on the previous value:

Output:
A = [ d   x    y    z
      0   1    2    5
      1   2    1    9
      2   8    1    2
      3   3    40   7
      4   6    1    7
      5   4    30   3]

The expected answer should be :

Output:
A = [ d   x    y    z
      0   1    2    5
      1   2    2    9
      2   8    2    2
      3   3    40   7
      4   6    40   7
      5   4    30   3]

import csv
import numpy as np
import numpy
import pandas as pd

conn = sqlite3.connect('data.db')
conn.text_factory = str 
cur = conn.cursor()
A = cur.execute("SELECT * FROM CH1_data")
data = cur.execute('UPDATE CH2_data t1 SET y = (SELECT t2.y FROM CH2_data t2 WHERE t2.d < t1.d AND t2.y <> 1 ORDER BY t2.d DESC LIMIT 1) WHERE y = "" ')

conn.commit()


with open('output_data1001.csv', 'w') as f: 
  writer = csv.writer(f)
  writer.writerow(['d', 'x','y','z'])
  writer.writerows(A)

It does not work. Please help me.......

[laithsky1]

any help guys?!

[buran]

cross-posted on SO

[micseydel]

Your question is very very jumbled. The root of it is (as was mentioned on SO) that your SQL syntax is wrong - you probably need to learn SQL. You should remove Python from the equation and just use a shell to interact with the database until you're comfortable with it, then you can add the additional complexity of Python.