Can't insert single column value in python using MySQL

Question

I have a single column table. I need to insert values in this column. The program runs correctly without errors. But when I check the database, nothing gets inserted. When I added another column to the code and table, the program inserts data correctly. Can you tell me how to insert data for a single column table? This is the single column code that does not insert anything to the table.

import MySQLdb
conn = MySQLdb.connect(host= "localhost",
                  user="root",
                  passwd="123",
                  db="dbname")
cursor = conn.cursor()
x=100
try:
    sql="""INSERT INTO table (col1) VALUES ('%s')"""
    cursor.execute(sql, (x))
    conn.commit()
except:
    conn.rollback()

conn.close()

This is the two columns code.

import MySQLdb
conn = MySQLdb.connect(host= "localhost",
                  user="root",
                  passwd="123",
                  db="dbname")
cursor = conn.cursor()
x=100
y=2
try:
    sql="""INSERT INTO table (col1,col2) VALUES ('%s','%s')"""
    cursor.execute(sql, (x,y))
    conn.commit()
except:
    conn.rollback()

conn.close()

thebjorn · Accepted Answer · 2016-12-24 00:10:50Z

12

You need to lose the quotes around %s, after that you need to know that the second argument to cursor.execute() is a tuple, and that a one-tuple is written:

(item,)

note the comma. The solution is then:

sql="""INSERT INTO table (col1) VALUES (%s)"""
cursor.execute(sql, (x,))

answered Dec 24, 2016 at 0:10

thebjorn

27.6k12 gold badges107 silver badges152 bronze badges

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

2 Comments

user6875880 Over a year ago

I tried this sql="""INSERT INTO table (col1,col2) VALUES (%s)""" cursor.execute(sql, (x,)). It runs but it does not insert the data to DB.

thebjorn Over a year ago

Your two column version is correct if you remove the quotes around the place holders.

Carles Mitjans · Accepted Answer · 2016-12-24 00:06:14Z

0

You can try either of these:

Don't use '%s', you can use ? instead
Instead of '%s', just use %s without quotes

answered Dec 24, 2016 at 0:06

Carles Mitjans

4,8763 gold badges22 silver badges39 bronze badges

Comments

liferacer · Accepted Answer · 2016-12-24 00:04:08Z

-3

try this:

sql="""INSERT INTO table (col1) VALUES ({});""".format(x)
cursor.execute(sql)

answered Dec 24, 2016 at 0:04

liferacer

2,5332 gold badges19 silver badges16 bronze badges

3 Comments

Blag Over a year ago

Please don't forget to explain / comment your code, and provide relevant documentation [from review]

ad absurdum Over a year ago

Please avoid code-only answers. Try to explain how your code solves OP's problem. Others that find this question may not be as experienced as you; help them to understand your solution.

thebjorn Over a year ago

This strategy is also a sql-injection waiting to happen (although in this particular case you'll probably get a sql syntax-error first).

Collectives™ on Stack Overflow

Can't insert single column value in python using MySQL

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related