SQL error

[John Machin]

On Feb 12, 11:57 am, Dan McKenzie <d... at puddle.net.au> wrote:
> 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