Python MySql Insert not working

Question

I am using python MySQL API to connect to Mysql database from python program. I am facing a problem from few days. I am unable to insert records into the database and dont know whats the reason. Here is the way i connect and insert records into the database.

db = MySQLdb.connect("localhost","root","padmaramulu","pdfsearch" )
cursor = db.cursor()
#cursor.execute("""CREATE TABLE IF NOT EXISTS documents (docid INT NOT NULL ,PRIMARY KEY(docid),docname CHAR(30)) engine=innodb""")
temp = "hello";number = 2;
cursor.execute( 'insert into documents(docid,docname) values("%d","%s")' % (number,temp) )
db.close()

Why is it so?

You should mark the answer that solved your problem. The person who wrote that answer will get well-deserved bonus points, and readers will instantly now that the solution has been applied and works. — CamilB
– CamilB, Commented May 14, 2013 at 6:17

Remi Guan · Accepted Answer · 2015-11-10 03:00:10Z

165

Before closing the connection, you should add db.commit().

edited Nov 10, 2015 at 3:00

Remi Guan

22.5k17 gold badges68 silver badges90 bronze badges

answered May 17, 2011 at 7:00

miette

1,9611 gold badge11 silver badges12 bronze badges

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

5 Comments

SRC Over a year ago

alternatively you can use db.autocommit(True) at the begning of your script. since auto commit is disabled for MySQLdb starting with 1.2.0

hd1 Over a year ago

@SRC whilst your solution does work, it is only used when developing. In production, you should always manage your own transactions

Daniel Sikes Over a year ago

Thank you sir! I was scratching my head until I read this. Much appreciated!

joe_evans Over a year ago

db.autocommit(True) worked for me but db.commit did not... until... i realised that I had typed db.commit and not db.commit() I FORGOT THE BRACKETS AT THE END - such a fool I am

Username Over a year ago

note: add .commit() to the original db connection not the cursor

Muthamizhchelvan. V · Accepted Answer · 2018-12-12 09:29:51Z

6

if you are using InnoDB engine in MySQL you should add

db.commit()

else change your MySQL engine into MyISAM no need to change anything in code.

answered Dec 12, 2018 at 9:29

Muthamizhchelvan. V

1,2611 gold badge17 silver badges31 bronze badges

Comments

Budi Mulyo · Accepted Answer · 2020-12-21 04:25:57Z

Maybe someone got same problem, and maybe this helpful,,

Any 2 option :

a. for who using MySqldb :

db.commit()

b. for who using Mysql Connector :

cnx.commit()

Add this code before close the db or Mysql Connector :

a. sample : (Azure)

import mysql.connector
from mysql.connector import errorcode

# Obtain connection string information from the portal
config = {
  'host':'<mydemoserver>.mysql.database.azure.com',
  'user':'<myadmin>@<mydemoserver>',
  'password':'<mypassword>',
  'database':'<mydatabase>',
  'client_flags': [ClientFlag.SSL],
  'ssl_cert': '/var/wwww/html/DigiCertGlobalRootG2.crt.pem'
}

# Construct connection string
try:
   conn = mysql.connector.connect(**config)
   print("Connection established")
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with the user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  cursor = conn.cursor()

  # Drop previous table of same name if one exists
  cursor.execute("DROP TABLE IF EXISTS inventory;")
  print("Finished dropping table (if existed).")

  # Create table
  cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
  print("Finished creating table.")

  # Insert some data into table
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("banana", 150))
  print("Inserted",cursor.rowcount,"row(s) of data.")
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("orange", 154))
  print("Inserted",cursor.rowcount,"row(s) of data.")
  cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);", ("apple", 100))
  print("Inserted",cursor.rowcount,"row(s) of data.")

  # Cleanup
  conn.commit()
  cursor.close()
  conn.close()
  print("Done.")

b. sample : (Dev Mysql)

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

# Insert salary information
data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database
cnx.commit()

cursor.close()
cnx.close()

Leon · Accepted Answer · 2019-11-07 02:17:10Z

0

I think your code should be fixed

cursor.execute( 'insert into documents(docid,docname) values(%d, %s)', (number,temp) )

and then You should add db.commit() before you close database connection.

answered Nov 7, 2019 at 2:17

Leon

1492 silver badges9 bronze badges

Comments

Hoppo · Accepted Answer · 2020-07-12 00:12:27Z

0

You may set python to autocommit changes in your database like this:

db = MySQLdb.connect("localhost","root","padmaramulu","pdfsearch, autocommit=True")```

edited Jul 12, 2020 at 0:12

Hoppo

1,1701 gold badge14 silver badges34 bronze badges

answered Jul 11, 2020 at 15:24

Gabriel Nunes

413 bronze badges

Collectives™ on Stack Overflow

Python MySql Insert not working

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related