Connect python to the mysql database

ImportMySQLdb # OpendatabaseconnectiondbMySQLdb. connect (& amp; quot; localhost & amp; quot;, & amp; quot; testuser & amp; quot;, & amp; quot; test123 & amp; quot ;, & amp; quot; TESTDB & amp; quot;) # prepareacursorobjectusingcursor () methodcursor... 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.exe cute (SQL)
# Commit your changes in the database
Db. commit ()
Except t:
# Rollback in case there is any error
Db. rollback ()

# Disconnect from server
Db. close ()

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

Fetchall (): This method gets all rows in the result set. If some rows have been extracted from the result set, the fetchAll () method retrieves the remaining rows in the result set.

Rowcount: this is a read-only attribute and returns the execute () method of the affected rows.


#! /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.exe cute (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 t:
Print "Error: unable to fecth data"

# Disconnect from server
Db. close ()

UPDATE operation:

Any database update operation means that one or more records can be updated in the database. The following is the process of updating all records as "M" SEX. Here we add all male ages to 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.exe cute (SQL)
# Commit your changes in the database
Db. commit ()
Except t:
# Rollback in case there is 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. Below are all records of employees deleted by the program, of which the age is 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.exe cute (SQL)
# Commit your changes in the database
Db. commit ()
Except t:
# Rollback in case there is any error
Db. rollback ()

# Disconnect from server
Db. close ()

Execute the transaction:

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

Atomicity: whether the transaction ends or nothing happens at all.

Consistency: you must start a transaction in the consistent state and exit the system.

Isolation: beyond the current transaction, the intermediate results of the transaction are invisible.

Durability: Once a transaction is committed, the effect is persistent, even if the system fails.

Python db api 2.0 provides two methods to commit or roll back transactions.


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

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 )"

Param = [
(1, 'Bright ', '20170101', 'jackieming \ @ sina.cn', 1385625423,1385625423, 1 ),
(2, 'Bright ', '20170101', 'jackieming \ @ sina.cn', 1385625423,1385625423, 1 ),
(3, 'Bright ', '20170101', 'jackieming \ @ sina.cn', 1385625423,1385625423, 1 ),
]
Cursor.exe cute (SQL, param)
Cursor. close ()
Con. close ()