Python 連 mysql

Posted on 2017-09-082023-08-25

How to install Python MySQLdb module using pip?

如果 mysql 從 5.7 升到 mysql 8.0以後，請改用官方的 connector，現在應該都是使用這一段code:l

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 16 / 18 / 20 / 22, Debian 8.6 (jessie)

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

which is obviously specific to ubuntu/debian, but I just wanted to share my success 🙂

mysqlclient 的文件： https://mysqlclient.readthedocs.io/

如果你的 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()

如果還是出錯，需要服用下面的指令：

The solution is to install corresponding Python 3 module:

sudo apt-get install python3-mysql.connector

It fixes import mysql.connector error:

$ python3
Python 3.5.2 (default, Nov 23 2017, 16:37:01) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector
>>>

And similar for Python 2:

sudo apt-get install python-mysql.connector

$ python2
Python 2.7.12 (default, Dec  4 2017, 14:50:18) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql.connector
>>>

在Ubuntu 20.04 安裝 mysql 8.0

先輸入下面這二行：

wget https://dev.mysql.com/get/mysql-apt-config_0.8.20-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.20-1_all.deb

雖然是使用 0.8.20 ，在2021-12-04 最近的版本是 0.8.27, 再使用下面二句，Update MySQL Repository on Ubuntu20.04|22.04：

sudo apt update
sudo apt-cache policy mysql-server

Install MySQL 8.0:

sudo apt install mysql-client mysql-community-server mysql-server

Secure MySQL Installationon Ubuntu20.04

透過下面指令，調整要不要停用root 遠端登入，建議是不要，因為很危險，還有移掉test datatbase，設定密碼的規則。

sudo mysql_secure_installation

最後使用下面的指令就可以登入了：

mysql -u root -p

Max的程式語言筆記