Python database insert MySQL

Question

I am trying to create a table in Python with MySQL, and then insert new values into the table!

The program works with no error message, but the table stays empty all the time. Here are my python scripts:

cursor = conn.cursor()
cursor.execute('DROP TABLE twitter')
cursor.execute("CREATE TABLE twitter (id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id CHAR, state CHAR(2), city CHAR, tweet CHAR, PRIMARY KEY (id))")

### after getting some data, then insert the data into the table: 
cursor.execute('INSERT INTO twitter VALUES ({0},{1},{2},{3})'.format(tw_userid, tw_state, tw_city, tw_text))
cursor.close()
conn.commit()
conn.close()

But any time I try to select data from this table, I get an empty set.

I also tried without using format option, but still I get an empty set:

cursor.execute('INSERT INTO twitter VALUES ({0},{1},{2},{3})', (tw_userid, tw_state, tw_city, tw_text))

Any idea why the insert command doesn;t change the table at all?

Alex.Ritna · Accepted Answer · 2014-05-21 03:55:57Z

1

You need to use a comma to separate parameters in python.

cursor.execute('INSERT INTO twitter VALUES ({0},{1},{2},{3})'.format(tw_userid, tw_state, tw_city, tw_text))

Should be:

data = (tw_userid, tw_state, tw_city, tw_text)
cursor.execute('INSERT INTO twitter VALUES ({0},{1},{2},{3})', data)

You can see the documentation for the python connector on the mysql documentation page.

edited May 21, 2014 at 3:55

answered May 21, 2014 at 3:49

Alex.Ritna

1,9842 gold badges17 silver badges26 bronze badges

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

2 Comments

Vahid Mirjalili Over a year ago

I made that change, but still I get empty table!

Alex.Ritna Over a year ago

@VahidMir see my updated answer. You can read more about format() here

Collectives™ on Stack Overflow

Python database insert MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related