Python連接MySQL并使用fetchall()方法過(guò)濾特殊字符

更新時(shí)間：2016年03月13日 15:17:29 作者：Jacman

這篇文章主要介紹了Python連接MySQL的方法并講解了如何使用fetchall()方法過(guò)濾特殊字符,示例環(huán)境為Ubuntu操作系統(tǒng),需要的朋友可以參考下

來(lái)一個(gè)簡(jiǎn)單的例子，看Python如何操作數(shù)據(jù)庫(kù)，相比Java的JDBC來(lái)說(shuō)，確實(shí)非常簡(jiǎn)單，省去了很多復(fù)雜的重復(fù)工作，只關(guān)心數(shù)據(jù)的獲取與操作。
準(zhǔn)備工作
需要有相應(yīng)的環(huán)境和模塊：

Ubuntu 14.04 64bit
Python 2.7.6
MySQLdb

注意:Ubuntu 自帶安裝了Python，但是要使用Python連接數(shù)據(jù)庫(kù)，還需要安裝MySQLdb模塊，安裝方法也很簡(jiǎn)單：

sudo apt-get install MySQLdb

然后進(jìn)入Python環(huán)境，import這個(gè)包，如果沒(méi)有報(bào)錯(cuò)，則安裝成功了：

python
Python 2.7.6 (default, Jun 22 2015, 17:58:13) 
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>

Python標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)接口的Python DB-API（包括Python操作MySQL）。大多數(shù)Python數(shù)據(jù)庫(kù)接口堅(jiān)持這個(gè)標(biāo)準(zhǔn)。不同的數(shù)據(jù)庫(kù)也就需要不同額模塊，由于我本機(jī)裝的是MySQL，所以使用了MySQLdb模塊，對(duì)不同的數(shù)據(jù)庫(kù)而言，只需要更改底層實(shí)現(xiàn)了接口的模塊，代碼不需要改，這就是模塊的作用。
Python數(shù)據(jù)庫(kù)操作
首先我們需要一個(gè)測(cè)試表
建表語(yǔ)句：

CREATE DATABASE study;
use study;
DROP TABLE IF EXISTS python_demo;
CREATE TABLE python_demo (
 id int NOT NULL AUTO_INCREMENT COMMENT '主鍵，自增',
 user_no int NOT NULL COMMENT '用戶編號(hào)',
 user_name VARBINARY(50) NOT NULL COMMENT '用戶名',
 password VARBINARY(50) NOT NULL COMMENT '用戶密碼',
 remark VARBINARY(255) NOT NULL COMMENT '用戶備注',
 PRIMARY KEY (id,user_no)
)ENGINE =innodb DEFAULT CHARSET = utf8 COMMENT '用戶測(cè)試表';

INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1001,'張三01','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1002,'張三02','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1003,'張三03','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1004,'張三04','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1005,'張三05','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1006,'張三06','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1007,'張三07','admin','我是張三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
 (1008,'張三08','admin','我是張三');

Python代碼

# --coding=utf8--
import ConfigParser

import sys
import MySQLdb

def init_db():
  try:
    conn = MySQLdb.connect(host=conf.get('Database', 'host'),
                user=conf.get('Database', 'user'),
                passwd=conf.get('Database', 'passwd'),
                db=conf.get('Database', 'db'),
                charset='utf8')
    return conn
  except:
    print "Error:數(shù)據(jù)庫(kù)連接錯(cuò)誤"
    return None

def select_demo(conn, sql):
  try:
    cursor = conn.cursor()
    cursor.execute(sql)
    return cursor.fetchall()
  except:
    print "Error:數(shù)據(jù)庫(kù)連接錯(cuò)誤"
    return None

def update_demo():
  pass

def delete_demo():
  pass

def insert_demo():
  pass

if __name__ == '__main__':
  conf = ConfigParser.ConfigParser()
  conf.read('mysql.conf')
  conn = init_db()
  sql = "select * from %s" % conf.get('Database', 'table')
  data = select_demo(conn, sql)
  pass

fetchall()字段特殊字符過(guò)濾處理
最近在做數(shù)據(jù)倉(cāng)庫(kù)的遷移工作,之前數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)都是用的shell腳本來(lái)抽取,后來(lái)?yè)Q了python腳本.
但是在把數(shù)據(jù)抽取存放到hadoop時(shí),出現(xiàn)了一個(gè)問(wèn)題:
由于數(shù)據(jù)庫(kù)字段很多,提前也不知道數(shù)據(jù)庫(kù)字段會(huì)存儲(chǔ)什么內(nèi)容,hive建表是以\t\n做分隔,這就導(dǎo)致了一個(gè)問(wèn)題,如果mysql字段內(nèi)容里面本身含有\(zhòng)t\n,那么就會(huì)出現(xiàn)字段錯(cuò)位情況,并且很頭疼的是mysql有100多個(gè)字段,也不知道哪個(gè)字段會(huì)出現(xiàn)這個(gè)問(wèn)題.
shell腳本里的做法是在需要抽取的字段上用mysql的replace函數(shù)對(duì)字段進(jìn)行替換,例如,假設(shè)mysql里的字段是column1 varchar(2000),那么很可能就會(huì)出現(xiàn)有特殊字符的情況,在查詢的sql語(yǔ)句里加上

select replace(replace(replace(column1,'\r',''),'\n',''),'\t','')

之前一直是這么干的,但是這樣寫(xiě)sql特別長(zhǎng),特別是有100多個(gè)字段,也不知道哪個(gè)有特殊字符,只要都加上.
所以在python中對(duì)字段不加處理,最終導(dǎo)致hive表字段對(duì)應(yīng)出現(xiàn)偏差,所以在python里從mysql查詢到的字段在寫(xiě)到文件之前需要對(duì)每個(gè)字段進(jìn)行過(guò)濾處理
看個(gè)例子,我就以mysql測(cè)試為例,首先建一張測(cè)試表

CREATE TABLE `filter_fields` (
 `field1` varchar(50) DEFAULT NULL,
 `field2` varchar(50) DEFAULT NULL,
 `field3` varchar(50) DEFAULT NULL,
 `field4` varchar(50) DEFAULT NULL,
 `field5` varchar(50) DEFAULT NULL,
 `field6` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有六個(gè)字段,都是varchar類型,插入新數(shù)據(jù)可以在里面插入特殊字符.簡(jiǎn)單插入條數(shù)據(jù)測(cè)試看看:

insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test01','test02','test03','test04','test05','test06');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test11\ntest11','test12\n\n','test13','test14','test15','test16');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test21\ttest21','test22\ttest22\ttest22','test23\t\t\t','test4','test5','test6');
insert into filter_fields(field1,field2,field3,field4,field5,field6) VALUES
('test21\rest21','test22\r\rest22\r\rest22','test23\r\r\r','test4','test5','test6');

其中數(shù)據(jù)里插入的特殊字符,可能連在一起,也有不連在一起的.
python測(cè)試代碼:

# coding=utf-8

import MySQLdb
import sys

db_host = '127.0.0.1'  # 數(shù)據(jù)庫(kù)地址
db_port = 3306     # 數(shù)據(jù)庫(kù)端口
db_user = 'root'    # mysql用戶名
db_pwd = 'yourpassword' # mysql用戶密碼,換成你的密碼
db_name = 'test'    # 數(shù)據(jù)庫(kù)名
db_table = 'filter_fields' # 數(shù)據(jù)庫(kù)表

# 過(guò)濾sql字段結(jié)果中的\t\n
def extract_data(table_name):
  try:
    conn = MySQLdb.connect(host=db_host, port = db_port, user=db_user,
                passwd = db_pwd, db = db_name, charset = "utf8")
    cursor = conn.cursor()
  except MySQLdb.Error, e:
    print '數(shù)據(jù)庫(kù)連接異常'
    sys.exit(1)

  try:
    sql = 'select * from %s;'%(table_name)
    cursor.execute(sql)
    rows = cursor.fetchall()

    print '====字段未過(guò)濾查詢結(jié)果===='
    for row in rows:
      print row

    print '====字段過(guò)濾之后結(jié)果===='
    rows_list = []
    for row in rows:
      row_list = []
      for column in row:
        row_list.append(column.replace('\t', '').replace('\n', '').replace('\r', ''))
      rows_list.append(row_list)
      print rows_list[-1] # [-1]表示列表最后一個(gè)元素
    return rows_list
  except MySQLdb.Error, e:
    print '執(zhí)行sql語(yǔ)句失敗'
    cursor.close()
    conn.close()
    sys.exit(1)

if __name__ == '__main__':
  print 'begin:'
  rows = extract_data(db_table)
  pass

看看輸出結(jié)果:

字段未過(guò)濾查詢結(jié)果

(u'test01', u'test02', u'test03', u'test04', u'test05', u'test06')
(u'test11\ntest11', u'test12\n\n', u'test13', u'test14', u'test15', u'test16')
(u'test21\ttest21', u'test22\ttest22\ttest22', u'test23\t\t\t', u'test4', u'test5', u'test6')
(u'test21\rest21', u'test22\r\rest22\r\rest22', u'test23\r\r\r', u'test4', u'test5', u'test6')

字段過(guò)濾之后結(jié)果

[u'test01', u'test02', u'test03', u'test04', u'test05', u'test06']
[u'test11test11', u'test12', u'test13', u'test14', u'test15', u'test16']
[u'test21test21', u'test22test22test22', u'test23', u'test4', u'test5', u'test6']
[u'test21est21', u'test22est22est22', u'test23', u'test4', u'test5', u'test6']

可以看到,制表符,換行符,回車都被過(guò)濾了.
建議:最后說(shuō)點(diǎn)題外話,不要小視\r,回車符.很多人以為回車符就是換行符,其實(shí)不是的,\r表示回車符,\n表示新行.之前代碼里其實(shí)是過(guò)濾掉了\t\n的,但是抽取的數(shù)據(jù)還是不對(duì),后來(lái)看了源碼之后才發(fā)現(xiàn),原來(lái)是沒(méi)有過(guò)濾\r,就這個(gè)不同導(dǎo)致了很多數(shù)據(jù)抽取不對(duì).

您可能感興趣的文章: