MySQL Database insert Syntax via Python

Question

I am trying to insert a row into my table called users via mySQL. The following code outputs an error saying pymysql.err.InternalError: (1054, "Unknown column 'Mike' in 'field list'"). Or if I use the %s, %s, %s, then: NameError: name 'Mike' is not defined.

All I want to do is add a row with a user, what seems to be the problem?

import pymysql

# connect and login to db

conn = pymysql.connect(host = 'hosting',
                   user= 'user',
                   password= 'databasepassword',
                   db = 'databasename')

# create cursor object

a = conn.cursor()


# sql command


sql = 'INSERT INTO users(id, user, pwd) VALUES (2, Mike, password2)'

# execute sql commend

a.execute(sql)

Enclose the field values into quotes: `INSERT INTO users(id, user, pwd) VALUES (2, "Mike", "password2")`. — alecxe, Mar 23 '18 at 20:47
Have you tried encapsulating each of your values in single quotes? — Matt_G, Mar 23 '18 at 20:47

score 0 · Answer 1 · answered Mar 23 '18 at 20:51

0

It thinks Mike is a column name because you are missing the quotes. Try:

import pymysql

# connect and login to db

conn = pymysql.connect(host = 'hosting',
                   user= 'user',
                   password= 'databasepassword',
                   db = 'databasename')

# create cursor object

a = conn.cursor()


# sql command


sql = "INSERT INTO users(id, user, pwd) VALUES (2, 'Mike', 'password2')"

# execute sql commend

a.execute(sql)

answered Mar 23 '18 at 20:51

kjmerf

4,275
3
21
29

better see at this https://stackoverflow.com/questions/775296/python-mysql-parameterized-queries – bato3 Mar 23 '18 at 20:52
Thank you. Had to commit the data to the db as well! – Mitchell Lawrence Mar 23 '18 at 22:36

MySQL Database insert Syntax via Python

1 Answers1