Python之pymysql的使用小结

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

下载tar包
wget http://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

#创建数据库及表，然后插入数据
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'))

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.
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() #提交数据
cursor.close()
db.close()
或者在execute提供插入的数据
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() #提交数据
cursor.close()
db.close()

在mysql中使用"--"代表注释，比如现在来实现一个用户登录的小程序：
用户名和密码都存在表userinfo中，表内容如下：
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('登录失败')
#正确登录的运行结果
username:frank
password:123
result: 1
登录成功
#错误登录的运行结果
username:frank
password:1231231
result: 0
登录失败
看起来没有什么问题，但是试试下面的方式吧
----------------------------------------------
username:' or 1=1 -- 
password:123
result: 3
登录成功
----------------------------------------------
咦~也登录成功了.
为什么呢？可以看一下现在的执行的sql语句：
select * from userinfo where username='' or 1=1 -- ' and passwd='123'
这里--后面的会被注释，所以where一定会成功，这里等于查看了所有行的内容，返回值也不等于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() #提交数据
cursor.close()
db.close()

sql = "delete from userinfo where username=%s"
res = cursor.executemany(sql,("jack",))
print("res=",res)
#运行结果
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() #提交数据
cursor.close()
db.close()

#运行结果
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() #第一次执行
print(res)
res = cursor.fetchone() #第二次执行
print(res)
cursor.close()
db.close()

#运行结果
(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() #第一次执行
print(res)
res = cursor.fetchall() #第二次执行
print(res)
cursor.close()
db.close()
#运行结果
((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()
#运行结果
[{'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() #第二次获取到的内容
print(res)

#运行结果
[{'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: #获取数据库连接的对象
  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()

#运行结果
{'id': 1, 'username': 'frank', 'passwd': '123'}
{'id': 5, 'username': 'bob', 'passwd': '123'}