How to use MYSQLDB modules in Python

Python connection to MySQL's solution is Oursql, Pymysql, Myconnpy, MySQL Connector, etc., but this article to say is indeed another class library mysqldb,mysqldb is used for Python link MySQL database interface, It implements the Python database API specification V2.0, based on the MySQL C API. can be obtained and installed from: Https://pypi.python.org/pypi/MySQL-python, and many distributions of Linux sources have this module, can be directly installed through the source.

One, database connection
MYSQLDB provides a connect method for establishing a connection to the database, receiving several parameters, and returning the Connection object:

The code is as follows	Copy Code
Conn=mysqldb.connect (host= "localhost", user= "root", passwd= "361way", db= "test", charset= "UTF8")

The more commonly used parameters include:

Host: Database host name. Default is Local host
User: Database login name. default is current user
PASSWD: The Secret of database login. Null default
DB: The name of the database to use. No default value
The TCP port used by the Port:mysql service. Default is 3306
CharSet: Database encoding
More information about the parameters can be found here http://mysql-python.sourceforge.net/MySQLdb.html

Then, this connection object also provides support for transaction operations, standard methods:

Commit () Commit
Rollback () rollback
A simple query example:

The code is as follows

Copy Code

#!/usr/bin/python # Encoding:utf-8 Import MySQLdb # Open Database connection db = MySQLdb.connect ("localhost", "root", "361way", "test") # Use the cursor () method to get the action cursor cursor = Db.cursor () # Execute SQL statement using the Execute method Cursor.execute ("SELECT VERSION ()") # Use the Fetchone () method to get a database. data = Cursor.fetchone () Print "Database version:%s"% data # Close Database connection Db.close () Script execution results are as follows: Database version:5.5.40

Two, cursor method execution and return value

The code is as follows

Copy Code

The cursor method provides two types of operations: 1. Execute command, 2. Receive return value. Cursor the method used to execute the command Used to execute the stored procedure, the received parameter is the stored procedure name and the parameter list, and the return value is the number of rows affected Callproc (self, procname, args) Executes a single SQL statement, the parameters received are the SQL statement itself and the list of parameters used, and the return value is the number of rows affected Execute (Self, query, args) Executes a singled out SQL statement, but repeats the parameters in the parameter list, returning the value to the number of rows affected Executemany (self, query, args) Move to the next result set Nextset (self) Cursor the method used to receive the return value Receives all returned result rows. Fetchall (self) Receive the size bar returns the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned Fetchmany (self, size=none) Returns a result row Fetchone (self) Move the pointer to a row. If mode= ' relative ' indicates that the value bar is moved from the current row, and if mode= ' absolute ', the value bar is moved from the first row of the result set Scroll (self, value, mode= ' relative ') This is a read-only property and returns the number of rows affected after executing the Execute () method RowCount

Third, the database operation shows
1. Create DATABASE tables

If a database connection exists we can use the Execute () method to create a table for the database, as shown in the following CREATE TABLE employee:

The code is as follows

Copy Code

#!/usr/bin/python # Encoding:utf-8 Import MySQLdb # Open Database connection db = MySQLdb.connect ("localhost", "root", "361way", "test") # Use the cursor () method to get the action cursor cursor = Db.cursor () # If a datasheet already exists use the Execute () method to delete the table. Cursor.execute ("DROP TABLE IF EXISTS EMPLOYEE") # Create a datasheet SQL statement sql = "" "CREATE TABLE EMPLOYEE ( First_Name CHAR (not NULL), Last_Name CHAR (20), Age INT, SEX CHAR (1), Income FLOAT) "" " Cursor.execute (SQL) # Close Database connection Db.close ()

2. Database insert operation

code is as follows

copy code

#!/usr/bin/python # encoding:utf-8 Import mysqldb # Open database connection db = MyS Qldb.connect ("localhost", "root", "361way", "Test") # Use Cursor () method to get action cursors cursor = Db.cursor () # SQL INSERT Statements sql = "" "INSERT into EMPLOYEE (first_name,          last_name, age, SEX, Income)          VALUES (' Mac ', ' Mohan ', ', ' M ', Watts) "" Try: & nbsp;  # Execute SQL statements    cursor.execute (sql)    # Commit to Database execution    db.commit () Except:    # Rollback in case there be any error    db.rollback () # Close database connection Db.close ()

Here is an example of a single SQL execution, and Cursor.executemany's example can refer to an example of an AWS host asset management System I wrote earlier.

The above example can also be written in the form of a placeholder Fu, as follows:

The code is as follows

Copy Code

#!/usr/bin/python # encoding:utf-8 Import mysqldb # Open database connection db = MySQLdb.connect ("localhost", " TestUser "," test123 "," TestDB ") # Use Cursor () method to get action cursor cursor = db.cursor () # SQL INSERT statement SQL = INSERT INTO EMP Loyee (first_name,\        last_name, age, SEX, income) \         VALUES ('%s ', '%s ', '%d ', '%c ', '%d ') '%\        (' Mac ', ' Mohan ', 2 0, ' M ', (+) Try:    # Execute SQL statement    cursor.execute (sql)    # Commit to database execution &NB sp;  db.commit () except:    # Roll back    db.rollback () # Close database connection Db.close () The can also pass parameters in the form of a variable, as follows: .................................. user_id = "Test" Password = "Password123" Con.execute (' INSERT into Login values ('%s ', '%s ') '%\ (user_id, password)) ..................................

3. Database query operation

For example, to query for all data in the employee table where the salary (payroll) field is greater than 1000:

The code is as follows

Copy Code

#!/usr/bin/python # Encoding:utf-8 Import MySQLdb # Open Database connection db = MySQLdb.connect ("localhost", "root", "361way", "test") # Use the cursor () method to get the action cursor cursor = Db.cursor () # SQL Query Statement sql = "SELECT * FROM Employee\ WHERE income > '%d '% (1000) Try # Execute SQL statement Cursor.execute (SQL) # Get all records list Results = Cursor.fetchall () For row in results: fname = row[0] LName = row[1] Age = Row[2] Sex = row[3] Income = Row[4] # Print Results Print "fname=%s,lname=%s,age=%d,sex=%s,income=%d"%\ (FName, lname, age, sex, income) Except Print "error:unable to FECTH data" # Close Database connection Db.close () The above script execution results are as follows: Fname=mac, Lname=mohan, age=20, Sex=m, income=2000

4. Database Update operation

The update operation is used to update the data in the datasheet, and the following instance modifies the SEX field in the test table to ' M ' and the Age field increments by 1:

The code is as follows

Copy Code

# Encoding:utf-8 #!/usr/bin/python Import MySQLdb # Open Database connection db = MySQLdb.connect ("localhost", "root", "361way", "test") # Use the cursor () method to get the action cursor cursor = Db.cursor () # SQL UPDATE statement sql = "UPDATE EMPLOYEE SET age = age + 1" WHERE SEX = '%c '% (' M ') Try # Execute SQL statement Cursor.execute (SQL) # Commit to Database execution Db.commit () Except # rollback When an error occurs Db.rollback () # Close Database connection Db.close ()

5. Executive Affairs

Transaction mechanisms ensure data consistency.

Transactions should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are commonly referred to as ACID properties.

Atomic Sex (atomicity). A transaction is an indivisible unit of work, and all operations included in the transaction are either done or not done.
Consistency (consistency). The transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.
Isolation (Isolation). Execution of a transaction cannot be interfered by other transactions. That is, the operations within a transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other.
Persistence (durability). Persistence is also called permanence (permanence), meaning that once a transaction is committed, its changes to the data in the database should be permanent. The rest of the operation or failure should not have any effect on it.
The Python DB API 2.0 Transaction provides two methods for commit or rollback. Instance:

The code is as follows	Copy Code
# SQL Delete Record statement sql = "DELETE from EMPLOYEE WHERE age > '%d '"% (20) Try # Execute SQL statement Cursor.execute (SQL) # Submit to Database Db.commit () Except # rollback When an error occurs Db.rollback ()

For databases that support transactions, in Python database programming, a stealth database transaction is automatically initiated when the cursor is established. All update operations of the commit () method cursor, the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.