sanpingz/mysql-connector

Deprecated, use mysql-connector-python instead

mysql-connector's Issues

executemany can`t support batch syntax use REPLACE INTO or INSERT IGNORE INTO statment

I want batch insert data use REPLACE INTO or INSERT IGNORE INTO
but can`t support

Testing process:

Table:

CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  `male` tinyint(4) NOT NULL,
  `other` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_fl_name` (`first_name`,`last_name`),
  UNIQUE KEY `udx_other` (`other`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4

batch insert code:

import mysql.connector
cnx = mysql.connector.connect(user='xxx', password='xxxx',
                              host='10.10.10.1',
                              database='test')
cursor = cnx.cursor()

sql_replace = '''
REPLACE INTO t1
(`id`, `first_name`, `last_name`, `age`, `male`, `other`)
VALUES
(%(id)s, %(first_name)s, %(last_name)s, %(age)s, %(male)s, %(other)s)
'''

values = []
for i in range(20, 25):
    value = {}
    value['id'] = i
    value['first_name'] = i
    value['last_name'] = i
    value['age'] = i
    value['male'] = i
    value['other'] = i
    values.append(value)
    
cursor.executemany(sql_replace, values)
cnx.commit()

cursor.close()
cnx.close()

binlog output:

BEGIN
/*!*/;
# at 3104
#180531 11:23:47 server id 3306137012  end_log_pos 3233 CRC32 0xf6760f60        Rows_query
# REPLACE INTO t1
# (`id`, `first_name`, `last_name`, `age`, `male`, `other`)
# VALUES
# (20, 20, 20, 20, 20, 20)
......
#180531 11:23:47 server id 3306137012  end_log_pos 3475 CRC32 0x7b1a12ea        Rows_query
# REPLACE INTO t1
# (`id`, `first_name`, `last_name`, `age`, `male`, `other`)
# VALUES
# (21, 21, 21, 21, 21, 21)
......
#180531 11:23:47 server id 3306137012  end_log_pos 3717 CRC32 0xffc8053e        Rows_query
# REPLACE INTO t1
# (`id`, `first_name`, `last_name`, `age`, `male`, `other`)
# VALUES
# (22, 22, 22, 22, 22, 22)
......
#180531 11:23:47 server id 3306137012  end_log_pos 3959 CRC32 0xf1ec47e8        Rows_query
# REPLACE INTO t1
# (`id`, `first_name`, `last_name`, `age`, `male`, `other`)
# VALUES
# (23, 23, 23, 23, 23, 23)
......
#180531 11:23:47 server id 3306137012  end_log_pos 4201 CRC32 0x1d24c291        Rows_query
# REPLACE INTO t1
# (`id`, `first_name`, `last_name`, `age`, `male`, `other`)
# VALUES
# (24, 24, 24, 24, 24, 24)
......
#180531 11:23:51 server id 3306137012  end_log_pos 4345 CRC32 0x820b85e3        Xid = 30
COMMIT/*!*/;

source code only inspection INSERT INTO statement

file: https://github.com/sanpingz/mysql-connector/blob/master/lib/mysql/connector/cursor.py

line: 42

......

RE_SQL_INSERT_STMT = re.compile(
    r"({0}|\s)*INSERT({0}|\s)*INTO\s+[`'\"]?.+[`'\"]?(?:\.[`'\"]?.+[`'\"]?)"
    r"{{0,2}}\s+VALUES\s*\(.+(?:\s*,.+)*\)".format(SQL_COMMENT),
    re.I | re.M | re.S)

......

line: 651

......

        if re.match(RE_SQL_INSERT_STMT, operation):
            if not seq_params:
                self._rowcount = 0
                return
            stmt = self._batch_insert(operation, seq_params)
            if stmt is not None:
                return self.execute(stmt)

        rowcnt = 0

......

I think can add tow variable RE_SQL_REPLACE_STMT and RE_SQL_INSERT_IGNORE_STMT

RE_SQL_REPLACE_STMT = re.compile(
    r"({0}|\s)*REPLACE({0}|\s)*INTO\s+[`'\"]?.+[`'\"]?(?:\.[`'\"]?.+[`'\"]?)"
    r"{{0,2}}\s+VALUES\s*\(.+(?:\s*,.+)*\)".format(SQL_COMMENT),
    re.I | re.M | re.S)
    
RE_SQL_INSERT_IGNORE_STMT = re.compile(
    r"({0}|\s)*INSERT({0}|\s)*IGNORE({0}|\s)*INTO\s+[`'\"]?.+[`'\"]?(?:\.[`'\"]?.+[`'\"]?)"
    r"{{0,2}}\s+VALUES\s*\(.+(?:\s*,.+)*\)".format(SQL_COMMENT),
    re.I | re.M | re.S)

edit executemany function:

        if (re.match(RE_SQL_INSERT_STMT, operation) or
            re.match(RE_SQL_REPLACE_STMT, operation) or
            re.match(RE_SQL_INSERT_IGNORE_STMT, operation)):
            if not seq_params:
                self._rowcount = 0
                return
            stmt = self._batch_insert(operation, seq_params)
            if stmt is not None:
                return self.execute(stmt)

        rowcnt = 0

2.2.3 Doesn't install

Using Python 3.5 on Ubuntu, 2.2.3 doesn't install:

Searching for mysql-connector
Reading https://pypi.python.org/simple/mysql-connector/
Downloading https://pypi.python.org/packages/e8/b4/b178e9f26261b4ab57189d74c50788d97649b58ba3e83a68ebf0e51733ee/mysql-connector-2.2.3.tar.gz#md5=b6cd75346040991687a07b8e7d65bc4d
Best match: mysql-connector 2.2.3
Processing mysql-connector-2.2.3.tar.gz
Writing /tmp/easy_install-v8bfebg4/mysql-connector-2.2.3/setup.cfg
Running mysql-connector-2.2.3/setup.py -q bdist_egg --dist-dir /tmp/easy_install-v8bfebg4/mysql-connector-2.2.3/egg-dist-tmp-cm6iex2o

Python architecture: 64-bit

Python ARCH_64BIT: True

Unable to find Protobuf include directory.
error: Setup script exited with 1

Reverting to 2.1.4 fixes this problem.

Can't install 2.2.3

python 3.5.3

Collecting mysql-connector
Using cached mysql-connector-2.2.3.tar.gz
Installing collected packages: mysql-connector
Running setup.py install for mysql-connector ... error
Complete output from command /Library/Frameworks/Python.framework/Versions/3.5/bin/python3.5 -u -c "import setuptools, tokenize;file='/private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" install --record /var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-btuokezl-record/install-record.txt --single-version-externally-managed --compile:
running install
Not Installing MySQL C Extension
running build
running build_py
creating build
creating build/lib.macosx-10.6-intel-3.5
creating build/lib.macosx-10.6-intel-3.5/mysql
copying lib/mysql/init.py -> build/lib.macosx-10.6-intel-3.5/mysql
creating build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/init.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/abstracts.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/authentication.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/catch23.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/charsets.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/connection.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/connection_cext.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/constants.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/conversion.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/cursor.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/cursor_cext.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/custom_types.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/dbapi.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/errorcode.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/errors.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/network.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/optionfiles.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/pooling.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/protocol.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/utils.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
copying lib/mysql/connector/version.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector
creating build/lib.macosx-10.6-intel-3.5/mysql/connector/locales
copying lib/mysql/connector/locales/init.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/locales
creating build/lib.macosx-10.6-intel-3.5/mysql/connector/locales/eng
copying lib/mysql/connector/locales/eng/init.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/locales/eng
copying lib/mysql/connector/locales/eng/client_error.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/locales/eng
creating build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/init.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/base.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/client.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/compiler.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/creation.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/features.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/introspection.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/operations.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/schema.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
copying lib/mysql/connector/django/validation.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/django
creating build/lib.macosx-10.6-intel-3.5/mysql/connector/fabric
copying lib/mysql/connector/fabric/init.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/fabric
copying lib/mysql/connector/fabric/balancing.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/fabric
copying lib/mysql/connector/fabric/caching.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/fabric
copying lib/mysql/connector/fabric/connection.py -> build/lib.macosx-10.6-intel-3.5/mysql/connector/fabric
creating build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/init.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/authentication.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/charsets.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/compat.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/connection.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/constants.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/crud.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/dbdoc.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/errorcode.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/errors.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/expr.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/protobuf.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/protocol.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/result.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
copying lib/mysqlx/statement.py -> build/lib.macosx-10.6-intel-3.5/mysqlx
creating build/lib.macosx-10.6-intel-3.5/mysqlx/locales
copying lib/mysqlx/locales/init.py -> build/lib.macosx-10.6-intel-3.5/mysqlx/locales
creating build/lib.macosx-10.6-intel-3.5/mysqlx/locales/eng
copying lib/mysqlx/locales/eng/init.py -> build/lib.macosx-10.6-intel-3.5/mysqlx/locales/eng
copying lib/mysqlx/locales/eng/client_error.py -> build/lib.macosx-10.6-intel-3.5/mysqlx/locales/eng
running build_ext
# Python architecture: 64-bit
# Python ARCH_64BIT: True
# Protobuf include directory: /usr/local/opt/protobuf/include
# Protobuf library directory: /usr/local/opt/protobuf/lib
# Protobuf protoc binary: /usr/local/opt/protobuf/bin/protoc
# Running protoc command: /usr/local/opt/protobuf/bin/protoc -I /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_connection.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_crud.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_datatypes.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_expect.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_expr.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_notice.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_resultset.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_session.proto /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx/protocol/mysqlx_sql.proto --cpp_out=/private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/src/mysqlxpb/mysqlx
mysqlx.proto: warning: Import mysqlx_sql.proto but not used.
mysqlx.proto: warning: Import mysqlx_session.proto but not used.
mysqlx.proto: warning: Import mysqlx_crud.proto but not used.
mysqlx.proto: warning: Import mysqlx_connection.proto but not used.
mysqlx.proto: warning: Import mysqlx_resultset.proto but not used.
mysqlx.proto: warning: Import mysqlx_expect.proto but not used.
mysqlx.proto: warning: Import mysqlx_notice.proto but not used.
building 'mysqlxpb' extension
creating build/temp.macosx-10.6-intel-3.5
creating build/temp.macosx-10.6-intel-3.5/src
creating build/temp.macosx-10.6-intel-3.5/src/mysqlxpb
creating build/temp.macosx-10.6-intel-3.5/src/mysqlxpb/mysqlx
/usr/bin/clang -fno-strict-aliasing -Wsign-compare -fno-common -dynamic -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -g -DPY3=1 -I/usr/local/opt/protobuf/include -I/Library/Frameworks/Python.framework/Versions/3.5/include/python3.5m -c src/mysqlxpb/mysqlx/mysqlx.pb.cc -o build/temp.macosx-10.6-intel-3.5/src/mysqlxpb/mysqlx/mysqlx.pb.o -isystem/Library/Frameworks/Python.framework/Versions/3.5/include/python3.5m -Wno-unknown-pragmas
In file included from src/mysqlxpb/mysqlx/mysqlx.pb.cc:5:
In file included from src/mysqlxpb/mysqlx/mysqlx.pb.h:23:
/usr/local/opt/protobuf/include/google/protobuf/arena.h:585:10: error: thread-local storage is not supported for the current target
static GOOGLE_THREAD_LOCAL ThreadCache thread_cache;
^
/usr/local/opt/protobuf/include/google/protobuf/stubs/port.h:302:29: note: expanded from macro 'GOOGLE_THREAD_LOCAL'
#define GOOGLE_THREAD_LOCAL __thread
^
1 error generated.
error: command '/usr/bin/clang' failed with exit status 1

----------------------------------------

Command "/Library/Frameworks/Python.framework/Versions/3.5/bin/python3.5 -u -c "import setuptools, tokenize;file='/private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/setup.py';f=getattr(tokenize, 'open', open)(file);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, file, 'exec'))" install --record /var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-btuokezl-record/install-record.txt --single-version-externally-managed --compile" failed with error code 1 in /private/var/folders/48/cbt_b0ln56q0byjhkfvw64fh0000gn/T/pip-build-5vnjuvbt/mysql-connector/

Code tries using mysql charset as python encoding

Using version 2.1.6 (and tried the same with 2.2.3 beta)

Connected with charset='utf8mb4' and tried using a prepared statement cursor and got:

  File "/path/local/lib/python3.5/dist-packages/mysql/connector/cursor.py", line 1141, in execute
    operation = operation.encode(self._connection.charset)
LookupError: unknown encoding: utf8mb4

So, switched to utf-8 thinking it should be a Python encoding and got:

  File "/path/local/lib/python3.5/dist-packages/mysql/connector/constants.py", line 630, in get_default_collation
    raise ProgrammingError("Character set '%s' unsupported." % (charset))
mysql.connector.errors.ProgrammingError: Character set 'utf-8' unsupported.

Seems that the Python encoding should be derived from the MySQL one (mapping utf8mb4 on the MySQL side to utf-8 on the Python one maybe)? Documentation suggests utf8mb4 should work.

2.2.3 Protobuf C++ Dependency?

For the unfamiliar (such as myself), what is the reason that Protobufs are now a required dependency? I see also that it is in the upstream as well, but no detail as far as I can see.

Django 1.11 not supported

https://bugs.mysql.com/bug.php?id=86105

I think I have met a bug in multiprocessing situation

Unable to install using pip, Python 3.5, Mac OSX

Hi sanpingz,

unfortunately, I am unable to install your connector.

(.py3)$ pip install mysql-connector
Collecting mysql-connector
  Using cached mysql-connector-2.1.3.zip
Installing collected packages: mysql-connector
  Running setup.py install for mysql-connector ... error
    Complete output from command /Users/username/.py3/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/6l/btfmdnh90pl0_0fdlw3c1tz8tdg9tj/T/pip-build-ah5o99fi/mysql-connector/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/6l/btfmdnh90pl0_0fdlw3c1tz8tdg9tj/T/pip-wa55htk5-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/username/.py3/include/site/python3.5/mysql-connector:
    usage: -c [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...]
       or: -c --help [cmd1 cmd2 ...]
       or: -c --help-commands
       or: -c cmd --help

    error: option --single-version-externally-managed not recognized

    ----------------------------------------
Command "/Users/username/.py3/bin/python3.5 -u -c "import setuptools, tokenize;__file__='/private/var/folders/6l/btfmdnh90pl0_0fdlw3c1tz8tdg9tj/T/pip-build-ah5o99fi/mysql-connector/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/6l/btfmdnh90pl0_0fdlw3c1tz8tdg9tj/T/pip-wa55htk5-record/install-record.txt --single-version-externally-managed --compile --install-headers /Users/username/.py3/include/site/python3.5/mysql-connector" failed with error code 1 in /private/var/folders/6l/btfmdnh90pl0_0fdlw3c1tz8tdg9tj/T/pip-build-ah5o99fi/mysql-connector/

Best regards,

T-002

PyPi presence

Hi sanpingz,

thanks for providing the pypi package to users via https://pypi.org/project/mysql-connector/ but please mind that we are (again) publishing via https://pypi.org/project/mysql-connector-python/ is it possible that you give up the name so users can find the latest version?

Thanks,
johannes (Oracle/MySQL)