使用Python操作MySQL

weixin_33890499發表於2018-10-28
PythonMySql

前言: 最近做內部運維資料的資料倉儲，最終將Hive中的資料清洗後需要業務決策相關的資料進行結構化處理，並儲存到關係型資料庫MySQL中，以供後期對外介面使用。本篇簡單記錄下使用Python操作MySQL資料庫的簡單操作。

MySQL資料庫環境準備

注意:在當前容器化基礎設施已經全面覆蓋的時代，為了快速驗證效果，我們及其推薦使用以Docker為代表的容器化基礎設施來快速構建你的基礎環境。

在DockerHub上有豐富的基礎中介軟體的映象，我們可以使用Docker快速的構建我們的MySQL基礎環境，而不必每次重新安裝各種複雜的中介軟體環境，因為我們只是使用者，我相信每個團隊都會有專門的中介軟體維護者。好吧，如果沒有，那你依然可以自己根據實際的需求和標準進行構建Docker映象，這樣就為我們創造了一個未來很長一段時間可複用的元件。總之，想說的一件事就是，下面的MySQL環境是用Docker容器跑的。

# 確保docker環境正常
$ docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

# 下載MySQL指定版本的映象
$ docker pull mysql:5.6
$ docker images | grep mysql
mysql                                     5.6                 d1f491b20727        2 days ago          256 MB

# 建立一個mysql例項[需要指定至少一個環境變數:MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD]
$ docker run -itd --name mysql -e MYSQL_ROOT_PASSWORD="123456"  -P mysql:5.6
6c4428b341516c7eeec48cbc5b658a464f76b5f7d42b3e689151392f5cd8ac56

# MySQL密碼為123456,埠為32773
$ docker ps
CONTAINER ID        IMAGE                                          COMMAND                  CREATED             STATUS              PORTS                                               NAMES
6c4428b34151        mysql:5.6                                      "docker-entrypoint.sh"   5 seconds ago       Up 3 seconds        0.0.0.0:32773->3306/tcp                             mysql

# mysql資料庫登入測試
$ mysql -h 127.0.0.1 -uroot -P 32773 -p123456
...
Server version: 5.6.42 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

# 檢視資料庫字元編碼格式
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

至此，一個MySQL資料庫已經準備好，需要我們注意的是，因為使用的是官方的Docker image，我們需要進行相關配置的檢查和設定，否則可能會為後期的操作造成一定麻煩，比如設定資料庫的字元編碼.
讓MySQL支援中文，一般而言需要關注以下幾個點:

    1. 修改MySQL配置中客戶端和服務端的字元編碼為utf8，分別為[mysqld的default-character-set和character-set-server引數以及client的default-character-set引數]
    1. 建立表時指定表的字元編碼(default charset=utf8;)
    1. 連結資料庫的時候指定連結字元編碼(charset=utf8)
    1. 使用Python運算元據庫時需要對Python檔案進行utf8支援(#encoding=utf-8和sys.setdefaultencoding(utf-8))
    1. 使用show variables like '%char%';命令檢查mysql字符集是否為utf8格式，並使用SET NAMES UTF8; 或者set character_set_server = utf8;進行設定

使用Python進行操作MySQL

首先，在使用之前我們需要對Python版的MySQL庫有一個瞭解，當前主流的庫有MySQLdb,PyMySQL和SQLAlchemy.

  • MySQLdb:一般是Linux系統發行版中預設支援的，通常包名為Python-MySQL，核心由C語言打造，介面精煉，效能最棒，缺點是環境依賴較多，安裝複雜，近兩年已停止更新，只支援Python2，不支援Python3
  • PyMySQL:純python打造，介面與Python-MySQL相容，安裝方便，支援Python3
  • SQLAlchemy: 一個ORM框架，它並不提供底層的資料庫操作，而是要藉助於MySQLdb、PyMySQL等第三方庫來完成，目前SQLAlchemy在Web程式設計領域應用廣泛
    備註:其實還有類似mysqlclient之類的庫，主要整合在一些web框架中作為依賴

由於為了快速實現業務邏輯，在接下來的操作中主要使用PyMySQL庫進行運算元據庫，雖然效能不及MySQLdb，但是可以使用pymysql.install_as_MySQLdb()來相容MySQLdb，在業務正式上線時可以不改變業務程式碼邏輯而平滑的使用MySQLdb庫。

安裝pymysql庫

在Linux環境下，大多數系統工具使用Python語言進行編寫，因此在安裝額外的Python模組時，通常會有幾種選擇:

    1. 使用系統自帶工具安裝apt-get install or yum install，該種方式會將模組預設安裝的系統環境，可能會影響系統環境
    1. 使用Python原聲的包管理工具pip install ，該種方式會預設安裝到pip命令所在的Python解釋環境下，因此取決於Python環境是否獨立於系統環境的Python，通常情況下會使用pyenv之類的工具進行環境隔離
    1. 使用包管理工具conda相關工具進行管理python，可以有效管理python多環境依賴，並且可以很方便構建資料科學相關環境.conda使用指南 
# 安裝pymysql庫
$ pip install pymysql
or
$ conda install pymysql

python連結MySQL以及基本使用

使用pymysql庫操作mysql

$ cat pytest_mysql.py
import pymysql
# 獲取一個mysql連結物件
conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset='utf8')
# 使用資料庫連結的cursor()方法獲取一個遊標物件
cursor = conn.cursor()
# 使用遊標物件的execute()方法進行執行sql語句
cursor.execute("SELECT VERSION()")
## execute方法的定義如下,其中args可以是tuple, list or dict,如果是list or tuple的話，%s會被當做查詢的一個佔位符;如果是dict的話%(name)s會被當做一個佔位符
## execute(self, query, args=None)


# 使用遊標物件的fetch類方法獲取資料
## fetchone返回一條資料,fetchall返回查詢的所有資料。fetch類方法會返回一個list型別的tuple結構型別物件.[(),()...]
onedata = cursor.fetchone()
alldata = cursor.fetchall()

# 提交資料庫操作[一般在更新資料庫操作時需要注意執行]
conn.commit()

# 及時關閉資料庫連結以及開啟的遊標[以防止在併發情況下系統開啟連線數過多]
cursor.close()
conn.close()

嘗試用python指令碼進行資料庫操作

$ cat test_show_table.py
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import pymysql

class mysqlObj:
    def __init__(self,host,dbname,user,passwd,port=3306):
        self.host = host
        self.dbname = dbname
        self.user = user
        self.passwd = passwd
        self.port = port
    def mysqlConIns(self):
        conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.dbname, charset='utf8')
        return conn
    def querydata(self,sql,args=None):
        conn = self.mysqlConIns()
        cur = conn.cursor()
        cur.execute(sql,args)
        alldata = cur.fetchall()
        cur.close()
        for data in alldata:
            print(data)
        conn.close()


if __name__ == '__main__':
    mysqlobj = mysqlObj('localhost','mysql','root','123456',32773)
    mysqlobj.querydata("show tables;")

# 對mysql庫進行檢視tables操作，返回的是一個tuple
$ python test_show_table.py
(u'columns_priv',)
(u'db',)
(u'event',)
(u'func',)
(u'general_log',)
(u'help_category',)
(u'help_keyword',)
(u'help_relation',)
(u'help_topic',)
(u'innodb_index_stats',)
(u'innodb_table_stats',)
(u'ndb_binlog_index',)
(u'plugin',)
(u'proc',)
(u'procs_priv',)
(u'proxies_priv',)
(u'servers',)
(u'slave_master_info',)
(u'slave_relay_log_info',)
(u'slave_worker_info',)
(u'slow_log',)
(u'tables_priv',)
(u'time_zone',)
(u'time_zone_leap_second',)
(u'time_zone_name',)
(u'time_zone_transition',)
(u'time_zone_transition_type',)
(u'user',)

MySQL資料庫常用的一些操作

注意:在之前我們建立的MySQL例項中僅是一個空的資料庫，在實際使用之前，我們需要進行資料庫的庫表結構建立，以及相關的資料庫授權，而這一部分操作通常會由專業的資料庫管理員(DBA)進行操作和處理

接下來對一個website資料庫和use表進行操作:

mysql> create database website;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE  IF NOT EXISTS useinfo (userid int(10) primary key not null auto_increment,username varchar(20) not null,usersite varchar(50),other varchar(50)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> describe useinfo;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| userid   | int(10)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| usersite | varchar(50) | YES  |     | NULL    |                |
| other    | varchar(50) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

# 資料庫授權[授權所有的主機可以以root使用者,123456的密碼去操作website庫]
mysql> grant all on website.* to root@'%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)

對指定資料庫進行相關查詢操作

# 依然是上面那個test_show_table.py指令碼,不過我們改變一下`__main__`
$ cat test_show_table.py
.....
if __name__ == '__main__':
    mysqlobj = mysqlObj('localhost','website','root','123456',32773)
    args = ["show tables;","describe useinfo;"]
    for arg in args:
        mysqlobj.querydata(arg)

# 可以看到我們的website庫下有useinfo一張表，並且該表包含userid,username,usersite,other4個欄位
$ python test_show_table.py
(u'useinfo',)
(u'userid', u'int(10)', u'NO', u'PRI', None, u'auto_increment')
(u'username', u'varchar(20)', u'NO', u'', None, u'')
(u'usersite', u'varchar(20)', u'YES', u'', None, u'')
(u'other', u'varchar(20)', u'YES', u'', None, u'')

對MySQL資料庫進行插入操作

注意:插入操作分為單條記錄插入和批量插入,一般資料庫都支援批量插入方法,在pysql中為cursor.executemany(sql,args)
為我們的mysqlObj類增加一個插入操作:

$ cat test_show_table.py
....
....
    #注意插入資料時單條記錄使用tuple()型別;批量插入資料時使用list()型別
    def changedata(self,sql,args=None):
        conn = self.mysqlConIns()
        cur = conn.cursor()
        try:
            #做一個粗暴的判斷當args是list時就進行批量插入
            if isinstance(args,list):
                #executemany(sql,args)方法args支援tuple或者list型別
                cur.executemany(sql,args)
            else:
                #execute(sql,args)方法args支援string,tuple,list,dict
                cur.execute(sql,args)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(e)
        finally:
            cur.close()
            conn.close()
....
...
if __name__ == '__main__':
    mysqlobj = mysqlObj('localhost','website','root','123456',32773)
    '''
    args = ["show tables;","describe useinfo;"]
    for arg in args:
        mysqlobj.querydata(arg)
    '''
    #插入一條資料
    sql = "insert into  useinfo (username) values(%s)"
    arg = "彪哥"
    mysqlobj.changedata(sql,arg)
    sql1 = "insert into  useinfo (username,usersite) values(%s,%s)"
    arg1 = ("xxbandy","http://xxbandy.github.io")
    mysqlobj.changedata(sql1,arg1)
    #批量插入資料

    argslist = [("彪哥","http://xxbandy.github.io"),("bgbiao","https://www.jianshu.com/u/9c46ece5b7bd")]
    mysqlobj.changedata(sql1,argslist)
    #查詢資料
    mysqlobj.querydata("select * from useinfo")

        print("updating the data")
        #更新資料[需要注意的是指定了欄位之後由於usersite是varchar型別，佔位符必須是"%s",如果是'%s'會有問題]
    data = "https://my.oschina.net/xxbAndy"
    mysqlobj.changedata('update useinfo set usersite=%s where userid = 1',data)
    mysqlobj.querydata("select * from useinfo")

# 插入資料並檢視資料
$ python /tmp/abc.py
(1, u'\u5f6a\u54e5', None, None)
(2, u'xxbandy', u'http://xxbandy.github.io', None)
(3, u'\u5f6a\u54e5', u'http://xxbandy.github.io', None)
(4, u'bgbiao', u'https://www.jianshu.com/u/9c46ece5b7bd', None)
updating the data
(1, u'\u5f6a\u54e5', u'https://my.oschina.net/xxbAndy', None)
(2, u'xxbandy', u'http://xxbandy.github.io', None)
(3, u'\u5f6a\u54e5', u'http://xxbandy.github.io', None)
(4, u'bgbiao', u'https://www.jianshu.com/u/9c46ece5b7bd', None)

# 資料庫查詢記錄
mysql> select * from website.useinfo;
+--------+----------+----------------------------------------+-------+
| userid | username | usersite                               | other |
+--------+----------+----------------------------------------+-------+
|      1 | 彪哥   | https://my.oschina.net/xxbAndy         | NULL  |
|      2 | xxbandy  | http://xxbandy.github.io               | NULL  |
|      3 | 彪哥   | http://xxbandy.github.io               | NULL  |
|      4 | bgbiao   | https://www.jianshu.com/u/9c46ece5b7bd | NULL  |
+--------+----------+----------------------------------------+-------+

原始碼

import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import pymysql

class mysqlObj:
    def __init__(self,host,dbname,user,passwd,port=3306):
        self.host = host
        self.dbname = dbname
        self.user = user
        self.passwd = passwd
        self.port = port
    def mysqlConIns(self):
        conn = pymysql.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.dbname, charset='utf8')
        return conn
    def querydata(self,sql,args=None):
        conn = self.mysqlConIns()
        cur = conn.cursor()
        cur.execute(sql,args)
        alldata = cur.fetchall()
        cur.close()
        for data in alldata:
            print(data)
        conn.close()
    #注意插入資料時單條記錄使用tuple()型別;批量插入資料時使用list()型別
    def changedata(self,sql,args=None):
        conn = self.mysqlConIns()
        cur = conn.cursor()
        try:
            #做一個粗暴的判斷當args是list時就進行批量插入
            if isinstance(args,list):
                #executemany(sql,args)方法args支援tuple或者list型別
                cur.executemany(sql,args)
            else:
                #execute(sql,args)方法args支援string,tuple,list,dict
                cur.execute(sql,args)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print(e)
        finally:
            cur.close()
            conn.close()



if __name__ == '__main__':

    mysqlobj = mysqlObj('localhost','website','root','123456',32773)
    '''
    args = ["show tables;","describe useinfo;"]
    for arg in args:
        mysqlobj.querydata(arg)
    '''
    #插入一條資料
    sql = "insert into  useinfo (username) values(%s)"
    arg = "彪哥"
    mysqlobj.changedata(sql,arg)
    sql1 = "insert into  useinfo (username,usersite) values(%s,%s)"
    arg1 = ("xxbandy","http://xxbandy.github.io")
    mysqlobj.changedata(sql1,arg1)
    #批量插入資料

    argslist = [("彪哥","http://xxbandy.github.io"),("bgbiao","https://www.jianshu.com/u/9c46ece5b7bd")]
    mysqlobj.changedata(sql1,argslist)


    #查詢資料
    mysqlobj.querydata("select * from useinfo")
    print("updating the data")
    #更新資料
    data = "https://my.oschina.net/xxbAndy"
    mysqlobj.changedata('''update useinfo set usersite=%s where userid = 1''',data)
    mysqlobj.querydata("select * from useinfo")

相關文章