MySQLdb/Python INSERT query not inserting data into table

Question

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.

Anil Chahal · Accepted Answer · 2015-06-03 10:42:42Z

7

If your query is correct (check with Print statement) and database connection is also alright then please execute this command

db.commit()

right after cur.execute(query) . Alternatively you can also set

db.autocommit(True)

At the beginning of your python script after making the database connection.

Once all the job is done use command

db.close()

for terminating database connection. I hope this will work for you.

answered Jun 3, 2015 at 10:42

Anil Chahal

2,6212 gold badges24 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GCooper · Accepted Answer · 2013-11-09 17:46:54Z

6

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)

answered Nov 9, 2013 at 17:46

GCooper

1406 bronze badges

3 Comments

tandy Over a year ago

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)

GCooper Over a year ago

do you close the cursor?

tandy Over a year ago

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!

Collectives™ on Stack Overflow

MySQLdb/Python INSERT query not inserting data into table

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related