PyMySQL 伊娃luation

 

PyMySQL Evaluation

This page will capture issues related to Openstack moving to
the PyMySQL driver for
MySQL/MariaDB dbapi access.

Rationale

While the MySQL-Python driver
is a very mature and stable driver, it does not provide compatibility
with either Python 3 or with eventlet
monkeypatching
 (MySQL-Python can
be
monkeypatched
 with
eventlet, but this feature disabled by default and missed in
documentation). So OpenStack’s usage of MySQL-Python, combined with the
fact that concurrency is provided by eventlet, means that we currently
have fully serialized database access within a single process, that is,
only one database command occurs at a time within an Openstack Python
process.

Drivers Under Consideration

The two drivers that are known to provide eventlet-monkeypatch
compatibility are MySQL-Connector and PyMySQL, as they are written in
pure Python. The two other well-known drivers for MySQL are MySQL-Python
and OurSQL, both of which are written in C and offer no explicit support
for eventlet or async. They can reportedly be built to support gevent
using a system called Greenify, but
the maturity and/or stabiltiy of this system is unknown.

A comparison
at http://www.diamondtin.com/2014/sqlalchemy-gevent-mysql-python-drivers-comparison/ illustrates
performance metrics observed with all four of these drivers, with
MySQL-python built both without and with the “greenify” system.

A summary of the status of all four drivers is as follows.

MySQL-Python

MySQL-Python is the most widely
used Python driver. However, it does not support Python 3, and it does
not support async systems unless used within a thread pool. While a
system known as Greenify can potentially resolve the latter situation,
the Python 3 limitation is still a deal-breaker – pull request with
Python 3 support was
proposed
 to MySQL-python
in April 2014 and still not merged.

mysqlclient

mysqlclient is a fork
of MySQL-python which works in Python 3.3. It is fully functional and
passes all SQLAlchemy unit tests, and is also maintained by the same
people that maintain PyMySQL.

OurSQL

OurSQL is an alternative MySQL
driver that is also written in C. It features fast performance and uses
a different execution model than MySQL-python, using prepared
statements. OurSQL does have a Python 3 port hosted
on launchpad but is not integrated
with OurSQL itself nor is it published on Pypi. Because it is written in
C, it does not offer compatibility with async systems including
eventlet-style monkeypatching.

Besides the shaky Python 3 support and lack of eventlet support, OurSQL
is also not maintained, seeing its last master commit
on 2012-06-05.

MySQL-Connector-Python

MySQL-connector-Python is
a pure Python MySQL driver, and is now released under the auspices of
the MySQL project itself, as owned by Oracle. MySQL-connector-Python
supports both Python 3 as well as eventlet monkeypatching, and is well
maintained. It is endorsed by Oracle as the official Oracle-supported
driver for MySQL, so to that extent, it is in most ways Openstack’s
first choice in driver. However, Oracle refuses to publish
MySQL-connector-Python on Pypi, which is critical to the Openstack
infrastructure. Repeated attempts to communicate with Oracle in order to
resolve this issue have not made any progress. Therefore, for this one
unfortunate reason, MySQL-connector-Python will not have a place in the
Openstack ecosystem unless this issue is resolved.

PyMySQL

PyMySQL is a pure Python MySQL
driver, first written as a rough port of the MySQL-Python driver.
PyMySQL meets all of Openstack’s criterion for a driver: it is fully
open source, hosted on Github, released on Pypi, is actively maintained,
is written in pure Python so is eventlet-monkeypatch compatible, and is
fully Python 3 compatible.

As this document is named “PyMySQL Evaluation”, it should be apparent
that this is the driver Openstack is currently leaning towards; because
it is the only one that meets all criteria fully, it is already most
likely the “winner”. However, it does have some minor code quality
issues which hopefully can be addressed in some way; the section below
titled “PyMySQL Code Review” will summarize the current state of the
code.

MySQL DB Drivers Comparison

Project PyPi hosted Eventlet friendly Python 3 compatibility Maturity and/or stability Comment
MySQL-Python Yes Partial No Yes Can be monkeypatched by eventlet, but only to enable thread pooling
mysqlclient Yes Partial Yes Yes Initial testing shows that this is a promising DBAPI if eventlet requirement can be dropped
OurSQL Yes No Yes, but not Pypi hosted No Development halted fairly early on, and has not seen commits/releases in two years
MySQL-Connector-Python No Yes Yes Yes, though the driver is still fairly new The official Oracle-supported driver for MySQL
PyMySQL Yes Yes Yes Yes, however see notes below. Actively maintained and popular.

PyMySQL Code Review

PyMySQL started roughly as a pure Python port of MySQL-Python. The
sections below will detail various aspects of the driver and the status
of the code. Overall, the general tone of PyMySQL is one of code that
was written in the spirit of pragmatism and immediate need; it is
straightforward, free of serious antipatterns and performs its task in a
matter-of-fact way. However, it lacks polish and completeness in many
areas that would normally be in better condition for a more mature
project. These areas are all highly fixable, and it is hoped that these
items can be shared with the current PyMySQL developers, through a
combination of this document itself as well as new issues and pull
requests on the PyMySQL tracker, so that this fairly promising library
can be pushed to the next level. This would require that the developers
are amenable to these improvements.

Coding Style / Pep8

PyMySQL’s coding style is pretty good. The majority of it passes all
flake8 tests, save for a few dozen whitespace issues and some long lines
here and there within the core modules, and moreso within the tests
which appear to be a bit more crufty than the core modules. A full run
of flake8 produces only 128 errors, and with a default run of autopep8
against E1,E2,E3, we can fix all of them in one pass except for 38
remaining long line warnings. The code layout is mostly idiomatic and
reasonable.

The codebase is probably short on docstrings. Those methods that are
part of the public DBAPI do have docstrings, but tend to be extremely
terse (example, docstring for cursor.execute(): “‘Execute a query'”).
Other methods that aren’t public tend to not have any docstrings, but
the codebase is not at all hard to read and the purpose of methods and
functions is pretty easy to discern just by their names and
implementations, however those who seek inline documentation as a means
to understand a codebase will be disappointed. The terseness of public
API docstrings is more of an issue in terms of the overall lack of
documentation for PyMySQL, see the section “Documentation” below.

Test Coverage

The test coverage for PyMySQL is definitely lacking. From a visual
inspection alone, it’s apparent that of the dozen or so test modules,
most of them have less then ten tests each and the modules are very
short. For a full test run, there’s only 126 tests; contrast this to
MySQL-Connector-Python which has 531 tests.

Additionally, all of the tests are live round-trip tests against a real
database. There are no non-live unit tests of any kind, leaving
codepaths that are not easily exercised on a generic MySQL database out
in the cold. This means that features designed for particular versions
of MySQL, particular datatypes, particular error conditions, and
particular kinds of message packets that aren’t created for real are
simply not tested.

The lack of tests is apparent when run with coverage. The test suite
itself does not appear to have any signs that it is normally run with
coverage turned on, with no directives in tox.ini or similar. With
coverage, the overall coverage is 85%, but the modules most lacking in
coverage are also the most critical to PyMySQL’s core functionality,
connections.py, converters.py and cursors.py:

Name                                                              Stmts   Miss  Cover
-------------------------------------------------------------------------------------
pymysql/__init__                                                     51      3    94%
pymysql/_compat                                                      14      4    71%
pymysql/_socketio                                                    72     30    58%
pymysql/charset                                                     228      3    99%
pymysql/connections                                                 827    188    77%
pymysql/constants/CLIENT                                             18      0   100%
pymysql/constants/COMMAND                                            32      0   100%
pymysql/constants/ER                                                471      0   100%
pymysql/constants/FIELD_TYPE                                         29      0   100%
pymysql/constants/FLAG                                               15      0   100%
pymysql/constants/SERVER_STATUS                                      10      0   100%
pymysql/constants/__init__                                            0      0   100%
pymysql/converters                                                  148     51    66%
pymysql/cursors                                                     273     37    86%
pymysql/err                                                          40      1    98%
pymysql/tests/__init__                                               14      5    64%
pymysql/tests/base                                                   25      2    92%
pymysql/tests/test_DictCursor                                        73      2    97%
pymysql/tests/test_SSCursor                                          56      6    89%
pymysql/tests/test_basic                                            186      4    98%
pymysql/tests/test_connection                                        57      8    86%
pymysql/tests/test_example                                           17      2    88%
pymysql/tests/test_issues                                           300     48    84%
pymysql/tests/test_load_local                                        39      2    95%
pymysql/tests/test_nextset                                           43      4    91%
pymysql/tests/thirdparty/__init__                                     7      5    29%
pymysql/tests/thirdparty/test_MySQLdb/__init__                        6      2    67%
pymysql/tests/thirdparty/test_MySQLdb/capabilities                  196     23    88%
pymysql/tests/thirdparty/test_MySQLdb/dbapi20                       423    121    71%
pymysql/tests/thirdparty/test_MySQLdb/test_MySQLdb_capabilities      73      6    92%
pymysql/tests/thirdparty/test_MySQLdb/test_MySQLdb_dbapi20          100      2    98%
pymysql/tests/thirdparty/test_MySQLdb/test_MySQLdb_nonstandard       57      3    95%
pymysql/times                                                        12      0   100%
pymysql/util                                                         14      7    50%
-------------------------------------------------------------------------------------
TOTAL                                                              3926    569    86%

The lack of coverage in converters.py refers to functions related to
handling incoming bound values as well as outgoing result values. Key
data escaping features such as escaping of dictionaries, sets, some
unicode objects and byte objects as well as many pathways into date and
time-processing functions are not covered.

In connections.py, uncovered features include various SSL and very
legacy (e.g. version 3.23) MySQL features, but also some protocol
parsing features.

In cursors.py, there is coverage for an elaborate and mostly
undocumented performance-related feature that rewrites an INSERT
statement to have an extended VALUES clause (_do_execute_many),
however it fails to cover the very likely case where the function will
need to break the input set into multiple chunks:

>         for arg in args:
>             v = values % escape(arg, conn)
>             if isinstance(v, text_type):
>                 v = v.encode(encoding)
>             if len(sql) + len(v) + 1 > max_stmt_length:
!                 rows += self.execute(sql)
!                 sql = bytearray(prefix)
>             else:
>                 sql += b','
>             sql += v
>         rows += self.execute(sql)

The most prominent missing coverage in cursors.py seems to be for its
“scrollable cursor” support.

Beyond the lack of completeness in testing, many of the tests themselves
are written in an expedient “one giant test” style where dozens of
assertions and individual behaviors are lumped into one big test case
(see test_SSCursor.py, test_basic.py->test_datatypes for
examples). Test cases like these are difficult to work with when
debugging regressions as well as when tests for new features need to be
added, and ideally these should be broken out into clean single-feature
tests with consistent fixtures.

There is also a suite called “test_issues”, which intends to accumulate
tests against specific issues that have been reported. This isn’t a bad
idea, though it would be nice to see an effort made into categorizing
these tests into the actual features they are testing, rather than a
meaningless list of issue numbers, which could then form as the basis
for new suites centered around those areas of functionality. The issues
reported should be used as the inspiration for improvement of the test
suite, rather than just another line-item to be filed away.

Library Documentation

As the Python DBAPI is already a well-documented API, and the users of
PyMySQL are typically coming from MySQL-Python which is documented to
some degree and for which the use contract is widely known, it seems
unlikely that users of PyMySQL are eager for comprehensive documentation
specific to this library.

That being said, PyMySQL does not appear to have any real library
documentation at all. There’s a README which essentially refers to
pep-249, an example.py in the root which shows a very basic connection /
round trip, and that’s it. In order to know anything about the API,
specific parameters, optimization strategies, behaviors, etc., one
either has to go off of the MySQL-Python documentation and assume
PyMySQL also features the same parameter, or read the source code.

PyMySQL probably hasn’t had much urgent need for real documentation thus
far. However, relying upon being a port of MySQL-Python is fast becoming
something that can no longer be relied upon, as MySQL-Python’s
development is quite stalled, and PyMySQL should aspire to move into the
future of MySQL and Python with its own featureset and behaviors. To
that end, it would be a great idea if it at least made the start of a
rudimentary Sphinx build including autodoc for module documentation
which could then be published up to RTD. This would be very easy to get
started, and once present, new documentation sections can be added
iteratively.

Architecture and Performance

As stated before, PyMySQL has a really matter-of-fact and
straightforward implementation. The code is very readable and it’s easy
to discern even unusual features such as the INSERT..VALUES rewriting.
As far as the design, there are lots of areas where performance suffers
a bit at the Python level; these areas could be greatly improved very
easily with a little bit of attention.

As we are dealing with a driver that is communicating with a low level
protocol over a socket, it’s critical that sending and receiving
messages is done as efficiently as possible. The mechanisms used in
PyMySQL rely heavily on standard Python objects, without the use of
__slots__; this means that for individual messages on a socket, we
are creating a heavyweight object, calling its __init__() method and
a creating a new __dict__ each time; the most common object,
OKPacketWrapper(), already wraps a MysqlPacket() object, so we’re doing
two __init__()s in this common case. OKPacketWrapper also implements
a `__getattr__`Oracle, scheme to proxy attribute access from itself to
the internal MysqlPacket; this is a an inefficient mode of operation in
Python as it means a lookup must first fail on the OKPacketWrapper
before it invokes the `__getattr__()` method and does another
attribute lookup. A reorg of the packet classes to produce far fewer
objects for messages as well as to use __slots__ would be
recommended.

There are also at least some areas where function call overhead is
unnecessarily high due to the organization of calls. For example, when
calling upon cursor.execute(), for parameters passed as a dictionary
under Python 2K, the isinstance() builtin function will be called on the
incoming arguments four times at a minimum, with two of each call
against the exact same criteria. Additional logic will then call
isinstance() on every key of the dictionary as well as at least twice on
every value within the dictionary, in order to determine if the value
needs to be converted to bytes as well as for some top-level escaping
logic checks; the escaping logic then goes further with more checks for
type() to match the object to a final converter function. While the flow
of this logic does appear to be reasonable and straightforward, it isn’t
ideal for a low-level database driver library where speed is a high
priority; the various wrappers and processors can easily be reorganized
here to reduce the number of isinstance() and type() calls
significantly, which would save a lot on function call overhead. It is
likely there are many other areas in the codebase where similar
reductions in call counts can be made without too much disruption.

In Appendix A, a short performance suite is illustrated. This is run
against PyMySQL, Mysql-Connector-Python, and MySQL-Python. PyMySQL does
in fact demonstrate the highest call-count behavior, logging 26,851,003
function calls compared to MySQL-connector’s 11,674,074 for the same
operations. To PyMySQL’s credit, it somehow completes the test 25-50%
faster than MySQL-connector despite having more than twice as many
function calls; this probably indicates the use of something very slow
within MySQL-connector, such as catching an exception within a tight
loop. However, the real bar we’re comparing to is MySQL-Python, which is
written in C and does the whole test with only 47,503 Python function
calls; while PyMySQL and MySQL-connector-Python compete for time within
the 10-15 second range without profiling, MySQL-Python completes the
whole test in **1.03 seconds**, a 1000% improvement over PyMySQL.
This speed difference is not PyMySQL’s fault, as pure Python is known to
be extremely slow compared to C code. However, there’s a lot of easy
wins in PyMySQL’s codebase where the function call count could probably
be reduced dramatically to be even better than that of
MySQL-connector-Python; ideally, the driver would be able to run this
test possibly 5-6x slower than MySQL-Python rather than 10x.

History Documentation

PyMySQL includes a CHANGELOG file in its root which describes the bug
fixes and features of each release. The construction of this file is
definitely within the “expedient” style noted for PyMySQL overall, and
for it to be genuinely useful for enterprise-level software, it would
need to be much more complete in its detail. Each bug fix mentioned is
only referred to tersely without any link to a pull request or bug
report. While some can be linked to a specific change through detective
work (“Cursor.fetchall() and .fetchmany now return list, not tuple”), it
would be very difficult for many (“Fixed BIT type handling”, “Fixed GC
errors” – what was the issue?) and in many cases impossible (“Improved
Py3k support”). The file also includes no dates so one has to look at
the git tags to figure this part out (fortunately, releases to seem to
be git tagged at least). For a user trying to ascertain if particular
issues are fixed or if behaviors have changed, this file is at best
highly frustrating.

A listing of release dates illustrates an average of two releases per
year, excluding 2012 when PyMySQL was seeking a new maintainer:

2014-12-02 12:05:10 -0200  (tag: pymysql-0.6.3)
2014-04-21 14:28:47 -0300  (tag: pymysql-0.6.2)
2013-10-11 13:36:56 -0300  (tag: pymysql-0.6.1)
2013-10-04 14:25:14 -0300  (tag: pymysql-0.6)
2011-11-08 10:27:41 -0800  (tag: pymysql-0.5)
2010-12-27 17:28:16 +0000  (tag: pymysql-0.4)
2010-09-03 00:55:29 +0000  (tag: pymysql-0.3)

 

Community Involvement

This is an area where PyMySQL seems to shine, and might even be an
advantage as compared to MySQL-Connector-Python, which remains under the
cloak of MySQL’s vastly overstuffed bugtracker under the ownership of
the famously-opaque Oracle. For a small, low key library without any
real documentation and only 126 tests, it features 33 contributors on
Github and is up to pull request #285, which is quite high. Because
PyMySQL is small and straightforward, it does lend itself to
contribution and seems to do very well in this area. The developers so
far have been responsive to issues and pull requests made by the author.

Based on github contributor graphs it appears that PyMySQL had a lull in
2012 but was picked up again afterwards in 2013. As of 2012, its most
prominent developer Pete Hunt stopped development, and on July 25, 2013
added a note to the README that the project was looking for a new
maintainer. As of 2013 it was picked up by the current most prominent
developers Marcel Rodrigues (github username lecram) and INADA Naoki
(github username methane).

Most issues that have been noted thus far within the areas of testing,
documentation and architecture are all very fixable; if the Openstack
community can provide resources to address some of these areas, and if
the PyMySQL developers are reasonably open to accepting new changes and
producing new releases in a timely fashion, the PyMySQL driver could
rapidly become a fully formidable contender in the MySQL driver field.

 

Appendix A – Performance Tests

import cProfile
import StringIO
import pstats
import contextlib
import time


@contextlib.contextmanager
def profiled(dbapi):
    pr = cProfile.Profile()
    pr.enable()
    yield
    pr.disable()
    s = StringIO.StringIO()
    ps = pstats.Stats(pr, stream=s).sort_stats('cumulative')
    ps.print_stats()
    print "DBAPI:  %s" % dbapi
    print s.getvalue()


@contextlib.contextmanager
def timeonly(dbapi):
    now = time.time()
    try:
        yield
    finally:
        total = time.time() - now
        print "DBAPI:  %s, total seconds %f" % (dbapi, total)

import MySQLdb
import pymysql
from mysql import connector as mysqlconnector


def go(dbapi, ctx):
    conn = dbapi.connect(
        user='scott', passwd='tiger', host='localhost', db='test')
    cursor = conn.cursor()
    cursor.execute("""
CREATE TABLE IF NOT EXISTS test_things (
    x INTEGER,
    y VARCHAR(255),
    z FLOAT
) engine=InnoDB
""")
    cursor.execute("DELETE from test_things")

    with ctx(dbapi):
        for row in xrange(1000):
            cursor.execute(
                "INSERT INTO test_things (x, y, z) "
                "VALUES (%(x)s, %(y)s, %(z)s)",
                {"x": row, "y": "row number %d" % row,
                 "z": row * 4.57292, }
            )

        for x in xrange(500):
            cursor.execute(
                "select * from test_things")
            for row in cursor.fetchall():
                row[0], row[1], row[2]

    cursor.close()
    conn.close()

go(pymysql, profiled)
go(mysqlconnector, profiled)
go(MySQLdb, profiled)

go(pymysql, timeonly)
go(mysqlconnector, timeonly)
go(MySQLdb, timeonly)

DBAPI:  <module 'pymysql' from '/Users/classic/dev/PyMySQL/pymysql/__init__.pyc'>
         26851003 function calls in 15.924 seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1500    0.010    0.000   15.923    0.011 /Users/classic/dev/PyMySQL/pymysql/cursors.py:105(execute)
     1500    0.003    0.000   15.883    0.011 /Users/classic/dev/PyMySQL/pymysql/cursors.py:269(_query)
     1500    0.004    0.000   15.859    0.011 /Users/classic/dev/PyMySQL/pymysql/connections.py:746(query)
     1500    0.003    0.000   15.819    0.011 /Users/classic/dev/PyMySQL/pymysql/connections.py:892(_read_query_result)
     1500    0.004    0.000   15.812    0.011 /Users/classic/dev/PyMySQL/pymysql/connections.py:1097(read)
      500    0.003    0.000   15.559    0.031 /Users/classic/dev/PyMySQL/pymysql/connections.py:1154(_read_result_packet)
      500    0.900    0.002   15.429    0.031 /Users/classic/dev/PyMySQL/pymysql/connections.py:1187(_read_rowdata_packet)
   500000    3.417    0.000    9.173    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1200(_read_row_from_packet)
  2012000    1.157    0.000    5.307    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:311(read_length_coded_string)
   504500    1.495    0.000    5.209    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:845(_read_packet)
  2014500    1.248    0.000    3.035    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:293(read_length_encoded_integer)
  3539500    2.439    0.000    2.750    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:245(read)
  1009000    0.779    0.000    2.307    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:870(_read_bytes)
  1009000    1.197    0.000    1.423    0.000 {method 'read' of '_io.BufferedReader' objects}
   508500    0.379    0.000    0.695    0.000 /Users/classic/dev/PyMySQL/pymysql/util.py:3(byte2int)
  5058000    0.465    0.000    0.465    0.000 {len}
   500500    0.222    0.000    0.455    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1146(_check_packet_is_eof)
   504500    0.205    0.000    0.375    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:342(check_error)
  2504000    0.275    0.000    0.275    0.000 {method 'append' of 'list' objects}
   501500    0.182    0.000    0.230    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:326(is_eof_packet)
     3500    0.009    0.000    0.226    0.000 /Users/classic/dev/PyMySQL/pymysql/_socketio.py:45(readinto)
     3500    0.207    0.000    0.207    0.000 {method 'recv_into' of '_socket.socket' objects}
  1022000    0.203    0.000    0.203    0.000 {_struct.unpack}
   526000    0.200    0.000    0.200    0.000 {isinstance}
  1501000    0.195    0.000    0.195    0.000 {method 'get' of 'dict' objects}
   504500    0.176    0.000    0.176    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:238(__init__)
   504500    0.170    0.000    0.170    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:339(is_error_packet)
  2014500    0.167    0.000    0.167    0.000 {ord}
      500    0.006    0.000    0.125    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1224(_get_descriptions)
     2000    0.003    0.000    0.087    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:361(__init__)
     2000    0.019    0.000    0.084    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:365(__parse_field_descriptor)
   500000    0.056    0.000    0.056    0.000 /Users/classic/dev/PyMySQL/pymysql/converters.py:301(through)
     1500    0.005    0.000    0.037    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:915(_execute_command)
     1500    0.003    0.000    0.027    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:886(_write_bytes)
     1500    0.002    0.000    0.024    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/socket.py:223(meth)
     1500    0.021    0.000    0.021    0.000 {method 'sendall' of '_socket.socket' objects}
     1500    0.020    0.000    0.020    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:276(_do_get_result)
     1000    0.003    0.000    0.019    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:95(_escape_args)
     4000    0.002    0.000    0.015    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:99(<genexpr>)
     1000    0.002    0.000    0.014    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1127(_read_ok_packet)
     3000    0.003    0.000    0.013    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:712(escape)
     1000    0.005    0.000    0.012    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:416(__init__)
     8000    0.011    0.000    0.011    0.000 {method 'decode' of 'str' objects}
     3500    0.003    0.000    0.007    0.000 {method '_checkReadable' of '_io._IOBase' objects}
     2000    0.003    0.000    0.006    0.000 /Users/classic/dev/PyMySQL/pymysql/converters.py:19(escape_item)
     4000    0.002    0.000    0.006    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:126(<genexpr>)
     7500    0.003    0.000    0.005    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:115(ensure_bytes)
     2000    0.003    0.000    0.005    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:386(description)
     5500    0.004    0.000    0.004    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:268(advance)
     1000    0.001    0.000    0.004    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:722(escape_string)
     3500    0.004    0.000    0.004    0.000 /Users/classic/dev/PyMySQL/pymysql/_socketio.py:87(readable)
      500    0.002    0.000    0.004    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:442(__init__)
     3500    0.003    0.000    0.003    0.000 {method '_checkClosed' of '_io._IOBase' objects}
     1000    0.001    0.000    0.003    0.000 /Users/classic/dev/PyMySQL/pymysql/converters.py:59(escape_string)
     1500    0.003    0.000    0.003    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1080(__init__)
     1500    0.001    0.000    0.003    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:92(nextset)
     4000    0.002    0.000    0.002    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:397(get_column_length)
     1000    0.002    0.000    0.002    0.000 /Users/classic/dev/PyMySQL/pymysql/converters.py:56(escape_float)
     1000    0.002    0.000    0.002    0.000 {method 'sub' of '_sre.SRE_Pattern' objects}
     3000    0.002    0.000    0.002    0.000 {_struct.pack}
     6000    0.002    0.000    0.002    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:62(_get_db)
     1500    0.001    0.000    0.002    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:80(_nextset)
     1500    0.001    0.000    0.001    0.000 /Users/classic/dev/PyMySQL/pymysql/util.py:9(int2byte)
      500    0.001    0.000    0.001    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:244(fetchall)
     1500    0.001    0.000    0.001    0.000 {min}
     2500    0.001    0.000    0.001    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:323(is_ok_packet)
     1000    0.001    0.000    0.001    0.000 /Users/classic/dev/PyMySQL/pymysql/converters.py:52(escape_int)
     2000    0.001    0.000    0.001    0.000 {method 'items' of 'dict' objects}
     1500    0.001    0.000    0.001    0.000 {getattr}
     1500    0.000    0.000    0.000    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:1093(__del__)
     1000    0.000    0.000    0.000    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:259(read_all)
      500    0.000    0.000    0.000    0.000 /Users/classic/dev/PyMySQL/pymysql/cursors.py:67(_check_executed)
      500    0.000    0.000    0.000    0.000 /Users/classic/dev/PyMySQL/pymysql/connections.py:336(is_load_local_packet)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/contextlib.py:21(__exit__)
        1    0.000    0.000    0.000    0.000 test.py:8(profiled)
        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}

DBAPI:  <module 'mysql.connector' from '/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/__init__.pyc'>
         11674074 function calls in 19.049 seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
      500    0.640    0.001   18.556    0.037 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:820(fetchall)
      500    0.003    0.000   11.196    0.022 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:655(get_rows)
      500    1.561    0.003   11.191    0.022 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:292(read_text_result)
   504500    3.303    0.000    7.306    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/network.py:219(recv_plain)
   500000    3.572    0.000    6.625    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:359(row_to_python)
  1009000    3.155    0.000    3.155    0.000 {method 'recv_into' of '_socket.socket' objects}
   500000    2.049    0.000    2.329    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/utils.py:220(read_lc_string_list)
   500000    0.653    0.000    2.116    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:531(_STRING_to_python)
   502000    0.378    0.000    1.329    0.000 {method 'decode' of 'bytearray' objects}
   502000    0.221    0.000    0.951    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/encodings/utf_8.py:15(decode)
   502000    0.730    0.000    0.730    0.000 {_codecs.utf_8_decode}
   500000    0.514    0.000    0.514    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:413(_INT_to_python)
   509500    0.510    0.000    0.510    0.000 {_struct.unpack_from}
     1500    0.009    0.000    0.492    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:452(execute)
     1500    0.004    0.000    0.437    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:705(cmd_query)
   500000    0.367    0.000    0.367    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:405(_FLOAT_to_python)
   504500    0.343    0.000    0.343    0.000 {method 'extend' of 'bytearray' objects}
     1500    0.007    0.000    0.315    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:481(_send_cmd)
  2501500    0.275    0.000    0.275    0.000 {method 'append' of 'list' objects}
   500500    0.246    0.000    0.246    0.000 {method 'startswith' of 'bytearray' objects}
   524002    0.150    0.000    0.150    0.000 {isinstance}
     1500    0.011    0.000    0.118    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:613(_handle_result)
  1004500    0.112    0.000    0.112    0.000 {len}
   500000    0.092    0.000    0.092    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:204(description)
     1500    0.007    0.000    0.042    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/network.py:118(send_plain)
     2000    0.013    0.000    0.041    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:226(parse_column)
     1000    0.008    0.000    0.034    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:346(_process_params_dict)
     1500    0.002    0.000    0.029    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/socket.py:223(meth)
     1500    0.026    0.000    0.026    0.000 {method 'sendall' of '_socket.socket' objects}
     5000    0.007    0.000    0.014    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/catch23.py:79(struct_unpack)
     1000    0.001    0.000    0.013    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:562(_handle_ok)
    12000    0.011    0.000    0.011    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/utils.py:167(read_lc_string)
     1000    0.004    0.000    0.010    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:199(parse_ok)
     3000    0.005    0.000    0.009    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:152(to_mysql)
     1500    0.003    0.000    0.007    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:118(make_command)
     3000    0.003    0.000    0.007    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:130(quote)
     1500    0.005    0.000    0.006    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/network.py:53(_prepare_packets)
     1500    0.003    0.000    0.006    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:406(_handle_result)
     3000    0.003    0.000    0.006    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:102(escape)
     1000    0.002    0.000    0.006    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:252(parse_eof)
      500    0.001    0.000    0.005    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:579(_handle_eof)
     2000    0.003    0.000    0.005    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:545(_handle_server_status)
     1500    0.003    0.000    0.004    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/utils.py:53(int1store)
     3000    0.003    0.000    0.003    0.000 {method 'encode' of 'str' objects}
     9001    0.003    0.000    0.003    0.000 {method 'replace' of 'str' objects}
     1500    0.003    0.000    0.003    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:295(_reset_result)
     4500    0.003    0.000    0.003    0.000 {_struct.pack}
     2500    0.003    0.000    0.003    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/utils.py:296(read_lc_int)
      500    0.002    0.000    0.003    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:757(_handle_eof)
     6027    0.003    0.000    0.003    0.000 {method 'format' of 'str' objects}
     3000    0.002    0.000    0.003    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:1221(_set_unread_result)
      500    0.001    0.000    0.002    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/protocol.py:219(parse_column_count)
     1000    0.002    0.000    0.002    0.000 {repr}
     4527    0.002    0.000    0.002    0.000 {getattr}
     4000    0.002    0.000    0.002    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/constants.py:34(flag_is_set)
     1000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:383(_handle_noresultset)
     4000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:1233(_get_unread_result)
     2001    0.001    0.000    0.001    0.000 {method 'items' of 'dict' objects}
      500    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:307(_have_unread_result)
     3000    0.001    0.000    0.001    0.000 {method 'lower' of 'str' objects}
      500    0.001    0.000    0.001    0.000 {range}
     1000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:173(_str_to_mysql)
     1000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:169(_float_to_mysql)
     1000    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/conversion.py:161(_int_to_mysql)
     1500    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/connection.py:1313(_get_getwarnings)
     1500    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:200(reset)
      500    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/cursor.py:397(_handle_resultset)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/encodings/__init__.py:71(search_function)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/encodings/ascii.py:41(getregentry)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/contextlib.py:21(__exit__)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/encodings/__init__.py:49(normalize_encoding)
        1    0.000    0.000    0.000    0.000 test.py:8(profiled)
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/codecs.py:77(__new__)
        1    0.000    0.000    0.000    0.000 {__import__}
        3    0.000    0.000    0.000    0.000 {method 'get' of 'dict' objects}
        1    0.000    0.000    0.000    0.000 {method 'split' of 'str' objects}
        1    0.000    0.000    0.000    0.000 {built-in method __new__ of type object at 0x1001534e8}
        1    0.000    0.000    0.000    0.000 {method 'translate' of 'str' objects}
        1    0.000    0.000    0.000    0.000 {method 'join' of 'str' objects}
        1    0.000    0.000    0.000    0.000 {hasattr}
        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}

DBAPI:  <module 'MySQLdb' from '/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/__init__.pyc'>
         47503 function calls in 0.918 seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1500    0.009    0.000    0.917    0.001 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:164(execute)
     1500    0.002    0.000    0.890    0.001 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:353(_query)
     1500    0.004    0.000    0.498    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:315(_do_query)
     1500    0.016    0.000    0.391    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:358(_post_get_result)
     1500    0.001    0.000    0.375    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:324(_fetch_row)
      500    0.374    0.001    0.374    0.001 {built-in method fetch_row}
     1500    0.007    0.000    0.362    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:142(_do_get_result)
     1500    0.002    0.000    0.351    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:351(_get_result)
     1500    0.349    0.000    0.349    0.000 {method 'store_result' of '_mysql.connection' objects}
     1500    0.131    0.000    0.131    0.000 {method 'query' of '_mysql.connection' objects}
     4000    0.002    0.000    0.010    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:184(<genexpr>)
     3000    0.002    0.000    0.008    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/connections.py:267(literal)
     1500    0.007    0.000    0.007    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:107(_warning_check)
     3000    0.002    0.000    0.006    0.000 {method 'escape' of '_mysql.connection' objects}
     6000    0.002    0.000    0.002    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:159(_get_db)
     1000    0.002    0.000    0.002    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/converters.py:81(Float2Str)
     2500    0.001    0.000    0.001    0.000 {isinstance}
     1000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/connections.py:202(string_literal)
      500    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:380(fetchall)
      500    0.001    0.000    0.001    0.000 {built-in method describe}
     1000    0.001    0.000    0.001    0.000 {method 'string_literal' of '_mysql.connection' objects}
     1500    0.001    0.000    0.001    0.000 {method 'insert_id' of '_mysql.connection' objects}
     1500    0.001    0.000    0.001    0.000 {method 'affected_rows' of '_mysql.connection' objects}
     1000    0.001    0.000    0.001    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/converters.py:69(Thing2Str)
     1500    0.000    0.000    0.000    0.000 {method 'warning_count' of '_mysql.connection' objects}
     1500    0.000    0.000    0.000    0.000 {method 'info' of '_mysql.connection' objects}
     1000    0.000    0.000    0.000    0.000 {method 'iteritems' of 'dict' objects}
      500    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/cursors.py:103(_check_executed)
      500    0.000    0.000    0.000    0.000 {len}
      500    0.000    0.000    0.000    0.000 {built-in method field_flags}
        1    0.000    0.000    0.000    0.000 /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/contextlib.py:21(__exit__)
        1    0.000    0.000    0.000    0.000 test.py:8(profiled)
        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}

DBAPI:  <module 'pymysql' from '/Users/classic/dev/PyMySQL/pymysql/__init__.pyc'>, total seconds 10.046012
DBAPI:  <module 'mysql.connector' from '/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/mysql/connector/__init__.pyc'>, total seconds 15.403260
DBAPI:  <module 'MySQLdb' from '/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQLdb/__init__.pyc'>, total seconds 1.028737

 

  • Oracle 1 
  • Oracle 2

相关文章