On Nov 21, 1:22 am, Dennis Lee Bieber <[EMAIL PROTECTED]> wrote:
> On Tue, 20 Nov 2007 15:11:38 -0800 (PST), mike5160 <[EMAIL PROTECTED]>
> 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
> [EMAIL PROTECTED] [EMAIL PROTECTED]
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff: [EMAIL PROTECTED])
> HTTP://www.bestiaria.com/98
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.
--
http://mail.python.org/mailman/listinfo/python-list
