Splitting lines from a database query

[Peter Machell]

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.

 

[ZeD]

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 != 'None':
print "<%s>%s</%s>" % (spam, x, spam)
else:
print "<%s></%s>" % (spam, spam)
i+=1
print

 

[Peter Machell]

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 != 'None':
print "<%s>%s</%s>" % (spam, x, spam)
else:
print "<%s></%s>" % (spam, spam)
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.

 

[Peter Machell]

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.

 

[Scott David Daniels]

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
(e-mail address removed)

 

[Gabriel Genellina]

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

 

[John Machin]

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