Clicky

first can i update csv like a database ? if yes how is it done ? the reason why is i duplicate my sqlite db with a csv that is transmitted ... sometimes before it is transmitted smetimes the data is updated... I don't want to add a new line I just want to update the line like I do with my sqlite db ... also can csv capture graphics? I know its just a bunch of tuples so I guess in theory it could be done ?

asked 10/20/2011 09:42

dolamitejenkins's gravatar image

dolamitejenkins ♦♦


10 Answers:
No, because there is no 'csv' server to do it.  You would have to find a routine or write your own.
link
DaveBaldwin's gravatar image

DaveBaldwin

any suggestions ?
link
dolamitejenkins's gravatar image

dolamitejenkins

CSV is a text format with delimiters, usually commas, to define where each piece of data starts and ends.  There is no simple way to include binary data in it.  You would still have to write your own 'server' to update it.

link
DaveBaldwin's gravatar image

DaveBaldwin

In order to be most generic I would go for a brute force approach (assuming your tables aren't too big'

convert csv to sqlite3 update it and reconvert to csv

If you don't insist on python:
For perl there is a module named DBD::CSV. (I don't know a python equivalent, but perhaps someobe else does)

Please note however that anyything more than minor modifications of a CSV file in an sql like manner might (performance wise) not be such a good idea.

link
gelonida's gravatar image

gelonida

The python module for reading / writing is called 'csv.

You can find several examples of converting csv to sql on the net:
- http://code.activestate.com/recipes/498130-create-sql-tables-from-csv-files/
- http://stackoverflow.com/questions/5942402/python-csv-to-sqlite

Concerning storing graphics in csv.
- convert your graph into a string, which doesn't violate the escape rules of your CSV file. The module Base64 might be an option.
http://docs.python.org/library/base64.html
link
gelonida's gravatar image

gelonida

Another option to modify csv in a NON SQL manner is
read the csv line by line,
filter with python
and write it out line by line to a different file

All depends on the complexity of you rmodification.

Performance wise it might make no sense to directly modify the csv.
It could be, that just reexporting your sqlite3 to csv  is already as efficient as you can get.
link
gelonida's gravatar image

gelonida

For the data that can be represented in csv format (no images), you can use the csv standard Python module.  You get the row of information that you can process and use as the row of data for the sqlite.  For the images (I do not know if sqlite is capable to store blobs), you may capture the paths/urls in the csv file and then load/process the images somehow and put them to the database.
link
pepr's gravatar image

pepr

thanks ... i wil;; keep you guys updated
link
dolamitejenkins's gravatar image

dolamitejenkins

here is a snippet of my code how I gerate my intial sqlite and csv entries...

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
def OnInsert(self, event):
        con = lite.connect('specialeventms2.sqlite')
        cur = con.cursor()
              Humid = str(self.weather3a)
        Wind=str(self.weather4a)
        TimeStamp =str(self.weather5a)
	Refusal='NO'
	Active_Incident_number= None
        
        cur.execute('insert into ptrecords values(?,?,?,?,?,?,?,?,?,?,?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)',
                    (Incident_number, Last_Name, First_Name,Age,Gender,Address,City,State,Zip
                     ,Ailment,Treatment,Patient_reprt,Initial_contact,Hospital,Destination,Inservice,
                     Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Weather, Temp,Humid,Wind,TimeStamp,Refusal))
        con.commit()
        cur.close()
        con.close()
	f = open("duplicate_table2.txt", "ab")
	
        f.write(Incident_number+','+Last_Name+ ','+First_Name+','+Age+ ','+Gender+','+Address+','+City+','+State+','+Zip+','+Ailment+','+Treatment+','+Patient_reprt+','+Initial_contact+','+Hospital+','
	        +Destination+','+Inservice+','+Provider_1+','+Provider_2+','+Sys+','+Dia+','+Pulse+','+Resp+',' +Weather+ ','+Temp+',' +Humid+','+Wind+','+TimeStamp+','+Refusal+'
')
        
        self.tc1.Clear()


but then there is a way to update the sqlite db but how would I also update the csv ?

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
def UpdateData(self,event):
       
        try:
            con = lite.connect('specialeventms2.sqlite')
            cur = con.cursor()
            Incident_number=self.data1.GetValue()
            Last_Name = self.data2.GetValue()
            Provider_1=self.data17.GetValue()
            Provider_2=self.data18.GetValue()
            Sys=self.data19.GetValue()
             Dia=self.data20.GetValue()
            Pulse=self.data21.GetValue()
            Resp=self.data22.GetValue()
            Refusal=self.data99
                     if self.wid.getData() == []:
                lines = 0
            else:
                lines = self.wid.getData()
            cur.execute('update ptrecords set Last_Name =?,First_Name =?, Age=?,Gender =?, Address =?, City =?,State=?,Zip=?, Ailment =?, Treatment =?,Patient_reprt=? ,Initial_contact =?, Hospital =?, Inservice =?,Provider_1=?,Provider_2 =?,Sys=? ,Dia=?, Pulse=?, Resp=?,Refusal=?, image =? where Incident_number = ?' ,
                        ( Last_Name, First_Name, Age,Gender,Address,City,State,Zip,Ailment,Treatment,Patient_reprt,Initial_Contact,Hospital,Inservice,Provider_1,Provider_2,Sys,Dia,Pulse,Resp,Refusal,lines,Incident_number))
                         
            con.commit()
            cur.close()
            con.close()

when I update the data it is based on the  Incident_number = ?
           
link
dolamitejenkins's gravatar image

dolamitejenkins

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×15

Asked: 10/20/2011 09:42

Seen: 794 times

Last updated: 10/22/2011 03:25