MySQL INSERT INTO using Jupyter Notebook ERROR: Invalid syntax

Question

The following code was run in the Jupyter Notebook.

import pandas
import pymysql

connection = pymysql.connect(host='localhost', user='root', password='123456', database='sakila')

cursor=connection.cursor()

insert_query= INSERT INTO film_text (film_id, title, description) VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin');

cursor.execute(insert_query)

connection.commit()

ERROR: File "", line 7 insert_query=INSERT INTO film_text (film_id,title,description) VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin'); ^ SyntaxError: invalid syntax

Answer 1

If don't plan to do highly advanced stuff with the data from python, maybe it's better to use a jupyter kernel to play with the database in plain SQL.

Answer 2

The type of insert_query will be a string.

insert_query = "INSERT INTO film_text (film_id, title, description) VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin')"

Answer 3

You can also directly write your code like this

cursor.execute("""INSERT INTO film_text (film_id, title, description) VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin'""");

Answer 4

You forgot to wrap the query in quotes. The following code should work perfectly:

import pandas
import pymysql

connection = pymysql.connect(host='localhost', user='root', password='123456', database='sakila')

cursor=connection.cursor()

insert_query= "INSERT INTO film_text (film_id, title, description) VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin');"

cursor.execute(insert_query)

Answer 5

you should warp the query string in quote

insert_query= "INSERT INTO film_text (film_id, title, description) 
      VALUES ('1001','ZZZ ZORRO','Zorro must Fight a Womanizer in Berlin')";