469,890 Members | 1,411 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Parsing log in SQL DB to change IPs to hostnames

Hi,

I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.

I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.

A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html

I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:

Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URL http://www.asite.com-10.10.10.10:/fo...lder/page.html

or some equivalent.

Here is what i have so far. Please be kind as it is my first python
program.... :)

#! /usr/bin/python

import socket
import re
import string
import MySQLdb

ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
print "regex match!" + ipRegEx.match(row[0])
# does not make it here.....
newMsg = ipRegEx.sub(query(ipRegEx.match(row[0])),
row[0])
if newMsg != 0:
cursor.execute("" "UPDATE logs SET msg = %s
WHERE seq = &d""", (newMsg,row[1]))
def query(ipAddress):
try:
return socket.gethostbyaddr(ipAddress)[0]
except:
return 0
This is built to sub the name... I would like to change it to the
hsotname-ipaddress instead since ip's can resolve to many
hostnames....

it runs but does not pass the if statements so it quietly does
absolutely nothing.... :)

Thanks so much for any advice....

Apr 10 '07 #1
9 1785
On Apr 10, 8:37 pm, "KDawg44" <KDaw...@gmail.comwrote:
Hi,

I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.

I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.

A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html

I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:

Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URLwww.asite.com-10.10.10.10:/folder/folder/page.html

or some equivalent.

Here is what i have so far. Please be kind as it is my first python
program.... :)

#! /usr/bin/python

import socket
import re
import string
import MySQLdb

ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
....
<snipped rest of the code>
See the documentation of the re module for the difference between
matching and searching.

--
Kushal

Apr 10 '07 #2
On Apr 10, 11:11 am, "Kushal Kumaran" <kushal.kuma...@gmail.com>
wrote:
On Apr 10, 8:37 pm, "KDawg44" <KDaw...@gmail.comwrote:
Hi,
I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.
I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.
A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html
I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URLwww.asite.com-10.10.10.10:/folder/folder/page.html
or some equivalent.
Here is what i have so far. Please be kind as it is my first python
program.... :)
#! /usr/bin/python
import socket
import re
import string
import MySQLdb
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)
cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM REMOVED WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
....
<snipped rest of the code>

See the documentation of the re module for the difference between
matching and searching.

--
Kushal
Thank you very much. I think I have it figured out, except for an
error on the SQL statement:
[----- BEGIN ERROR ---]
Traceback (most recent call last):
File "changeLogs.py", line 47, in ?
cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]

Here is my code

[----- BEGIN CODE ---]
#! /usr/bin/python

import socket
import sys
import re
import string
import MySQLdb

def resolveHost(ipAdds):
ipDict = {}
for ips in ipAdds:
try:
ipDict[ips] = socket.gethostbyaddr(ips)[0]
except:
ipDict[ips] = "Cannot resolve"
return ipDict
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
while(1):
row = cursor.fetchone()
ipAddresses = []
resolvedDict = {}
if row == None:
break
if ipRegEx.search(row[0]):
seqNum = row[1]
ipAddresses = ipRegEx.findall(row[0])
resolvedDict = resolveHost(ipAddresses)
newMsg = row[0]
for ip in resolvedDict.keys():
newMsg = newMsg.replace(ip,ip + "-" +
resolvedDict[ip])
cursor.execute("""UPDATE REMOVED SET msg = %s WHERE
seq = %i""", (newMsg,seqNum))
[----- END CODE ---]

Thanks again!
Apr 10 '07 #3
On Apr 10, 1:54 pm, "KDawg44" <KDaw...@gmail.comwrote:
On Apr 10, 11:11 am, "Kushal Kumaran" <kushal.kuma...@gmail.com>
wrote:
On Apr 10, 8:37 pm, "KDawg44" <KDaw...@gmail.comwrote:
Hi,
I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.
I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.
A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html
I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URLwww.asite.com-10.10.10.10:/folder/folder/page.html
or some equivalent.
Here is what i have so far. Please be kind as it is my first python
program.... :)
#! /usr/bin/python
import socket
import re
import string
import MySQLdb
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)
cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM REMOVED WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
....
<snipped rest of the code>
See the documentation of the re module for the difference between
matching and searching.
--
Kushal

Thank you very much. I think I have it figured out, except for an
error on the SQL statement:

[----- BEGIN ERROR ---]
Traceback (most recent call last):
File "changeLogs.py", line 47, in ?
cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]

Here is my code

[----- BEGIN CODE ---]
#! /usr/bin/python

import socket
import sys
import re
import string
import MySQLdb

def resolveHost(ipAdds):
ipDict = {}
for ips in ipAdds:
try:
ipDict[ips] = socket.gethostbyaddr(ips)[0]
except:
ipDict[ips] = "Cannot resolve"
return ipDict

ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
while(1):
row = cursor.fetchone()
ipAddresses = []
resolvedDict = {}
if row == None:
break
if ipRegEx.search(row[0]):
seqNum = row[1]
ipAddresses = ipRegEx.findall(row[0])
resolvedDict = resolveHost(ipAddresses)
newMsg = row[0]
for ip in resolvedDict.keys():
newMsg = newMsg.replace(ip,ip + "-" +
resolvedDict[ip])
cursor.execute("""UPDATE REMOVED SET msg = %s WHERE
seq = %i""", (newMsg,seqNum))

[----- END CODE ---]

Thanks again!
Also, i tried changing seqNum = row[1] to seqNum = int(row[1]) to cast
it as an integer and I get the same error (because I think that
pulling from a DB makes everything a string by default?)

Thanks.

Apr 10 '07 #4
KDawg44 wrote:
On Apr 10, 11:11 am, "Kushal Kumaran" <kushal.kuma...@gmail.com>
wrote:
>On Apr 10, 8:37 pm, "KDawg44" <KDaw...@gmail.comwrote:
>>Hi,
I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.
I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.
A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html
I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URLwww.asite.com-10.10.10.10:/folder/folder/page.html
or some equivalent.
Here is what i have so far. Please be kind as it is my first python
program.... :)
#! /usr/bin/python
import socket
import re
import string
import MySQLdb
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)
cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM REMOVED WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
....
<snipped rest of the code>
See the documentation of the re module for the difference between
matching and searching.

--
Kushal

Thank you very much. I think I have it figured out, except for an
error on the SQL statement:
[----- BEGIN ERROR ---]
Traceback (most recent call last):
File "changeLogs.py", line 47, in ?
cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]

Here is my code

[----- BEGIN CODE ---]
#! /usr/bin/python

import socket
import sys
import re
import string
import MySQLdb

def resolveHost(ipAdds):
ipDict = {}
for ips in ipAdds:
try:
ipDict[ips] = socket.gethostbyaddr(ips)[0]
except:
ipDict[ips] = "Cannot resolve"
return ipDict
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
while(1):
row = cursor.fetchone()
ipAddresses = []
resolvedDict = {}
if row == None:
break
if ipRegEx.search(row[0]):
seqNum = row[1]
ipAddresses = ipRegEx.findall(row[0])
resolvedDict = resolveHost(ipAddresses)
newMsg = row[0]
for ip in resolvedDict.keys():
newMsg = newMsg.replace(ip,ip + "-" +
resolvedDict[ip])
cursor.execute("""UPDATE REMOVED SET msg = %s WHERE
seq = %i""", (newMsg,seqNum))
[----- END CODE ---]

Thanks again!

Since the source line that the traceback complains about doesn't appear
in the quoted code it's difficult to know what's going wrong. I'd hazard
a guess that you have a string in seqNum instead of an integer message
number (in which case try using int(seqNum) instead).

Otherwise show us the real code, not the one after you modified it to
try and make it work, amd we might be able to help more ;-)

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com

Apr 10 '07 #5
On Apr 10, 2:47 pm, Steve Holden <s...@holdenweb.comwrote:
KDawg44 wrote:
On Apr 10, 11:11 am, "Kushal Kumaran" <kushal.kuma...@gmail.com>
wrote:
On Apr 10, 8:37 pm, "KDawg44" <KDaw...@gmail.comwrote:
>Hi,
I am brand new to Python. In learning anything, I find it useful to
actually try to write a useful program to try to tackle an actual
problem.
I have a syslog server and I would like to parse the syslog messages
and try to change any ips to resolved hostnames. Unfortunately, I am
not getting any matches on my regular expression.
A message will look something like this:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
10.10.10.10:/folder/folder/page.html
I would like to change the message to have the hostnames, or even
better actually, have it appear as hostname-ip address. So a changed
message would look like:
Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
URLwww.asite.com-10.10.10.10:/folder/folder/page.html
or some equivalent.
Here is what i have so far. Please be kind as it is my first python
program.... :)
#! /usr/bin/python
import socket
import re
import string
import MySQLdb
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)
cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM REMOVED WHERE seq = 507702")
# one specific message so that it doesn't parse the whole DB during
testing...
while(1):
row = cursor.fetchone()
if row == None:
break
if ipRegEx.match(row[0]):
....
<snipped rest of the code>
See the documentation of the re module for the difference between
matching and searching.
--
Kushal
Thank you very much. I think I have it figured out, except for an
error on the SQL statement:
[----- BEGIN ERROR ---]
Traceback (most recent call last):
File "changeLogs.py", line 47, in ?
cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]
Here is my code
[----- BEGIN CODE ---]
#! /usr/bin/python
import socket
import sys
import re
import string
import MySQLdb
def resolveHost(ipAdds):
ipDict = {}
for ips in ipAdds:
try:
ipDict[ips] = socket.gethostbyaddr(ips)[0]
except:
ipDict[ips] = "Cannot resolve"
return ipDict
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
sys.exit(1)
cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
while(1):
row = cursor.fetchone()
ipAddresses = []
resolvedDict = {}
if row == None:
break
if ipRegEx.search(row[0]):
seqNum = row[1]
ipAddresses = ipRegEx.findall(row[0])
resolvedDict = resolveHost(ipAddresses)
newMsg = row[0]
for ip in resolvedDict.keys():
newMsg = newMsg.replace(ip,ip + "-" +
resolvedDict[ip])
cursor.execute("""UPDATE REMOVED SET msg = %s WHERE
seq = %i""", (newMsg,seqNum))
[----- END CODE ---]
Thanks again!

Since the source line that the traceback complains about doesn't appear
in the quoted code it's difficult to know what's going wrong. I'd hazard
a guess that you have a string in seqNum instead of an integer message
number (in which case try using int(seqNum) instead).

Otherwise show us the real code, not the one after you modified it to
try and make it work, amd we might be able to help more ;-)

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com
hmmm... I tried seqNum = int(seqNum[i]) to make seqNum an integer.
That is the real code with the connection to the DB hidden and the
name of the table which I didnt remove on the first query anyway so it
was pointless. This is the whole code.

THanks.

Apr 10 '07 #6
On Apr 11, 1:15 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On 10 Apr 2007 11:54:18 -0700, "KDawg44" <KDaw...@gmail.comdeclaimed
the following in comp.lang.python:
[----- BEGIN ERROR ---]
Traceback (most recent call last):
File "changeLogs.py", line 47, in ?
cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]
Here is my code
[----- BEGIN CODE ---]

Comments interspersed
#! /usr/bin/python
import socket
import sys
import re
import string
import MySQLdb
def resolveHost(ipAdds):
ipDict = {}
for ips in ipAdds:
try:
ipDict[ips] = socket.gethostbyaddr(ips)[0]
except:
ipDict[ips] = "Cannot resolve"
return ipDict
ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)
try:
conn = MySQLdb.connect(host="REMOVED",
user="REMOVED",
passwd="REMOVED",
db="REMOVED")
except MySQLdb.Error, e:
print "Error connecting to the database: %s - %s " % (e.args[0], e.args[1])

#just use %s unless you need particular numeric formatting
#(field width and decimal places, as in %8.4f) sys.exit(1)
cursor = conn.cursor()

cursor.execute("SELECT msg, seq FROM logs WHERE seq = 507702")
#why the ` around the table name?
#I also presume at some point that 507702 becomes dynamic -- otherwise
the returned
#seq field will always be 507702,-- and if there are multiple instances,
the update
#statement below will change all of them each time

#while(1):
# row = cursor.fetchone()
for row in cursor: ipAddresses = []
resolvedDict = {}

# if row == None:
# break if ipRegEx.search(row[0]):
seqNum = row[1]
ipAddresses = ipRegEx.findall(row[0])
resolvedDict = resolveHost(ipAddresses)
newMsg = row[0]
for ip in resolvedDict.keys():
newMsg = newMsg.replace(ip,
ip + "-" +resolvedDict[ip])

cursor.execute(
"""UPDATE REMOVED SET msg = %s WHERE seq = %s""",
(newMsg, seqNum))
#the documented parameter code for MySQLdb is %s; use of
anything
#else relies upon luck -- luck that the safety logic inside
the module
#leaves the parameter in a format compatible with the format
code!
#%s is the only code you should use with MySQLd --
regardless of
#the type of the actual data

--
Wulfraed Dennis Lee Bieber KD6MOG
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/
ohhh okay. thanks so much. I knew that it came out as strings, i
guess it makes sense that I would have to send it back IN as a
string. Changed that and now it works! THanks so much.

I just specified once specific field so that it would not change the
whole db on a test run.

Thanks so much.

Apr 11 '07 #7
KDawg44 wrote:
[ ... ]
>
ohhh okay. thanks so much. I knew that it came out as strings, i
guess it makes sense that I would have to send it back IN as a
string. Changed that and now it works! THanks so much.

I just specified once specific field so that it would not change the
whole db on a test run.

Thanks so much.
It's great that you solved your problem, but please disabuse yourself of
the notion that the database returns everything as strings:
>>curs.execute("SELECT * from contact")
data = curs.fetchone()
for item in data:
... print type(item)
...
<type 'int'>
<type 'str'>
<type 'str'>
<type 'int'>
<type 'int'>
<type 'NoneType'>
<type 'NoneType'>
>>>
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com

Apr 11 '07 #8
On Apr 11, 11:58 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
On 11 Apr 2007 05:39:21 -0700, "KDawg44" <KDaw...@gmail.comdeclaimed
the following in comp.lang.python:
ohhh okay. thanks so much. I knew that it came out as strings, i
guess it makes sense that I would have to send it back IN as a
string. Changed that and now it works! THanks so much.

I'd be interested in the schema definition -- my experience has been
that numeric fields come out of MySQLdb as numerics...
>import MySQLdb
cn = MySQLdb.connect(host="localhost", user="BestiariaCP", db="bestiaria")
cr = cn.cursor()
cr.execute("select * from comics where banner is not Null")
35L
>cr.fetchone()

(2L, 'Anxiety Cafe', 'Anxiety Cafe',
'http://rockerbot.artistrealm.org/', 'images/ronandrockerbutton.jpg',
234L, 60L, 'Platitudes from a platypus.', 'On hiatus - site rebuild',
'N', 'Y')

Note the first field there -- a Python long integer (the schema
defines that as an integer auto_increment field). Also the "234L, 60L,"
(image width/height) -- all returned as numeric.

If your data is coming out as a string, I'd suspect the schema
defined it as a character type.
>cr.description

(('ID', 3, 2, 11, 11, 0, 0), ('name', 253, 33, 100, 100, 0, 0),
('sortname', 253, 33, 100, 100, 0, 0), ('URL', 253, 56, 75, 75, 0, 0),
('banner', 253, 29, 75, 75, 0, 1), ('width', 3, 3, 11, 11, 0, 1),
('height', 3, 3, 11, 11, 0, 1), ('description', 252, 170, 65535, 65535,
0, 0), ('occurs', 253, 58, 125, 125, 0, 1), ('isactive', 254, 1, 1, 1,
0, 0), ('isonline', 254, 1, 1, 1, 0, 0))

CREATE TABLE `comics` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`sortname` varchar(100) NOT NULL default '',
`URL` varchar(75) NOT NULL default '',
`banner` varchar(75) default NULL,
`width` int(11) default NULL,
`height` int(11) default NULL,
`description` text NOT NULL,
`occurs` varchar(125) default NULL,
`isactive` enum('N','Y') NOT NULL default 'Y',
`isonline` enum('N','Y') NOT NULL default 'Y',
PRIMARY KEY (`ID`),
KEY `namesort` (`sortname`)
) ENGINE=MyISAM AUTO_INCREMENT=92 DEFAULT CHARSET=latin1;
--
Wulfraed Dennis Lee Bieber KD6MOG
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/
I see your point. Somewhere in my head I must have mixed them up. It
goes in as a string but comes out as whatever data type.

Thanks.

Apr 11 '07 #9
KDawg44 wrote:
On Apr 11, 11:58 am, Dennis Lee Bieber <wlfr...@ix.netcom.comwrote:
>On 11 Apr 2007 05:39:21 -0700, "KDawg44" <KDaw...@gmail.comdeclaimed
the following in comp.lang.python:
>>ohhh okay. thanks so much. I knew that it came out as strings, i
guess it makes sense that I would have to send it back IN as a
string. Changed that and now it works! THanks so much.
I'd be interested in the schema definition -- my experience has been
that numeric fields come out of MySQLdb as numerics...
>>>>import MySQLdb
cn = MySQLdb.connect(host="localhost", user="BestiariaCP", db="bestiaria")
cr = cn.cursor()
cr.execute("select * from comics where banner is not Null")
35L
>>>>cr.fetchone()
(2L, 'Anxiety Cafe', 'Anxiety Cafe',
'http://rockerbot.artistrealm.org/', 'images/ronandrockerbutton.jpg',
234L, 60L, 'Platitudes from a platypus.', 'On hiatus - site rebuild',
'N', 'Y')

Note the first field there -- a Python long integer (the schema
defines that as an integer auto_increment field). Also the "234L, 60L,"
(image width/height) -- all returned as numeric.

If your data is coming out as a string, I'd suspect the schema
defined it as a character type.
>>>>cr.description
(('ID', 3, 2, 11, 11, 0, 0), ('name', 253, 33, 100, 100, 0, 0),
('sortname', 253, 33, 100, 100, 0, 0), ('URL', 253, 56, 75, 75, 0, 0),
('banner', 253, 29, 75, 75, 0, 1), ('width', 3, 3, 11, 11, 0, 1),
('height', 3, 3, 11, 11, 0, 1), ('description', 252, 170, 65535, 65535,
0, 0), ('occurs', 253, 58, 125, 125, 0, 1), ('isactive', 254, 1, 1, 1,
0, 0), ('isonline', 254, 1, 1, 1, 0, 0))

CREATE TABLE `comics` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`sortname` varchar(100) NOT NULL default '',
`URL` varchar(75) NOT NULL default '',
`banner` varchar(75) default NULL,
`width` int(11) default NULL,
`height` int(11) default NULL,
`description` text NOT NULL,
`occurs` varchar(125) default NULL,
`isactive` enum('N','Y') NOT NULL default 'Y',
`isonline` enum('N','Y') NOT NULL default 'Y',
PRIMARY KEY (`ID`),
KEY `namesort` (`sortname`)
) ENGINE=MyISAM AUTO_INCREMENT=92 DEFAULT CHARSET=latin1;
--
Wulfraed Dennis Lee Bieber KD6MOG
wlfr...@ix.netcom.com wulfr...@bestiaria.com
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: web-a...@bestiaria.com)
HTTP://www.bestiaria.com/

I see your point. Somewhere in my head I must have mixed them up. It
goes in as a string but comes out as whatever data type.

Thanks.
No, it doesn't even go *in* as a string (though a lot of database
modules will convert data of the wrong type if they reasonably can).

"%s" is just what's known as a "parameter marker" - each parameter
marker is substituted by suceeding values form the data tuple provided
as the second argument to cursor.execute().

Some database modules use "?" as a parameter marker.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
Recent Ramblings http://holdenweb.blogspot.com

Apr 11 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Gerrit Holl | last post: by
5 posts views Thread by Rafal 'Raf256' Maj | last post: by
2 posts views Thread by David Knaack | last post: by
1 post views Thread by Christoph Bisping | last post: by
5 posts views Thread by Martin Walke | last post: by
1 post views Thread by yonido | last post: by
13 posts views Thread by Chris Carlen | last post: by
1 post views Thread by padmagvs | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.