current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am attempting to insert values into a table via Python using the following code:

db = MySQLdb.connect(host="localhost",user="root",passwd="", db="x")
db.autocommit(True)
cur = db.cursor()
query = """INSERT INTO b (source_id,text,author,score,type,location) VALUES (%s,%s,%s,%s,%s,%s)""" % (1,Tweet.text,User.screen_name,score,search_type,User.location)
print query
cur.execute(query)

I see the query string being populated properly with all variable values in the print output (I'm not doing anything with special characters). But the values simply aren't being inserted into the database. My table looks like this: 

| freebee | CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source_id` int(5) NOT NULL,
`text` varchar(255) NOT NULL,
`author` varchar(120) DEFAULT NULL,
`score` tinyint(3) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`start_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`type` enum('food','event','stuff') NOT NULL,
`location` varchar(120) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=latin1 |

Notably, AUTO_INCREMENT is set to 132. I manually tested an insert query a few minutes ago and it was entered as id #132. I guess that means I've tried to insert via my python script 131 times and failed, yet the id key keeps incrementing. Thank you.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

it seems like your code is incorrect, it should be

query = """INSERT INTO b (source_id,text,author,score,type,location) VALUES (%s,%s,%s,%s,%s,%s)"""
print query
cur.execute(query, (1,Tweet.text,User.screen_name,score,search_type,User.location))

or, the text in VALUES(%s) should be sround with ', like ** VALUES('%s', '%s') % (1, 2)

query = """INSERT INTO b (source_id,text,author,score,type,location) VALUES ('%s','%s','%s','%s','%s','%s')""" % (1,Tweet.text,User.screen_name,score,search_type,User.location)
print query
cur.execute(query)
share|improve this answer
    
I've updated to make the change to include '' around %s and still nothing on the insert. query = """INSERT INTO b (source_id,text,author,score,type,location) VALUES ('%s','%s','%s','%s,'%s','%s')""" % (1,Tweet.text,User.screen_name,score,search_type,User.location) –  tandy Nov 9 '13 at 17:54
    
do you close the cursor? –  GCooper Nov 9 '13 at 17:59
    
I got it. In my last comment to you, you can see that I missed a ' on the 4th placeholder. (Fail.) It works now, with or without closing the cursor. This problem was also combined, I think, with the fact that many of my earlier attempts, I was not auto-committing (that was added before I sent the question in). Thank you! –  tandy Nov 9 '13 at 18:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.