Python database operations-Getting started with Pymysql

Pymysql is the module that operates MySQL in Python, consistent with the basic functionality of the MYSQLDB module previously used, Pymysql performance and mysqldb almost equal if performance requirements

Not particularly strong, the use of pymysql will be more convenient, Pymysql is completely written in Python, avoiding the hassle of installing mysqldb across systems separately.

Applicable environment

Python version >=2.6 or 3.3

MySQL version >=4.1

Installation

Execute the command at the command line:

Pip Install Pymysql

To install manually, please download it first. Download Address: Https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X.

One of the x.x is the version.

Unzip the package after downloading. Enter the extracted directory at the command line and execute the following command:

Python setup.py Install

It is recommended to use PIP installation, which automatically resolves package dependencies and avoids various errors in installation.

The basic operations of Pymysql are as follows:

#!/usr/bin/env python#   --coding = utf-8#   Author Allen leeimport pymysql# Create linked Object conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' root ', passwd= ' 123 ', db= ' Allen ') #创建游标cursor = Conn.cursor () #执行sql, update a single data and return the number of rows affected effect _row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ') #插入多条 and returns the affected function Effect_row = Cursor.executemany (" INSERT INTO hos TS (host,color_id) VALUES (%s,%s) ", [(" 1.0.0.1 ", 1,), (" 10.0.0.3 ", 2)]) #获取最新自增IDnew_id = cursor.lastrowid# Query data Cursor.execute ("SELECT * from hosts") #获取一行row_1 = Cursor.fetchone () #获取多 (3) Line row_2 = Cursor.fetchmany (3) #获取所有row_3 = Cursor.fetchall () #重设游标类型为字典类型cursor = Conn.cursor (cursor=pymysql.cursors.dictcursor) #提交, save new or modified Data conn.commit () # Close the cursor cursor.close () #关闭连接conn. Close ()

