PyMySQL evaluation

= 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.

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__` 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:  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 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 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:  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:  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 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: , total seconds 10.046012 DBAPI: , total seconds 15.403260 DBAPI: , total seconds 1.028737