Python MySQLdb insert issue

Question

there I am writting a simple app in Python that will monitor the usage of my cpu and ram and put it into a MySQL database for future processing here is my code :

import MySQLdb
import psutil


connection = MySQLdb.connect(host="localhost", port=8888, user="root", passwd="root", db="monitoring", unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock")
c = connection.cursor()

while True:

    usage = psutil.cpu_percent(interval=1)

    c.execute("INSERT INTO cpu (usage) VALUES (%s)", (usage))

    c.execute("SELECT * FROM cpu")
    print c.fetchall()

Here is the libary that I am using for for monitoring

Here is a dump for MySQL database :

    --
-- Table structure for table `cpu`
--

    CREATE TABLE `cpu` (
      `id` int(12) NOT NULL AUTO_INCREMENT,
      `usage` float NOT NULL,
      `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;

However I am unable to fix this error when making an INSERT :

_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 'usage) VALUES (12.9)' at line 1")

Any tips to fix this ? I am sorry, but I am new to Python :)

Thanks in advance.

Ike Walker · Accepted Answer · 2012-04-24 22:59:40Z

1

This is not a Python issue. The problem is that usage is a reserved word in MySQL. You should either change the name of your column to something else, or use backticks to quote it in your code:

c.execute("INSERT INTO cpu (`usage`) VALUES (%s)", (usage))

answered Apr 24, 2012 at 22:59

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

1 Comment

Michał Lach Over a year ago

holy mother of python, I didnt know about that. After your fixed it worked like a charm. Thank you very much.

Collectives™ on Stack Overflow

Python MySQLdb insert issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related