Unable to Insert into MySQL via Python and pymysql

Question

I've got a table with 9 columns, the first three are all text and the last 6 are setup as INT's.

When I insert into the text fields everything works as expected

conn = pymysql.connect(host='', port=, user='', passwd='', db='')
cur = conn.cursor()
cur.execute("INSERT INTO table(Name) VALUES ('Sample Name')")

That code runs fine, and allows me to insert the name into my database. However, when running an insert statement for my integer stuff I am in all kinds of trouble.

I've tried all of the following:

cur.execute("INSERT INTO table(Left Avg) VALUES (5)")
cur.execute("INSERT INTO table(Left Avg) VALUES (%s)", (5))
cur.execute("INSERT INTO table(Left Avg) VALUES (%s)" % (5))
cur.execute("""INSERT INTO mlb_data(Left Avg) VALUES (%s)""" % (5))

Essentially no matter what I try when inserting my integer into the table I get the following error.

Traceback (most recent call last):
File "testing.py", line 12, in <module>
cur.execute("""INSERT INTO mlb_data(Left Avg) VALUES (%s)""" % (5))
File "build/bdist.macosx-10.7-intel/egg/pymysql/cursors.py", line 117, in execute
File "build/bdist.macosx-10.7-intel/egg/pymysql/connections.py", line 189, in     defaulterrorhandler
pymysql.err.ProgrammingError: (1064, u"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 'Left   Avg) VALUES (5)' at line 1")

I have tried to change the MySQl tablet to be a VarChar rather than an INT, but regardless anytime I try to insert a number I get an error.

What am I missing here?

EDIT: I don't know if I didn't try this before or if my formatting was incorrect but I figured it out.

num = 5.23947824987
cur.execute("INSERT INTO mlb_data(LeftAvg) VALUES (%s)", (num,))

Works without a hitch. Is this correct, or did I just get very lucky?

Answer 1

how bout

cur.execute("INSERT INTO table(`Left Avg`) VALUES (?)", (5,))

assuming 'Left Avg' is the column name , you should probably try to avoid spaces in column names as a general rule

Answer 2

in answer to your final question: you just got lucky, and/or you figured out or remembered the Pythonic way of making SQL queries. you can see similar examples in site-packages/pymysql/tests/test_basic.py, such as:

            v = (True, -3, 123456789012, 5.7, "hello'\" world", u"Espa\xc3\xb1ol
", "binary\x00data".encode(conn.charset), datetime.date(1988,2,2), datetime.date
time(2014, 5, 15, 7, 45, 57), datetime.timedelta(5,6), datetime.time(16,32), tim
e.localtime())
            c.execute("insert into test_datatypes (b,i,l,f,s,u,bb,d,dt,td,t,st) 
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", v)

Answer 3

The easy way to INSERT in Python is using a concatenate string. Something like this:

id_user = 1
username = "Tudor"
address = "Cluj-Napoca"
sql = "INSERT INTO user(id_user,username,address) VALUES (" + id_user + ",'" + username + "','" + address + "');"
cursor.execute(sql)

But much attention to the " and ' symbols. I strongly recommend a print before executing query.