在python3.x中，可以使用pymysql来MySQL数据库的连接，并实现数据库的各种操作，本次博客主要介绍了pymysql的安装和使用方法。

 

PyMySQL的安装

一、.windows上的安装方法：

在python3.6中，自带pip3，所以在python3中可以直接使用pip3去安装所需的模块：

pip3 install pymysql -i https://pypi.douban.com/simple

二、.linux下安装方法：

1.tar包下载及解压

下载tar包
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
解压并展开tar包
tar xf PyMySQL-0.7.11.tar.gz

2.安装

[root@localhost PyMySQL-0.7.11]# python36 setup.py install

 

数据库的连接

本次测试创建的数据及表：

#创建数据库及表，然后插入数据
mysql> create database dbforpymysql;
mysql> create table userinfo(id int not null auto_increment primary key,username varchar(10),passwd varchar(10))engine=innodb default charset=utf8;
mysql> insert into userinfo(username,passwd) values('frank','123'),('rose','321'),('jeff',666);

#查看表内容
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
|  1 | frank    | 123    |
|  2 | rose     | 321    |
|  3 | jeff     | 666    |
+----+----------+--------+
3 rows in set (0.00 sec)

连接数据库：

import pymysql

#连接数据库
db = pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")

#使用cursor()方法创建一个游标对象
cursor = db.cursor()

#使用execute()方法执行SQL语句
cursor.execute("SELECT * FROM userinfo")

#使用fetall()获取全部数据
data = cursor.fetchall()

#打印获取到的数据
print(data)

#关闭游标和数据库的连接
cursor.close()
db.close()

#运行结果
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'))

要完成一个MySQL数据的连接，在connect中可以接受以下参数：

def __init__(self, host=None, user=None, password="",
             database=None, port=0, unix_socket=None,
             charset='', sql_mode=None,
             read_default_file=None, conv=None, use_unicode=None,
             client_flag=0, cursorclass=Cursor, init_command=None,
             connect_timeout=10, ssl=None, read_default_group=None,
             compress=None, named_pipe=None, no_delay=None,
             autocommit=False, db=None, passwd=None, local_infile=False,
             max_allowed_packet=16*1024*1024, defer_connect=False,
             auth_plugin_map={}, read_timeout=None, write_timeout=None,
             bind_address=None):
参数解释：
host: Host where the database server is located    #主机名或者主机地址
user: Username to log in as   #用户名
password: Password to use.    #密码
database: Database to use, None to not use a particular one.    #指定的数据库
port: MySQL port to use, default is usually OK. (default: 3306)    #端口，默认是3306
bind_address: When the client has multiple network interfaces, specify
    the interface from which to connect to the host. Argument can be
    a hostname or an IP address.    #当客户端有多个网络接口的时候，指点连接到数据库的接口，可以是一个主机名或者ip地址
unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
charset: Charset you want to use.    #指定字符编码
sql_mode: Default SQL_MODE to use. 
read_default_file:
    Specifies  my.cnf file to read these parameters from under the [client] section.
conv:
    Conversion dictionary to use instead of the default one.
    This is used to provide custom marshalling and unmarshaling of types.
    See converters.
use_unicode:
    Whether or not to default to unicode strings.
    This option defaults to true for Py3k.
client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorcl