Error loading log file data into mysql using cvs format and python

Question

I am trying to take a data from a log file in cvs format, open the log file and inserting row by row into mysql. I am getting an error like this:

ERROR Traceback (most recent call last): File "/Users/alex/PycharmProjects/PA_REPORTING/padb_populate.py", line 26, in VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', row) File "/Users/alex/anaconda/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute query = query % tuple([db.literal(item) for item in args]) TypeError: not all arguments converted during string formatting.

import csv
import MySQLdb

mydb = MySQLdb.connect(host='192.168.56.103',
    user='user',
    passwd='pass',
    db='palogdb')
cursor = mydb.cursor()

csv_data = csv.reader(file('/tmp/PALOG_DEMODATA-100.csv'))
for row in csv_data:

    cursor.execute('INSERT INTO palogdb(RECEIVE_TIME,SERIAL,TYPE,SUBTYPE,COL1,TIME_GENERATED,SRC,DST,NATSRC,NATDST,RULE,\
  SRCUSR,DSTUSR,APP,VSYS1,FROM,TO,INBOUND_IF,OUTBOUND_IF,LOGSET,COL2,SESSIONID,COL3,REPEATCNT,SOURCEPORT,NATSPORT,NATDPORT, \
    FLAGS,PROTO,ACTION,BYTES,BYTES_SENT,BYTES_RECEIVED,PACKETS,START,ELAPSED,CATEGORY,COL4,SEQNO,ACTIONFLAGS,SRCLOC,DSTLOC,NONE, \
    PKTS_SENT,PKTS_RECEIVED,SESSION_END_REASON) \
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', row)
#close the connection to the database.
mydb.commit()
cursor.close()

Answer 1

Is it possible, that you don't have enough data in row for all your %s's? Maybe your row is interpreted as one value, and thus only the first %s is expanded? Try *row to expand the vector to values.

To debug, you could try to build the string passed to execute by some other method, e.g.

sql_string = 'INSERT ... VALUES ({}, {}, {})'.format(*row)

and print it. If you get such an error, you can check, whether the generated string looks reasonable...