On Nov 21, 11:36 am, mike5160 <[EMAIL PROTECTED]> wrote:
> 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/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.
--
http://mail.python.org/mailman/listinfo/python-list
