python如何使用mysql-connector方式連接mysql數(shù)據(jù)庫(kù)

python如何使用mysql-connector方式連接mysql數(shù)據(jù)庫(kù)？一起跟小編來(lái)看看吧。                                                   

用linux上 用python對(duì)mysql進(jìn)行連接
前提遇到的問(wèn)題記錄：
在linux上安裝 
(base) [sqoop@flink-slave5 bin]$ ./pip install mysql                  
Looking in indexes: http://pypi.douban.com/simple
Collecting mysql
  Downloading http://pypi.doubanio.com/packages/bf/5f/b574ac9f70811df0540e403309f349a8b9fa1a25d3653824c32e52cc1f28/mysql-0.0.2.tar.gz (1.9 kB)
Collecting mysqlclient
  Downloading http://pypi.doubanio.com/packages/d0/97/7326248ac8d5049968bf4ec708a5d3d4806e412a42e74160d7f266a3e03a/mysqlclient-1.4.6.tar.gz (85 kB)
     |████████████████████████████████| 85 kB 4.0 MB/s 
    ERROR: Command errored out with exit status 1:
     command: /data/python_test/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-s6ioj0n3/mysqlclient/setup.py'"'"'; __file__='"'"'/tmp/pip-install-s6ioj0n3/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-s6ioj0n3/mysqlclient/pip-egg-info
         cwd: /tmp/pip-install-s6ioj0n3/mysqlclient/
    Complete output (12 lines):
    /bin/sh: mysql_config: command not found
    /bin/sh: mariadb_config: command not found
    /bin/sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-install-s6ioj0n3/mysqlclient/setup.py", line 16, in <module>
        metadata, options = get_config()
      File "/tmp/pip-install-s6ioj0n3/mysqlclient/setup_posix.py", line 61, in get_config
        libs = mysql_config("libs")
      File "/tmp/pip-install-s6ioj0n3/mysqlclient/setup_posix.py", line 29, in mysql_config
        raise EnvironmentError("%s not found" % (_mysql_config_path,))
    OSError: mysql_config not found
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

解決方法： [root@flink-slave5 ~]# yum install mysql-devel gcc gcc-devel python-devel 
參考鏈接 https://www.jianshu.com/p/5b6deb15bd21 

(base) [sqoop@flink-slave5 bin]$ pwd
/data/python_test/bin
(base) [sqoop@flink-slave5 bin]$ ./pip install mysql
Looking in indexes: http://pypi.douban.com/simple
Collecting mysql
  Downloading http://pypi.doubanio.com/packages/bf/5f/b574ac9f70811df0540e403309f349a8b9fa1a25d3653824c32e52cc1f28/mysql-0.0.2.tar.gz (1.9 kB)
Collecting mysqlclient
  Downloading http://pypi.doubanio.com/packages/d0/97/7326248ac8d5049968bf4ec708a5d3d4806e412a42e74160d7f266a3e03a/mysqlclient-1.4.6.tar.gz (85 kB)
     |████████████████████████████████| 85 kB 14.8 MB/s 
Building wheels for collected packages: mysql, mysqlclient
  Building wheel for mysql (setup.py) ... done
  Created wheel for mysql: filename=mysql-0.0.2-py3-none-any.whl size=1243 sha256=81bb94d982e366e617bf2dd96df9735009fea2ad983b60198d054773df25dde2
  Stored in directory: /home/sqoop/.cache/pip/wheels/42/09/8b/d90224fd8b5636800fa21b9ede51f208bb1781bf7f52b89085
  Building wheel for mysqlclient (setup.py) ... done
  Created wheel for mysqlclient: filename=mysqlclient-1.4.6-cp37-cp37m-linux_x86_64.whl size=59120 sha256=1d83233315a664a264e51acb1b0288979b0f621da6c15a0fc3688e5f1c09fda4
  Stored in directory: /home/sqoop/.cache/pip/wheels/f9/8e/98/19b6ee3ca946f2f107a41bc88a15c3844d406ceeae32446784
Successfully built mysql mysqlclient
Installing collected packages: mysqlclient, mysql
Successfully installed mysql-0.0.2 mysqlclient-1.4.6

然后執(zhí)行： (base) [sqoop@flink-slave5 bin]$ ./pip install mysql-connector 
...
...
...
在linux環(huán)境上測(cè)試成功 

(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql.py 
MySQL版本: 8.0.13 
(base) [sqoop@flink-slave5 majihui_test]$ pwd
/data/majihui_test
(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql.py 
# -*- coding: UTF-8 -*-
import mysql.connector
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)
# 獲取操作游標(biāo)
cursor = db.cursor()
# 執(zhí)行SQL語(yǔ)句
cursor.execute("SELECT VERSION()")
# 獲取一條數(shù)據(jù)
data = cursor.fetchone()
print("MySQL版本: %s " % data)
# 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
cursor.close()
db.close()
(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql.py 
MySQL版本: 8.0.13 

第三：  對(duì)數(shù)據(jù)表進(jìn)行增刪改查
    1、首先我們給student的表增加一個(gè)學(xué)生用戶(hù)：
        mysql> select * from student ;
            +-----------+--------+------+------+-------+
            | Sno       | Sname  | Ssex | Sage | Sdept |
            +-----------+--------+------+------+-------+
            | 100215122 | 劉晨   | 女   |   19 | CS    |
            | 100215123 | 王敏   | 女   |   18 | MA    |
            | 200215121 | 李勇   | 女   |   20 | CS    |
            +-----------+--------+------+------+-------+
            3 rows in set (0.00 sec)

# 插入學(xué)生代碼
sql = "INSERT INTO student (Sno, Sname, Ssex,Sage,Sdept) VALUES (%s, %s, %s, %s, %s)"
val = (200215122, "馬吉輝", "男", 27, CS)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "記錄插入成功。")

完整代碼如下：  執(zhí)行了2次，插入了2次數(shù)據(jù) 
(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql_insert.py                             
# -*- coding: UTF-8 -*-
import mysql.connector
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)

# 獲取操作游標(biāo)
cursor = db.cursor()
# 插入學(xué)生代碼
sql = "INSERT INTO student (Sno, Sname, Ssex,Sage,Sdept) VALUES (%s, %s, %s, %s, %s)"
val = (200215123, "王金燦", "男", 30, "CS")
# 執(zhí)行SQL語(yǔ)句
cursor.execute(sql, val)
# 提交sql語(yǔ)句對(duì)事務(wù)進(jìn)行提交
db.commit()
# 打印結(jié)果 
# 使用cursor.rowcount，返回查詢(xún)結(jié)果集中的行數(shù)。如果沒(méi)有查詢(xún)到數(shù)據(jù)或者還沒(méi)有查詢(xún)，則結(jié)果為 -1，否則會(huì)返回查詢(xún)得到的數(shù)據(jù)行數(shù)；
print(cursor.rowcount, "記錄插入成功。")
# 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
cursor.close()
db.close()

(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_insert.py 
1 記錄插入成功。

mysql> select * from student ;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 100215122 | 劉晨      | 女   |   19 | CS    |
| 100215123 | 王敏      | 女   |   18 | MA    |
| 200215121 | 李勇      | 女   |   20 | CS    |
| 200215122 | 馬吉輝    | 男   |   27 | CS    |
| 200215123 | 王金燦    | 男   |   30 | CS    |
+-----------+-----------+------+------+-------+
5 rows in set (0.00 sec)

代碼解釋?zhuān)?
        我們使用 cursor.execute 來(lái)執(zhí)行相應(yīng)的 SQL 語(yǔ)句，
        val 為 SQL 語(yǔ)句中的參數(shù)，SQL 執(zhí)行后使用 db.commit() 進(jìn)行提交。
        需要說(shuō)明的是，我們?cè)谑褂?SQL 語(yǔ)句的時(shí)候，可以向 SQL 語(yǔ)句傳遞參數(shù)，
        這時(shí) SQL 語(yǔ)句里要統(tǒng)一用（%s）進(jìn)行占位，否則就會(huì)報(bào)錯(cuò)。
        不論插入的數(shù)值為整數(shù)類(lèi)型，還是浮點(diǎn)類(lèi)型，都需要統(tǒng)一用（%s）進(jìn)行占位。
        另外在用游標(biāo)進(jìn)行 SQL 操作之后，還需要使用 db.commit() 進(jìn)行提交，否則數(shù)據(jù)不會(huì)被插入。
小結(jié)：
    上面這段代碼中有兩個(gè)重要的對(duì)象你需要了解下，分別是 Connection 和 Cursor。
        Connection 就是對(duì)數(shù)據(jù)庫(kù)的當(dāng)前連接進(jìn)行管理，我們可以通過(guò)它來(lái)進(jìn)行以下操作：
        1、通過(guò)指定 host、user、passwd 和 port 等參數(shù)來(lái)創(chuàng)建數(shù)據(jù)庫(kù)連接，這些參數(shù)分別對(duì)應(yīng)著數(shù)據(jù)庫(kù) IP 地址、用戶(hù)名、密碼和端口號(hào)；
        2、使用 db.close() 關(guān)閉數(shù)據(jù)庫(kù)連接；
        3、使用 db.cursor() 創(chuàng)建游標(biāo)，操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)；
        4、使用 db.begin() 開(kāi)啟事務(wù)；
        5、使用 db.commit() 和 db.rollback()，對(duì)事務(wù)進(jìn)行提交以及回滾。
    當(dāng)我們通過(guò)cursor = db.cursor()創(chuàng)建游標(biāo)后，就可以通過(guò)面向過(guò)程的編程方式對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行操作：
        1、使用cursor.execute(query_sql)，執(zhí)行數(shù)據(jù)庫(kù)查詢(xún)；
        2、使用cursor.fetchone()，讀取數(shù)據(jù)集中的一條數(shù)據(jù)；
        3、使用cursor.fetchall()，取出數(shù)據(jù)集中的所有行，返回一個(gè)元組 tuples 類(lèi)型；
        4、使用cursor.fetchmany(n)，取出數(shù)據(jù)集中的多條數(shù)據(jù)，同樣返回一個(gè)元組 tuples；
        5、使用cursor.rowcount，返回查詢(xún)結(jié)果集中的行數(shù)。如果沒(méi)有查詢(xún)到數(shù)據(jù)或者還沒(méi)有查詢(xún)，則結(jié)果為 -1，否則會(huì)返回查詢(xún)得到的數(shù)據(jù)行數(shù)；
        6、使用cursor.close()，關(guān)閉游標(biāo)。 

2、# 查詢(xún)年齡大于20的
sql = 'SELECT * FROM student WHERE Sage>=20'
cursor.execute(sql)
data = cursor.fetchall()
for each_player in data:
  print(each_player)

完整代碼如下：
(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_select.py 
('200215121', '李勇', '女', 20, 'CS')
('200215122', '馬吉輝', '男', 27, 'CS')
('200215123', '王金燦', '男', 30, 'CS')
(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql_select.py                             
# -*- coding: UTF-8 -*-
import mysql.connector
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)

# 獲取操作游標(biāo)
cursor = db.cursor()
# 查詢(xún)年齡大于20的
sql = 'SELECT * FROM student WHERE Sage>=20'
cursor.execute(sql)
data = cursor.fetchall()
for each_student in data:
  print(each_student)
# 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
cursor.close()
db.close()
(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_select.py                          
('200215121', '李勇', '女', 20, 'CS')
('200215122', '馬吉輝', '男', 27, 'CS')
('200215123', '王金燦', '男', 30, 'CS')

----
3、如何修改數(shù)據(jù)呢？
# 修改馬吉輝的CS 修改成SS 
sql = 'UPDATE student SET Sdept = %s WHERE Sname = %s'
val = ("SS", "馬吉輝")
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "記錄被修改。")

完整代碼如下：
(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql_update.py                             
# -*- coding: UTF-8 -*-
import mysql.connector
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)

# 獲取操作游標(biāo)
cursor = db.cursor()
sql = 'UPDATE student SET Sdept = %s WHERE Sname = %s'
val = ("SS", "馬吉輝")
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "記錄被修改。")
# 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
cursor.close()
db.close()
(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_update.py    
1 記錄被修改。

mysql> select * from student where Sname = "馬吉輝";
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215122 | 馬吉輝    | 男   |   27 | SS    |
+-----------+-----------+------+------+-------+
1 row in set (0.00 sec)

---------
4、最后我們看下如何刪除王金燦這個(gè)同學(xué)的數(shù)據(jù)：
# 刪除王金燦這個(gè)同學(xué)的數(shù)據(jù)
sql = 'DELETE FROM student WHERE Sname = %s'
val = ("王金燦",)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "記錄刪除成功。")

完整代碼如下：
(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql_delete.py                             
# -*- coding: UTF-8 -*-
import mysql.connector
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)

# 獲取操作游標(biāo)
cursor = db.cursor()
# 刪除王金燦這個(gè)同學(xué)的數(shù)據(jù)
sql = 'DELETE FROM student WHERE Sname = %s'
val = ("王金燦",)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, "記錄刪除成功。")
# 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
cursor.close()
db.close()

(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_delete.py    
1 記錄刪除成功。

mysql> select * from student ;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 100215122 | 劉晨      | 女   |   19 | CS    |
| 100215123 | 王敏      | 女   |   18 | MA    |
| 200215121 | 李勇      | 女   |   20 | CS    |
| 200215122 | 馬吉輝    | 男   |   27 | SS    |
+-----------+-----------+------+------+-------+
4 rows in set (0.00 sec)

第四：
    針對(duì)上面的操作過(guò)程，你可以模擬下數(shù)據(jù)的 CRUD 操作，但有幾點(diǎn)你需要注意。
    1. 打開(kāi)數(shù)據(jù)庫(kù)連接以后，如果不再使用，則需要關(guān)閉數(shù)據(jù)庫(kù)連接，以免造成資源浪費(fèi)。
    2. 在對(duì)數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，可能會(huì)出現(xiàn)異常，這時(shí)就需要用try...except捕獲異常信息。
    3、比如針對(duì)插入同學(xué)王金燦這個(gè)操作，你可以寫(xiě)成下面這樣：

第五：  
    加入python異常完整代碼如下：

(base) [sqoop@flink-slave5 majihui_test]$ cat python_connect_mysql_insert_traceback.py                         
# -*- coding: UTF-8 -*-
import mysql.connector
import traceback
# 打開(kāi)數(shù)據(jù)庫(kù)連接
db = mysql.connector.connect(
       host="10.9.36.253",
       user="rpt",
       passwd="Rpt1234!", # 寫(xiě)上你的數(shù)據(jù)庫(kù)密碼
       database='rpt',
       auth_plugin='mysql_native_password'
)

# 獲取操作游標(biāo)
cursor = db.cursor()
# 插入學(xué)生代碼
try:
    sql = "INSERT INTO student (Sno, Sname, Ssex,Sage,Sdept) VALUES (%s, %s, %s, %s, %s)"
    val = (200215123, "王金燦", "男", 30, "CS")
    # 執(zhí)行SQL語(yǔ)句
    cursor.execute(sql, val)
    # 提交sql語(yǔ)句對(duì)事務(wù)進(jìn)行提交
    db.commit()
    # 打印結(jié)果 
    # 使用cursor.rowcount，返回查詢(xún)結(jié)果集中的行數(shù)。如果沒(méi)有查詢(xún)到數(shù)據(jù)或者還沒(méi)有查詢(xún)，則結(jié)果為 -1，否則會(huì)返回查詢(xún)得到的數(shù)據(jù)行數(shù)；
    print(cursor.rowcount, "記錄插入成功。")
except Exception as e:
    # 打印異常信息
    traceback.print_exc()
    # 回滾  
    db.rollback()
finally:
    # 關(guān)閉游標(biāo)&數(shù)據(jù)庫(kù)連接
    cursor.close()
    db.close()

(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_insert_traceback.py
1 記錄插入成功。
mysql> select * from student ;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 100215122 | 劉晨      | 女   |   19 | CS    |
| 100215123 | 王敏      | 女   |   18 | MA    |
| 200215121 | 李勇      | 女   |   20 | CS    |
| 200215122 | 馬吉輝    | 男   |   27 | SS    |
| 200215123 | 王金燦    | 男   |   30 | CS    |
+-----------+-----------+------+------+-------+
5 rows in set (0.00 sec)

再執(zhí)行一次，就會(huì)報(bào)錯(cuò)：
(base) [sqoop@flink-slave5 majihui_test]$ /data/python_test/bin/python python_connect_mysql_insert_traceback.py
Traceback (most recent call last):
  File "python_connect_mysql_insert_traceback.py", line 20, in <module>
    cursor.execute(sql, val)
  File "/data/python_test/lib/python3.7/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/data/python_test/lib/python3.7/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/data/python_test/lib/python3.7/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '200215123' for key 'PRIMARY'

第八：
    我們?cè)趐ython代碼中寫(xiě)了 mysql的賬號(hào)和密碼如何 # 建議吧數(shù)據(jù)庫(kù)鏈接信息寫(xiě)到配置文件里，防止密碼泄露。

import json
import traceback
import mysql.connector

# 讀取數(shù)據(jù)庫(kù)鏈接配置文件
with open('mysql.json', encoding='utf-8') as con_json:
    con_dict = json.load(con_json)

# 打開(kāi)數(shù)據(jù)庫(kù)鏈接
db = mysql.connector.connect(
    host=con_dict['host'],
    user=con_dict['user'],
    passwd=con_dict['passwd'],
    database=con_dict['database'],
    auth_plugin=con_dict['auth_plugin'],
)

# 獲取操作游標(biāo)
cursor = db.cursor()
try:
    sql = 'SELECT id, name, hp_max FROM heros WHERE hp_max>6000'
    cursor.execute(sql)
    data = cursor.fetchall()
    print(cursor.rowcount, '查詢(xún)成功。')
    for each_hero in data:
        print(each_hero)
except Exception as e:
    # 打印異常信息
    traceback.print_exc()
finally:
    cursor.close()
    db.close()
# 建議吧數(shù)據(jù)庫(kù)鏈接信息寫(xiě)到配置文件里，防止密碼泄露。