SQL error

[Dan McKenzie]

Hi Guys,

I am trying to move data from a file into our mysql database.

The format of the text file is - ipaddress ipaddress bytes packets
interface-in interface-out eg: 192.168.1.1 192.168.1.2 1522 12 * rob

The sql table is 'ipflows'

This is the code:
____________________________________________________________

#!/usr/bin/python

host = 'localhost'
user = 'username'
passwd = 'password'
dbname = 'databasename'

import MySQLdb

conn = MySQLdb.connect(host = host,
user = user,
passwd = passwd,
db = dbname)

cursor = conn.cursor()

file = open ('ipflow.txt',"r")

for line in file:
data = line.split()
if not line: break
query = '''INSERT INTO ipflows (to,from,bytes,packets) VALUES
("%s","%s","%s","%s","%s","%s"))''' %
(data[0],data[1],data[2],data[3],data[4],data[5])
cursor.execute(query)
file.close()

cursor.close()
conn.commit()
conn.close()
__________________________________________________________


It is returning and error:

Traceback (most recent call last):
File "./process_ipflow.py", line 23, in <module>
cursor.execute(query)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line
166, in execute
self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py",
line 35, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near \'to,from,bytes,packets)
VALUES ("192.168.1.1","192.168.1.2","1522","12","*","\' at line 1')

Seems I have the sql syntax wrong but I can't seem to find the answer

Any help would be appreciated.

Regards,

Dan





Regards,

Dan McKenzie
Puddlenet
Community Broadband Networks
Brisbane, Australia Q4074
+61 (0) 407 622 557
+61 (0) 7 3376 8539

 

[John Machin]

Hi Guys,

I am trying to move data from a file into our mysql database.

The format of the text file is - ipaddress ipaddress bytes packets  
interface-in interface-out eg: 192.168.1.1 192.168.1.2 1522 12 * rob

The sql table is 'ipflows'

This is the code:
____________________________________________________________

#!/usr/bin/python

host    = 'localhost'
user    = 'username'
passwd  = 'password'
dbname  = 'databasename'

import MySQLdb

conn = MySQLdb.connect(host =   host,
                       user =   user,
                       passwd = passwd,
                       db =     dbname)

cursor = conn.cursor()

file = open ('ipflow.txt',"r")

for line in file:
  data = line.split()
  if not line: break

The above line of code is redundant; it can't happen; "line" will
never be "false".

  query = '''INSERT INTO ipflows (to,from,bytes,packets) VALUES  
("%s","%s","%s","%s","%s","%s"))''' %  

You have 6 values but only 4 column names ... looks a bit suss to me.

(data[0],data[1],data[2],data[3],data[4],data[5])

Ummm .. why not just
data
instead of
(data[0],data[1],data[2],data[3],data[4],data[5])
?

  cursor.execute(query)
file.close()

cursor.close()
conn.commit()
conn.close()
__________________________________________________________

It is returning and error:

Traceback (most recent call last):
  File "./process_ipflow.py", line 23, in <module>
    cursor.execute(query)
  File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line  
166, in execute
    self.errorhandler(self, exc, value)
  File "/var/lib/python-support/python2.5/MySQLdb/connections.py",  
line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your  
SQL syntax; check the manual that corresponds to your MySQL server  
version for the right syntax to use near \'to,from,bytes,packets)

TO and FROM are reserved words in just about everybody's version of
SQL. And they're not very meaningful either especially when compared
with interface_in and interface_out.

BUT I'm surprised [not being familiar with MySQL] that you were
allowed to do a CREATE TABLE with those column names.
 

VALUES ("192.168.1.1","192.168.1.2","1522","12","*","\' at line 1')

Dan McKenzie
Puddlenet

whose mascot no doubt is a duck named Jemima

Community Broadband Networks
Brisbane, Australia Q4074

Greetings from Melbourne.

Cheers,
John