[Tutor] CSV file processing...

[Spencer Parker]

This is why I should not be allowed to write emails at the end of the day.
At least ones that need deep explanation.  Let's try this a second time.

I am running virt-top on a Xen box.  This grabs information about various
things.  I can set how many iterations of it I want to run.  I only have it
running once now.  It dumps all of its information into a CSV file.  Now I
have 10 virtual machines running on this Xen box right now.  It has one line
that is all of the headers.  Then on the next line it lists all of the
machines information...all on one line.  So I basically have something that
is 70 columns long + 2 columns of host machine specific data.  I want to
insert all of this into a MySQL database that then is processed it all into
a webpage.

I have a test server that only had one machine running so I could experiment
in getting the data into the database.  That part I have working properly.
My problem is that I need to be able to get all of the data of all the
machine running into the database as well.  All of them in their own
specific row.

The first 20 columns in the CSV are for the host machine...then the next 7
items are specific to the virtual machine.  All it lists after the first
20+7 is virtual machine data.  So I want to join the first 20 columns up
with the first 7...then the first 20 columns up with the next 7...so on and
so forth.

This is what I have now...

#!/usr/bin/python

import MySQLdb
import csv
import sys

try:
    db = MySQLdb.connect (host = "localhost",user = "user",passwd =
"password",db = "stats")
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

try:
    co = db.cursor()
    csv_data = csv.reader(file('output.txt','r'))

    for row in csv_data: print row
    co.execute("""
        INSERT INTO stats (Hostname, Time, Arch, PhysicalCPU, Count,
Running, Blocked, Paused, Shutdown, Shutoff, Crashed, Active, Inactive,
PCPU, TotalMemory, Totalguestmemory, TotalCPUtime, DomainID, Domainname,
CPUTime, PGCPU, RDRQ, WRRQ, RXBY, TXBY)
        VALUES
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
            """,row)

    co.close()

except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

db.commit()
db.close()

Sorry for the confusion I caused everyone...trying to run out of work...and
write an email is not the best combo ever.  Thanks again for all of the
help.


On Thu, Mar 20, 2008 at 9:08 PM, bob gailer <bgailer at alum.rpi.edu> wrote:

>  On Thu, Mar 20, 2008 at 03:16:05PM -0600, Spencer Parker wrote:
>
> I've interspersed a bunch of comments to show you how hard it has been to
> read and understand your problem description. After going thru it I think I
> understand the question.
>
> Please in the future try to express the question more clearly.
>
>  I am trying to read a CSV file and the get that information into a MySQL
> database.  I am able to do this
>
>
> You are able to do what? Read csv files? Insert into my SQL?
>
>  but I have a small problem.  I have a piece of software
>
>  Is this a Python program? Will you post it so we can see it?
>
>  that runs
>
>  Good - it is nice to have running software
>
>  and each iteration is one like
>
>  Like what?
>
>  It only runs once right now
>
>  Is that a problem?
>
>  there is only one line + the headers.
>
>  In the input file??
>
>  I use the csv module to kill
>
>  Kill?
>
>  the headers and import
>
>  import in Python has a specific meaning. I don't think you mean import in
> the Python sense.
>
>  the one line.
>
>
>  The problem is...I need to have it split the csv file
>
>  the file or the line following the headers?
>
>   at some point. I need to first 20 items taken off
>
>  Of what? To understand this we'd need to see an example of the csv file.
>
>  and then have the next 7 + the first 20 imported into the database
>
>  Huh? I know all of this makes sense to you but not to me. So you need to
> give me a much better picture.
>
>  then have it do this for the next 7 + the first 20...so on and so forth until hits the
> end of the line.
>
>
>  Having waded thru all that I will make a guess:
>
> The csv file format is:
> Headers Line
> Data Line
>
> and Data Line looks like:
> item1,. item2, ... item20, data11, data12, ... data17, data21, data22, ...
> data27, etc.
>
> And you want to insert into the datbase:
> item1,. item2, ... item20, data11, data12, ... data17
> item1,. item2, ... item20, data21, data22, ... data27
> etc
>
>  Any ideas?
>
>
>   If I were you and my guesses above are correct I'd reduce the problem
> statement to:
> given a line from a csv file of the format:
> item1,. item2, ... item20, data11, data12, ... data17, data21, data22, ...
> data27, etc.
> How do I create a series of INSERT statements to insert into the database
> item1,. item2, ... item20, data11, data12, ... data17
> item1,. item2, ... item20, data21, data22, ... data27
>
> --
> Bob Gailer
> 919-636-4239 Chapel Hill, NC
>
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> http://mail.python.org/mailman/listinfo/tutor
>
>


-- 
Spencer Parker
_______________________________________________________

"if you can't go to heaven, may you at least die in Ireland."

_______________________________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20080321/be7f4f6f/attachment.htm