By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,095 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,095 IT Pros & Developers. It's quick & easy.

Splitting lines from a database query

P: n/a
I have an application where I need to take a query from an existing
database and send it to a web api.

Here's a cut down version of my existing code:

for foo in mssql.fetch_array();
bar = foo[2] #trims the first result which we don't use
for x in bar:
for y in x:
print y
print

This produces each value on a line, here's an example result:

Jane Mary
SIMPSON
0411231234
Dr I Feelgood
2006-12-27 15:00:00

John
DOE
None
Dr I Feelgood
2006-12-27 15:30:00

Frank
SPENCER

Dr I Feelgood
2006-12-27 16:00:00

There are always 5 values, but some are blank and some are 'None'.
I'd like to split the lines so I get something resembling XML, like this:

<FNAME>Frank</FNAME>
<SNAME>Spencer</SNAME>
<PHONE></PHONE>
<DR>Dr I Feelgood</DR>
<TIME>2006-12-27 16:00:00</TIME>

Thanks in advance for your help,
Peter.
Dec 26 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
ZeD
Peter Machell wrote:
I have an application where I need to take a query from an existing
database and send it to a web api.
[...]
There are always 5 values, but some are blank and some are 'None'.
I'd like to split the lines so I get something resembling XML, like this:
<FNAME>Frank</FNAME>
<SNAME>Spencer</SNAME>
<PHONE></PHONE>
<DR>Dr I Feelgood</DR>
<TIME>2006-12-27 16:00:00</TIME>
quick and dirty solution:

bar = (
("Jane Mary","SIMPSON","0411231234","Dr I Feelgood","2006-12-27 15:00:00"),
("John","DOE","None","Dr I Feelgood","2006-12-27 15:30:00"),
("Frank","SPENCER","","Dr I Feelgood","2006-12-27 16:00:00")
)

spam ="FNAME", "SNAME", "PHONE", "DR","TIME"

for x in bar:
i=0
while i<5:
if x[i] != 'None':
print "<%s>%s</%s>" % (spam[i], x[i], spam[i])
else:
print "<%s></%s>" % (spam[i], spam[i])
i+=1
print

--
Under construction
Dec 26 '06 #2

P: n/a
ZeD wrote:
Peter Machell wrote:
>I have an application where I need to take a query from an existing
database and send it to a web api.

[...]
>There are always 5 values, but some are blank and some are 'None'.
I'd like to split the lines so I get something resembling XML, like this:
<FNAME>Frank</FNAME>
<SNAME>Spencer</SNAME>
<PHONE></PHONE>
<DR>Dr I Feelgood</DR>
<TIME>2006-12-27 16:00:00</TIME>

quick and dirty solution:

bar = (
("Jane Mary","SIMPSON","0411231234","Dr I Feelgood","2006-12-27 15:00:00"),
("John","DOE","None","Dr I Feelgood","2006-12-27 15:30:00"),
("Frank","SPENCER","","Dr I Feelgood","2006-12-27 16:00:00")
)

spam ="FNAME", "SNAME", "PHONE", "DR","TIME"

for x in bar:
i=0
while i<5:
if x[i] != 'None':
print "<%s>%s</%s>" % (spam[i], x[i], spam[i])
else:
print "<%s></%s>" % (spam[i], spam[i])
i+=1
print
Thanks very much ZeD. This will do what I need to.

The next step is to do some regex on the phone number to ensure it's
local and correct. How can I split these up so each value has a key?

Peter.
Dec 26 '06 #3

P: n/a
Peter Machell wrote:
ZeD wrote: <An excellent response providing code>

Thanks very much ZeD. This will do what I need to.
The next step is to do some regex on the phone number to ensure it's
local and correct. How can I split these up so each value has a key?
Well, you should try that, unless you intend to get the newsgroup to
write your code for you. Come back with your efforts and any problems
you have with them.

--Scott David Daniels
sc***********@acm.org
Dec 26 '06 #4

P: n/a
Scott David Daniels wrote:
Peter Machell wrote:
>ZeD wrote: <An excellent response providing code>

Thanks very much ZeD. This will do what I need to.
The next step is to do some regex on the phone number to ensure it's
local and correct. How can I split these up so each value has a key?

Well, you should try that, unless you intend to get the newsgroup to
write your code for you. Come back with your efforts and any problems
you have with them.
As we say in Australia, fair enough.
I can almost do it this way:

for x in bar:
fname = x[0]
if fname == "":
fname == "None"
sname = x[1]
if sname == "":
sname == "None"

print "<FNAME>"+fname+"</FNAME>"+"<SNAME>"+sname+"</SNAME>"

Except that I should be using a list and loop to do the null checking,
and it still stops when (I think) it hits a blank value:
TypeError: cannot concatenate 'str' and 'NoneType' objects

thanks,
Peter.
Dec 26 '06 #5

P: n/a
Peter Machell wrote:
I can almost do it this way:

for x in bar:
fname = x[0]
if fname == "":
fname == "None"
sname = x[1]
if sname == "":
sname == "None"

print "<FNAME>"+fname+"</FNAME>"+"<SNAME>"+sname+"</SNAME>"
for this:
for row in bar:
print "<FNAME>%s</FNAME><SNAME>%s</SNAME>" % (
row[0] or 'None', row[1] or 'None')

Except that I should be using a list and loop to do the null checking,
and it still stops when (I think) it hits a blank value:
TypeError: cannot concatenate 'str' and 'NoneType' objects
What's the data and program that does that?

--Scott David Daniels
sc***********@acm.org
Dec 26 '06 #6

P: n/a
At Tuesday 26/12/2006 18:57, Peter Machell wrote:
>for x in bar:
fname = x[0]
if fname == "":
fname == "None"
sname = x[1]
if sname == "":
sname == "None"

print "<FNAME>"+fname+"</FNAME>"+"<SNAME>"+sname+"</SNAME>"

Except that I should be using a list and loop to do the null checking,
and it still stops when (I think) it hits a blank value:
TypeError: cannot concatenate 'str' and 'NoneType' objects
Perhaps this is what you intended to write:

if fname == "":
fname = "None"

and since None != "", you have to test for it:

if fname is None or fname == "":
fname = "None"

Note that doing this will deliberately write the text "None" whenever
the field is empty, so you will never get <FNAME></FNAME>, instead:
<FNAME>None</FNAME>. If you want to get the former, use: if fname is
None: fname = ""
--
Gabriel Genellina
Softlab SRL


__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

Dec 26 '06 #7

P: n/a
Peter Machell wrote:
Scott David Daniels wrote:
Peter Machell wrote:
ZeD wrote: <An excellent response providing code>

Thanks very much ZeD. This will do what I need to.
The next step is to do some regex on the phone number to ensure it's
local and correct. How can I split these up so each value has a key?
Well, you should try that, unless you intend to get the newsgroup to
write your code for you. Come back with your efforts and any problems
you have with them.

As we say in Australia, fair enough.
I can almost do it this way:
As we say in Australia, it's good to see that you're neither a bludger
nor an utter dill/drongo/nong :-)
>
for x in bar:
Insert some code to show you what you've actually got:

print repr(x)
fname = x[0]
if fname == "":
fname == "None"
sname = x[1]
if sname == "":
sname == "None"

print "<FNAME>"+fname+"</FNAME>"+"<SNAME>"+sname+"</SNAME>"

Except that I should be using a list and loop to do the null checking,
That's *not* a null that you're checking for, mate, it's a zero-length
string.
and it still stops when (I think) it hits a blank value:
TypeError: cannot concatenate 'str' and 'NoneType' objects
It's not a "blank value", it's an object None which you get as a
substitute for a NULL in the database; it means "no value at all", as
opposed to a zero-length string, which is a value.

You can test for None by:
if thing is None:
You can test for both "" and None in one hit by doing this:
if not thing:

BTW, why do you want to fill in the missing data with the string value
"None" (which could conceivably be a valid surname), instead of leaving
it blank or empty?

Cheers,
John

Dec 26 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.