Python Forum
How to modify and save a column in the sqlite3 database using python?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to modify and save a column in the sqlite3 database using python?
#1
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.......
Reply
#2
any help guys?!
Reply
#3
cross-posted on SO
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#4
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Inserting a variable as column name in sqlite3 python JellyCreeper6 3 3,755 Nov-01-2020, 01:44 PM
Last Post: ibreeden
  How to modify df column SriMekala 1 2,438 Aug-14-2019, 11:06 PM
Last Post: boring_accountant
  Bar Plot with Python ,matplotlib and sqlite3 tables gauravbhardwajee 0 4,928 Sep-25-2018, 06:17 PM
Last Post: gauravbhardwajee

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020