Bottom Page

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.......
buran wrote Dec-15-2018, 08:46 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.
Quote
#2
any help guys?!
Quote
#3
cross-posted on SO
Quote
#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.
Feel like you're not getting the answers you want? Checkout the help/rules for things like what to include/not include in a post, how to use code tags, how to ask smart questions, and more.

Pro-tip - there's an inverse correlation between the number of lines of code posted and my enthusiasm for helping with a question :)
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  How to modify df column SriMekala 1 568 Aug-14-2019, 11:06 PM
Last Post: boring_accountant
  How to delete column if entire column values are "nan" Sri 4 546 Apr-13-2019, 12:16 PM
Last Post: Sri
  Bar Plot with Python ,matplotlib and sqlite3 tables gauravbhardwajee 0 907 Sep-25-2018, 06:17 PM
Last Post: gauravbhardwajee

Forum Jump:


Users browsing this thread: 1 Guest(s)