Python mysql Connector Error when tying to write to database only when not In Table

Question

I am trying to write some data to a database but only when its not already in the table. Im using the Variables data , data1 ,timestamp, and name.My Python MySql Connector looks like this

mycursor.execute("""INSERT INTO Messung (Date,capacity,Cpu,HostID) VALUES (%s, %s, %s, %s)WHERE NOT EXISTS (SELECT * FROM Messung WHERE Date = 'timestamp' AND HostID = 'name')""", (timestamp ,data ,data1 ,name))

I get the Following Error

       mycursor.execute("""INSERT INTO Messung (Date,capacity,Cpu,HostID) VALUES (%s, %s, %s, %s)WHERE NOT EXISTS (SELECT * FROM Messung WHERE Date = 'timestamp' AND HostID = 'name')""", (timestamp ,data ,data1 ,name))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'WHERE NOT EXISTS (SELECT * FROM Messung WHERE Date = 'timestamp' AND HostID = 'n' at line 1

I dont get whats wrong with the Syntax I also dont want to update an Existing entry just to skip them SAMPLE DATA:

timestamp=2019-11-20 00:00:00

name =testpc

data= 18.14

data1= 58.53

I am Splitting strings like this

2019-11-18 00:00:00 testpc data 11.58 data1 29.20

This looks like it's more of a SQL question. Can you share some information about the data set you are working with, including sample data? — artemis
– artemis, Commented Feb 11, 2020 at 13:29

Pankaj_Dwivedi · Accepted Answer · 2020-02-11 14:29:20Z

1

MySQL Insert Syntax doesn't support where clause in it. May be you can try

INSERT ON DUPLICATE KEY UPDATE.



mycursor.execute("""INSERT INTO Messung (Date,capacity,Cpu,HostID) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE Date = 'timestamp' """, (timestamp ,data ,data1 ,name))

https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/

answered Feb 11, 2020 at 14:29

Pankaj_Dwivedi

5804 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

leo Stahl Over a year ago

I dont want to update the result . I just want it to be ignored

Pankaj_Dwivedi Over a year ago

Then declare your ignored columns to be unique and use "INSERT IGNORE INTO table" in your query.

Collectives™ on Stack Overflow

Python mysql Connector Error when tying to write to database only when not In Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related