python connection mysql database operation

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor ()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('%s', '%s', '% d', '%c', '%d' )" %
('Mac', 'Mohan', 20, 'M', 2000)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()

# disconnect from server
db.close()

Read operation:
fetchone(): This method fetches the next row of the query result set. When the result set is an object, a cursor object will be returned for querying the table.

fetchall(): This method fetches all rows of the result set. If some rows have been fetched from the result set, the fetchAll() method retrieves the remaining rows of the result set.

rowcount: This is a read-only property that returns the number of rows affected by the execute() method.


#!/usr /bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE
              WHERE INCOME > '%d'" % (1000)  
try:
  # Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2] sex = row[3] income = row[4] # Now print fetched result
print "fname=%s,lname=%s,age=%d,sex=%s,income = % d " % n (FNAME, LNAME, Age, Sex, Income)
except:
Print" Error: Unable to Fecth Data "

# Disconnect from Server
db.close () Any database update operation means that updates can already be made to one or more records in the database. The following is the process of updating all records to "M" SEX. Here we add one year to the age of all males.

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123" ,"TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
 WHERE SEX = '%c'" % ('M')
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()

# disconnect from server
db.close()

Delete operation:

DELETE operation is required when you want to delete some records from the database. The following is a program to delete all records of employees who are over 20 years old.

Example:

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost", "testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()

# disconnect from server
db.close()

Execute transactions:

Transactions are mechanisms to ensure data consistency. Transactions should have the following four properties:

Atomicity: Either the transaction ends or nothing happens at all All.

Consistency: A transaction must start in a consistent state and leave the system in a consistent state.

Isolation: Intermediate results of the transaction are not visible outside the current transaction.

Persistence: Once a transaction commits, The effect is durable, even after a system failure.

The Python DB API 2.0 provides two methods to commit or rollback a transaction.


------------------ --------------------------------------------------

import MySQLdb
con = MySQLdb.connect(host='localhost', user='root', passwd='root', db='hr_resume_center', charset='utf8')
cursor = con.cursor()

sql = "INSERT INTO hr_resume_core (resume_id,name,mobile,email,add_time,sys_time,version) VALUES(%s,%s,%s,%s,%s,%s,%s)"

param = [
(1,'bright','13641154657','jackieming@sina.cn',1385625423,1385625423,1),
(2,'bright','13641154657','jackieming@sina.cn',1385625423, 1385625423,1),
(3,'bright','13641154657','jackieming@sina.cn',1385625423,1385625423,1),
]
cursor.execute(sql,param)
cursor.close()
con.close()