Python database operations - Getting started with PyMySQL

PyMySQL is a module for operating MySQL in Python. It has the same basic functions as the MySQLdb module used before. The performance of PyMySQL is almost the same as that of MySQLdb. If the performance requirements

are not particularly strong, it will be more convenient to use PyMySQL. PyMySQL is completely using python Written to avoid the trouble of installing MySQLdb separately across systems.

Applicable environment

python version >=2.6 or 3.3

mysql version >=4.1

Installation

Execute the command on the command line:

pip install pymysql

Manual installation, please download first. Download address: https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X.

where X.X is the version.

Unzip the compressed package after downloading. Enter the decompressed directory on the command line and execute the following instructions:

python setup.py install

It is recommended to use pip to install, which can automatically solve package dependency issues and avoid various errors during installation.

The basic operation of pymysql is as follows:

#!/usr/bin/env python
#   --coding = utf-8
#   Author Allen Lee
import pymysql
#创建链接对象
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='Allen')
#创建游标
cursor = conn.cursor()
#执行sql，更新单条数据，并返回受影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'")
#插入多条，并返回受影响的函数
effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)",[("1.0.0.1",1,),("10.0.0.3",2)])
#获取最新自增ID
new_id = cursor.lastrowid
#查询数据
cursor.execute("select * from hosts")
#获取一行
row_1 = cursor.fetchone()
#获取多(3)行
row_2 = cursor.fetchmany(3)
#获取所有
row_3 = cursor.fetchall()
#重设游标类型为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#提交，保存新建或修改的数据
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()