MySQL database connector for Python (with Python 3 support)

Related tags

Database Drivers mysql python
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)

  • v2.0.3(Nov 17, 2021)

  • v2.0.1(Dec 4, 2020)

  • 1.3.8(Oct 2, 2016)

  • 1.3.7(Sep 5, 2016)

Owner
PyMySQL
PyMySQL
GitHub https://mysqlclient.readthedocs.io/
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