MySQL database connector for Python (with Python 3 support)

Overview

mysqlclient

This project is a fork of MySQLdb1. This project adds Python 3 support and fixed many bugs.

Support

Do Not use Github Issue Tracker to ask help. OSS Maintainer is not free tech support

When your question looks relating to Python rather than MySQL:

Or when you have question about MySQL:

Install

Windows

Building mysqlclient on Windows is very hard. But there are some binary wheels you can install easily.

macOS (Homebrew)

Install MySQL and mysqlclient:

# Assume you are activating Python 3 venv
$ brew install mysql
$ pip install mysqlclient

If you don't want to install MySQL server, you can use mysql-client instead:

# Assume you are activating Python 3 venv
$ brew install mysql-client
$ echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.bash_profile
$ export PATH="/usr/local/opt/mysql-client/bin:$PATH"
$ pip install mysqlclient

Linux

Note that this is a basic step. I can not support complete step for build for all environment. If you can see some error, you should fix it by yourself, or ask for support in some user forum. Don't file a issue on the issue tracker.

You may need to install the Python 3 and MySQL development headers and libraries like so:

  • $ sudo apt-get install python3-dev default-libmysqlclient-dev build-essential # Debian / Ubuntu
  • % sudo yum install python3-devel mysql-devel # Red Hat / CentOS

Then you can install mysqlclient via pip now:

$ pip install mysqlclient

Customize build (POSIX)

mysqlclient uses mysql_config or mariadb_config by default for finding compiler/linker flags.

You can use MYSQLCLIENT_CFLAGS and MYSQLCLIENT_LDFLAGS environment variables to customize compiler/linker options.

$ export MYSQLCLIENT_CFLAGS=`pkg-config mysqlclient --cflags`
$ export MYSQLCLIENT_LDFLAGS=`pkg-config mysqlclient --libs`
$ pip install mysqlclient

Documentation

Documentation is hosted on Read The Docs

Comments
  • Change the import name from MySQLdb to mysqlclient to avoid conflicts, or taking over

    Change the import name from MySQLdb to mysqlclient to avoid conflicts, or taking over "MySQL-python" on PyPI

    It is destabilizing to expect that a particular application that wishes to make use of "mysqlclient" under Python 2K must therefore in one step use mysqlclient for all applications that share the same virtual or actual Python environment. it should be be possible for some applications to use the traditional MySQLdb and for others to use mysqlclient.

    Other DBAPIs that began as forks or ports of others have followed this practice, e.g. fdb is a port of kinterbasdb, psycopg2cffi is a pypy port of psycopg2, etc. It only creates confusion and reduces options when two totally different codebases share the exact same module name. Especially on Linux distributions it means that two packages will necessarily overwrite each other and be in conflict.

    opened by zzzeek 83
  • Not working with python 3.5

    Not working with python 3.5

    This is the error I get when I try installing mysqlclient. I am using Windows and python 3.5.

    Installing 'mysqlclient'
    Collecting mysqlclient
      Using cached mysqlclient-1.3.6.tar.gz
    Installing collected packages: mysqlclient
      Running setup.py install for mysqlclient
        Complete output from command "c:\users\buccaneer\documents\visual studio            2013\Projects\del1\del1\envrm\Scripts\python.exe" -c "import setuptools, tokenize;__file__='C:\\Users\\BUCCAN~1\\AppData\\Local\\Temp\\pip-build-wuhkxhu3\\mysqlclient\\setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record C:\Users\BUCCAN~1\AppData\Local\Temp\pip-3jbluuk2-record\install-record.txt --single-version-externally-managed --compile --install-headers "c:\users\buccaneer\documents\visual studio 2013\Projects\del1\del1\envrm\include\site\python3.5\mysqlclient":
    running install
    running build
    running build_py
    creating build
    creating build\lib.win32-3.5
    copying _mysql_exceptions.py -> build\lib.win32-3.5
    creating build\lib.win32-3.5\MySQLdb
    copying MySQLdb\__init__.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\compat.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\converters.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\connections.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\cursors.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\release.py -> build\lib.win32-3.5\MySQLdb
    copying MySQLdb\times.py -> build\lib.win32-3.5\MySQLdb
    creating build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\__init__.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\CR.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\FIELD_TYPE.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\ER.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\FLAG.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\REFRESH.py -> build\lib.win32-3.5\MySQLdb\constants
    copying MySQLdb\constants\CLIENT.py -> build\lib.win32-3.5\MySQLdb\constants
    running build_ext
    building '_mysql' extension
    error: Unable to find vcvarsall.bat
    
    opened by piyushspss 65
  • Regression in bulk insert behavior

    Regression in bulk insert behavior

    The following query should be able to be executed in bulk:

    INSERT INTO users (id, name) VALUES (1, 'foo'), (2, 'bar') /* insert multiple users in one query */
    

    With mysqlclient 1.3.6, this query is emitted as a bulk insert.

    I upgraded mysqclient to 1.3.9, and suddenly, this query is emitted as two individual inserts.

    opened by ajm188 33
  • Use _binary prefix for bytes/bytearray parameters

    Use _binary prefix for bytes/bytearray parameters

    PyMySQL already does this via escape_bytes() converters.py. For compatibility maybe this is a good idea? (I think this would behave the same as with PyMySQL in that nothing it only affects bytes~~/bytearray~~ + Python 3 and bytearray for both versions.)

    I think it's not possible to add to Connection.encoders (via constructor) because e.g. self.encoders[bytes] is overridden after the conv argument is parsed.

    opened by vtermanis 27
  • pip3 install mysqlclient fails on macOS

    pip3 install mysqlclient fails on macOS

    I am unable to install mysqlclient on macOS anymore via pip.

    I'm on latest macOS, Python 3.6.1 and pip 9.0.1, within virtualenv.

    The installation ends with:

    Collecting mysqlclient
      Using cached mysqlclient-1.3.10.tar.gz
        Complete output from command python setup.py egg_info:
        Traceback (most recent call last):
          File "<string>", line 1, in <module>
          File "/private/var/folders/rv/a_a/T/pip-build-nyaa8t95/mysqlclient/setup.py", line 17, in <module>
            metadata, options = get_config()
          File "/private/var/folders/rv/a_a/T/pip-build-nyaa8t95/mysqlclient/setup_posix.py", line 54, in get_config
            libraries = [dequote(i[2:]) for i in libs if i.startswith('-l')]
          File "/private/var/folders/rv/a_a/T/pip-build-nyaa8t95/mysqlclient/setup_posix.py", line 54, in <listcomp>
            libraries = [dequote(i[2:]) for i in libs if i.startswith('-l')]
          File "/private/var/folders/rv/a_a/T/pip-build-nyaa8t95/mysqlclient/setup_posix.py", line 12, in dequote
            if s[0] in "\"'" and s[0] == s[-1]:
        IndexError: string index out of range
    
        ----------------------------------------
    Command "python setup.py egg_info" failed with error code 1 in /private/var/folders/rv/a_a/T/pip-build-nyaa8t95/mysqlclient/
    

    Result is the same with locally installed MySQL, or with the light-weight option brew install mysql-connector-c.

    Similar issues have been posted to Stack Overflow by several people. Any ideas appreciated.

    opened by janik6n 26
  • Can't install mysqlclient on MacOS

    Can't install mysqlclient on MacOS

    Hi, I'm on MacOSX Sierra, and I can't install mysqlclient in a virtualenv with Python 3.5.2:

    Failed building wheel for mysqlclient
    Command "/Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-931sqj34-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/../include/site/python3.5/mysqlclient" failed with error code 1 in /private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/
    

    Running pip install -v mysqlclient I'm getting this:

    Collecting mysqlclient
      1 location(s) to search for versions of mysqlclient:
      * https://pypi.python.org/simple/mysqlclient/
      Getting page https://pypi.python.org/simple/mysqlclient/
      Looking up "https://pypi.python.org/simple/mysqlclient/" in the cache
      Current age based on date: 382
      Freshness lifetime from max-age: 600
      Freshness lifetime from request max-age: 600
      The response is "fresh", returning cached response
      600 > 382
      Analyzing links from page https://pypi.python.org/simple/mysqlclient/
        Found link https://pypi.python.org/packages/09/29/6648563af4b45798ba667f17ea55166d7b0ed8717937c06977a1fdbe7df6/mysqlclient-1.3.6.tar.gz#md5=58d7c9c617a4286a88db290e7857d3aa (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.6
        Found link https://pypi.python.org/packages/17/d9/03b08e6a033401335b720806b87596aee8d7f7aa87539713238905c4a8d1/mysqlclient-1.3.4.tar.gz#md5=dada1730c92e2b7b27c61cba6bf99c30 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.4
        Found link https://pypi.python.org/packages/26/53/c575db342bfca0213c9243ed2642bfadcd420b18d2f477dc812543e3d83a/mysqlclient-1.3.3.tar.gz#md5=dc61ce0c49bab96a8bc8d0d0272760b6 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.3
        Skipping link https://pypi.python.org/packages/29/f7/f842aa57a8c0d4a3627917028f5504225dd629dd38bc05ad379be4d99f53/mysqlclient-1.3.6-cp34-none-win32.whl#md5=6fff5edd9ebf0bd99afdf0de30cf72af (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Found link https://pypi.python.org/packages/2d/df/5440ee86bbb248325cdd6e1fc9cbbba365018a0d2d57f673610e020e6b1d/mysqlclient-1.3.2.tar.gz#md5=f07292f9803b3906aaedd36e8f365b24 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.2
        Found link https://pypi.python.org/packages/31/ce/7ea049b3d5929b3cce3104967ccca218a9f054517f0d15dfdaf4e76da2a0/mysqlclient-1.3.8.tar.gz#md5=118bcf8473a341766e1f69f97e523a31 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.8
        Skipping link https://pypi.python.org/packages/3d/70/cc949d4b4c059238947c3ed0bbf6432cf88176c0a1916a589489f8c7a58f/mysqlclient-1.3.4-cp34-none-win_amd64.whl#md5=84810d8759a523150cebc7757d5879b2 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/55/10/d3c4eff2d9bfa6123425c869121a11bc1ba5129a031a03d578af42727067/mysqlclient-1.3.3-cp27-none-win32.whl#md5=9e159db34a62a67cced6305d06c90dd9 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/56/40/abaa1574d5deca0018adc2f2e35a7eaa32cc5a65b8ce50bef42d70a34476/mysqlclient-1.3.6-cp27-none-win32.whl#md5=7f069de3beaa7d765fe12f0af01bd05d (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/5b/09/659d886ce9a4a4c1e8a972e1ca962a40cefea964ffb57faf5ca8148e9741/mysqlclient-1.3.4-cp34-none-win32.whl#md5=5e07263774deb3432b20ba79d4e78bee (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/5f/87/c97e0486e080d3bb5cf4f28e3f405eae0f5d0b05e7e890bf92da4e853b1a/mysqlclient-1.3.4-cp27-none-win32.whl#md5=44b8f769939f432161ac1cf3fa03d325 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Found link https://pypi.python.org/packages/6a/91/bdfe808fb5dc99a5f65833b370818161b77ef6d1e19b488e4c146ab615aa/mysqlclient-1.3.0.tar.gz#md5=bc078ccb7bc5f3668ed452a8df0d3e0a (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.0
        Found link https://pypi.python.org/packages/6b/ba/4729d99e85a0a35bb46d55500570de05b4af10431cef174b6da9f58a0e50/mysqlclient-1.3.1.tar.gz#md5=1e010a945369daea60060a80feb8ee94 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.1
        Skipping link https://pypi.python.org/packages/72/0d/5f5e4a8c03f9ee0435e97586b49158220d81bdc7381f4052b763013bd3af/mysqlclient-1.3.9-cp27-cp27m-win32.whl#md5=6baa90596c2a179d50e035e627b4be48 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Found link https://pypi.python.org/packages/74/ff/4e964e20b559e55d7afa60fbccc6a560f2adf289813bd3d7eb4eb8a87093/mysqlclient-1.3.7.tar.gz#md5=2ec5a96cbd9fd6ef343fa9b581a67fc4 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.7
        Skipping link https://pypi.python.org/packages/76/c6/07ea4013b7b6fa8814b0408a762f6698488c5c388517e0d6078eb66cc0a1/mysqlclient-1.3.6-cp27-none-win_amd64.whl#md5=0265f98c651eb41e967cb53898b50fb9 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/7b/95/b84387abe50cc9f5cccab962ed85a963bd9ce28f991fded635abaa9dfd4a/mysqlclient-1.3.1-cp27-none-win32.whl#md5=eff814f574b2aa5f8a0b46e3e427b647 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Found link https://pypi.python.org/packages/82/ea/336daf6c7baa564d0c434ab748218d1a7dc61dfc7066e98b72afd24fd2b9/mysqlclient-1.3.5.tar.gz#md5=19c1d772df40fb5a955ce4c139a91602 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.5
        Skipping link https://pypi.python.org/packages/8b/3b/17a0ec31a4963b0c965d193063522bcefc2b26d835db7fa2885a19e73cc2/mysqlclient-1.3.9-cp27-cp27m-win_amd64.whl#md5=de42308aea4879eddfd618bb1e95e806 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/a5/2b/8621632a6b165c65e1388125f2bd009c35bbef1ae7dd82d2333a51031e1d/mysqlclient-1.3.3-cp34-none-win32.whl#md5=613ae92a4660d7dcb1fb119d514e2571 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/a8/b9/f1fb3e689de24fcc69a625991d25a65adcdf8776f58433c2fdcd3b202b4f/mysqlclient-1.3.6-cp34-none-win_amd64.whl#md5=70587d8824e29eeb5ffe9a242b9563ed (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/aa/6c/2b908e9fdc4f4ed1c243534ce84c17c4f074b103ecc68ad9b5666885ee4d/mysqlclient-1.3.1-cp34-none-win32.whl#md5=ca5b403901dbbf34c669a78d5ab31380 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/b6/20/b8843b47166cbda23564f44ac710975be7e3fb8eb5312363af2929834e40/mysqlclient-1.3.3-cp34-none-win_amd64.whl#md5=15bec44e9ec1c9ae7e17d1e64647c92d (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/bf/a3/812055bfebdb4555b23476e99beb9fac09def49a8eb403771ecd0bac6aa6/mysqlclient-1.3.7-cp27-none-win32.whl#md5=e9e726fd6f1912af78e2bf6ab56c02f3 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/c0/0d/85b17ada02b7a52bf2855b1b88b02bf239d9b63ada6a6f0fd12523f41325/mysqlclient-1.3.7-cp27-none-win_amd64.whl#md5=cb60cd9b973d2eb96d47b8192c1c6727 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/cb/aa/bfb6b542a60c72df5a1b4fcab9d0376956fd549f6a5f55b39bf54ea888bb/mysqlclient-1.3.3-cp27-none-win_amd64.whl#md5=f220d1f0ac3b60932c14cc1c46c90834 (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Found link https://pypi.python.org/packages/db/f5/c8e1657985c31dda16e434edf5257c31572fa5faacd7e48b1618390e4b18/mysqlclient-1.3.9.tar.gz#md5=a9df2961f4664bfd4b9dac50d7b28048 (from https://pypi.python.org/simple/mysqlclient/), version: 1.3.9
        Skipping link https://pypi.python.org/packages/e4/68/023b559ca42cc1194686d4d1e8b1170f392344dd06496fd06058af144dbf/mysqlclient-1.3.1-cp27-none-win_amd64.whl#md5=daf338b9034add51b9e5eb0d5e7386cb (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/e9/05/f25e15bb060c61675dd6c98015ca8de10fed0b98394d0e779ead90c905e4/mysqlclient-1.3.4-cp27-none-win_amd64.whl#md5=128f08713e31fe62524e5269835ee40e (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
        Skipping link https://pypi.python.org/packages/fd/e3/502eb5758593206f9d270208564f4c583fb631155e122792d4b2c462e63e/mysqlclient-1.3.1-cp34-none-win_amd64.whl#md5=8e391e7fc39ce9f45dd1a46e6a06145f (from https://pypi.python.org/simple/mysqlclient/); it is not compatible with this Python
      Using version 1.3.9 (newest of versions: 1.3.0, 1.3.1, 1.3.2, 1.3.3, 1.3.4, 1.3.5, 1.3.6, 1.3.7, 1.3.8, 1.3.9)
      Looking up "https://pypi.python.org/packages/db/f5/c8e1657985c31dda16e434edf5257c31572fa5faacd7e48b1618390e4b18/mysqlclient-1.3.9.tar.gz" in the cache
      Current age based on date: 1552
      Freshness lifetime from max-age: 31557600
      The response is "fresh", returning cached response
      31557600 > 1552
      Using cached mysqlclient-1.3.9.tar.gz
      Downloading from URL https://pypi.python.org/packages/db/f5/c8e1657985c31dda16e434edf5257c31572fa5faacd7e48b1618390e4b18/mysqlclient-1.3.9.tar.gz#md5=a9df2961f4664bfd4b9dac50d7b28048 (from https://pypi.python.org/simple/mysqlclient/)
      Running setup.py (path:/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py) egg_info for package mysqlclient
        Running command python setup.py egg_info
        running egg_info
        creating pip-egg-info/mysqlclient.egg-info
        writing pip-egg-info/mysqlclient.egg-info/PKG-INFO
        writing dependency_links to pip-egg-info/mysqlclient.egg-info/dependency_links.txt
        writing top-level names to pip-egg-info/mysqlclient.egg-info/top_level.txt
        writing manifest file 'pip-egg-info/mysqlclient.egg-info/SOURCES.txt'
        warning: manifest_maker: standard file '-c' not found
    
        reading manifest file 'pip-egg-info/mysqlclient.egg-info/SOURCES.txt'
        reading manifest template 'MANIFEST.in'
        warning: no files found matching 'GPL-2.0'
        writing manifest file 'pip-egg-info/mysqlclient.egg-info/SOURCES.txt'
      Source in /private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient has version 1.3.9, which satisfies requirement mysqlclient from https://pypi.python.org/packages/db/f5/c8e1657985c31dda16e434edf5257c31572fa5faacd7e48b1618390e4b18/mysqlclient-1.3.9.tar.gz#md5=a9df2961f4664bfd4b9dac50d7b28048
    Building wheels for collected packages: mysqlclient
      Running setup.py bdist_wheel for mysqlclient: started
      Destination directory: /var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/tmppesztx3npip-wheel-
      Running command /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" bdist_wheel -d /var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/tmppesztx3npip-wheel- --python-tag cp35
      running bdist_wheel
      running build
      running build_py
      creating build
      creating build/lib.macosx-10.11-x86_64-3.5
      copying _mysql_exceptions.py -> build/lib.macosx-10.11-x86_64-3.5
      creating build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/__init__.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/compat.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/converters.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/connections.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/cursors.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/release.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      copying MySQLdb/times.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
      creating build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/__init__.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/CR.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/ER.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/FLAG.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/REFRESH.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      copying MySQLdb/constants/CLIENT.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
      running build_ext
      building '_mysql' extension
      creating build/temp.macosx-10.11-x86_64-3.5
      clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -Dversion_info=(1,3,9,'final',1) -D__version__=1.3.9 -I/usr/local/Cellar/mysql/5.7.16/include/mysql -I/usr/local/Cellar/python3/3.5.2_3/Frameworks/Python.framework/Versions/3.5/include/python3.5m -c _mysql.c -o build/temp.macosx-10.11-x86_64-3.5/_mysql.o -fno-omit-frame-pointer
      clang -bundle -undefined dynamic_lookup build/temp.macosx-10.11-x86_64-3.5/_mysql.o -L/usr/local/Cellar/mysql/5.7.16/lib -lmysqlclient -lssl -lcrypto -o build/lib.macosx-10.11-x86_64-3.5/_mysql.cpython-35m-darwin.so
      ld: library not found for -lssl
      clang: error: linker command failed with exit code 1 (use -v to see invocation)
      error: command 'clang' failed with exit status 1
      Running setup.py bdist_wheel for mysqlclient: finished with status 'error'
      Running setup.py clean for mysqlclient
      Running command /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" clean --all
      running clean
      removing 'build/temp.macosx-10.11-x86_64-3.5' (and everything under it)
      removing 'build/lib.macosx-10.11-x86_64-3.5' (and everything under it)
      'build/bdist.macosx-10.11-x86_64' does not exist -- can't clean it
      'build/scripts-3.5' does not exist -- can't clean it
      removing 'build'
    Failed to build mysqlclient
    Installing collected packages: mysqlclient
      Running setup.py install for mysqlclient: started
        Running command /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-931sqj34-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/../include/site/python3.5/mysqlclient
        running install
        running build
        running build_py
        creating build
        creating build/lib.macosx-10.11-x86_64-3.5
        copying _mysql_exceptions.py -> build/lib.macosx-10.11-x86_64-3.5
        creating build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/__init__.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/compat.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/converters.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/connections.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/cursors.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/release.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        copying MySQLdb/times.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb
        creating build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/__init__.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/CR.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/ER.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/FLAG.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/REFRESH.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        copying MySQLdb/constants/CLIENT.py -> build/lib.macosx-10.11-x86_64-3.5/MySQLdb/constants
        running build_ext
        building '_mysql' extension
        creating build/temp.macosx-10.11-x86_64-3.5
        clang -Wno-unused-result -Wsign-compare -Wunreachable-code -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -Dversion_info=(1,3,9,'final',1) -D__version__=1.3.9 -I/usr/local/Cellar/mysql/5.7.16/include/mysql -I/usr/local/Cellar/python3/3.5.2_3/Frameworks/Python.framework/Versions/3.5/include/python3.5m -c _mysql.c -o build/temp.macosx-10.11-x86_64-3.5/_mysql.o -fno-omit-frame-pointer
        clang -bundle -undefined dynamic_lookup build/temp.macosx-10.11-x86_64-3.5/_mysql.o -L/usr/local/Cellar/mysql/5.7.16/lib -lmysqlclient -lssl -lcrypto -o build/lib.macosx-10.11-x86_64-3.5/_mysql.cpython-35m-darwin.so
        ld: library not found for -lssl
        clang: error: linker command failed with exit code 1 (use -v to see invocation)
        error: command 'clang' failed with exit status 1
        Running setup.py install for mysqlclient: finished with status 'error'
    Cleaning up...
      Removing source in /private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient
    Exception information:
    Traceback (most recent call last):
      File "/Users/antares/Documents/codefu/devel/web.hosting/.venv/lib/python3.5/site-packages/pip/basecommand.py", line 215, in main
        status = self.run(options, args)
      File "/Users/antares/Documents/codefu/devel/web.hosting/.venv/lib/python3.5/site-packages/pip/commands/install.py", line 317, in run
        prefix=options.prefix_path,
      File "/Users/antares/Documents/codefu/devel/web.hosting/.venv/lib/python3.5/site-packages/pip/req/req_set.py", line 742, in install
        **kwargs
      File "/Users/antares/Documents/codefu/devel/web.hosting/.venv/lib/python3.5/site-packages/pip/req/req_install.py", line 880, in install
        spinner=spinner,
      File "/Users/antares/Documents/codefu/devel/web.hosting/.venv/lib/python3.5/site-packages/pip/utils/__init__.py", line 718, in call_subprocess
        % (command_desc, proc.returncode, cwd))
    pip.exceptions.InstallationError: Command "/Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-931sqj34-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/antares/Documents/codefu/devel/web.hosting/.venv/bin/../include/site/python3.5/mysqlclient" failed with error code 1 in /private/var/folders/dg/5dm8hwrn33g76sjw1653dcdm0000gn/T/pip-build-m1tl5912/mysqlclient/
    

    Is there any dependency I'm missing?

    opened by zzantares 25
  • Macos M1 mysqlclient Symbol not found: _mysql_affected_rows ERROR

    Macos M1 mysqlclient Symbol not found: _mysql_affected_rows ERROR

    Hello I'm trying to install mysqlclient with this command pip install mysqlclient but i'm having this errors mysql : 8.0.2 python : 3.7.10 Mac: OS version: 11.2.3 M1

    ImportError: dlopen(/Users/shiwei/insight_r2_backend/env3/lib/python3.7/site-packages/MySQLdb/_mysql.cpython-37m-darwin.so, 2): Symbol not found: _mysql_affected_rows Referenced from: /Users/shiwei/insight_r2_backend/env3/lib/python3.7/site-packages/MySQLdb/_mysql.cpython-37m-darwin.so Expected in: flat namespace

    I set LD_LIBRARY_PATH=/opt/homebrew/lib

    opened by kouyalong 21
  • undefined symbol: mysql_real_escape_string_quote

    undefined symbol: mysql_real_escape_string_quote

    I am getting an error:

    ImportError: /opt/conda/envs/_test/lib/python3.5/site-packages/_mysql.cpython-35m-x86_64-linux-gnu.so: undefined symbol: mysql_real_escape_string_quote
    

    When trying to build a conda package with mysqlclient master branch. I think this is caused by commit #109?

    opened by ostrokach 20
  • IntegrityError raised twice; from cursor.execute() and  cursor.close()

    IntegrityError raised twice; from cursor.execute() and cursor.close()

    I'm trying this only on version 1.3.7 because i'm unable to run newer version (getting 'undefined symbol: OPENSSL_add_all_algorithms_noconf' after import, MariaDB 10.1), one i'm using was installed as original package from Debian 9.

    When using SSCursor or SSDictCursor, i'm getting IntegrityError on 'execute' command AND the same IntegrityError error also on cursor close(). 100% reproducable, also see few comments in the code:

    import MySQLdb
    
    db_conn = MySQLdb.connect(host="localhost", user="user", passwd="password", db="database")
    db_conn.autocommit(True)
    db_curr = db_conn.cursor(MySQLdb.cursors.SSDictCursor)
    # uncoment this to 'fix' the error
    #db_curr = db_conn.cursor()
    try:
            db_curr.execute("CREATE TABLE `test` (test VARCHAR(255) UNIQUE) ENGINE=InnoDB")
    except:
            pass
    # this is interesting, the error only appears if there is any SELECT before
    db_curr.execute("SELECT * FROM `test`")
    mysql_result = db_curr.fetchall()
    try:
            db_curr.execute("INSERT INTO `test` SET test='test'")
            db_curr.execute("INSERT INTO `test` SET test='test'")
    except MySQLdb.IntegrityError:
            print "catched IntegrityError"
    db_curr.close()
    db_conn.close()
    

    Output:

    catched IntegrityError
    Traceback (most recent call last):
      File "test_db.py", line 19, in <module>
        db_curr.close()
      File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 91, in close
        while self.nextset():
      File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 138, in nextset
        self.fetchall()
      File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 474, in fetchall
        r = self._fetch_row(0)
      File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 351, in _fetch_row
        return self._result.fetch_row(size, self._fetch_type)
    _mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'test' for key 'test'")
    
    opened by azurit 18
  • False positive with `ON DUPLICATE` in `executemany`

    False positive with `ON DUPLICATE` in `executemany`

    This allows any text after ON DUPLICATE: https://github.com/PyMySQL/mysqlclient-python/blob/f96cb44ef18007a33d1400ff98ce9b887cd983d3/MySQLdb/cursors.py#L31

    Here you claim it is not broken: https://github.com/PyMySQL/mysqlclient-python/issues/163#issuecomment-298204035

    opened by asottile 16
  • MySQLdb.converters is inconsistent about returning bytes or str in Python 3

    MySQLdb.converters is inconsistent about returning bytes or str in Python 3

    The MySQLdb.converters module mostly returns strs in Python 3 (which I think is correct), but it ends up calling into _mysql.string_literal in some cases, which returns bytes instead. When escaping tuples this can lead to TypeErrors:

    In [35]: sys.version
    Out[35]: '3.6.0 (default, Dec 26 2016, 07:53:45) \n[GCC 6.2.0 20160901]'
    
    In [36]: MySQLdb.version_info
    Out[36]: (1, 3, 9, 'final', 1)
    
    In [37]: MySQLdb.escape(('a',), MySQLdb.converters.conversions)
    
    
    
    Traceback:
      File "/home/jelle/ans/venv64/lib/python3.6/site-packages/IPython/core/interactiveshell.py", line 2881, in run_code
        exec(code_obj, self.user_global_ns, self.user_ns)
      File "<ipython-input-37-d4b2dd1db689>", line 1, in <module>
        MySQLdb.escape(('a',), MySQLdb.converters.conversions)
      File "/home/jelle/ans/venv64/lib/python3.6/site-packages/MySQLdb/converters.py", line 90, in quote_tuple
        return "(%s)" % (','.join(escape_sequence(t, d)))
    
    TypeError: sequence item 0: expected str instance, bytes found
    
    opened by JelleZijlstra 16
  • RE_INSERT_VALUES matches values starts with % and ends with s

    RE_INSERT_VALUES matches values starts with % and ends with s

    Describe the bug

    The regex pattern RE_INSERT_VALUES defined and used in cursors.py is wrong.

    RE_INSERT_VALUES = re.compile(
        "".join(
            [
                r"\s*((?:INSERT|REPLACE)\b.+\bVALUES?\s*)",
                r"(\(\s*(?:%s|%\(.+\)s)\s*(?:,\s*(?:%s|%\(.+\)s)\s*)*\))",
                r"(\s*(?:ON DUPLICATE.*)?);?\s*\Z",
            ]
        ),
        re.IGNORECASE | re.DOTALL,
    )
    

    %\(.+\)s in the value part: r"(\(\s*(?:%s|%\(.+\)s)\s*(?:,\s*(?:%s|%\(.+\)s)\s*)*\))" allows values starts with % and ends with s.

    Theses wrong query text (which you don't want?) will be matched:

    INSERT INTO xxx(a, b, c) VALUES (%(xxx)s, NOW(), %(ooo)s) INSERT INTO xxx(a, b, c) VALUES (%(xxx)s, abc124*(!@*$!@)$&)&$)s)

    I think what you want is all %s in values, or all %(xxx)s-liked in values.
    However, I don't know why you only want to allow these patterns, since this string-formatting will also works: "NOW(), %(a)s, %(b)s, NOW + INTERVAL 1 MINUTE" % {"a": 100, "b": 3}

    Why cant the values part in RE_INSERT_VALUES become: r"(\(.+\))" ?
    Developers using function executemany should check whether the args and the query text are match or not.
    With r"(\(.+\))" values part, users can use SQL functions, numbers, strings, ... etc in query text, which helps developers a lot.


    One more thing is: users don't know this regex rule until they meet some issue, you should explain how to use executemany in the document, something like:

    `executemany` only support `INSERT INTO` or `REPLACE` statments
    The format of VALUES in query text should be all %s or all named string-formatting, for example: `(%s, %s, %s)` or `(%(a)s, %(b)s, %(c)s)`, .
    If your query text is not matches the rules above, it will use `execute` function with looping args instead.
    

    To Reproduce

    Code

    con = MySQLdb.connect(...)
    cur = con.cursor()
    query_text_1 = """INSERT INTO xxx(a, b, c) VALUES (%(xxx)s, NOW(), %(b)s)"""
    query_text_2 = """INSERT INTO xxx(a, b, c) VALUES (NOW(), %(xxx)s, %(b)s)"""
    args = {"a": 1, "b": 2}
    cur.executemany(con, query_text_1, args) # works
    cur.executemany(con, query_text_2, args) # NOT works
    

    Environment

    MySQL Server

    MySQL 5.7

    MySQL Client

    • OS (e.g. Windows 10, Ubuntu 20.04): macOS 12.5.1

    • Python (e.g. Homebrew Python 3.7.5): python install with pyenv: 3.7, 3.8, 3.10

    • Connector/C (e.g. Homebrew mysql-client 8.0.18): mysql-client

    opened by Griiid 0
  • Add collate option

    Add collate option

    Fixes #563

    This change does not affect existing behavior if the collate option is not specified.

    To test this out locally, e.g. in a Django project:

    • Make sure you have MySQL or MariaDB C connector, and a C compiler installed.
    • pip install git+https://github.com/vsalvino/mysqlclient.git
    • Set your database connection as so:
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.mysql",
            "HOST": "",
            "NAME": "",
            "USER": "",
            "PASSWORD": "",
            "OPTIONS": {
                "charset": "utf8mb4",
                "collate": "utf8mb4_unicode_ci",
            },
        }
    }
    
    opened by vsalvino 2
  • Impossible to set correct collation

    Impossible to set correct collation

    Describe the bug

    Use case: the database is configured with a character set, and a non-default collation for that character set.

    [mysqld]
    character_set_server=utf8mb4
    collation_server=utf8mb4_unicode_ci
    

    For reference, note that on this particular installation (MariaDB 10.3), the default collation for utf8mb4 is utf8mb4_general_ci, NOT utf8mb4_unicode_ci.

    The bug in this case is that when using mysqlclient, it is IMPOSSIBLE to set the correct collation. There is an option charset, which whether it is provided, blank, or empty, issues a command as such:

    SET NAME <charset>
    

    SET NAME will ALWAYS use the default collation for the charset if the collation is not specified, which is documented here: https://mariadb.com/kb/en/set-names/, and much more deeply documented here: https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html.

    In this case we are specifying charset="utf8mb4". The command is being generated is:

    SET NAMES utf8mb4
    

    When COLLATION is omitted, this uses the default collation for utf8mb4 which utf8mb4_general_ci. This is hard-coded into MariaDB and cannot be changed: https://mariadb.com/kb/en/change-is_default-value-for-information_schemacollations/

    The problem is that certain SQL queries, such as a CAST as generated by the Django Cast function, will infer the connection's collation, and can fail if the connection's collation does not match the table:

    (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='")
    

    The simple documented solution is to also specify the collation:

    SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
    

    I have tried several workarounds to set the collation, which currently fail, because set_character_set is always run AFTER the workaround, therefore negating it.

    Workaround 1

    Use the init_command option to send SET NAME utf8mb4 COLLATE utf8mb4_unicode_ci after connecting. Unfortunately, from examining the query log, charset is set AFTER init_command, therefore negating it. The query log looks as such:

    31 Query    SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci  -- run by init_command
    31 Query    SET NAMES utf8mb4  -- from mysqlclient
    

    Workaround 2

    Use the init_connect option on the MySQL server. Unfortunately, the story is the same, as the charset is issued after the init_connect script is run, therefore negating it.

    32 Query    SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci  -- run by the server
    32 Query    SET NAMES utf8mb4  -- from mysqlclient
    

    Possible fixes

    Fix 1: Do not set charset if empty.

    If charset is empty, or possibly None, do not set it. This way, it does not override the server's default behavior or the init_command https://github.com/PyMySQL/mysqlclient/blob/5c04abf87d32a3254dd481c03740a8c56520bc3a/MySQLdb/connections.py#L195-L197

    It seems this behavior (which is the source of my bug) was introduce in 2.1: https://github.com/PyMySQL/mysqlclient/pull/509

    Fix 2: Add a collation option.

    This deviates from the MySQL C connector behavior, but it would be really nice to be able to specify both "charset" and "collation". Special implementation in Python might be required for this, e.g. to issue a SQL command after connecting.

    Environment

    MySQL Server

    • MariaDB (Debian 11, Windows 10)
    • MariaDB 10.3.35

    MySQL Client

    • Debian, Windows

    • Python tested on multiple versions: 3.7+

    opened by vsalvino 0
  • mysqlclient returning wrong lastrowid when calling insert with auto_increment with negative value

    mysqlclient returning wrong lastrowid when calling insert with auto_increment with negative value

    Describe the bug

    lastrowid returns 18446744073709551615 instead of -1 or 0 (in case auto increment is not used). https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-lastrowid.html

    https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html

    The return value of [mysql_insert_id()](https://dev.mysql.com/doc/c-api/5.7/en/mysql-insert-id.html) is always zero unless explicitly updated under one of the following conditions:
    
    [INSERT](https://dev.mysql.com/doc/refman/5.7/en/insert.html) statements that store a value into an AUTO_INCREMENT column. This is true whether the value is automatically generated by storing the special values NULL or 0 into the column, or is an explicit nonspecial value.
    

    To Reproduce

    Schema

    CREATE TABLE `auth_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(150) NOT NULL,
      `first_name` varchar(150) NOT NULL,
      `last_name` varchar(150) NOT NULL,
      `email` varchar(254) NOT NULL,
      `password` varchar(128) NOT NULL,
      `is_staff` tinyint(1) NOT NULL,
      `is_active` tinyint(1) NOT NULL,
      `is_superuser` tinyint(1) NOT NULL,
      `last_login` datetime DEFAULT NULL,
      `date_joined` datetime NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `username` (`username`),
      KEY `email` (`email`),
      KEY `last_name_and_frist_name` (`last_name`,`first_name`),
      FULLTEXT KEY `auth_user_ft_first_last_email_user` (`first_name`,`last_name`,`email`,`username`)
    ) ENGINE=InnoDB AUTO_INCREMENT=31039988 DEFAULT CHARSET=utf8;
    

    Code

    con = MySQLdb.Connection(...)
    cur = con.cursor()
    cur.execute("INSERT INTO `auth_user` (id, password, `last_login`, `is_superuser`, `username`, `first_name`, "
                 "`last_name`, `email`, `is_staff`, `is_active`, `date_joined`) "
                 "VALUES (-1, '', NULL, 0, '1111113', 'Some', 'User', '', 0, 1, '2022-11-02 08:29:59.021095')")
    print(cur.lastrowid)
    

    Environment

    MySQL Server

    • Server OS (e.g. Windows 10, Ubuntu 20.04): Docker mysql:5.7.39
    • Server Version (e.g. MariaDB 10.3.16): 5.7.39

    MySQL Client

    • OS (e.g. Windows 10, Ubuntu 20.04): MacOSX 12.6

    • Python (e.g. Homebrew Python 3.7.5): pyenv python 3.8.13

    • Connector/C (e.g. Homebrew mysql-client 8.0.18): brew install [email protected]

    Additional context

    Add any other context about the problem here.

    opened by matejsp 0
  • `BaseCursor#close(..)` can exhaust the system memory

    `BaseCursor#close(..)` can exhaust the system memory

    Describe the bug

    The BaseCursor#close(..) method invokes BaseCursor#nextset(..), which will invoke self.fetchall() under certain circumstances:

    • https://github.com/PyMySQL/mysqlclient/blob/8f0cbacba853f0328b839b742b32a0ebb0687b8e/MySQLdb/cursors.py#L83
    • https://github.com/PyMySQL/mysqlclient/blob/8f0cbacba853f0328b839b742b32a0ebb0687b8e/MySQLdb/cursors.py#L134

    Unfortunately, this can cause Python to run out of memory when the cursor yields hundreds of millions of records.

    To Reproduce

    1. Create a new table
    2. Populate it with hundreds of millions of records
    3. Create a cursor that will yield all of the records from this table. (Ex: SELECT * FROM HugeTable)
    4. Fetch a handful of records from the cursor. (ie: cursor.fetchmany(100) )
    5. Close the cursor

    Environment

    This bug is present regardless of environment; however, here's the environment that I was using:

    MySQL Server

    • AWS RDS. (Presumably, the use Amazon Linux)
    • Server Version 8.0.23

    MySQL Client

    • OS: Ubuntu Jammy
    • Python >= 3.7
    • mysqlclient 2.0.3 and mysqlclient 2.1.1

    Additional Context

    I can probably implement a fix for this once we confirm that it's a legitimate bug. At a high-level, I suspect the call to self.fetchall() probably needs to be replaced with a call to self.fetchmany(..). However, this might be complicated by structural details that I'm not aware of.

    opened by briandamaged 0
  • C extension bug / Uncaught in Python causing kernel to die

    C extension bug / Uncaught in Python causing kernel to die

    Describe the bug

    I accidentally shared a connection object between threads in python3 ThreadPoolExecutor. I did not receive the error from my scripts because the execption is not caught by Python. It looks like something related to C. Sharing connection object works with psycopg2 or sqlite. This happens wity mysqlclient.

    To Reproduce

    Schema

    Schema is generated by the script below. It is not very relevant.

    Code

    docker-compose -f docker-compose.yaml up --build

    docker-compose.yaml

    services:
      mysql:
        image: mysql:8
        container_name: 'sqlalchmey'
        ports:
          - 3311:3306
        environment:
          - MYSQL_ROOT_PASSWORD=python
          - MYSQL_PASSWORD=python
          - MYSQL_USER=python
          - MYSQL_DATABASE=python
    

    python3 execute.py --share=1 execute.py

    # execute.py
    import sqlalchemy as sa
    from concurrent.futures import ThreadPoolExecutor
    import os
    import logging 
    import traceback
    import argparse 
    
    logger = logging.getLogger("Tester")
    
    def create_table(engine:sa.engine.Engine):
        sql = "DROP TABLE IF EXISTS test"
        engine.connect().execute(sql)
        sql = "CREATE TABLE test (id int, bar INT);"
        engine.connect().execute(sql)
        return 
    
    
    def insert(table_obj:str,record:tuple,conn:sa.engine.Connection,engine:sa.engine.Engine):
        ##generates insert query
        query = table_obj.insert().values(id=record[0],bar=record[1])
        if conn is None:
            conn = engine.connect()
        conn.execute(query)
        logger.info("Inserted Record...")
        return 
    
    def run(share_connection=True):
        #tries to insert some records in the test table using threads
        engine = sa.create_engine("mysql://python:[email protected]:3311/python")
        create_table(engine=engine)
        metadata = sa.MetaData(bind=engine)
    
        table_obj = sa.Table('test', metadata, autoload=True)
    
        
    
        if share_connection:
            engine_param = None 
            conn = engine.connect()
        else:
            conn = None
            engine_param = engine
        
        records = [(i,i*2) for i in range(1,30)] 
        with ThreadPoolExecutor(max_workers=os.cpu_count()-1) as tpe:
            threads = [tpe.submit(insert,table_obj,record,conn,engine_param) for record in records]
    
    
        if conn is not None: conn.close()
    
    def main():
        #wrapper for run
        try:
            parser = argparse.ArgumentParser()
            parser.add_argument('--share', default=False, type=int)
            share_connection = True if parser.parse_args().share==1 else False
            run(share_connection=share_connection)
            logger.info("success")
        except:
            logger.error(f"This now: {traceback.format_exc()}")
    
    if __name__=='__main__':
        main()
    

    requirements.txt

    mysqlclient==2.1.1
    SQLAlchemy==1.4.40
    greenlet==1.1.3
    

    To Reproduce

    bash

    python3 -m venv .venv.test 
    source .venv.test/bin/activate 
    pip install -r requirements.txt 
    python3 execute.py --share=1
    

    Output

    free(): double free detected in tcache 2
    free(): double free detected in tcache 2
    Segmentation fault
    

    Also sometimes:

    free(): double free detected in tcache 2
    double free or corruption (!prev)
    Aborted
    

    Environment

    • OS: Ubuntu 20.04 LTS (on WSL2)
    • Python: 3.8.10
    • SQLAlchemy: 1.4.40
    • Database: MySQL 8
    • DBAPI (eg: psycopg, cx_oracle, mysqlclient):mysqlclient==2.1.1

    Additional context

    This uncaught error kills jupyter notebook also without any tracebacks. That is how I discovered it.

    opened by FnayouSeif 0
Releases(v2.1.1)
  • v2.1.1(Jun 22, 2022)

    What's Changed

    • Add module attributes to exception classes. by @methane in https://github.com/PyMySQL/mysqlclient/pull/523
    • Fix out of range bug by @methane in https://github.com/PyMySQL/mysqlclient/pull/538
    • Fix docstring for _mysql.connect by @Llewyllen in https://github.com/PyMySQL/mysqlclient/pull/540
    • Update CI settings by @methane in https://github.com/PyMySQL/mysqlclient/pull/541

    New Contributors

    • @Llewyllen made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/540

    Full Changelog: https://github.com/PyMySQL/mysqlclient/compare/v2.1.0...v2.1.1

    Source code(tar.gz)
    Source code(zip)
  • v2.1.0(Nov 18, 2021)

    What's Changed

    • Use unittest.mock instead of mock by @methane in https://github.com/PyMySQL/mysqlclient/pull/469
    • Actions: Fix pytest args. by @methane in https://github.com/PyMySQL/mysqlclient/pull/470
    • Actions: Fix measuring coverage by @methane in https://github.com/PyMySQL/mysqlclient/pull/471
    • Let multi statements be optional by @simlun in https://github.com/PyMySQL/mysqlclient/pull/500
    • Remove bytes encoder that was specifically for Django 1.11 by @rsiemens in https://github.com/PyMySQL/mysqlclient/pull/490
    • update remnants of passwd and db also in docs by @ziegenberg in https://github.com/PyMySQL/mysqlclient/pull/488
    • Better support for building on Windows by @sparkyb in https://github.com/PyMySQL/mysqlclient/pull/484
    • Fix tests with MySQL 8.0 by @methane in https://github.com/PyMySQL/mysqlclient/pull/501
    • Windows: Update MariaDB Connector to 3.2.4 by @methane in https://github.com/PyMySQL/mysqlclient/pull/508
    • set_character_set() sends "SET NAMES" always. by @methane in https://github.com/PyMySQL/mysqlclient/pull/509
    • Remove escape() and escape_string() from MySQLdb. by @methane in https://github.com/PyMySQL/mysqlclient/pull/511
    • _mysql: db -> database, passwd -> password by @methane in https://github.com/PyMySQL/mysqlclient/pull/513
    • Always set MULTI_RESULTS flag by @methane in https://github.com/PyMySQL/mysqlclient/pull/515
    • Actions: Add Python 3.10 and remove 3.5. by @methane in https://github.com/PyMySQL/mysqlclient/pull/516
    • Action: Run Django tests by @methane in https://github.com/PyMySQL/mysqlclient/pull/519
    • Fix typos by @kianmeng in https://github.com/PyMySQL/mysqlclient/pull/520

    New Contributors

    • @simlun made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/500
    • @rsiemens made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/490
    • @ziegenberg made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/488
    • @sparkyb made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/484
    • @kianmeng made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/520

    Full Changelog: https://github.com/PyMySQL/mysqlclient/compare/v2.0.3...v2.1.0

    Source code(tar.gz)
    Source code(zip)
  • v2.1.0rc1(Nov 17, 2021)

    What's Changed

    • Use unittest.mock instead of mock by @methane in https://github.com/PyMySQL/mysqlclient/pull/469
    • Actions: Fix pytest args. by @methane in https://github.com/PyMySQL/mysqlclient/pull/470
    • Actions: Fix measuring coverage by @methane in https://github.com/PyMySQL/mysqlclient/pull/471
    • Let multi statements be optional by @simlun in https://github.com/PyMySQL/mysqlclient/pull/500
    • Remove bytes encoder that was specifically for Django 1.11 by @rsiemens in https://github.com/PyMySQL/mysqlclient/pull/490
    • update remnants of passwd and db also in docs by @ziegenberg in https://github.com/PyMySQL/mysqlclient/pull/488
    • Better support for building on Windows by @sparkyb in https://github.com/PyMySQL/mysqlclient/pull/484
    • Fix tests with MySQL 8.0 by @methane in https://github.com/PyMySQL/mysqlclient/pull/501
    • Windows: Update MariaDB Connector to 3.2.4 by @methane in https://github.com/PyMySQL/mysqlclient/pull/508
    • set_character_set() sends "SET NAMES" always. by @methane in https://github.com/PyMySQL/mysqlclient/pull/509
    • Remove escape() and escape_string() from MySQLdb. by @methane in https://github.com/PyMySQL/mysqlclient/pull/511
    • _mysql: db -> database, passwd -> password by @methane in https://github.com/PyMySQL/mysqlclient/pull/513
    • Always set MULTI_RESULTS flag by @methane in https://github.com/PyMySQL/mysqlclient/pull/515
    • Actions: Add Python 3.10 and remove 3.5. by @methane in https://github.com/PyMySQL/mysqlclient/pull/516

    New Contributors

    • @simlun made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/500
    • @rsiemens made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/490
    • @ziegenberg made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/488
    • @sparkyb made their first contribution in https://github.com/PyMySQL/mysqlclient/pull/484

    Full Changelog: https://github.com/PyMySQL/mysqlclient/compare/v2.0.3...v2.1.0rc1

    Source code(tar.gz)
    Source code(zip)
SAP HANA Connector in pure Python

SAP HANA Database Client for Python A pure Python client for the SAP HANA Database based on the SAP HANA Database SQL Command Network Protocol. pyhdb

SAP 299 Nov 20, 2022
MariaDB connector using python and flask

MariaDB connector using python and flask This should work with flask and to be deployed on docker. Setting up stuff 1. Docker build and run docker bui

Bayangmbe Mounmo 1 Jan 11, 2022
A Relational Database Management System for a miniature version of Twitter written in MySQL with CLI in python.

Mini-Twitter-Database This was done as a database design course project at Amirkabir university of technology. This is a relational database managemen

Ali 12 Nov 23, 2022
Creating a python package to convert /transfer excelsheet data to a mysql Database Table

Creating a python package to convert /transfer excelsheet data to a mysql Database Table

Odiwuor Lameck 1 Jan 7, 2022
aiomysql is a library for accessing a MySQL database from the asyncio

aiomysql aiomysql is a "driver" for accessing a MySQL database from the asyncio (PEP-3156/tulip) framework. It depends on and reuses most parts of PyM

aio-libs 1.5k Jan 3, 2023
Class to connect to XAMPP MySQL Database

MySQL-DB-Connection-Class Class to connect to XAMPP MySQL Database Basta fazer o download o mysql_connect.py e modificar os parâmetros que quiser. E d

Alexandre Pimentel 4 Jul 12, 2021
Sample code to extract data directly from the NetApp AIQUM MySQL Database

This sample code shows how to connect to the AIQUM Database and pull user quota details from it. AIQUM Requirements: 1. AIQUM 9.7 or higher. 2. An

null 1 Nov 8, 2021
Async database support for Python. 🗄

Databases Databases gives you simple asyncio support for a range of databases. It allows you to make queries using the powerful SQLAlchemy Core expres

Encode 3.2k Dec 30, 2022
Pure Python MySQL Client

PyMySQL Table of Contents Requirements Installation Documentation Example Resources License This package contains a pure-Python MySQL client library,

PyMySQL 7.2k Jan 9, 2023
A library for python made by me,to make the use of MySQL easier and more pythonic

my_ezql A library for python made by me,to make the use of MySQL easier and more pythonic This library was made by Tony Hasson , a 25 year old student

null 3 Nov 19, 2021
A simple Python tool to transfer data from MySQL to SQLite 3.

MySQL to SQLite3 A simple Python tool to transfer data from MySQL to SQLite 3. This is the long overdue complimentary tool to my SQLite3 to MySQL. It

Klemen Tusar 126 Jan 3, 2023
Script em python para carregar os arquivos de cnpj dos dados públicos da Receita Federal em MYSQL.

cnpj-mysql Script em python para carregar os arquivos de cnpj dos dados públicos da Receita Federal em MYSQL. Dados públicos de cnpj no site da Receit

null 17 Dec 25, 2022
Implementing basic MySQL CRUD (Create, Read, Update, Delete) queries, using Python.

MySQL with Python Implementing basic MySQL CRUD (Create, Read, Update, Delete) queries, using Python. We can connect to a MySQL database hosted locall

MousamSingh 5 Dec 1, 2021
Python MYSQL CheatSheet.

Python MYSQL CheatSheet Python mysql cheatsheet. Install Required Windows(WAMP) Download and Install from HERE Linux(LAMP) install packages. sudo apt

Mohammad Dori 4 Jul 15, 2022
a small, expressive orm -- supports postgresql, mysql and sqlite

peewee Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use. a small, expressive ORM p

Charles Leifer 9.7k Dec 30, 2022
Pandas on AWS - Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretManager, PostgreSQL, MySQL, SQLServer and S3 (Parquet, CSV, JSON and EXCEL).

AWS Data Wrangler Pandas on AWS Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretMana

Amazon Web Services - Labs 3.3k Dec 31, 2022
MySQL Operator for Kubernetes

MySQL Operator for Kubernetes The MYSQL Operator for Kubernetes is an Operator for Kubernetes managing MySQL InnoDB Cluster setups inside a Kubernetes

MySQL 462 Dec 24, 2022
A wrapper for SQLite and MySQL, Most of the queries wrapped into commands for ease.

Before you proceed, make sure you know Some real SQL, before looking at the code, otherwise you probably won't understand anything. Installation pip i

Refined 4 Jul 30, 2022
Python interface to Oracle Database conforming to the Python DB API 2.0 specification.

cx_Oracle version 8.2 (Development) cx_Oracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database

Oracle 841 Dec 21, 2022