Python MySQL Module

Use Python to manipulate MySQL database several times, first share with you about how to use Python to manipulate MySQL database. MySQL is not a module that comes with Python, so you need to download the installation. (The use process is described under the Windows platform)

1. Download/install Python-mysql

: https://pypi.python.org/pypi/MySQL-python/1.2.5

Double-click on the downloaded file, always select next to install it (provided that you have already installed Python), note the version of Python-mysql and Python, otherwise there will be unexpected errors during use.

2. Check whether the installation is successful

Open the Python interface, enter import MYSQLDB, no error indicates a successful installation.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/4B/E7/wKiom1Q1BwmhuQFQAAD8KhjDYt0669.jpg "title=" First.png "alt=" Wkiom1q1bwmhuqfqaad8khjdyt0669.jpg "/>

3. How to use

The test database is:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4B/F2/wKioL1Q1_PySvM4aAABndSJAeiA507.jpg "title=" User.png "alt=" Wkiol1q1_pysvm4aaabndsjaeia507.jpg "/>

3.1 Establishing a connection to the database

# using the MySQLdb.connect () Method connection = MySQLdb.connect (host= "127.0.0.1", port=3306, user= "root", passwd= "root", db= "test ") # Host: hostname (IP) # Port: port number, mysql default is 3306# User: Username # passwd: password # DB: Database (default connected database)" Optional "# CharSet: Encoding" optional "# if DB genus not specified Use connection.select_db ("database name") to select the specified database

3.2 Getting Cursor objects

# The specific SQL execution is done through the cursor object; Gets the cursor object # Initial state cursor execution first element by the cursor method of a Connection object cursor = Connection.cursor ()

    3.3 Execute SQL statement

#  is divided into a single SQL execution and bulk SQL execution, and whether parameterized (which can prevent SQL injection) # query: sql string # args : If the SQL string is% s placeholder then args is a tuple or list, if the SQL string placeholder is% (key) s form ## , then the dictionary type. Otherwise none (default) #  syntax 1:cursor.execute (query, args) #  syntax 2:cursor.executemany (query, args)  #   Example 1: Querying data Using Syntax 1 import mysqldbif __name__ ==  "__main__":    #  Create mysql connection    connection = mysqldb.connect (host= " 127.0.0.1 ",  port=3306, user=" root ",  passwd=" root ",  db=" test ")     #  get cursor     cursor = connection.cursor ()      #  returns the number of execution results     # nums = cursor.execute ("Select * from  user where id=%s ",  [1])  #  using the%s placeholder     nums =  Cursor.execute ("select * from user where id = % (ID) S",  {"id" &nbSP;: 1})  #  use the% (key) s placeholder     print (nums)     print ( Cursor.fetchone ())     #  Example 2: Use Syntax 2 to query a single piece of data import mysqldbif __name__ ==   "__main__":    # create mysql connection     Connection = mysqldb.connect (host= "127.0.0.1",  port=3306, user= "root",  passwd= "root",  db= "Test")     # get cursor     cursor =  connection.cursor ()     #  Returns the number of execution results;    nums =  Cursor.executemany ("select * from user where id=%s",  [[1], [2])       print (nums)     print (Cursor.fetchone ())      Print (Cursor.fetchmany ())     print (Cursor.fetchall ()) #  result is:nums = 2,  But the result of the query is the result of id=2, because nums indicates how many execute methods were executed, #&nbsp, while executing the query result overwrites the previous result, so when you use Syntax 2 query, execution returns the result of the last condition 

For the above two syntaxes, here are some elaboration:

1. Execute: Executes a single SQL statement and returns the number of results executed by the SQL statement

2, Executemany: Executes multiple SQL statements, the internal is actually multiple calls to execute, but more than the display of such a call more efficient, return to execute the number of successful executions (the actual SQL statement is the number of SQL execution results.)

When you perform an update (insert, modify, delete) operation, you need to display the execution submission through Connection.commit () to map the results of execute or executemany execution to the database.

When you perform a query operation, you need to use Cursor.fetchone (), Cursor.fetchmany (size), Cursor.fetchall (), to get the results of one, multiple, and all SQL execution queries. If you use Cursor.frtchmany () to get one by default, you can use the Cursor.fetchmany (size=2) method if you want to get the specified number.

3.4 Understanding of cursors at query time

3.4.1 Cursor Rules

If the query results are executed using the same cursor.execute (), the initial state cursor executes the first element, and when cursor.fetch* is used, the cursor moves down;

Cursor.fetchone: Move down one position

Cursor.fetchmany (size=num): Move size down to specify numeric position

Cursor.fetchall (): Cursor moves to the end

For example:

Import mysqldbif __name__ = = "__main__": # create MySQL Connection connection = MySQLdb.connect (host= "127.0.0.1", PO rt=3306, user= "root", passwd= "root", db= "test") # get cursor cursor = connection.cursor () # Returns the number of execution results nums = CU Rsor.execute ("SELECT * from User") print (Cursor.fetchone ()) Print (Cursor.fetchmany (size=1)) print (Cursor.fetchal L ())

Execution Result:

(1L, ' admin ')

((2L, ' Wangzp '),)

((6L, ' wangzp12 '), (5L, ' wangzp123 '))

Depending on the results, the cursor will move, following the rules described above.

3.4.2 Setting the cursor position

Relative position cursors and absolute position cursors can be set by Cursor.scroll (position, mode= "relative | Absolute") methods.

Method parameter Description:

Position: Cursor Position

Mode: The pattern of the cursor position, relative: default mode, relative to the current position; absolute: absolute position

For example:

Mode=relative, position=1; indicates that the cursor is set to the position +1 of the current position, that is, moving down one position

Mode=absolute, position=2; Move the cursor to a location indexed to 2

     code example:

import mysqldbif __name__ ==  "__main__":     # create mysql connection    connection =  MySQLdb.connect (host= "127.0.0.1",  port=3306, user= "root",  passwd= "root",  db= "test")      # get cursor     cursor = connection.cursor ()     #  returns the number of execution results     nums = cursor.execute ("select *  from user ")      print (Cursor.fetchone ())  #  after execution, the cursor moves to the index position of 1     cursor.scroll (1)  #  relative cursor movement mode, current index +1, i.e. cursor position is 2    print ( Cursor.fetchmany (size=1)  #  so get 3rd element     cursor.scroll (0, mode= "Absolute")  #  Absolute Index Mode, resets the cursor to 0    print (Cursor.fetchall ())  #  so gets all the data 

Operation Result:

(1L, ' admin ')

((6L, ' wangzp12 '),)

((1L, ' admin '), (2L, ' Wangzp '), (6L, ' wangzp12 '), (5L, ' wangzp123 ')

3.5 Transaction Management

Use Connection.commit () to submit, Connection.rollback () rollback.

Summarize:

In addition to some of the above usages, there are some methods of injecting execution of stored procedures, not introduced here, details can refer to related documents. In fact, the use of relative or relatively simple. General development can be divided into the following steps:

1. Establish database connection

2. Fetching cursors

3. Execute SQL

4, if SQL is a query, then use the FETCH series function query, but you need to pay attention to how the cursor moves.

The following is a simple encapsulation Code (part):

Import mysqldbclass dbutil (object):         @staticmethod     def getconnection (HOST, PORT, USER, PASSWORD, DB):          "Get mysql connection"          connection = None        try:             connection = mysqldb.connect (host=host,  PORT=PORT, USER=USER, PASSWD=PASSWORD, DB=DB)          except mysqldb.error, e:             Print (e)         return connection              @staticmethod     def getcursor (connection):          "Get cursor"         cursor = none         try:             cursor = connection.cursor ()         except  mysqldb.error, e:            print (e)         return cursor              @staticmethod     def update (Cursor, sql, args ):         return cursor.execute (Sql, args)               @staticmethod     def  Updateandcommit (Connection, cursor, sql, args):         Nums = cursor.execute(Sql, args)         connection.commit ()          return nums         @staticmethod      def updatebatch (Cursor, sql, args):         return cursor.executemany (Sql, args)               @staticmethod     def updatebatchandcommit (Connection, cursor,  sql, args):         nums = cursor.executemany (SQL,  args)         connection.commit ()          return nums    if __name__ ==  "__main__":            connection = dbutil.getconnection (" 127.0.0.1 ", 3306, " root", " root ", " test ")     cursor = dbutil.getcursor (connection)      nums = cursor.execute ("Select * from user")      Print (Cursor.fetchall ())

This article is from the "Java Program Ghost" blog, please be sure to keep this source http://793404905.blog.51cto.com/6179428/1561650

Python MySQL Module