How-to use Mysql-python in Python

For database operations, and TCP/IP three handshake similar to the wonderful, establish a connection, perform operations, disconnect. This, of course, requires the creation of a connected tool.

Python connects MySQL with Oursql, Pymysql, Myconnpy, MySQL Connector, but this is really another class library mysqldb,mysqldb is the interface for the Python link MySQL database, It implements the Python database API specification V2.0, built on the MySQL C API.

ReferencePackage Mysql-python mysqldb User ' s guide

Installation

Yum Install Mysql-python-ypip install Mysql-python Source extract go to main directory execute Python setup.py install

Use

1. Connection to the database

MYSQLDB provides the Connect method to establish a connection to the database, receive several parameters, and return the connection object:
Conn=mysqldb.connect (host= "hostname", user= "username", passwd= "password", db= "dbname", charset= "Utf8″")

The more commonly used parameters include:
Host: the database hostname. By default, the local host
User: Database login name. The default is the current user
PASSWD: The Secret of database landing. Default is Empty
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
The connection object is then provided with support for transactional operations, in the standard way:
Commit () Commit
Rollback () rollback

#!/usr/bin/env python# -*- coding=utf-8 -*-import mysqldb try:     conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ', port=3306)          #  use the cursor () method to get the operation Cursor     cur=conn.cursor ()     #   Select Database     conn.select_db (' Test ')     #  Execute SQL statement using the Execute method     cur.execute ("Select version ()")     #  using  fetchone ()   Method gets a database.     data = cur.fetchone ()     print  "database  version : %s  " % data        #  Close connection     conn.commit ()     cur.close ()     conn.close ()  except MySQLdb.Error,e:     print  "mysql error %d: %s"  %  (E.ARGS[0], E.ARGS[1]) 

Execution results

Database version:5.1.73

2. Cursor method execution and return value

The cursor method provides two types of operations:
1. Execution of commands
2. Receive return value
The cursor method used to execute the command

#用来执行存储过程, 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)
#执行单条sql语句, the parameters that are received are the SQL statement itself and the parameter list used, and the return value is the number of rows affected
Execute (Self, query, args)
#执行单挑sql语句, but the parameters in the parameter list are repeated, and the return value is the number of rows affected
Executemany (self, query, args)
#移动到下一个结果集
Nextset (self)
The cursor is used to receive the return value of the method
#接收全部的返回结果行.
Fetchall (self)
#接收size条返回结果行. 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)
#返回一条结果行
Fetchone (self)
#移动指针到某一行. If mode= ' relative ', 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 ')
#这是一个只读属性 and returns the number of rows affected after executing the Execute () method
RowCount

3. Database operations

A. Creating a table

#!/usr/bin/env python# -*- coding=utf-8 -*-import mysqldb try:     conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ', port=3306)          #  use the cursor () method to get the operation Cursor     cur=conn.cursor ()     #   Select Database     conn.select_db (' test ')     #  if the data table already exists using   Execute ()   method to delete the table.     cur.execute ("Drop table if exists stu_info")      #  Create data Table SQL statement     sql =  "" create table stu_info  (               ' id '  int (TEN)  not null  AUTO_INCREMENT PRIMARY KEY,               ' name '  char  NOT NULL,               ' age '  INT,              ' Sex '  char (6)) "" "    cur.execute (SQL)     conn.commit ()      cur.close ()     conn.close ()  except MySQLdb.Error,e:      print  "mysql error %d: %s"  %  (E.args[0], e.args[1])

B. Inserting data

Add a single line record

#!/usr/bin/env python#-*-coding=utf-8-*-import mysqldb # Create connection conn=mysqldb.connect (host= ' localhost ', user= ' root ', Passwd= ", port=3306) # Get the operation cursor using the cursor () method Cur=conn.cursor () # Select Database conn.select_db (' Test ') # Insert a record sql =" INSERT INTO Stu_ info (name,age,sex) VALUES (%s,%s,%s) "Cur.execute (SQL, (' Lisa ',", ' female ')) Conn.commit () Cur.close () Conn.close ()

Add multiple rows of records

#!/usr/bin/env python#-*-coding=utf-8-*-import mysqldb# Create Connection conn = MySQLdb.connect (host= ' localhost ', user= ' root ', Passwd= ", port=3306) # Get an action cursor using the cursor () method cur = conn.cursor () # Select Database conn.select_db (' Test ') # Insert a record sql =" INSERT INTO Stu_ Info (name, age, Sex) values (%s,%s,%s) "Cur.executemany (SQL, [(' Jack ', ' d ', ' Male '), (' Danny ', ' + ', ' female '),]) c Onn.commit () Cur.close () Conn.close ()

The Executemany () method can insert multiple values at a time, execute a single-heads SQL statement, but repeatedly execute parameters in the parameter list, with the returned value being the number of rows affected.

C. Querying data

#!/usr/bin/env python#-*-coding=utf-8-*-import mysqldb# Create Connection conn = MySQLdb.connect (host= ' localhost ', user= ' root ', Passwd= ", port=3306) # Gets the operation cursor using the cursor () method cur = conn.cursor () # Select Database conn.select_db (' Test ') # get the number of record bars Rec_count = Cur.execute ("SELECT * from Stu_info") print "There has%s records"% rec_count# print data in table #rows = Cur.fetchmany (rec_count) row s = Cur.fetchall () for row in Rows:print Rowconn.commit () cur.close () Conn.close ()

Execution results

There has 3 records (1L, ' Lisa ', 18L, ' female ') (2L, ' Jack ', 20L, ' Male ') (3L, ' Danny ', 19L, ' female ')

The above code is used to get all the results out, but when printing is a meta-ancestor print per line, now we use the method to take out the individual data:

Import MYSQLDB # Create connection conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ', port=3306) # get an operation cursor using the cursor () method cur =conn.cursor () # Select Database conn.select_db (' Test ') # execute that query, here is the SELECT statement Cur.execute ("SELECT * from Stu_info") #  Use Cur.rowcount to get the number of bars of the result set numrows = Int (cur.rowcount) for I in Range (numrows): row = Cur.fetchone () print str (row[0]) + "," + ROW[1] + "," + str (row[2]) + "," + Row[3]conn.commit () cur.close () Conn.close ()

Execution results

1,lisa,18,female2,jack,20,male3,danny,19,female

Use the dictionary cursor to get the result set (you can use the table field name to access the value)

Import MYSQLDB # Create connection conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ', port=3306) # get an operation cursor using the cursor () method cur =conn.cursor () # Select Database conn.select_db (' Test ') # Gets the dictionary cursor on the connection, note the method that gets, # each cursor is actually a subclass of the cursor cur = conn.cursor ( MySQLdb.cursors.DictCursor) # EXECUTE statement invariant cur.execute ("SELECT * from Stu_info") # Get Data method Unchanged rows = Cur.fetchall () # The Traverse data also does not change (more directly than the previous) for row in rows: # Here, you can use the key-value pair method, the key name to get the data print '%s%s%s '% (str (row["id"), row["name"], str (RO W["Age"]) Conn.commit () Cur.close () Conn.close ()

Execution Result:

1 Lisa 182 Jack 203 Danny 19

Executing a query using prepared statements

Import MYSQLDB # Create connection conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ', port=3306) # get an operation cursor using the cursor () method cur =conn.cursor () # Select Database conn.select_db (' Test ') # We see that this can be done by writing an SQL statement that can be assembled Cur.execute ("UPDATE stu_info SET Name =%s WHERE Id =%s ", (" Cherry "," 3 ") # Use Cur.rowcount to get how many lines are affected by the print" Number of rows updated:%d "% cur.rowcountconn.commit () c Ur.close () Conn.close ()

Execution results

: <eof>number of rows updated:1in [16]:

transaction– transactions

Transaction mechanisms ensure data consistency.
A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.
① atomicity (atomicity). A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.
② 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 one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.
④ persistence (durability). Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

The transaction for Python DB API 2.0 provides two methods of commit or rollback.
For a database that supports transactions, in Python database programming, when the cursor is established, an invisible database transaction is automatically started. Commit () method all the update operations of the cursor, the rollback () method rolls back all operations of the current cursor. Each method starts a new transaction.

import mysqldbtry:    #  Creating a connection     conn =  MySQLdb.connect (        host= ' localhost ',  user= ' root ',  Passwd= ",  port=3306)     #  use the cursor () method to get an operation cursor     cur =  conn.cursor ()     #  Select Database     conn.select_db (' test ')      #  If a database supports transactions, it will automatically open     #  MySQL is used here, so the transaction will be turned on automatically (if the Myism engine is not)     cur.execute ("Update stu_info set name = %s where id  = %s ",                     ("Tonny",  "1")     cur.execute ("update stu_infos  set name = %s where id = %s ",                     ("Jim",  "2")     #  transaction characteristics 1, Atomic manual commit      conn.commit ()     cur.close ()     conn.close () except  mysqldb.error, e:    #  If an error occurs, it can be rolled back, that is, the above three statements are either executed or not executed  [  storage engine support Things  ]    #  If the storage engine is not just a transaction [myism],  only the successful results     conn.rollback ()     print  "error %d: %s"  %  (E.args[0], e.args[1])

Execution results

Error 1146:table ' Test.stu_infos ' doesn ' t exist

Http://www.suzf.net/thread-1104-435.html to be continued ....

This article is from the "Jeffrey blog" blog, please be sure to keep this source http://oceanszf.blog.51cto.com/6268931/1710322

How-to use Mysql-python in Python