CODEWITHSUNDEEP
pythonmysqlinsert
Yohan Obadia
Yohan Obadia

Reputation: 2672

MySQL connector Python: fail to insert

I am trying to INSERT values into a MySQL table via a python script. However, I get the following error:

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 'like) VALUES ('0', '1', 'https://twitter.com/LesEchos/status/815950910832705538'' at line 1

I looked for extra/missing blank space as suggested in other answers but coult not find the error. I also inserted values in other tables using the same fashion... You will find bellow the whole code:

import mysql.connector
import datetime

cnx = mysql.connector.connect(user='root',
                              host='localhost',
                              database='realcv')

cursor = cnx.cursor()
creation_date = datetime.datetime.now()

add_content = ("INSERT INTO CONTENT_TWITTER "
               "(id_content, id_account, url, is_active, creation_date, text, retweet, like) "
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")

id_account = ['1', '2', '3', '5', '6', '6', '6']
url = ['https://twitter.com/LesEchos/status/815950910832705538',
       'https://twitter.com/franceinter/status/815950791131283457',
       'https://twitter.com/LeHuffPost/status/815949875481378816',
       'https://twitter.com/lemondefr/status/815945788979290113',
       'https://twitter.com/frenchweb/status/815944495103496193',
       'https://twitter.com/frenchweb/status/815988537426378752',
       'https://twitter.com/frenchweb/status/815980996118114111']
is_active = ['1', '1', '1', '1', '1', '1', '0']
text = ["Les grands magasins Printemps signent à leur tour un accord sur l'ouverture du dimanche http:/trib.al/zIVr5id ",
        "L'uritrottoir c'est l'urinoir en forme de jardinière qui transforme son contenu en engrais ► http:/bit.ly/urinoir-rue  cc @MoreauEmm",
        "Mort du skieur Jean Vuarnet, champion olympique qui a donné son nom à la marque de lunettes",
        "« Les trois piliers de l’islam », une leçon de lecture du Coran pour les djihadistes",
        "[Étude de Cas] Comment Auchan.fr augmente ses ventes de +2,5% en personnalisant l’expérience de ses utilisateurs http:/bit.ly/2hQDeIh ",
        "#Automobile : les 4 tendances #IoT à venir à horizon 2020 http:/bit.ly/2hBfgEs ",
        "AAAAAAAAAAAA"]
retweet = ['20', '30', '18', '45', '27', '12', '5']
like = ['10', '25', '30', '12', '17', '54', '3']
count = 0
for i in range(len(id_account)):
    data_content = (str(count), id_account[i], url[i], is_active[i], creation_date, text[i], retweet[i], like[i])
    cursor.execute(add_content, data_content)
    count += 1

# Commit changes
cnx.commit()
cnx.close()

The table I try to write to is defined as follows:

CREATE TABLE IF NOT EXISTS `realcv`.`CONTENT_TWITTER` (
  `id_content` DOUBLE NOT NULL,
  `id_account` DOUBLE NULL,
  `url` VARCHAR(100) NULL,
  `is_active` TINYINT(1) NULL,
  `creation_date` DATETIME NULL,
  `text` MEDIUMTEXT NULL,
  `retweet` INT NULL,
  `like` INT NULL,
  PRIMARY KEY (`id_content`),
  INDEX `account_content_idx` (`id_account` ASC),
  CONSTRAINT `account_content`
    FOREIGN KEY (`id_account`)
    REFERENCES `realcv`.`ACCOUNT` (`id_account`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Upvotes: 0

Views: 627

Answers (2)

Harsha Biyani
Harsha Biyani

Reputation: 7268

Column name should not be a keyword. But you can escape those reserve words as a column name.

In your program, column names:"like" and "text" are keywords. You can escape those using back tick character (`) :

While creating : 

CREATE TABLE IF NOT EXISTS CONTENT_TWITTER
  (
  id_content TEXT,
  id_account TEXT, 
  url TEXT,
  is_active TEXT,
  creation_date TEXT, 
  `text` TEXT, 
  retweet TEXT,
  `like` TEXT)

While adding/inserting : 

add_content = ("INSERT INTO CONTENT_TWITTER "
               "(id_content, id_account, url, is_active, creation_date, `text`, retweet, `like`) "
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")

Upvotes: 1

AssenKhan
AssenKhan

Reputation: 566

Like is a keyword use backticks for reserverd keyword

("INSERT INTO CONTENT_TWITTER "
               "(id_content, id_account, url, is_active, creation_date, text, retweet, `like`) "
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")

Upvotes: 1

Related Questions