本博客为使用python连接mysql数据库，对其主要方法进行解析

pymysql主要使用cursor对数据库进行交互操作和结果处理，所以cursor对象为重中之重

0、一个简单的数据库交互案例

环境安装：pymysql

pip3 install pymysql

本文测试数据库结构：

create table TEST_TABLE
(
	ID int auto_increment
		primary key,
	USER_NAME varchar(255) null,
	USER_PASSWORD varchar(255) null
);

新建一个.py文件，进行一次数据库交互

# 导入package
import pymysql

# 建立数据库连接，可传参数（详见1.2）
db = pymysql.connect(host="localhost", user="root", password="123456", db="Test", charset='utf8mb4')

# 使用 cursor() 方法创建一个游标对象 cursor，可传参数，表示返回到python的结果用什么方式存储（详见2.2）
cursor = db.cursor()

# 使用List作为参数进行查询，塞入参数
params = ['Jack Ma', '123123']

# 使用 execute()  方法执行 SQL 查询，会返回受影响的行数（详见3 - 4）
cursor.execute("select * from TEST_TABLE where USER_NAME = %s and USER_PASSWORD = %s",params)

# 获取结果（详见5）
results = cursor.fetchall()
for result in results:
	  print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s",result[0],result[1],result[2])
	  
db.close()

可以看到，使用pymysql与数据库交互非常简单，大体分为四步：

  1. 建立数据库连接：db = pymysql.connect(host="localhost", user="root", password="123456", db="Test", charset='utf8mb4')
  2. 获取游标对象：cursor = db.cursor()
  3. 调用游标的cursor.execute()方法执行SQL语句
  4. 通过各种cursor.fetch()操作拿到结果集

以下为各个步骤的关键函数的详细解析

1、pymysql.connect()

创建一个数据库连接

1.1、使用案例

# 可在值前指定传入哪个参数
db = pymysql.connect(host = "localhost", user = "root", password = "123456", db = "Test",charset='utf8')

1.2、可选参数

源码注释

class Connection(object):
    """
    Representation of a socket with a mysql server.

    The proper way to get an instance of this class is to call
    connect().

    Establish a connection to the MySQL database. Accepts several
    arguments:
    
:param host: Host where the database server is located
:param user: Username to log in as
:param password: Password to use.
:param database: Database to use, None to not use a particular one.
:param port: MySQL port to use, default is usually OK. (default: 3306)
:param 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.
:param unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
:param read_timeout: The timeout for reading from the connection in seconds (default: None - no timeout)
:param write_timeout: The timeout for writing to the connection in seconds (default: None - no timeout)
:param charset: Charset you want to use.
:param sql_mode: Default SQL_MODE to use.
:param read_default_file:
    Specifies  my.cnf file to read these parameters from under the [client] section.
:param conv:
    Conversion dictionary to use instead of the default one.
    This is used to provide custom marshalling and unmarshalling of types.
    See converters.
:param use_unicode:
    Whether or not to default to unicode strings.
    This option defaults to true for Py3k.
:param client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
:param cursorclass: Custom cursor class to use.
:param init_command: Initial SQL statement to run when connection is established.
:param connect_timeout: Timeout before throwing an exception when connecting.
    (default: 10, min: 1, max: 31536000)
:param ssl:
    A dict of arguments similar to mysql_ssl_set()'s parameters.
:param read_default_group: Group to read from in the configuration file.
:param compress: Not supported
:param named_pipe: Not supported
:param autocommit: Autocommit mode. None means use server default. (default: False)
:param local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
:param 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).
:param defer_connect: Don't explicitly connect on construction - wait for connect call.
    (default: False)
:param 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)
:param server_public_key: SHA256 authentication plugin public key value. (default: None)
:param db: Alias for database. (for compatibility to MySQLdb)
:param passwd: Alias for password. (for compatibility to MySQLdb)
:param binary_prefix: Add _binary prefix on bytes and bytearray. (default: False)
"""

2、db.cursor()

创建一个游标，其返回到python的存储形式，需在创建的时候指定（不指定使用默认值 cursor）

2.1、使用案例

db = pymysql.connect(host = "localhost", user = "root", password = "123456", db ="LicenseServer",charset='utf8')

# 使用 cursor() 方法创建一个默认游标对象 cursor
cursor = db.cursor()
# 或者创建其他游标对象（需要导入对应包）
SSCursor = db.cursor(SSCursor)

2.2、可选参数

如果无参数，默认为Cursor，可选SSCursor,DictCursor,SSDictCursor

表示返回到Python的结果用什么方式存储，如Dict.Cursor是以字典方式存储

def cursor(self, cursor=None):
    """
    Create a new cursor to execute queries with.

    :param cursor: The type of cursor to create; one of :py:class:`Cursor`,
        :py:class:`SSCursor`, :py:class:`DictCursor`, or :py:class:`SSDictCursor`.
        None means use Cursor.
    """

Cursor

普通的游标对象，默认创建的游标对象

SSCursor

不缓存游标，主要用于当操作需要返回大量数据的时候

DictCursor

以字典的形式返回操作结果

SSDictCursor

不缓存游标，将结果以字典的形式进行返回

3、cursor.execute()

3.1、使用案例

# 直接传入字符串进入 execute()  方法执行 SQL 查询
cursor.execute("select * from TEST_TABLE")
# 使用 execute(),传入一个list作为参数执行 SQL 查询
params = [1,'Jack Ma']
cursor.execute("select * from LICENSE where id = %s and USER_NAME = %s",params)

3.2、可选参数

默认args为空

如果传入的args为一个List或者tuple，可以在sql语句中使用%s 进行替换

如果传入的args为一个dict，可以使用%(name)s

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.

4、cursor.executemany()

向数据表中插入多条数据，使用executemany方法，在生产环境中插入多条数据 ，在后台中获取数据后，以列表的形式传入语句（[(‘v1’,‘v2’),(‘v3’,‘v4’)]）

4.1、使用案例

使用DML语句时，一定需要加上db.commit()，否则更改不生效

或者使用db.autocommit(true)开启自动commit

# 一次性向数据库添加两条数据
insertParams = [['kuifeng yuan','123123'],['neil','123123123']]
affected_rows  = cursor.executemany("insert into TEST_TABLE(`USER_NAME`,`USER_PASSWORD`) values(%s,%s)",insertParams)
print(affected_rows)

# 切记commit，否则insert不生效
db.commit()

4.2、可选参数

query为必填参数，为sql语句

args为填入sql语句的参数，可以为空，以list传入

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().
  """

5、cursor.fetch()

cursor.fetch()不是一个方法，而是表示该类操作的集合。作用为读取被cursor返回的数据

关于fetch有三个具体的方法：

  • cursor.fetchone()：读取下一条数据，不用传入任何参数
  • cursor.fetchmany()：读取多条数据，需要传入需要读取下面的多少条数据（如 size = 5 表示读取以下五条）
  • cursor.fetchall()：读取所有数据，不用传入任何参数

5.1、使用案例

5.1.1、cursor.fetchall()

cursor.execute("select * from TEST_TABLE")
results =  cursor.fetchall()
for result in results:
    print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s" % \
          (result[0],result[1],result[2]))

执行结果：打印所有查询到的值

ID: 1, USER_NAME:Jack Ma, USER_PASSWORD:123123
ID: 2, USER_NAME:Small Ma, USER_PASSWORD:312312123
ID: 3, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 4, USER_NAME:Edison, USER_PASSWORD:345234
ID: 5, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 6, USER_NAME:Edison, USER_PASSWORD:345234
ID: 7, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 8, USER_NAME:Edison, USER_PASSWORD:345234
ID: 9, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 10, USER_NAME:Edison, USER_PASSWORD:345234
ID: 11, USER_NAME:Jackson, USER_PASSWORD:532342

5.5.2、cursor.fetchone()

cursor.execute("select * from TEST_TABLE")
results =  cursor.fetchone()
print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s" % \
          (results[0],results[1],results[2]))

执行结果，只有一条，并且光标自动下移一位

ID: 1, USER_NAME:Jack Ma, USER_PASSWORD:123123

5.5.3、cursor.fetchmany()

需要传入size参数

cursor2.execute("select * from TEST_TABLE")
results =  cursor2.fetchmany(3)

for result in results:
    print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s" % \
          (result[0],result[1],result[2]))

执行结果：打印往下三条数据

ID: 1, USER_NAME:Jack Ma, USER_PASSWORD:123123
ID: 2, USER_NAME:Small Ma, USER_PASSWORD:312312123
ID: 3, USER_NAME:Jackson, USER_PASSWORD:532342

6、cursor.callproc()

作用：调用mysql中的存储过程

6.1、使用案例

  1. 创建mysql存储过程
create
    definer = root@localhost procedure insertTEST_TABLE(_USER_NAME varchar(255), _USER_PASSWORD varchar(255))
BEGIN
    insert into TEST_TABLE(`USER_NAME`,`USER_PASSWORD`) values(_USER_NAME,_USER_PASSWORD);
end;
  1. 调用.callproc()方法
cursor.callproc("insertTEST_TABLE",("Robert","4443222"))
  1. 结果：数据库成功插入数据

6.2、可选参数

调用cursor.callproc()时，必须传入mysql数据库中存储过程名字，args为可选参数

注意：args为一个元祖类型，不可直接传入（见使用案例）

def callproc(self, procname, args=()):
    """Execute stored procedure procname with args
		procname -- string, name of procedure to execute on server
		args -- Sequence of parameters to use with procedure
		Returns the original args.

7、cursor.scroll()

移动cursor光标

7.1、使用案例

cursor.execute("select * from TEST_TABLE")
results =  cursor.fetchmany(3)
for result in results:
    print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s" % \
          (result[0],result[1],result[2]))
    
# 使光标移动两位，后面的mode参数为optional
cursor.scroll(2,"relative")

# 继续向下fetch三位
results = cursor.fetchmany(3)
for result in results:
    print("ID: %s, USER_NAME:%s, USER_PASSWORD:%s" % \
          (result[0],result[1],result[2]))

运行结果：由于移动了两位光标，ID为4，5的数据并没有被展示出来（实际已被查出）

ID: 1, USER_NAME:Jack Ma, USER_PASSWORD:123123
ID: 2, USER_NAME:Small Ma, USER_PASSWORD:312312123
ID: 3, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 6, USER_NAME:Edison, USER_PASSWORD:345234
ID: 7, USER_NAME:Jackson, USER_PASSWORD:532342
ID: 8, USER_NAME:Edison, USER_PASSWORD:345234

7.2、可选参数

必须传入一个value值，通过源码得知，mode默认为relative

  • 如果mode为relative，移动cursor到相对位置
  • 如果mode为absolute，移动cursor到绝对位置
def scroll(self, value, mode='relative'):
  self._check_executed()
        if mode == 'relative':
            r = self.rownumber + value
        elif mode == 'absolute':
            r = value
        else:
            raise err.ProgrammingError("unknown scroll mode %s" % mode)

        if not (0 <= r < len(self._rows)):
            raise IndexError("out of range")
        self.rownumber = r

8、cursor.mogrify()

作用： 转换，把SQL语句和参数相结合，对其中特殊字符进行加\转义，返回一个execute()方法发送到数据库确切执行的字符串,避免注入语句生成。

一句话版本：得到真实获得执行的SQL语句

8.1、使用案例

sql = "select * from TEST_TABLE WHERE ID = %s"
mogrify = cursor.mogrify(sql,args=1)
print(mogrify)

控制台打印

select * from TEST_TABLE WHERE ID = 1

8.2、可选参数

query为必传参数，args默认为空

def mogrify(self, query, args=None):
    """
    Returns the exact string that is sent to the database by calling the
    execute() method.

    This method follows the extension to the DB API 2.0 followed by Psycopg.
    """
    conn = self._get_db()
    if PY2:  # Use bytes on Python 2 always
        query = self._ensure_bytes(query, encoding=conn.encoding)

    if args is not None:
        query = query % self._escape_args(args, conn)

    return query

以上为使用pymysql与数据库交互所需要的主要方法。此外，在db对象中，还有可以获得一些辅助信息，如:

  1. db.autocommit(True):打开事物自动提交
  2. db.affected_rows()：获得受影响的行数
  3. db.get_host_info() db.get_server_info()：获得host或者server信息
  4. …

希望本文能够抛砖引玉，如果错误还望各位多多指正