Python Connect 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 ', +, ' M ', 2000)
Try
# Execute The SQL command
Cursor.execute (SQL)
# Commit your changes in the database
Db.commit ()
Except
# Rollback In case there are any error
Db.rollback ()

# Disconnect from server
Db.close ()

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

Fetchall (): This method gets all the rows of the result set. If some rows have been extracted 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"% \
(FName, lname, age, sex, income)
Except
Print "error:unable to FECTH data"

# Disconnect from server
Db.close ()

Update operation:

Updating operations on any database means updating one or more records that are already available in the database. The following is the process of updating all of the records as "M" sex. Here, we will increase the age of all males by one year.

#!/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 are any error
Db.rollback ()

# Disconnect from server
Db.close ()

Delete operation:

The delete operation is required when you want to delete some records from the database. The following is a procedure for deleting all records of an employee, aged over 20 years.

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 the 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 are any error
Db.rollback ()

# Disconnect from server
Db.close ()

To perform a transaction:

Transactions are mechanisms to ensure data consistency, and transactions should have the following four properties:

Atomicity: Either the end of the transaction or nothing happens at all.

Consistency: You must start a transactionally consistent state and leave the system in a consistent state.

Isolation: The intermediate result of a transaction is not visible outside of the current transaction.

Persistence: Once a transaction commits, the effect is persistent, even after a system failure.

There are two methods for Python DB API 2.0 to commit or roll back 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 ()

