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

下載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

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

#創(chuàng)建數(shù)據(jù)庫及表，然后插入數(shù)據(jù)
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);

#查看表內(nèi)容
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

#連接數(shù)據(jù)庫
db = pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")

#使用cursor()方法創(chuàng)建一個游標對象
cursor = db.cursor()

#使用execute()方法執(zhí)行SQL語句
cursor.execute("SELECT * FROM userinfo")

#使用fetall()獲取全部數(shù)據(jù)
data = cursor.fetchall()

#打印獲取到的數(shù)據(jù)
print(data)

#關閉游標和數(shù)據(jù)庫的連接
cursor.close()
db.close()

#運行結(jié)果
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'))

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):
參數(shù)解釋：
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.  #指定的數(shù)據(jù)庫
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.  #當客戶端有多個網(wǎng)絡接口的時候，指點連接到數(shù)據(jù)庫的接口，可以是一個主機名或者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.
cursorclass: Custom cursor class to use.
init_command: Initial SQL statement to run when connection is established.
connect_timeout: Timeout before throwing an exception when connecting.
  (default: 10, min: 1, max: 31536000)
ssl:
  A dict of arguments similar to mysql_ssl_set()'s parameters.
  For now the capath and cipher arguments are not supported.
read_default_group: Group to read from in the configuration file.
compress; Not supported
named_pipe: Not supported
autocommit: Autocommit mode. None means use server default. (default: False)
local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
  Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
defer_connect: Don't explicitly connect on contruction - wait for connect call.
  (default: False)
auth_plugin_map: A dict of plugin names to a class that processes that plugin.
  The class will take the Connection object as the argument to the constructor.
  The class needs an authenticate method taking an authentication packet as
  an argument. For the dialog plugin, a prompt(echo, prompt) method can be used
  (if no authenticate method) for returning a string from the user. (experimental)
db: Alias for database. (for compatibility to MySQLdb)
passwd: Alias for password. (for compatibility to MySQLdb)

class Cursor(object):
  """
  This is the object you use to interact with the database.
  """
  def close(self):
    """
    Closing a cursor just exhausts all remaining data.
    """
  def setinputsizes(self, *args):
    """Does nothing, required by DB API."""

  def setoutputsizes(self, *args):
    """Does nothing, required by DB API."""    
  def execute(self, query, args=None):
    """Execute a query

    :param str query: Query to execute.

    :param args: parameters used with query. (optional)
    :type args: tuple, list or dict

    :return: Number of affected rows
    :rtype: int

    If args is a list or tuple, %s can be used as a placeholder in the query.
    If args is a dict, %(name)s can be used as a placeholder in the query.
    """
  def executemany(self, query, args):
    # type: (str, list) -> int
    """Run several data against one query

    :param query: query to execute on server
    :param args: Sequence of sequences or mappings. It is used as parameter.
    :return: Number of rows affected, if any.

    This method improves performance on multiple-row INSERT and
    REPLACE. Otherwise it is equivalent to looping over args with
    execute().
    """
  def fetchone(self):
    """Fetch the next row"""
  def fetchmany(self, size=None):
    """Fetch several rows"""
  def fetchall(self):
    """Fetch all the rows"""
  ......

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES('jack','123')"
cursor.execute(sql)
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()
或者在execute提供插入的數(shù)據(jù)
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("bob","123")) 
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()

在mysql中使用"--"代表注釋，比如現(xiàn)在來實現(xiàn)一個用戶登錄的小程序：
用戶名和密碼都存在表userinfo中，表內(nèi)容如下：
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
user = input("username:")
pwd = input("password:")
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo where username='%s' and passwd='%s'" %(user,pwd)
result=cursor.execute(sql)
cursor.close()
db.close()
if result:
  print('登錄成功')
else:
  print('登錄失敗')
#正確登錄的運行結(jié)果
username:frank
password:123
result: 1
登錄成功
#錯誤登錄的運行結(jié)果
username:frank
password:1231231
result: 0
登錄失敗
看起來沒有什么問題，但是試試下面的方式吧
----------------------------------------------
username:' or 1=1 -- 
password:123
result: 3
登錄成功
----------------------------------------------
咦~也登錄成功了.
為什么呢？可以看一下現(xiàn)在的執(zhí)行的sql語句：
select * from userinfo where username='' or 1=1 -- ' and passwd='123'
這里--后面的會被注釋，所以where一定會成功，這里等于查看了所有行的內(nèi)容，返回值也不等于0，所以就登錄成功了。
解決方法就是將變量或者實參直接寫到execute中即可：
result=cursor.execute(sql,(user,pwd))
在鍵入類似' or 1=1 -- 的時候就不會登錄成功了。 

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.executemany(sql,[("tom","123"),("alex",'321')])
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()

sql = "delete from userinfo where username=%s"
res = cursor.executemany(sql,("jack",))
print("res=",res)
#運行結(jié)果
res= 1

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("zed","123"))
print("the last rowid is ",cursor.lastrowid)
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()

#運行結(jié)果
the last rowid is 10

mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
| 5 | bob   | 123  |
| 8 | jack   | 123  |
| 10 | zed   | 123  |
+----+----------+--------+
6 rows in set (0.00 sec)

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchone() #第一次執(zhí)行
print(res)
res = cursor.fetchone() #第二次執(zhí)行
print(res)
cursor.close()
db.close()

#運行結(jié)果
(1, 'frank', '123')
(2, 'rose', '321')

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall() #第一次執(zhí)行
print(res)
res = cursor.fetchall() #第二次執(zhí)行
print(res)
cursor.close()
db.close()
#運行結(jié)果
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'), (5, 'bob', '123'), (8, 'jack', '123'), (10, 'zed', '123'))
()

cursor = db.cursor(cursor=pymysql.cursors.DictCursor) #在實例化的時候，將屬性cursor設置為pymysql.cursors.DictCursor

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.close()
db.close()
#運行結(jié)果
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]

cursor.scroll(1,mode='relative') # 相對當前位置移動
cursor.scroll(2,mode='absolute') # 相對絕對位置移動

sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.scroll(0,mode='absolute') #相對首行移動了0，就是把行指針移動到了首行
res = cursor.fetchall() #第二次獲取到的內(nèi)容
print(res)

#運行結(jié)果
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor: #獲取數(shù)據(jù)庫連接的對象
  sql = "SELECT * FROM userinfo"  
  cursor.execute(sql)
  res = cursor.fetchone()
  print(res)
  cursor.scroll(2,mode='relative')
  res = cursor.fetchone()
  print(res)
  cursor.close()
db.close()

#運行結(jié)果
{'id': 1, 'username': 'frank', 'passwd': '123'}
{'id': 5, 'username': 'bob', 'passwd': '123'}