Problem Using a Function Parameter in a MySQL Statement

[mece]

I am attempting to input data from a log file into a database with tables for each network device. The table name is set via a parameter for the function. The script crashes after running stating the table name does not exist. If the actual table name (routerA) is listed in the insert statement the script runs correctly.

I have tried assigning the parameter to a variable within the function and changing the type to str() with no success. Does anyone have a suggestion?

Thanks in advance

Mece

Error Message

Traceback (most recent call last):
File "router_parser.py", line 61, in ?
log_process(line, table_name="routerA")
File "router_parser.py", line 54, in log_process
q.execute('INSERT INTO table_name (device_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")' % (device_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts))
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table 'router.table_name' doesn't exist")

import fileinput
import MySQLdb

def log_process(line,table_name=""):
        line = line.split()
        device_name = line[3]
        event_date = line[0]
        event_time = line[1]
        event_action = line[11]
        event_protocol = line[12].upper()
        event_src_ip = line[13].split("(")[0]
        event_src_port = line[13].split("(")[1].rstrip(")")
        event_dst_ip = line[15].split("(")[0]
        event_dst_port = line[15].split("(")[1].replace("),","")
        event_num_pckts = line[16]
        q.execute('INSERT INTO table_name (device_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")' % (device_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts))

for line in fileinput.input("log_file.txt"):
    if "routerA" in line and "ACCESS" in line:
        log_process(line, table_name="routerA")

[woooee]

Note the last line

_mysql_exceptions.ProgrammingError: (1146, "Table 'router.table_name' doesn't exist")

It is looking for "table_name" not the contents of the variable. So you want to insert the actual name which can be done in a number of ways along the general line of the following. This is just your statement with one extra "%s" added for the table_name. It is not tested, and I don't use MySQL, but the principle should work.

SQL_statement = 'INSERT INTO %s(device_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")' %
(table_name,vice_name,event_date,event_time,event_action,event_protocol,event_src_ip,event_src_port,event_dst_ip,event_dst_port,event_num_pckts) 

print SQL_statement  ## examine for correctness
q.execute(SQL_statement)

[mece]

Thanks that worked!