Formatting question.

[mike5160]

On Nov 21, 11:36 am, mike5160 <mike5... at gmail.com> wrote:
> On Nov 21, 1:22 am, Dennis Lee Bieber <wlfr...137 at ix.netcom.com> wrote:
>
>
>
> > On Tue, 20 Nov 2007 15:11:38 -0800 (PST), mike5160 <mike5.....139 at gmail.com>
> > declaimed the following in comp.lang.python:
>
> > > Hi all,
>
> > > My input file looks like this : ( the data is separated by tabs )
>
> > > 11/26/2007 56.366  898.90  -10.086 23.11   1212.3
> > > 11/26/2007 52.25   897.6   -12.5   12.6    13333.5
> > > 11/26/2007 52.25   897.6   -12.5   12.6    133.5
>
> > > The output I'm trying to get is as follows :
>
> > >  ( Insert NBUSER.Grochamber Values
> > > '11/26/2007','56.366','898.90','-10.086','23.11','1212.3', )
> > >  ( Insert NBUSER.Grochamber Values
> > > '11/26/2007','52.25','897.6','-12.5','12.6','13333.5', )
> > >  ( Insert NBUSER.Grochamber Values
> > > '11/26/2007','52.25','897.6','-12.5','12.6','133.5', )
>
> >         <snip>
>
> > > 2. How do I avoid the "," symbol after the last entry in the line?
> > > (this are supposed to be sql-queries - importing excel based tabbed
> > > data to sql database)
>
> >         If those are SQL inserts, the ( is in the wrong place...
>
> >         insert into NBUSER.Grochamber values (v1, v2, ... , vx)
>
> > > 3. What do I do when the data is missing? Like missing data?
>
> >         First, for reading the file, recommend you look at the CSV module,
> > which can be configured to use TABS rather than COMMAS.
>
> >         For SQL -- if you are going to be writing raw text strings to an
> > output file for later batching, YOU are going to have to supply some
> > means to properly escape the data. The better way is to have the program
> > connect to the database, using the applicable database adapter: MySQLdb
> > for MySQL, pysqlite2 (or some variant) for SQLite3, some generic ODBC
> > adapter if going that route... Let IT do the escaping.
>
> >         Now, since MySQLdb just happens to expose the escaping function, AND
> > just uses %s formatting of the results, one could easily get stuff to
> > write to a file.
>
> > >>> import MySQLdb
> > >>> con = MySQLdb.connect(host="localhost", user="test", passwd="test", db="test")
> > >>> data = [   "11/26/2007        56.366  898.90  -10.086 23.11   1212.3",
>
> > ...             "11/26/2007                897.6   O'Reilly        12.6    13333.5",
> > ...             "11/26/2007        52.25   897.6   -12.5   12.6    133.5"     ]
>
> >         Note how I left out a field (two tabs, nothing between), and how I
> > put in a data item with a ' in it.
>
> > >>> for ln in data:
>
> > ...     flds = ln.split("\t")
> > ...     placeholders = ", ".join(["%s"] * len(flds))
> > ...     sql = BASE % placeholders
> > ...     sql = sql % con.literal(flds)
> > ...     print sql
> > ...
> > insert into NBUSER.Grochamber values ('11/26/2007', '56.366', '898.90',
> > '-10.086', '23.11', '1212.3')
> > insert into NBUSER.Grochamber values ('11/26/2007', '', '897.6',
> > 'O\'Reilly', '12.6', '13333.5')
> > insert into NBUSER.Grochamber values ('11/26/2007', '52.25', '897.6',
> > '-12.5', '12.6', '133.5')
>
> >         Note how the empty field is just '' (If you really need a NULL,
> > you'll have to do some games to put a Python None entity into that empty
> > string field). Also note how the single quote string value has been
> > escaped.
>
> >         Something like this for NULL in STRING DATA -- if a field were
> > numeric 0 it would get substituted with a NULL too...
>
> > >>> for ln in data:
>
> > ...     flds = ln.split("\t")
> > ...     placeholders = ", ".join(["%s"] * len(flds))
> > ...     sql = BASE % placeholders
> > ...     flds = [(fld or None) for fld in flds]
> > ...     sql = sql % con.literal(flds)
> > ...     print sql
> > ...
> > insert into NBUSER.Grochamber values ('11/26/2007', '56.366', '898.90',
> > '-10.086', '23.11', '1212.3')
> > insert into NBUSER.Grochamber values ('11/26/2007', NULL, '897.6',
> > 'O\'Reilly', '12.6', '13333.5')
> > insert into NBUSER.Grochamber values ('11/26/2007', '52.25', '897.6',
> > '-12.5', '12.6', '133.5')
>
> > --
> >         Wulfraed        Dennis Lee Bieber               KD6MOG
> >         wlfr.....140 at ix.netcom.com              wulfr.....141 at bestiaria.com
> >                 HTTP://wlfraed.home.netcom.com/
> >         (Bestiaria Support Staff:               web-a.....142 at bestiaria.com)
> >                 HTTP://www.bestiaria.com/98143
>
> Hi Dennis,
>
> Thanks to you for your reply. I  am a newbie to Python and appreciate
> you helping me. Now, I am importing data from an excel sheet and
> getting it ready for a derby database. I am to use netbeans, since our
> research team uses that. However, derby database uses sql entries to
> update the database. And I m trying to format all the excel data I
> have, which I got from using labview. I suggested that we use awk/perl/
> python etc. and finally after looking at the documentation available I
> figured Python would be best. However, (see my reply above) I am
> looking for a sample book/document etc. somebody suggested we try
> Python Phrasebook. But that one covers a lot of different fields
> whereas for my purposes I need a book with examples on using Python in
> the above manner. If you or anybody knows about this kind of book
> please let me know.
>
> Thank you very much for your help,
> Mike.

Oops! Sorry I did not know what I did , but I just noticed that I
changed the subject of the Discussion twice. I just want every body to
know that it was unintentional.

Thanks,
Mike.