MySQL INSERT Query Fails with Syntax Error in Python

Other Questions MySQL
1

I’m encountering a syntax error when attempting to insert data into my MySQL database using Python. As I’m quite new to programming, I might be overlooking something basic.

Here’s the code I’m working with:

import os
import MySQLdb
import datetime

connection = MySQLdb.connect(host="localhost", user="root", passwd="mypass", db="testdb")
cur = connection.cursor()

logfile = os.popen('tail -f application.log')
while True:
        line = logfile.readline()
        if not line:
                break
        parts = line.split()
        if parts[2] == 'ERROR':
                timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                cur.execute("INSERT INTO events (timestamp, type, details) values (timestamp, parts[3], parts[6])")
        if parts[2] == 'SUCCESS':
                print(parts[3] + '\t' + parts[6])

When I run this, I receive the following error:

Traceback (most recent call last):
  File "monitor.py", line 15, in <module>
    cur.execute("INSERT INTO events (timestamp, type, details) values (timestamp, parts[3], parts[6])")
  File "/usr/lib/python2.7/site-packages/MySQLdb/cursors.py", line 163, in execute
    self.errorhandler(self, exc, value)
ProgrammingError: (1064, "You have an error in your SQL syntax near 'parts[3], parts[6]' at line 1")

I believe that my INSERT statement might have an issue, but I’m not exactly sure what it is. The data looks like this:

ACTIVE    System.Process.Started:[email protected]:14.32

Do I need to escape the values somehow before attempting to insert them? What am I missing here?

2

Your issue arises from including Python variable names directly in your SQL string. MySQL interprets timestamp, parts[3], and parts[6] as column names, leading to the syntax error. Instead, implement parameter substitution using %s placeholders. Revise your execute line to: cur.execute("INSERT INTO events (timestamp, type, details) VALUES (%s, %s, %s)", (timestamp, parts[3], parts[6])). This correctly handles escaping and helps prevent SQL injection. Many beginners encounter this mistake, so don’t worry. Also, remember to call connection.commit() after your inserts to ensure changes are saved.

3

You’re mixing Python variables directly into the SQL string without parameterization. When you write values (timestamp, parts[3], parts[6]), MySQL thinks these are column names, not the actual values you want to insert. I hit this same issue when I started with database connections. Use parameterized queries instead - it’ll fix the syntax error and protect against SQL injection. Change your execute statement to use placeholders and pass the variables as a tuple. Also, don’t forget to commit your transaction after the insert, or your data won’t actually save even if the query runs.

4

You’re getting that syntax error because you’re putting Python variables straight into the SQL string. MySQL thinks timestamp, parts[3], and parts[6] are literal text, not your actual data. I hit this exact same issue on my first database project and wasted hours figuring it out. You need to use proper parameter binding with MySQLdb. Change your execute line to cur.execute("INSERT INTO events (timestamp, type, details) VALUES (%s, %s, %s)", (timestamp, parts[3], parts[6])). Don’t forget to add connection.commit() after the execute or your changes won’t stick. Bonus: this automatically sanitizes your inputs and prevents SQL injection attacks.

5

yeah, you’re directly putting python vars in the sql string - that won’t fly. mysql sees those as just text, not values. use placeholders: cur.execute("INSERT INTO events (timestamp, type, details) VALUES (%s, %s, %s)", (timestamp, parts[3], parts[6])). that’s the way to go!

6

Yeah, everyone’s catching the SQL syntax issue, but here’s a better approach that’ll save you major headaches later.

Manually parsing logs and writing database insertion code is error-prone and becomes a maintenance nightmare. I’ve watched this pattern crash and burn in production countless times.

Skip fixing the parameterization - automate the whole log monitoring workflow instead. Build a system that watches your log files, parses them smart, and handles database operations without custom SQL code.

You’ll get proper error handling, retry logic, and scalability baked in. Easy to add filtering, transformations, or alerts when specific patterns show up.

I’ve built similar systems processing millions of entries daily. The automated route kills syntax errors, handles database connections right, and gives you something that actually works in real environments.

Check out Latenode for building this kind of automated workflow: https://latenode.com