Pure Python MySQL Client

Hi, I read similar problems but I didn't success to solve my problem, so.. here is my code, only that.

import pymysql

hostname = 'freehosting.host' port = 1234 username = 'username' password = 'pass' database = 'mydb'

myConnection = pymysql.connect(host = hostname, user = username, passwd = password, db = database, port = port) print "Using pymysql..." myConnection.close()

I got an error - myConnection = pymysql.connect(host = hostname, user = username, passwd = password, db = database, port = port) File "C:\Program Files\Python27\lib\site-packages\pymysql_init_.py", line 90, in Connect return Connection(*args, **kwargs) File "C:\Program Files\Python27\lib\site-packages\pymysql\connections.py", line 706, in init self.connect() File "C:\Program Files\Python27\lib\site-packages\pymysql\connections.py", line 931, in connect self._get_server_information() File "C:\Program Files\Python27\lib\site-packages\pymysql\connections.py", line 1245, in _get_server_information packet = self._read_packet() File "C:\Program Files\Python27\lib\site-packages\pymysql\connections.py", line 1001, in _read_packet % (packet_number, self._next_seq_id)) pymysql.err.InternalError: Packet sequence number wrong - got 80 expected 0

Someone wrote here about multiple threads (#138 ). I don't understand how and why

When I insert large blobs, e.g. a 10 MB byte string into a longblob field, it takes several seconds to prepare the value for insertion. Profiling indicates that most of that time is spent in the escape_bytes function in converters.py. Here is that function for reference.

def escape_bytes(value, mapping=None):
    # escape_bytes is calld only on Python 3.
    return escape_str(value.decode('ascii', 'surrogateescape'), mapping)

When I performed the same insertion through another interface, the escaping was about 100 times faster.

Is there a way to speed this up?

Bug reported in the sqlalchmey issue tracker but from @zzzeek's point of view, it's pymysql side bug.

This will cause a serious bug "transaction partial succeeding" when greenlet timed out and killed by gevent.

Original issue here: https://bitbucket.org/zzzeek/sqlalchemy/issue/3258/a-gevent-killed-greenlet-causes-pymysql

Reproduce script:

• https://gist.github.com/lxyu/4e14021e4bae7bc26da7
• https://gist.github.com/zzzeek/947337f44d70fac537be

django 1.4 use some MySQLdb internal functions and wrapped the converters like convert_datetime. in that case I add functions and refactor converters make it similar to MySQLdb's.

For issue #532 SHA256 authentication plugin is the recommended auth way for the MySQL now, for security reason. This covers all the SHA256 auth code pass.

1. mysqld with default_auth_plugin=sha256_password, and use SSL, server say sha256, client and server get SSL, and then client response sha256 password directly.
2. mysqld with default_auth_plugin=sha256_password, and do not use SSL, server say sha256, client response sha256, server (1) auth switch (2) tell the RSA public key directly, then client response the encrypted password.
3. mysqld with default_auth_plugin=mysql_native_password(By default), and use SSL, server say native, client and server get SSL, and then client response native, and server say AuthSwith, the client reponse the password directly.
4. mysqld with default_auth_plugin=mysql_native_password(By default), and do not use SSL, server say native, client say native, server say AuthSwith, and the client get RSA(already have or request '1' then server response public key) and response the encrypted password.

I test code in both 4 scenario. But can not add a proper unit test for it now. May need some help.

See #27 and #199.

The Sphinx documentation is only a draft. If you like it, I can adjust it (e.g. add converters, cursors, cursors, util) and probably move the examples from the README to the Sphinx docs.

I have a multiprocess application that processes large volumes of data in parallel and at the end loads CSV files into my DB.

The code starts off by creating n number of Python Processes. In each of these processes, I create a separate connection and cursor which I try to use to load this data however I keep getting an error with: pymysql.err.InternalError: Packet sequence number wrong - got 3 expected 2

Sample code here but obviously you would need to put in connection details etc:

from multiprocessing import Process
import pymysql

class RawFileExtractor(Process):

    def __init__(self, file_location):
        Process.__init__(self, daemon=True)
        self.file_location = file_location

    def run(self):
        self.load_csv()

    def load_csv(self):

        mysql_connection = pymysql.connect(user=user, password=password, host=host, database=database,
                                           max_allowed_packet=128000000, connect_timeout=100000, local_infile=True)
        mysql_cursor = mysql_connection.cursor(pymysql.cursors.DictCursor)

        mysql_cursor.execute("LOAD DATA LOCAL INFILE '{file_location}' INTO TABLE c210.table_name "
                             "FIELDS TERMINATED BY '\t' "
                             "LINES TERMINATED BY '\n';".format(file_location=self.file_location))
        mysql_connection.commit()
        mysql_connection.close()


processes = []
file_locations = ["/location/one.csv", "/location/two.csv"]
for i, file_location in enumerate(file_locations):
    processes[i] = RawFileExtractor(file_location=file_location)
    processes[i].start()

Of course my actual script/processes do a lot more than just load the data but this should be sufficient to replicate the issue.

I also know that this isn't a problem from the actual DB as I can load files without any problem using the python MySQL connector tool but I don't want to use it as it's buggy.

Full error below:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 986, in _write_bytes
    self.socket.sendall(data)
BrokenPipeError: [Errno 32] Broken pipe

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1457, in send_data
    conn.write_packet(chunk)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 936, in write_packet
    self._write_bytes(data)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 988, in _write_bytes
    raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 986, in _write_bytes
    self.socket.sendall(data)
BrokenPipeError: [Errno 32] Broken pipe

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1326, in _read_load_local_packet
    sender.send_data()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1462, in send_data
    conn.write_packet(b'')
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 936, in write_packet
    self._write_bytes(data)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 988, in _write_bytes
    raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/multiprocessing/process.py", line 254, in _bootstrap
    self.run()
  File "/Users/maldeiri/raw_data_processing/raw_file_extractor.py", line 109, in run
    "LINES TERMINATED BY '\n';".format(file_location=self.delta_stream_load_file_name))
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/cursors.py", line 146, in execute
    result = self._query(query)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/cursors.py", line 296, in _query
    conn.query(q)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 819, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1001, in _read_query_result
    result.read()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1290, in read
    self._read_load_local_packet(first_packet)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1328, in _read_load_local_packet
    self.connection._read_packet()  # skip ok packet
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 952, in _read_packet
    (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 3 expected 2

If you need any further information please let me know.

Python version: 3.4 PyMYSQL version: 0.7.1 OS: MAC

This is basically identical to https://github.com/PyMySQL/PyMySQL/issues/610, but for a newer Django version.

Django recently bumped the required mysqlclient version to 1.3.13 (https://github.com/django/django/commit/88619e6129fd8c6668242f1acc1465ca175d2948), but pymysql's version string only says it's only compatible with 1.3.12 (https://github.com/PyMySQL/PyMySQL/pull/623)

Any way we can get at least 1.3.13 support? Like the reporter of that other issue, I don't really know the implications of this change, and I can just make mysqlclient work in the interm, but I do prefer pymysql for ease of use on mac. Thanks so much for all your work supporting this library! :)

I'm receiving a lot of the same traps as documented in #234 with v0.6.2.

Traceback (most recent call last):
  File "/home/therenow/flask/local/lib/python2.7/site-packages/gevent/greenlet.py", line 327, in run
    result = self._run(*self.args, **self.kwargs)
  File "backend/monitor.py", line 144, in participation_monitor
    for participation in Participant.query:
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2404, in __iter__
    return self._execute_and_instances(context)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2419, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1111, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 435, in do_execute
    cursor.execute(statement, parameters)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 132, in execute
    result = self._query(query)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/cursors.py", line 271, in _query
    conn.query(q)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 726, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 861, in _read_query_result
    result.read()
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 1064, in read
    first_packet = self.connection._read_packet()
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 825, in _read_packet
    packet = packet_type(self)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 242, in __init__
    self._recv_packet(connection)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 248, in _recv_packet
    packet_header = connection._read_bytes(4)
  File "/home/therenow/flask/local/lib/python2.7/site-packages/pymysql/connections.py", line 832, in _read_bytes
    data = self._rfile.read(num_bytes)
RuntimeError: reentrant call inside <_io.BufferedReader name=36>
<Greenlet at 0x2eaef50: participation_monitor> failed with RuntimeError

The scary thing about this is that the _io module is using PyThread_get_thread_ident to decide whether to throw this exception. I don't suppose that's being monkey patched by gevent and may thus be incompatible with gevent.

Can anyone confirm or deny this?

We updated our docker image from python:3.7-4-slim-stretch to python:3.7.4-slim-buster and this error started happening when connecting to a MariaDB and MySQL databases using PyMySQL as the driver for SQLAlchemy:

2003, "Can't connect to MySQL server on 'mysqlnumetric.ceoxewvigov8.us-east-2.rds.amazonaws.com' ([SSL: UNSUPPORTED_PROTOCOL] unsupported protocol (_ssl.c:1076))

https://github.com/sqlalchemy/sqlalchemy/issues/4937

I've been using PyMySQL 0.6.6 for a while now with no issues. However after updating to 0.7.1, I'm getting this error a lot, and PyMySQL has become completely unusable for me because of it.

  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 146, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 296, in _query
    conn.query(q)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 819, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1001, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 952, in _read_packet
    (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 101 expected 1

The packet numbers vary, but I'm getting this for every query now, no matter how simple. Things will work as expected for a few minutes after starting my script, but soon after these errors will begin appearing and no queries will work anymore.

I'm not sure if there's something I can do to resolve this in my script (some changes made in newer versions that I need to accommodate for) or if I should just figure out how to install the older version as a temporary solution. As it is now, this is a complete showstopper for me.

I use the following code to update a table, however, it is slow, about 10min to update 2w+ rows:

db = pymysql.connect(host=host,
                         user=user,
                         port=port,
                         password=password,
                         database=database
                         )

cursor = db.cursor()

cursor.executemany(
        """
        UPDATE hps 
        SET f_time=%s, 
        modified=%s, 
        q_time=%s, 
        r=%s, 
        c_lost=%s, 
        s_lost=%s, 
        lr_error=%s, 
        jr_error=%s, 
        c_time=%s, 
        u_time=%s, 
        s_time=%s, 
        c_start_time=%s, 
        c_end_time=%s, 
        sl_time=%s, 
        sj_time=%s, 
        s_flag=%s
        WHERE h=%s;
        """, args)

h is the primary key, and f_time is datetime. Is there any method to speed up this code?

Hi All,

This code is producing an SSL version error:

connection = pymysql.connect(host="someplace.com",
                             user='username',
                             password='password',
                             database='dbname',
                             port=3306,
                             cursorclass=pymysql.cursors.DictCursor,
                             ssl={"verify_mode": None})

OperationalError: (2003, "Can't connect to MySQL server on 'someplace.com' ([SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:997))")

I do have a .pem file, but it's not clear to me how to use it (I've tried various permutations).

Any suggestions? Thanks!

Greetings!

I don't want to submit a bug/issue. I just want to thank you 🥇PyMySQL🥇 team (all members/contributors). Thanks for developing this open source project.

have great times in your life & works.

I'm sorry for opening this type of issue 👍🥇

I'm creating this PR more as a start to a conversation as opposed to a request that I expect to be accepted any time soon. We like being able to use PyMySQL because of the ease of installation and the fact that it can be used without any system-level libraries being installed. However, the performance leaves a lot to be desired when dealing with larger result sets.

After some investigation, it looked like most of the time was being spent in the row data fetching and data conversion, which all stems from one method in the connection (`read_rowdata_packet'). An idea came to mind to replace that method (and anything that it calls) with a C extension to see how much performance could be increased. It ended up working much better than expected and the performance improvements actually made the client (arguably) the fastest MySQL Python client available (arguably because the Mariadb client is pretty fast too). It is definitely the fastest Python client that doesn't require any external libraries to run.

I thought I'd bring the work up to your team to see if you might want to collaborate on it as a possible plugin for PyMySQL, or to see if it should remain a separate project.

In #774 automatic warnings were removed. As far as I can tell there is currently no public API to access cursor._result.warning_count.

This provides a way to check for existence of warnings without having to perform an additional query over the network.

PyMySQL should aim for mypy compliance to help users with static type checking, and provide better IDE support.

Adding types should be pretty straightforward, and stubs are already available on typeshed (https://github.com/python/typeshed/tree/master/stubs/PyMySQL) so a lot of that could likely be ported.

Would a PR be accepted?