Parsing log in SQL DB to change IPs to hostnames

K

KDawg44

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 www.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]):
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....
 
K

Kushal Kumaran

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.
 
K

KDawg44

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.

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!
 
K

KDawg44

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.

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.
 
S

Steve Holden

KDawg44 said:
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.

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
 
K

KDawg44

KDawg44 said:
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.
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) 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.
 
D

Dennis Lee Bieber

[----- 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
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
D

Dennis Lee Bieber

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).
That source line is from the MySQLdb adapter itself. db.literal() is
the function that converts parameters to "safe" values for insertion
into the database.

I suspect the problem is that it is quoting the seqNum value -- but
since the query is NOT using %s (as documented for MySQLdb), instead
specifying %d /integer/ replacement, the string interpolation fails.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
K

KDawg44

[----- 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
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
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.
 
S

Steve Holden

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:
... print type(item)
...
<type 'int'>
<type 'str'>
<type 'str'>
<type 'int'>
<type 'int'>

regards
Steve
 
D

Dennis Lee Bieber

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...
(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.
(('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
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
K

KDawg44

I'd be interested in the schema definition -- my experience has been
that numeric fields come out of MySQLdb as numerics...


(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.


(('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
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
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.
 
S

Steve Holden

KDawg44 said:
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
 
D

Dennis Lee Bieber

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.

The MySQLdb module doesn't help much, as it does, internally, use
the Python string interpolation to populate the query string (as could
be seen in the original trace dump). However, the parameter tuple is not
passed directly to the query string -- it gets fed to a function that
applies quote marks around string data, escapes special codes internal
to strings, converts Python None to SQL NULL [string text, not internal
representation]... and probably converts long integers to a string
format so it can strip the "L" (which is not valid for MySQL integers)
{and this is what would have led to the error trace, as the string
representation can't be converted by a %d format code).

Since a query sent to the database engine is a string, the only
thing that identifies string data /in/ the query is the presence of the
proper quote marks... Marks which are /added/ to the string itself by
MySQLdb...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,898
Latest member
BlairH7607

Latest Threads

Top