MySQL database connector for Python (with Python 3 support)

Related tags

Database Driversmysqlpython
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 Repository https://mysqlclient.readthedocs.io/
AWS SDK for Python

Boto3 - The AWS SDK for Python Boto3 is the Amazon Web Services (AWS) Software Development Kit (SDK) for Python, which allows Python developers to wri

the boto project 7.8k Jan 04, 2023
A fast PostgreSQL Database Client Library for Python/asyncio.

asyncpg -- A fast PostgreSQL Database Client Library for Python/asyncio asyncpg is a database interface library designed specifically for PostgreSQL a

magicstack 5.8k Dec 31, 2022
Use SQL query in a jupyter notebook!

SQL-query Use SQL query in a jupyter notebook! The table I used can be found on UN Data. Or you can just click the link and download the file undata_s

Chuqin 2 Oct 05, 2022
Simplest SQL mapper in Python, probably

SQL MAPPER Basically what it does is: it executes some SQL thru a database connector you fed it, maps it to some model and gives to u. Also it can cre

2 Nov 07, 2022
Python PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more.

Python PG Extras Python port of Heroku PG Extras with several additions and improvements. The goal of this project is to provide powerful insights int

Paweł Urbanek 35 Nov 01, 2022
PostgreSQL database adapter for the Python programming language

psycopg2 - Python-PostgreSQL Database Adapter Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main fe

The Psycopg Team 2.8k Jan 05, 2023
DataStax Python Driver for Apache Cassandra

DataStax Driver for Apache Cassandra A modern, feature-rich and highly-tunable Python client library for Apache Cassandra (2.1+) and DataStax Enterpri

DataStax 1.3k Dec 25, 2022
SQL for Humans™

Records: SQL for Humans™ Records is a very simple, but powerful, library for making raw SQL queries to most relational databases. Just write SQL. No b

Ken Reitz 6.9k Jan 03, 2023
A CRUD and REST api with mongodb atlas.

Movies_api A CRUD and REST api with mongodb atlas. Setup First import all the python dependencies in your virtual environment or globally by the follo

Pratyush Kongalla 0 Nov 09, 2022
google-cloud-bigtable Apache-2google-cloud-bigtable (🥈31 · ⭐ 3.5K) - Google Cloud Bigtable API client library. Apache-2

Python Client for Google Cloud Bigtable Google Cloud Bigtable is Google's NoSQL Big Data database service. It's the same database that powers many cor

Google APIs 39 Dec 03, 2022
ClickHouse Python Driver with native interface support

ClickHouse Python Driver ClickHouse Python Driver with native (TCP) interface support. Asynchronous wrapper is available here: https://github.com/myma

Marilyn System 957 Dec 30, 2022
dbd is a database prototyping tool that enables data analysts and engineers to quickly load and transform data in SQL databases.

dbd: database prototyping tool dbd is a database prototyping tool that enables data analysts and engineers to quickly load and transform data in SQL d

Zdenek Svoboda 47 Dec 07, 2022
GINO Is Not ORM - a Python asyncio ORM on SQLAlchemy core.

GINO - GINO Is Not ORM - is a lightweight asynchronous ORM built on top of SQLAlchemy core for Python asyncio. GINO 1.0 supports only PostgreSQL with

GINO Community 2.5k Dec 29, 2022
New generation PostgreSQL database adapter for the Python programming language

Psycopg 3 -- PostgreSQL database adapter for Python Psycopg 3 is a modern implementation of a PostgreSQL adapter for Python. Installation Quick versio

The Psycopg Team 880 Jan 08, 2023
Py2neo is a comprehensive toolkit for working with Neo4j from within Python applications or from the command line.

Py2neo Py2neo is a client library and toolkit for working with Neo4j from within Python applications and from the command line. The library supports b

Nigel Small 1.2k Jan 02, 2023
Kafka Connect JDBC Docker Image.

kafka-connect-jdbc This is a dockerized version of the Confluent JDBC database connector. Usage This image is running the connect-standalone command w

Marc Horlacher 1 Jan 05, 2022
Example Python codes that works with MySQL and Excel files (.xlsx)

Python x MySQL x Excel by Zinglecode Example Python codes that do the processes between MySQL database and Excel spreadsheet files. YouTube videos MyS

Potchara Puttawanchai 1 Feb 07, 2022
A fast unobtrusive MongoDB ODM for Python.

MongoFrames MongoFrames is a fast unobtrusive MongoDB ODM for Python designed to fit into a workflow not dictate one. Documentation is available at Mo

getme 45 Jun 01, 2022
#crypto #cipher #encode #decode #hash

🌹 CYPHER TOOLS 🌹 Written by TMRSWRR Version 1.0.0 All in one tools for CRYPTOLOGY. Instagram: Capture the Root 🖼️ Screenshots 🖼️ 📹 How to use 📹

50 Dec 23, 2022
Import entity definition document into SQLie3. Manage the entity. Also, create a "Create Table SQL file".

EntityDocumentMaker Version 1.00 After importing the entity definition (Excel file), store the data in sqlite3. エンティティ定義（Excelファイル）をインポートした後、データをsqlit

G-jon FujiYama 1 Jan 09, 2022