In Windows 10, use Pycharm2016 to install the Mysql Driver Based on Anaconda3 Python3.6,

In Windows 10, use Pycharm2016 to install the Mysql Driver Based on Anaconda3 Python3.6,

This article records: Install the Python for Mysql Driver Based on Anaconda3 Python3.6 in PyCharm2016.3.3. I tried to install Mysql-Connector successfully, but failed to connect to the database with a drive bug. I tried to install MySQLdb because of Python compatibility problems. Finally, PyMysql is successfully installed. The detailed installation process is as follows.

If you don't want to worry about it, and the system environment is the same as mine, we recommend that you install PyMysql directly.

I. System Environment

Windows 10 64-bit, installed with Anaconda3 and PyCharm2016.3.3. In addition, python is installed in Windows 10 (environment variables have been configured), such:

 

The Python interpreter configured in PyCharm is the Python environment that comes with Anaconda3, for example:

The installed Mysql version is as follows:

mysql  Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.2

 

II. Introduction to Python for Mysql driver

For Python, there are many drivers for accessing Mysql, mainly Mysqldb, mysqlconnector and pymysql (refer to this Article)

MySQL-python: This package contains the MySQLdb module, which is written in C.
It is one of the most commonly used Python packages for MySQL.mysql-connector-python: This package contains the mysql.connector module, which is written entirely in Python.PyMySQL: This package contains the pymysql module, which is written entirely in Python. It is designed to be a drop-in replacement for the MySQL-python package.

 

3. Install MySQL Connector/Python in the local environment

On the MySQL Connector/Python official website download: mysql-connector-python-2.1.7-py3.4-windows-x86-64bit.msi double-click installation successful.

Run import mysql. connector successfully on the cmd command line, for example:

 

However, I want to use the Python environment that comes with Anaconda3 instead of the local Python3.4 environment. Because Anaconda3 has provided many third-party dependent packages for us, such as the ORM framework in Python: SQLAlchemy. The following will also test the Mysql database connection through "from sqlalchemy import create_engine"

Then, open Anaconda Prompt and execute the command:

conda install -c anaconda mysql-connector-python 

Mysql-connector-python is successfully installed. Reference: how do I get mysql to work with python 3.6 in anaconda ide (spyder )?

As a result, I began to happily test whether the database can be successfully connected.

Open PyCharm and go to "Tools" ---> "Python console" on the menu bar to open the Python Console:

Run the following command:

from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://username:password.@HOST_IP:PORT/db_name?charset=utf8')
conn = engine.connect()

The following error is reported:

sqlalchemy.exc.InterfaceError: (mysql.connector.errors.InterfaceError) Failed parsing handshake; end byte not present in buffer

I searched the internet and said it was a Bug. The error cannot be solved. Refer to here. If you can solve the problem, please leave a message to inform us. Thank you very much.

Therefore, install MySQLdb. After reading this article, run conda install mysql-python and The following error is returned: The following specifications were found to be in conflict.

MySQLdb does not support Python 3.6. For more information, see this article.

The mysql-python conda package hasn't been built for python 3 . 
You can try creating a python 2.7 environment and install mysql-python into that.

Try again: The goal of the PyMysql project is to replace MySQLdb

This package contains a pure-Python MySQL client library. 
The goal of PyMySQL is to be a drop-in replacement for MySQLdb and work on CPython, PyPy and IronPython.

Run the following command in Anaconda Prompt:Conda install-c anaconda pymysql, refer to: here

 

After the installation is successful, in PyCharm, Ctrl + Alt + S search for "python" and open the project interpreter. You can see that pymysql is successfully installed.

Therefore, you can use PyMySQL to test the connection to the Mysql database. The following command is successfully executed. (Replace it with your own database name ). So far, Python 3.6 Based on Anaconda3 in PyCharm can finally access the Mysql database.

from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://username:password.@HOST_IP:PORT/db_name?charset=utf8')
conn = engine.connect()
r = conn.execute('SELECT field1,field2 FROM DB_NMAE limit 1 ')

 

In addition, for different drivers, the statements for connecting to Mysql are different: For details, refer to the official sqlalchemy documentation.

#MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

#pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

#mysqlconnector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>


	 


	Original article: http://www.cnblogs.com/hapjin/p/7800959.html