Python 連 mysql

How to install Python MySQLdb module using pip?

for Python 2.x:

pip install MySQL-python

已經進入 python3 的世界,請改用下面的 pip:

python3 -m pip install mysqlclient

如果 mysql 從 5.7 升到 mysql 8.0 請改用官方的 connector

python3 -m pip install mysql-connector-python

附註:Note that mysql-connector is deprecated. You should be using mysql-connector-python.

mysql 8.0 + python 的官方範例:
https://dev.mysql.com/doc/connector-python/en/connector-python-examples.html

import mysql.connector
cnx = mysql.connector.connect(user='max', password='password',
                              host='127.0.0.1',
                              database='employees', auth_plugin='mysql_native_password')
cnx.close()

Note: Some dependencies might have to be in place when running the above command. Some hints on how to install these on various platforms:

Ubuntu 14, Ubuntu 16, Debian 8.6 (jessie)

for Python 2:

sudo apt-get install python-pip python-dev libmysqlclient-dev

for Python 3:

sudo apt-get install python3-pip python3-dev libmysqlclient-dev

Fedora 24:

sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc

Mac OS

brew install mysql


現在,大家都在使用python3 了,但是 MySQLdb 因為沒有人維護了,無法使用 python3,不相容的原因是:

In Python 3, ConfigParser has been renamed to configparser for PEP 8 compliance. It looks like the package you are installing does not support Python 3.

發生的 Error Message:

from ConfigParser import SafeConfigParser
ModuleNotFoundError: No module named ‘ConfigParser’

在  python3 解法:

You can instead use the mysqlclient package as a drop-in replacement for MySQL-python. It is a fork of MySQL-python with added support for Python 3.

I had luck with simply

pip install mysqlclient

in my python3 virtualenv after

sudo apt-get install python3-dev libmysqlclient-dev

which is obviously specific to ubuntu/debian, but I just wanted to share my success 🙂   mysqlclient 的文件: https://mysqlclient.readthedocs.io/

如果你的環境裡有多個 python3.x 的版本,例如 3.5, 3.6, 3.7,如果你想指定在python 3.7 裡安裝,而且在執行指令:

python3.7 -m pip install mysqlclient

如果你的 MacOS 在上面都安裝好後,在 import MySQLdb 遇到下面的error:

import MySQLdb
   File "/usr/local/lib/python3.7/site-packages/MySQLdb/__init__.py", line 18, in <module>
     from . import _mysql
 ImportError: dlopen(/usr/local/lib/python3.7/site-packages/MySQLdb/_mysql.cpython-37m-darwin.so, 2): Library not loaded: /usr/local/opt/mysql/lib/libmysqlclient.20.dylib
   Referenced from: /usr/local/lib/python3.7/site-packages/MySQLdb/_mysql.cpython-37m-darwin.so
   Reason: image not found 

原因是更新的 mysql 的版本所造成。新的 mysql 是使用 libmysqlclient.21.dylib 解法:

ln -s libmysqlclient.21.dylib libmysqlclient.20.dylib

如果遇到錯誤訊息:

MySQLdb/_mysql.c:37:10: fatal error: Python.h: No such file or directory

解法:
If it is for python 3.7, you have to install corresponding python3-dev, i.e:

apt-get install python3.7-dev

除了 MySQLdb 可以使用,也可以用  mysql.connector

import mysql.connector

cnx = mysql.connector.connect(user='scott', password='tiger',
 host='127.0.0.1',
 database='employees')
cnx.close()

所有平台的 MySQL official connector 在這裡 http://dev.mysql.com/doc/index-connectors.html

MySQL Python connector 的 API guide 請看這裡 http://dev.mysql.com/doc/connector-python/en/connector-python-reference.html

connector 下載請到這裡
http://dev.mysql.com/downloads/connector/python/


安裝好 MySQLdb 後便可以編寫程式碼,以下是簡單的例子:

#!/usr/bin/python
    
# 引入 MySQL 模組
import MySQLdb
    
# 連接到 MySQL
db = MySQLdb.connect(host="localhost", user="db_user", passwd="db_pass", db="db_name")
cursor = db.cursor()
    
# 執行 SQL 語句
cursor.execute("SELECT * FROM db_table")
result = cursor.fetchall()
    
# 輸出結果
for record in result:
    print record[0]

使用這個「標準」的範例在 Mac OS X 裡會出錯錯誤:

TypeError: not all arguments converted during string formatting

或這一個錯:

OperationalError(1136, "Column count doesn't match value count at row 1")

但是,在 Linux 下跑卻又都正常。

範例:

cursor.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

Instead of this:

cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", search )

Try this:

cur.execute( "SELECT * FROM records WHERE email LIKE '%s'", [search] )

See the MySQLdb documentation. The reasoning is that execute‘s second parameter represents a list of the objects to be converted, because you could have an arbitrary number of objects in a parameterized query. In this case, you have only one, but it still needs to be an iterable (a tuple instead of a list would also be fine).


不管是改用 () 還是 []  傳進去  cursor.execute()  在 Mac OS X 裡都會顯示錯誤。替代方案是改用會比較有安全性上的問題的寫法:

with security issues:

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

with escaping:

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

或是改用別的 mysql database object 試看看。


Download Connector/Python

https://dev.mysql.com/downloads/connector/python/

Installing Connector/Python from a Binary Distribution
https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html

在 linux 上安裝:

sudo yum update mysql-community-release
sudo yum install mysql-connector-python

mysql.connector 使用教學:

Connecting to MySQL Using Connector/Python
https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

import mysql.connector
cnx = mysql.connector.connect(user='scott', password='tiger',
 host='127.0.0.1',
 database='employees')
cnx.close()

相關文章

寫留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *