0

I'm trying to insert a row into a table that I've set up in a database.

My Code:

cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station) VALUES(%s, %s, %s, %s, timestamp, comport)""",[(prefix, code_id, answer, station)])

I'm getting for an error that is saying that it doesn't like what is in "[]".

I don't completely understand how "%s" works. I've tried many examples and all have failed me.

I got this example from http://mysql-python.sourceforge.net/MySQLdb.html

Improve this question
3
  • 1
    What a pity so many answerers don't seem to have heard of SQL Injection. Always remember Little Johnny Tables, folks. Commented Jun 30, 2011 at 20:59
  • @Daniel Do you have a suggestion to avoid SQL Injection? Commented Jun 30, 2011 at 21:09
  • You have the right idea, and RoundTower should have put you on the right track. Please, please ignore the answers from Gunadine and Gabriel, as they definitely will open you up to all sorts of trouble. Commented Jun 30, 2011 at 21:11

3 Answers 3

Reset to default
2

Why do you have the parameters inside two sets of brackets? The second argument should be a sequence containing four items, to match the four %ss in your query. Instead, you have given it a list containing one item: that item is a sequence with four items in it.

Instead, try:

cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station) VALUES(%s, %s, %s, %s, timestamp, comport)""",(prefix, code_id, answer, station))
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Traceback (most recent call last): File "test.py", line 48, in <module> cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station) VALUES (%s, %s, %s, %s, timestamp, comport)""",(prefix, code_id, answer, station)) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau lterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1136, "Column count doesn't match value cou nt at row 1")
Er, because you've got four columns in the first set of brackets defining which columns to enter, and six in the second defining the actual values...
@Daniel ah I see. So if I add the other 2 columns to the first it should theoretically work right?
@daniel When I did tried adding in the other 2 to the first brackets I get Traceback (most recent call last): File "test.py", line 48, in <module> cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station,timesta mp,comport) VALUES(%s, %s, %s, %s, timestamp, comport)""",[(prefix, code_id, ans wer, station)]) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 159, in execute query = query % db.literal(args) TypeError: not enough arguments for format string
But now you've restored the unnecessary square brackets in the parameter list! Plus, you need to get values for timestamp and comport from somewhere.
|
0 
cursor.execute('INSERT INTO scan (prefix, code_id, answer, station) VALUES("%s", "%s", "%s", "%s")' %(prefix, code_id, answer, station))

You might get an error if if you miss the "" around %s ie if you use simply %s instead of this "%s" in values.

Improve this answer

6 Comments

Traceback (most recent call last): File "test.py", line 48, in <module> cursor.execute("INSERT INTO scan (prefix, code_id, answer, station) VALUES(% s, %s, %s, %s, timestamp, comport)" %(prefix, code_id, answer, station)) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau lterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1136, "Column count doesn't match value cou nt at row 1")
I don't understand why are you using timestamp, comport
@Guanidene I'm using timestamp, comport because they are fields in my table that I would like to insert.
@Guanidene I just used the edited piece you posted and I got the same here except the operational error says unknown column station in field list
@MarcBrigham let us continue this discussion in chat
|
-1

You can use Python's new 'format' string method:

cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station) VALUES({}, {}, {}, {}, timestamp, comport)""".format(prefix, code_id, answer, station))

You can also number the parameters, like "{0}", "{1}", and so on. This might be required depending on your Python version (I think 2.6 already supports parameters with no numbering).

For a reference on using this, check http://docs.python.org/library/string.html#format-examples

Improve this answer

6 Comments

This didn't work. I got a _mysql_operational error. does it matter if station is an INT data type?
@gabriel Traceback (most recent call last): File "test.py", line 48, in <module> cursor.execute("""INSERT INTO scan (prefix, code_id, answer, station) VALUES ({}, {}, {}, {}, timestamp, comport)""".format(prefix, code_id, answer, station) ) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau lterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1136, "Column count doesn't match value cou nt at row 1")
You need to give values to "timestamp" and "comport" too (not only their names). Also, you need to add "timestamp" and "comport" to the fields list. It will be something like this: INSERT INTO scan (prefix, code_id, answer, station, timestamp, comport) VALUES({}, {}, {}, {}, {}, {}) Then, in the 'format' parameters, you need to put two more values: the ones of 'timestamp' and 'comport'.
Timestamp is automatically generating a timestamp when I create the field. I'm using the datetime value for it in MySQL
@gabriel comport isn't assigned a value
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.