CODEWITHSUNDEEP
pythonmysqlinnodbmysql-python
tandy
tandy

Reputation: 1948

MySQLdb/Python INSERT query not inserting data into table

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.

Upvotes: 3

Views: 14097

Answers (2)

Anil Chahal
Anil Chahal

Reputation: 2613

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.

Upvotes: 7

GCooper
GCooper

Reputation: 140

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)

Upvotes: 6

Related Questions