title: "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 '' at line 1"
body: <p>I'm trying to execute an insert query. <strong>It works when I directly copy and paste it to the mysql command prompt, but fails when I execute it from Python.</strong> I'm getting this error with MySQLdb (also tried using _mysql directly and get the same error). </p>
  
  <p>The error is the same as this question, but the answer does not work for my problem (my query is on a single line): <a href="https://stackoverflow.com/questions/11155489/mysql-the-right-syntax-to-use-near-at-line-1-error">MySQL the right syntax to use near &#39;&#39; at line 1 error</a></p>
  
  <pre><code>query = """INSERT INTO %s(%s) VALUES (%f) ON DUPLICATE KEY UPDATE %s=%f"""%(table_name,measurement_type,value,measurement_type,value)
  print query 
  cur.execute(query)
  </code></pre>
  
  <p>Result (it prints the query, which when copied directly into MySQL command prompt executes fine, and then crashes):</p>
  
  <pre><code>INSERT INTO D02CA10B13E5$accelerometer_X(periodic) VALUES (79.000000) ON DUPLICATE KEY UPDATE periodic=79.000000
  Traceback (most recent call last):
  File "collect_data.py", line 145, in &lt;module&gt;
  process_data_array(data_bytes[start:start+sensor_packet_size])
  File "collect_data.py", line 105, in process_data_array
  record_data(MAC,sensor_name,"X",code_name,X,cur)
  File "collect_data.py", line 58, in record_data
  cur.execute(query)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
  self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, 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 '' at line 1")
  </code></pre>
  
  <p>I tried turning on profiling because I can't seem be able to access _last_executed from my cursor (something others have suggested). It seems that my cursor does not have this property (was it removed?).</p>
  
  <pre><code>query = """INSERT INTO %s(%s) VALUES (%f) ON DUPLICATE KEY UPDATE %s=%f"""%(table_name,measurement_type,value,measurement_type,value)
  print query 
  #cur.execute(query)
  try:
      cur.execute(query)
  except:
      cur.execute('show profiles')
      for row in cur:
          print row
      cur.execute("set profiling = 0")
      exit()
  </code></pre>
  
  <p>This shows me an incomplete query:</p>
  
  <pre><code>INSERT INTO D02CA10B13E5$accelerometer_X(periodic) VALUES (80.000000) ON DUPLICATE KEY UPDATE periodic=80.000000
  (1L, 5.925e-05, 'INSERT INTO D02CA10B13E5')
  </code></pre>
  
  <p>Some suggest splitting the query into multiple lines (which contradicts the  suggestion in the link I posted above). Anyway, it narrows the search to one line (apparently the Python module doesn't like either my table name or column name which are on the third line)</p>
  
  <pre><code>query = "INSERT \nINTO \n%s(%s) \nVALUES \n(%f) \nON \nDUPLICATE \nKEY \nUPDATE %s=%f"%(table_name,measurement_type,value,measurement_type,value)
  print query 
  cur.execute(query)
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>INSERT 
  INTO 
  D02CA10B13E5$accelerometer_X(periodic) 
  VALUES 
  (80.000000) 
  ON 
  DUPLICATE 
  KEY 
  UPDATE periodic=80.000000
  Traceback (most recent call last):
  File "collect_data.py", line 145, in &lt;module&gt;
  process_data_array(data_bytes[start:start+sensor_packet_size])
  File "collect_data.py", line 105, in process_data_array
  record_data(MAC,sensor_name,"X",code_name,X,cur)
  File "collect_data.py", line 58, in record_data
  cur.execute(query)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
  self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, 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 '' at line 3")
  </code></pre>
  
  <p>Again, this query executes fine on MySQL, so I'm not sure what's going on here in Python. Any pointers are appreciated. Thanks!</p>