Python MySql Insert not working
PythonMysqlPython Problem Overview
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?
Python Solutions
Solution 1 - Python
Before closing the connection, you should add db.commit()
.
Solution 2 - Python
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.
Solution 3 - Python
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()
Solution 4 - Python
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.
Solution 5 - Python
You may set python to autocommit changes in your database like this:
db = MySQLdb.connect("localhost","root","padmaramulu","pdfsearch, autocommit=True")```